Monday, July 11, 2011

Lock Waits ... Who is locking Who and What ?

Database row level locking mechanism is critical to achieve data consistency. The way Oracle deals with locks is complex and very rich. However, it's the application that accesses the database that really takes the decision of locking records or not depending on the its intention to modify data or not.

In Client-Server architectures, the locking strategy has most of the time been to use the "pessimistic" mode, which behavior can be described as: "as soon as I have the intention to modify, let me acquire a lock and reserve the record exclusively for my updates preventing any other users to interfere".

This mode sometimes is subject to perverse effects of egoist applications : Locking data but taking time to modify or never modifying the record preventing and making the other users to wait until the egoist application decide to release the record or fail.

With the advent of 3-tier architectures, new data representation is implemented at the midtier level, most of the time via Object Relational Mapping technics (examples are EJBs, ADF BCs) with the aim to map the database tables and views as the middle-tier level.
In this cases the pessimistic perverse effects can be amplified and that's why usually an optimistic mode is preferred.

In ADF you can choose to use one or the other at the database connection definition level on the Application Module depending on the type of application and your locking strategy.

In any case, locks issues always ends at the DBA's desk.

One of the issues is when a user tries to acquire a lock on an already locked record at the database level, the application can choose to either wait or fail.
In case the application waits for the lock without or with a long timed-out setting, the user will feel that the application has hanged . If the user kills the application and restart a session, same will happen and he will call IT department who will have to find out who is sleeping with pessimist locks on records and wake him up to release the lock.

Here is two simple queries to achieve that :

1/. Check who is locking who :

select s1.username || '@' || s1.machine
   || ' ( SID=' || s1.sid || ' )  is blocking '
   || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) '
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid   
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
 and l2.id2 = l2.id2;

2/. Check what table is locked to confirm your suspicion on the sleeping user :

select object_name,oracle_username from sys.dba_objects a,sys.v_$locked_object b
where a.object_id=b.object_id and oracle_username='The suspected user name'

Of course you must have the corresponding privileges to select from the v$xx, v_$xx & dba_xx database objects to do this.

Good lock hunt !

1 comment: