Using the Oracle DBMS_LOCK Package

As a standard DBMS practice Oracle provides all sort of row level and table level locking mechanism for data integrity. This built-in locking mechanism is strongly coupled with database transactions e.g. a lock acquired through the LOCK command is released automatically at the end of the transaction. Sometimes you need explicit locking with full control and independent of the transactions. This feature is provided by DBMS_LOCK.

The other day I was stuck in a legacy system where I had to provide a locking mechanism on call to a stored procedure at the database level. The procedure is called from a web farm consisting of a number of web servers and I wanted to make sure that only one web servers accesses the procedure at any given time. Although I hated such a procedure in my database but that looked like the only solution to the problem without changing some fundamentals. The DBMS_LOCK package came for my rescue and here is what I learnt from it.

This package is available to the SYS user only (may be because of its significance and impact). So if you try to access it from another user you will get an error “IDENTIFIER MUST BE DECLARED”. The solution is to write  functions on top of the DBMS_LOCK package and grant access to those functions. The DBA can thus regulate which operations are allowed to the user and which are forbidden.

CREATE OR REPLACE PROCEDURE xmode_lock_procedure (lockhandle varchar2, v_result OUT number) IS
v_lock varchar2(200);
dbms_lock.allocate_unique(lockhandle, v_lock);
v_result := dbms_lock.request(v_lock, dbms_lock.x_mode);

Grant access on this procedure to the user:

GRANT ACCESS ON xmode_lock_procedure TO user1;

Granting access to public is never recommended due to sensitivity of this procedure.

Similarly a procedure which releases this lock can be written as:

CREATE OR REPLACE PROCEDURE lock_release (lockhandle varchar2, v_result OUT number) IS
v_lock varchar2(200);
dbms_lock.allocate_unique(lockhandle, v_lock);
v_result := dbms_lock.release (v_lock);

This will release the lock and in the meantime if another users requests a lock he will be blocked until this users releases the same lock. Note that the lock uniqueness is decided by the LOCKHANDLE parameter passed to the procedures.

The result returned by these procedures can be:

1: Timeout occurred
2. Deadlock occurred
3. Parameter Error
4. Already owned
5. Illegal lock handle




Share this post : digg it! Facebook it! live it! reddit! technorati! yahoo!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s