Search My Ramblings

Thursday, September 10, 2009

Using DBMS_LOCK package to implement singleton pattern

One thing that is inevitable as you start replacing batch-oriented integration strategies with more real-time, transactional strategies like BPEL and ESB, especially in a clustered environment, is that you'll run into APIs and database procedures that worked fine in a batch mode, but in a multi-threaded real time integration start breaking.

One option we implemented was to use the Oracle database's own DBMS_LOCK package to request a lock object specific to what we were doing and the data we were doing it with. The package is very straight-forward to use, but conceptually we needed to implement the equivalent of a semaphore in other programming languages in PL/SQL so multiple threads of BPEL processes would not be inserting the same data for the same student at the same time. In our case one example of where this caused issues was determining whether to insert or update a revenue code entry for a given student/event. Since concurrent threads from BPEL were processing the same student but for different events, they were both determining that they needed to insert rather than update, which caused issues in their application. Another situation that caused this was trying to create an invoice for the same customer for different events in concurrent threads. The solution for this case is shown below.

Initially for the revenue code issue we tried resolving by explicitely committing after the insert, but found that the second process was still trying to insert since the first one's session hadn't committed before the second one started and queried whether to insert or update.

So after some research we found Oracle had a package specifically for synchronizing access to PL/SQL code based on logical names. We decided to create locks named based on the activity being performed in that code section concatenated with the ID of the student/customer in the transaction, to avoid unexpectedly locking out other areas of code in the database for the same student/customer unrelated to what we were doing. Below is a snippet of the code implemented for the use case where we call the create invoice API of Oracle E-Business Suite. First, here are the additional declarations we added to support this:

V_LOCKNAME VARCHAR2(100);
V_LOCKHANDLE VARCHAR2(128);
V_LOCKRESULT INTEGER := 1;
V_LOCKCOUNTER INTEGER := 0;
C_LOCKCOUNTERMAX CONSTANT INTEGER := 2;
CANNOTACQUIRELOCKFAULT EXCEPTION;

Next is the code that implements the primary locking logic:

1 --Lock for this student
2 V_LOCKNAME := 'CREATE_SINGLE_INVOICE' || P_TRX_HEADER_TBL_(1).BILL_TO_CUSTOMER_ID;
3 DBMS_LOCK.ALLOCATE_UNIQUE(V_LOCKNAME, V_LOCKHANDLE);

4 WHILE V_LOCKRESULT = 1 AND V_LOCKCOUNTER < lockhandle =""> V_LOCKHANDLE,
timeout => 30 --seconds
);

7 V_LOCKCOUNTER := V_LOCKCOUNTER + 1;
8 END LOOP;

9 IF V_LOCKRESULT != 0 THEN
10 RAISE CANNOTACQUIRELOCKFAULT;
11 END IF;

12 -- Call the API
13 APPS.AR_INVOICE_API_PUB.CREATE_SINGLE_INVOICE( P_API_VERSION, P_INIT_MSG_LIST, P_COMMIT, P_BATCH_SOURCE_REC_, P_TRX_HEADER_TBL_, P_TRX_LINES_TBL_, P_TRX_DIST_TBL_, P_TRX_SALESCREDITS_TBL_, X_CUSTOMER_TRX_ID, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);

14 --Release the lock
15 V_LOCKRESULT := DBMS_LOCK.RELEASE(V_LOCKHANDLE);


I have added line numbers to make explaining the code simpler. On line 2, I create a logical lock name that combines what action is being performed with the unique identifier of the customer. So if another customer ID is also trying to create an invoice at the same time it will not cause contention since they will use different locks. On line 3 we need to create a lock handle based on our lock name. This call will return the same lock handle from concurrent threads if the same lock name is used.

Line 4 starts a While loop that is essentially looping while the lock request was denied due to a timeout(and was initialized with this return value above), and also while we have not exceeded some looping maximum number of attempts. In this situation we are calling this from BPEL which is a synchronous call, so we limit the waiting period for the lock to 30 seconds and the number of retries to 1 for a total of 60 seconds trying to acquire the lock to avoid timeouts from the BPEL process calling it.

On Line 6 we request the lock using the lock handle obtained in line 3, and wait for up to 30 seconds for it to become available. If this call times out because the lock is not available in that time period it will return 1 as the result which will have us retry the second time. If it succeeds(status=0) or fails for other reasons (status>1) we will exit the loop. If after the loop the status is not successful (status = 0), we throw a custom fault where we return that we were not able to acquire the lock and do NOT try to release it since we never had it.

Otherwise we call the API with the lock in hand then release it when finished. What I did not show is the When Others exception handling that also releases the lock if it was acquired, to make sure that even in exception cases we always release the lock when we exit.

A couple of gotchas to watch out for is to make sure when using the request and release functions you must assign the response to a variable, as with other functions, otherwise Oracle will not compile your code. This little fact caused several minutes of research on grants, etc. that were already in place, we just needed to capture the response from the functions even if we're not interested in them, as with the release function above in the code.

No comments: