Identifying the exact locked record a session is waiting for
You use DBA_WAITERS,V$BLOCKER or simply V$LOCK to identify a locking issue between two (or more) sessions.
One or more session(s) are waiting for locking rows that are already locked by other session(s). How to identify the exact row that is locked and is being waited for?
Below is a 5 step process on how to get the exact record (you may merge into one). I am not sure if this will work in Oracle 8i now, I had to change something for Oracle 9i/10g (you may try it though).
=== Get the locked record ===
1. Check DBA_WAITERS to identify holding and waiting sessions. In this case, session 55 is waiting and we need to find out what record it is waiting for.
SQL>select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD
--------------- --------------- -------------------------- --------------
55 54 Transaction Exclusive
1 row selected.
2. Check the rowid specific columns available in V$SESSION to create the rowid of the requested record.
SQL>select sid, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2 from v$session where sid in (55, 54);
SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------------- --------------- -------------
54 1 20 0 0
55 119793 20 18130 3
2 rows selected.
3. Check DBA_OBJECTS to find the object name and the data object id.
SQL>select owner, object_type, object_name, data_object_id 2 from dba_objects where object_id = 119793; OWNER OBJECT_TYPE OBJECT_NAME DATA_OBJECT_ID -------------------- ------------------ -------------------- -------------- AMAR TABLE AM703 119793
4. Create the rowid using the above columns from V$SESSION (ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) and DBA_OBJECTS (DATA_OBJECT_ID). In our case the values are as below:-
ROWID TYPE = 1 (for extended)
OBJECT NUMBER = 119793
RELATIVE FILE NUMBER = 20
BLOCK NUMBER = 18130
ROW NUMBER IN THE BLOCK = 3
SQL>select dbms_rowid.rowid_create(1, 119793, 20, 18130, 3) from dual; DBMS_ROWID.ROWID_C ------------------ AAAdPxAAUAAAEbSAAD 1 row selected.
5. There you are, that is the record being requested and is locked by the holding session. Select the record from the table found above to get the data.
SQL>select * from am703 where rowid = 'AAAdPxAAUAAAEbSAAD';
COL1 COL2
---------- --------------------
4 power
1 row selected.
=== Drawbacks ===
The above will only give you the immediate record that is requested by a session and is locked by someone else. It will not give you the complete set if more than one records are requested by the waiting session for locking.
The above has worked for me most of the time (though I have not yet found notes from oracle confirming this). If you do happen to hit a case where the above is not working, let me know.
Best viewed in medium text size. Please refresh (F5) to view the latest information.
This page was created on 06-Sep-2008. Last updated on 06-Sep-2008.
please forward all queries to amar@amar-padhi.com