User defined locking with DBMS_LOCK

User defined locks with DBMS_LOCKS often brings up unexpected results, especially when it occurs within a mix of transactions in an application layer. There are essentially three procedures for creating user defined locks, namely: DBMS_LOCK.REQUEST, DBMS_LOCK.ALLOCATE_UNIQUE and DBMS_LOCK.RELEASE. ALLOCATE_UNIQUE issues an implicit commit. The four anonymous blocks below highlight what is expected for the LOCK STATUS, whenever a REQUEST and RELEASE are performed, versus the actual value. All scripts were tested in an 11.2.0.3 release.

The main source of confusion arises from the use of the RELEASE_ON_COMMIT parameter coupled with the fact that an implicit COMMIT that occurs when calling DBMS_LOCK.ALLOCATE_UNIQUE. Some developers attempt to use PRAGMA AUTONOMOUS_TRANSACTION  to skirt around the issue, which often masks the source of the underlying issue. Four scenarios in the form of scripts highlight the unexpected results that can occur.

Possible values for LOCK STATUS include:

0 - Success

3 - Parameter error

4 - Lock not owned

5 - Illegal lock handle

 

Scenario 1

Use an explicit lock ID as a handle, i.e. not generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to TRUE.

SQL> set serveroutput on
SQL> declare
  2    lock_id             number := 34789;
  3    l_Lock_Status       number;
  4    b_release_on_commit boolean := true;
  5  begin
  6    l_Lock_Status:= dbms_lock.request
  7      (id               =>lock_id
  8      ,lockmode         =>dbms_lock.x_mode
  9      ,release_on_commit=>b_release_on_commit);
 10
 11    dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
 12
 13    l_Lock_Status:= dbms_lock.request
 14      (id               =>lock_id
 15      ,lockmode         =>dbms_lock.x_mode
 16      ,release_on_commit=>b_release_on_commit );
 17
 18    dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
 19
 20    l_Lock_Status:= dbms_lock.release(id => lock_id);
 21    dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
 22
 23    l_Lock_Status:= dbms_lock.release(id => lock_id );
 24    dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
 25
 26  end;
 27  /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 0
Expect 4. Actual: 4

PL/SQL procedure successfully completed.

Exactly as expected, all values match, so far so good :-)

 

Scenario 2

Use a lock handle, generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to TRUE. In addition, ensure an explicit RELEASE occurs.

SQL> set serveroutput on
SQL> declare
  2    l_Lock_Status       number;
  3    b_Release_On_Commit boolean := true;
  4    l_Lock_Handle       varchar2(128 byte);
  5
  6  begin
  7    dbms_lock.allocate_unique('Lock 1',l_Lock_Handle);
  8
  9    l_Lock_Status:= dbms_lock.request
 10      (lockhandle       =>l_Lock_Handle
 11      ,lockmode         =>dbms_lock.x_mode
 12      ,release_on_commit=>b_release_on_commit);
 13
 14    dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
 15
 16    l_Lock_Status:= dbms_lock.request
 17      (lockhandle       => l_Lock_Handle
 18      ,lockmode         => dbms_lock.x_mode
 19      ,release_on_commit=> b_release_on_commit);
 20
 21    dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
 22    dbms_lock.allocate_unique('Lock 1', l_Lock_Handle);
 23
 24    l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
 25    dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
 26
 27    l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
 28    dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
 29
 30  end;
 31  /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 4
Expect 4. Actual: 4

PL/SQL procedure successfully completed.

As can be seen from values on the third line, expected is 0, but actual is 4!!! The root cause of the problem is that an implicit COMMIT occurs when calling DBMS_LOCK.ALLOCATE_UNIQUE. The implicit COMMIT coupled with RELEASE_ON_COMMIT set to TRUE causes the ownership of the lock to change.

Use the following useful query to identify locks requested, but not yet expired:

select * from dba_lock dl
join dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
where dla.name like 'Lock 1';

Use as follows for scenario 3:

SQL> set serveroutput on
SQL> declare
  2    l_Lock_Status       number;
  3    b_Release_On_Commit boolean := true;
  4    l_Lock_Handle       varchar2(128 byte);
  5
  6    ty_Lock_Rec         dba_lock%rowtype;
  7
  8    l_Lock_Name         varchar2(10) := 'Lock 3';
  9
 10  begin
 11    dbms_lock.allocate_unique(l_Lock_Name,l_Lock_Handle);
 12
 13    l_Lock_Status:= dbms_lock.request
 14      (lockhandle       =>l_Lock_Handle
 15      ,lockmode         =>dbms_lock.x_mode
 16      ,release_on_commit=>b_Release_On_Commit);
 17
 18    dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
 19
 20    l_Lock_Status:= dbms_lock.request
 21      (lockhandle       => l_Lock_Handle
 22      ,lockmode         => dbms_lock.x_mode
 23      ,release_on_commit=> b_Release_On_Commit);
 24
 25    begin
 26      select  dl.session_id
 27             ,dl.lock_type
 28             ,dl.mode_held
 29             ,dl.mode_requested
 30             ,dl.lock_id1
 31             ,dl.lock_id2
 32             ,dl.last_convert
 33             ,dl.blocking_others
 34      into
 35              ty_Lock_Rec
 36      from
 37              dba_lock dl
 38      join
 39              dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
 40      where   dla.name like l_Lock_Name;
 41
 42      dbms_output.put_line('Session ID     :' || ty_Lock_Rec.Session_ID);
 43      dbms_output.put_line('Lock type      :' || ty_Lock_Rec.Lock_Type);
 44      dbms_output.put_line('Mode held      :' || ty_Lock_Rec.Mode_Held);
 45      dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
 46      dbms_output.put_line('Lock ID 1      :' || ty_Lock_Rec.lock_id1);
 47      dbms_output.put_line('Lock ID 2      :' || ty_Lock_Rec.lock_id2);
 48      dbms_output.put_line('Last Convert   :' || ty_Lock_Rec.last_convert);
 49      dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
 50
 51      exception
 52        when no_data_found then
 53          dbms_output.put_line('No data exists in DBA_LOCK');
 54    end;
 55
 56    dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
 57    dbms_lock.allocate_unique(l_Lock_Name, l_Lock_Handle);
 58
 59    begin
 60      select  dl.session_id
 61             ,dl.lock_type
 62             ,dl.mode_held
 63             ,dl.mode_requested
 64             ,dl.lock_id1
 65             ,dl.lock_id2
 66             ,dl.last_convert
 67             ,dl.blocking_others
 68      into
 69              ty_Lock_Rec
 70      from
 71              dba_lock dl
 72      join
 73              dbms_lock_allocated dla on dl.lock_id1 = dla.lockid
 74      where   dla.name like l_Lock_Name;
 75
 76      dbms_output.put_line('Session ID     : ' || ty_Lock_Rec.Session_ID);
 77      dbms_output.put_line('Lock type      :' || ty_Lock_Rec.Lock_Type);
 78      dbms_output.put_line('Mode held      :' || ty_Lock_Rec.Mode_Held);
 79      dbms_output.put_line('Mode requested :' || ty_Lock_Rec.Mode_Requested);
 80      dbms_output.put_line('Lock ID 1      :' || ty_Lock_Rec.lock_id1);
 81      dbms_output.put_line('Lock ID 2      :' || ty_Lock_Rec.lock_id2);
 82      dbms_output.put_line('Last Convert   :' || ty_Lock_Rec.last_convert);
 83      dbms_output.put_line('Blocking Others:' || ty_Lock_Rec.blocking_others);
 84
 85      exception
 86        when no_data_found then
 87          dbms_output.put_line('No data exists in DBA_LOCK');
 88    end;
 89
 90    l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
 91
 92    dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
 93
 94
 95    l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
 96    dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
 97
 98  end;
 99  /
Expect 0. Actual: 0
Session ID     :159
Lock type      :PL/SQL User Lock
Mode held      :Exclusive
Mode requested :None
Lock ID 1      :1073742997
Lock ID 2      :0
Last Convert   :0
Blocking Others:Not Blocking
Expect 4. Actual: 4
No data exists in DBA_LOCK
Expect 0. Actual: 4
Expect 4. Actual: 4

PL/SQL procedure successfully completed.

 

Scenario 3

Use a lock handle, generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to FALSE. Perform an explicit RELEASE.


SQL> set serveroutput on
SQL> declare
  2    l_Lock_Status       number;
  3    b_Release_On_Commit boolean := false;
  4    l_Lock_Handle       varchar2(128 byte);
  5
  6  begin
  7    dbms_lock.allocate_unique('Lock 1',l_Lock_Handle);
  8
  9    l_Lock_Status:= dbms_lock.request
 10      (lockhandle       => l_Lock_Handle
 11      ,lockmode         => dbms_lock.x_mode
 12      ,release_on_commit=> b_release_on_commit);
 13
 14    dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
 15
 16    l_Lock_Status:= dbms_lock.request
 17      (lockhandle       => l_Lock_Handle
 18      ,lockmode         => dbms_lock.x_mode
 19      ,release_on_commit=> b_release_on_commit);
 20
 21    dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
 22    dbms_lock.allocate_unique('Lock 1', l_Lock_Handle);
 23
 24    l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
 25    dbms_output.put_line('Expect 0. Actual: '||l_Lock_Status);
 26
 27    l_Lock_Status:= dbms_lock.release(lockhandle=>l_Lock_Handle);
 28    dbms_output.put_line('Expect 4. Actual: '||l_Lock_Status);
 29
 30  end;
 31  /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 0
Expect 4. Actual: 4

PL/SQL procedure successfully completed.

Setting REALEASE_ON_COMMIT to FALSE resolves the problem. The implicit COMMIT does not effect the explicit release so results will match. In some cases developers include a PRAGMA AUTONOMOUS_TRANSACTION when calling ALLOCATE_UNIQUE to hide the implicit COMMIT. Although this does seem to work, this can have even more unexpected results and should be avoided.

 

Scenario 4

Use a lock handle, generated with ALLOCATE_UNIQUE and set RELEASE_ON_COMMIT to TRUE. Perform an explicit RELEASE. Wrap the ALLOCATE_UNIQUE in a PRAGMA AUTONOMOUS TRANSACTION.

SQL> set serveroutput on
SQL> declare
  2    l_Lock_Status       number;
  3    b_Release_On_Commit boolean := true;
  4    l_Lock_Handle       varchar2(128 byte);
  5
  6      function Get_Lock(p_Lock_In in varchar2) return varchar2
  7      as
  8        pragma autonomous_transaction;
  9        v_lock_handle varchar2(128 byte);
 10        begin
 11          dbms_lock.allocate_unique(lockname  =>p_Lock_In
 12                                   ,lockhandle=>v_lock_handle);
 13          return v_lock_handle;
 14      end Get_Lock;
 15
 16  begin
 17
 18    l_Lock_Handle := Get_lock('Lock 1');
 19
 20    l_Lock_Status:= dbms_lock.request
 21      (lockhandle        => l_Lock_Handle
 22      ,lockmode          => dbms_lock.x_mode
 23      ,release_on_commit => b_release_on_commit);
 24
 25    dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
 26
 27    l_Lock_Status:= dbms_lock.request
 28      (lockhandle        => l_Lock_Handle
 29      ,lockmode          => dbms_lock.x_mode
 30      ,release_on_commit => b_release_on_commit);
 31
 32    dbms_output.put_line('Expect 4. Actual: '|| l_Lock_Status);
 33    l_Lock_Handle := Get_Lock('Lock 1');
 34
 35    l_Lock_Status:=dbms_lock.release(lockhandle=>l_Lock_Handle);
 36    dbms_output.put_line( 'Expect 0. Actual: '||l_Lock_Status);
 37
 38    l_Lock_Status:=dbms_lock.release(lockhandle=>l_Lock_Handle);
 39    dbms_output.put_line( 'Expect 4. Actual: '||l_Lock_Status);
 40
 41  end;
 42  /
