|Author's name: Mark D Powell
Author's Email: Mark.Powell@eds.com
|Date written: 26th July 2001
Oracle version(s): 22.214.171.124
|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