The Oracle (tm) Users' Co-Operative FAQ

How do you identify all locked rows in a table?


Author's name: Mark D Powell

Author's Email: Mark.Powell@eds.com

Date written: 26th July 2001

Oracle version(s): 8.1.7.0

How do you identify all locked rows in a table ?

Back to index of questions


Because the number of locks needed to protect all rows that have been modified by DML actions in an Oracle database could in theory equal every row of every table in the database, subject to rollback segment space limitations, someone in Oracle made the wise decision to store row level locking information in the Oracle data block with the affected data.  Oracle uses the interested transaction list, ITL, set by the initrans parameter for tables (default 1) and indexes (default 2) to keep track of changed data.  This greatly reduces the memory overhead that row level locking would have required if performed in the SGA.  This also means that row level locking information is not stored anywhere in the SGA where it can be queried

You can reference the Concepts manual chapter on data concurrency and consistency for more information related to this subject.

Normally if a session issues a DML statement against a row that has already been changed the session will become lock waited.  Oracle is very patient and will wait forever for the locking session to issue a commit or rollback.  This is usually undesirable for online transactions and internally Oracle often made use of a parameter called nowait to prevent certain operations like create index, prior to online index builds, from going into a lock waiting state.  This parameter was later made available for general use with the select for update statement.  When an attempt is made to access a locked row with the nowait parameter set the error: ORA-00054: resource busy and acquire with NOWAIT specified is returned.

This parameter can be used with a select for update and an exception clause to identify every locked row in the table at the time of the reading.  This is generally not useful information, but sample code follows:

UT1> select * from marktest;

FLD1             FLD2 FLD3
---------- ---------- ---------
one                 1 16-MAY-01
three               3 15-JUN-01
two                 2 13-JUL-01
four                4 13-JUL-01
five                5 25-JUL-01

Marktest has five rows and I have locked rows where fld1 = one, three, and five. Here is the pl/sql code and results showing the locked rows:

UT1> set echo on
UT1> @nowait
UT1> set serveroutput on
UT1> declare
  2  v_rowid       rowid             ;
  3  v_fld1        marktest.fld1%type;
  4  v_fld1b       marktest.fld1%type;
  5  --
  6  cursor c_fulltable is
  7   select  rowid
  8          ,fld1
  9   from      marktest
 10   order by fld2;
 11  --
 12  nowait_cond   exception                    ;
 13  pragma     exception_init(nowait_cond,-00054) ;
 14  --
 15  begin
16	open c_fulltable;
17  loop
18   fetch c_fulltable into v_rowid, v_fld1;
19   exit when c_fulltable%notfound;
20   begin
21     select fld1
22     into   v_fld1b
23     from   marktest a
24     where  a.rowid = v_rowid
25     for update nowait;
26     dbms_output.put_line('Got row '||v_fld1);
27     exception
28       when nowait_cond then
29         dbms_output.put_line('Row is locked '||v_fld1);
30     end;
31  end loop;
32  close c_fulltable;
33  end;
34  /
Row is locked one
Got row two
Row is locked three
Got row four
Row is locked five

New rows that have been inserted but not committed will not show while rows that are pending for delete will show due to the consistency model.On an active table the results of this code is wrong by the time it completes.

See the common FAQ entry for Who is locking a specific row for related row level locking material about finding the sessions blocking other sessions.

The dynamic performance tables v$access and v$locked_object contain entries for currently locked objects.

You can look at the number of rows affected by a session in the v$transaction table whose addr column can be joined to v$session table column taddr and the number of rows updated by a session can be seen in the v$transaction column used_urec.You can use v$transaction to watch the number of affected blocks and rows decline as a rollback is in process.

-- Mark D Powell --


Further reading: N/A


Back to top

Back to index of questions