Expect 0. Actual: 0
Expect 4. Actual: 4
Expect 0. Actual: 0
Expect 4. Actual: 4

PL/SQL procedure successfully completed.

 

Implicit Savepoint when deadlock detection occurs.

The following example describes how Oracle
sets an implicit Savepoint after each UPDATE.

For this example, when a deadlock error occurs, ORA-00060, not all
DML statements in the transaction will therefore be rolled back,
but only the single statement within the session in which the deadlock was
detected.

The following illustrates the point;

Create a test table and data first;

create table a (x number);

insert all
  into a (x) values (1)
  into a (x) values (2)
select 1
from dual;

commit;

The data would look as follows;

SQL> select * from a;

         X
----------
         1
         2
        
Now issue a series of statements in two sessions to generate a deadlock error;

Time DML Statement                       Notes
==== =============                       =====          
     Session 1         Session 2
     =========         =========

t0   update a
     set    x = 3
     where  x = 1;
--------------------------------------------------------------------
t1                     update a
                       set    x = 4
                       where  x = 2;
--------------------------------------------------------------------
t2   update a                        At this point Session 1
     set    x = 6                    hangs, because Session 2
     where  x = 2;                   has issued an UPDATE on
                                     the same row at point t1.
--------------------------------------------------------------------
t3                     update a      At this point Session 2
                       set    x = 5  hangs as well.                        where  x = 1; Session 1 is now waiting
                                     for a row that Session 2 
                                     has locked while Session 2 
                                     is waiting for a row that 
                                     Session 1 has locked.
--------------------------------------------------------------------
t4  update                           At this point Oracle will
    a set   x = 6                    will detect a deadly
    where   x = 2;                   embrace (deadlock) due to
                                     a perpetual wait for
    update a set                     shareable resources as
        *                            described at point T3.
    ERROR at line 1:
    ORA-00060:
    deadlock detected
    while waiting for
    resource
--------------------------------------------------------------------
t5  commit;                          The waiting in Session 2
                                     ceases and the UPDATE
                                     shows as being
                                     successfully completed.
--------------------------------------------------------------------
t6  SQL> select *                    At this point the data
      2  from a;                     indicates that 1 was
                                     successfully updated to 3,
             X                       but 2 was not.
             -
             3
             2                                 
            
The reason that one of the updates appeared to have worked, when viewing
the data at point T6, is that Oracle created an implicit Savepoint at point
T2, so when the error was encountered at T5, the entire transaction was not
rolled back, but only the single UPDATE in which the deadlock was detected.

Session 2 would have continued to hang (wait) at point T4, even though a
partial rollback occurred to an implicit savepoint due to the deadlock detection, since the row, updated at T3, was locked at T0.

The UPDATE in session 2 was successful once a COMMIT was issued, at T5,
in session 1 however.

Use this SQL to view the number of deadlocks that have occurred within the database;

SQL> select name, value
  2  from v$sysstat
  3  where name = 'enqueue deadlocks';

NAME                                         VALUE
--------------------------------------- ----------
enqueue deadlocks                                3

To avoid deadlocks, always lock rows in the same order,

For example, for the above set of transactions, had the order
of UPDATES occurred as follows;


t0   update a
     set    x = 3
     where  x = 1;
------------------------------------
t1                     update a
                       set    x = 5
                       where  x = 1;
------------------------------------
t2   update a                      
     set    x = 6                  
     where  x = 2;                 
                                   
------------------------------------
t3                     update a    
                       set    x = 4
                       where  x = 2;

Then a deadlock error would not have occurred.

Identifying lock modes

The SQL in the post "Identify blocking session for an UPDATE",
bluefrog-oracle.blogspot.com/2011/11/identify-blocking-session-for-update.html
highlighted how to identify which sessions were blocking and which were waiting.

The sample output for the SQL in the above post would be something like;

BLOCKING_STATUS
-------------------------------------------------------------------
USER1@XX\HOST ( SID=214 )  is blocking USER1@XX\HOST ( SID=26 )

The scenario, illustrated with the above output, is that a session, connected as USER1, is blocking a session, also connected as USER1.

The SQL below shows more detail in relation to the types of
locks and modes that are causing blocking for each session.

The script is meant for running on a RAC server.

col User_Session  format a10
col object_name   format a40
col sid           format 99999999
col type          format a4
col serial#       format 999999999
col held          format 9999
col request       format 99999999
col Inst_ID       format 99999
col machine       format a30

set linesize 400
set pagesize 30000

select l.*, s.machine, s.osuser
from
    (
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid           SID
           ,s.username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,' '             object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
    where
            s.sid           = l.sid
    and     s.username      <> ' '
    and     s.paddr         = p.addr
    and     l.type          <> 'TM'
    and    (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
    union
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid           SID
           ,s.Username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,object_name     object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
           ,sys.dba_objects o
    where
            s.sid           = l.sid
    and     o.object_id     = l.id1
    and     l.type          = 'TM'
    and     s.username      <> ' '
    and     s.paddr         = p.addr
    union
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid SID
           ,s.Username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,'(Rollback='||rtrim(r.name)||')' object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
           ,v$rollname       r
    where
            s.sid           = l.sid
    and     l.type          = 'TX'
    and     l.lmode         = 6
    and     trunc(l.id1/65536) = r.usn
    and     s.username      <> ' '
    and     s.paddr         = p.addr
   )          l
  ,v$session  s
where
          s.sid         = l.sid
order by l.Object_Name, l.Held
/

There are only two types of general locks, shared and exclusive.

A many to one relationship can exist between several shared locks and
a single resource, whereas only a one to one scenario can exist
between an exclusive lock and a single resource.

Oracle manages locks on resources through enqueues.
Enqueues as shared memory structures that serialize access to database
resources.

A database resource, in relation to data locks, can be an object structure
or a transaction.

Oracle has two types of data locks:

TX = Row Locks - Finest grain of locking possible.
Oracle stores locking information of a locked row
within the data block the row resides in.

A row lock automatically generates a table lock (TM), to prevent
DDL from occurring on the table while the transaction is in progress.

TM = Table locks - Acquired by a transaction (INSERT,UPDATE,DELETE,MERGE,LOCK TABLE or SELECT FOR UPDATE).

Table locks can be one of six modes, namely;

0 = none
1 = null 
2 = row-S (RS or SS)     
Row share lock or sub-share lock. 
Transaction holds locked rows and an update is pending.
Acquired when issuing either;
  SELECT FOR UPDATE;
  LOCK TABLE IN ROW SHARE mode;

Least restrictive, since other transactions can query, insert update or delete concurrently on the same table on rows other than the ones locked with SELECT for UPDATE.
Other transactions can therefore obtain RS, RX or SRX locks.
Prevents an exlusive table (X) lock however.

3 = row-X (RX or SX)
Row exclusive lock. Transaction holding locked rows has completed the update(s) to the table.
Acquired automatically with one of the following four statements;
  INSERT;
  UPDATE;
  DELETE;
  LOCK TABLE IN ROW EXCLUSIVE MODE;

More restrictive than row share lock, since other transactions cannot concurrently
lock the table in share mode (S), share exclusive mode (SRX) using one of the following statements;
  LOCK TABLE IN SHARE MODE
  LOCK TABLE IN EXCLUSIVE MODE
in addition to not permitting any other types of exclusive (X) locks.
RX is the default locking behavior for Oracle.

4 = share (S)
Share Table. Acquired when explicitly issuing the LOCK table in SHARE MODE statement.
Only allows other transactions to query the table in share mode (S).
Prevents other transactions from modifying the same table in SRX, RX and X modes.
Transactions can hold share (S) table locks concurrently, but a SELECT for UPDATE update can only occur if there are no other transactions in share mode (S) as well.

5 = S/Row-X (SRX or SSX) 
Share Row Exclusive. Acquired explicitly when issuing a LOCK TABLE in SHARE ROW EXCLUSIVE mode. Only a single transaction at a time can acquire a share row exclusive lock.
Other transactions can however query the table, but they cannot update the table.
Prevents all types of locks from other transactions. 
Permits SELECT FOR UPDATE locks within the same transaction after issuing the LOCK TABLE statement.

6 = Exclusive (X)        
Most restrictive. Prevents any DML on the table. Acquired with
LOCK TABLE IN EXCLUSIVE MODE. Only a single transaction can obtain an exclusive lock at a time. Querying can still occur though.


This is a sample output from the above query and one can clearly

INST_ID  SID USER_SESSI SERIAL# TYPE OBJECT_NAME HELD REQUEST
------- ---- ---------- ------- ---- ----------- ---- -------
      1  222 USER2        34254 TX                  0       6
      1  221 USER1         8366 TM   TEST_LOCK      3       0


The REQUEST column will indicate the mode of lock on a Resource
that is sought, which is currently held by another
session (indicated by HELD).

A Resource can be a table definition, a transaction or any structure
that is shareable. The allowable range of values will be the same as
per the HELD column.

Oracle uses enqueues as a locking mechanisms for queuing access to
resources.

Lock conversion occurs when a lock with a lower restrictiveness is converted
to one of higher restrictiveness. For example, if a SELECT for UPDATE
is issued, the initial locks on the table would be row share (RS).

When eventually the UPDATE WHERE CURRENT OF occurs, the RS lock will be converted
to a RX lock.

Identify SQL in blocking and waiting sessions

col Event format a25
col DML_BLOCKING format a45
col DML_In_Waiting format a45
set linesize 400
set pagesize 3000

-- LABEL: STATEMENT A
select  distinct
        a.sid                     as Waiting_SID
       ,d.sql_text                as DML_In_Waiting
       ,o.Owner                   as Object_Owner
       ,o.Object_Name             as Locked_Object
       ,a.Blocking_Session        as Blocking_SID
       ,c.sql_text                as DML_Blocking
from
        v$session                 a
       ,v$active_session_history  b
       ,v$sql                     c
       ,v$sql                     d
       ,all_objects               o
where
        a.event                   = 'enq: TX - row lock contention'
and     a.sql_id                  = d.sql_id
and     a.blocking_session        = b.session_id
and     c.sql_id                  = b.sql_id
and     a.Row_Wait_Obj#           = o.Object_ID
and     b.Current_Obj#            = a.Row_Wait_Obj#
and     b.Current_File#           = a.Row_Wait_File#
and     b.Current_Block#          = a.Row_Wait_Block#

-- LABEL: STATEMENT B
select  distinct
        a.sid                     as Waiting_SID
       ,a.event                   as Event
       ,c.sql_text                as DML_Blocking
       ,b.sid                     as Blocking_SID
       ,b.event                   as Event
       ,b.sql_id                  as Blocking_SQL_ID
       ,b.prev_sql_id             as Blocking_Prev_SQL_ID
       ,d.sql_text                as DML_Blocking
from
        v$session                 a
       ,v$session                 b
       ,v$sql                     c
       ,v$sql                     d
where
        a.event                   = 'enq: TX - row lock contention'
and     a.blocking_session        = b.sid
and     c.sql_id                  = a.sql_id
and     d.sql_id                  = nvl(b.sql_id,b.prev_sql_id);

The first statement is generally more accurate than the second, since
the session holding the lock may have issued several DML
statements since issuing the lock, so the DML that is output
as holding the lock may not be related to the blocking DML.

Occasionally you may find that the SQL_ID for the
session holding the lock is NULL, reason being that since issuing the
lock some PL/SQL code may have executed or a COMMIT is being performed
or some other ongoing latch activity is busy, in which case there
is no SQL_ID to identify the session with.

In such cases, join to NVL(B.SQL_ID,B.PREV_SQL_ID).


Sample output after simulating a blocking and waiting session;

In session 1, issue a full table lock;

create table test_lock (a varchar2(2), bb varchar2(2));
insert into test_lock values ('i', '1');
insert into test_lock values ('ii','2');
commit;
select * from test_lock for update;

In session 2, issue an update on one of the rows in table TEST_LOCK

update test_lock set a='i' where bb='1';

The above UPDATE statement will simply hang. Do not issue a rollback
in session1.

In Session 1, run the first SQL statement, labelled as "STATEMENT A" above.

Sample output;

WAITING_SID DML_IN_WAITING                          OBJECT_OWNER LOCKED_OBJECT                  BLOCKING_SID DML_BLOCKING
----------- --------------------------------------- ------------ ------------------------------ ------------ ----------------------------------
       2081 update test_lock set a='i' where bb='1' USER1        TEST_LOCK                               382 select * from test_lock for update

Identify blocking session for an UPDATE

Simulate a blocking session first:

Open 2 session windows, either in SQL Plus or any other cilent tool
you may be using.
In session 1 create a table and insert some data

create table test_lock (a varchar2(2), bb varchar2(2));
insert into test_lock values ('i','1');
insert into test_lock values ('ii', '2');
select * from test_lock;
commit;

Before starting the simulation, obtain the unique session ID's
for both sessions by using the following statement;

select distinct sid from v$mystat;

Obtain an exclusive lock in session 1 as follows;

select * from test_lock for update;



Now, in session 2, run these queries to identify blocking sessions;

update test_lock set a='i' where bb='1';

This statement will simply hang, since session 1 holds an exclusive lock on all rows within the table. Do not issue a rollback or commit;

Now query V$LOCK in session 1 using the following 3 statements;

set pagesize 3000
set linesize 1000
select * from v$lock;

select l1.sid, ' IS BLOCKING ', l2.sid, l1.lmode, l2.request
from   v$lock l1, v$lock l2
where   l1.block    = 1
and     l2.request  > 0
and     l1.id1      =l2.id1
and    l1.id2       =l2.id2;

select    s1.username
       || '@'
       || s1.machine
       || ' ( SID='
       || s1.sid
       || ' )  is blocking '
       || s2.username
       || '@'
       || s2.machine
       || ' ( SID='
       || s2.sid
       || ' ) ' AS blocking_status
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;

Finally, use the next few statements to identify the object ID and
blocked row information.
Replace the session id in the where clause with the
waiting session id, session 2.

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session
where sid=&sid;


Replace the SID in the where clause with that of session 2
to obtain the Object name and ROWID

select do.object_name
      ,row_wait_obj#
      ,row_wait_file#
      ,row_wait_block#
      ,row_wait_row#
      ,dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#
                                   ,ROW_WAIT_FILE#
                                   ,ROW_WAIT_BLOCK#
                                   ,ROW_WAIT_ROW# )
from   v$session       s
      ,dba_objects     do
where  sid             = &sid -- this is the SID from session2
and    s.ROW_WAIT_OBJ# = do.OBJECT_ID;



Replace the ROWID from the previous result set to obtain the
actual row data

select *
from test_lock
where rowid='AAAVnHAAQAAAp0tAAA';