Oralce: How to ensure Oracle function is executed serially

user_5389537 0
Viewed 75

I'm using Oracle database version - 12.1.0.2.0

I've an Oracle function that I need to ensure is executed serially. I want to restrict concurrent execution of this function by different sessions.

Couple of ways this can be achieved is:

  1. Update a "static" row in a parameter table at beginning of function and commit before function ends. Since no other session will be able to update same row, it will ensure concurrent access is restricted.
  2. Implement logic using user locks.

Is there any other way this control can be achieved. I've read about "latch", but I believe it is used for internal mechanism to control access to Oracle data structures (mainly resource in SGA). Is there a way to implement latch (or something similar) to fulfill my requirement.

I understand latch is lightweight as against locks which are heavier in comparison.

Thanks in advance.

oracle12c concurrency oracle
1 Answers

Oracle implementes DBMS_LOCK.ALLOCATE_UNIQUE for this purpose.

At the beginning of the procedure allocate a unique lockhandle for a given lockname. Then REQUEST the lock

 /* lock parallel executions */
DBMS_LOCK.ALLOCATE_UNIQUE( v_lockname, v_lockhandle);
v_res := DBMS_LOCK.REQUEST( lockhandle=>v_lockhandle, release_on_commit => TRUE);

Perform your serial stuff and at the end of the function RELEASE the lock.

v_res :=  DBMS_LOCK.RELEASE (v_lockhandle);

Do not forget to release the lock in the EXCEPTION section to be not blocked after abnormal termination

user_48081220
Related