Understanding and Troubleshooting Locks in Oracle
What’s Causing My Session to Hang?
When an application slows down or users face unresponsive transactions, a common culprit is blocking caused by session locks. Pinpointing not only the session responsible for the block but also the specific object or row involved can save time and frustration. Let’s walk through how to create, identify, and investigate such scenarios.
---
Simulating a Blocking Lock
To explore lock behavior, start by creating a test case with two database sessions.
In Session 1:
CREATE TABLE tstlock (foo VARCHAR2(1), bar VARCHAR2(1));
INSERT INTO tstlock VALUES (1, 'a');
INSERT INTO tstlock VALUES (2, 'b');
COMMIT;
Now lock the entire table:
SELECT * FROM tstlock FOR UPDATE;
In Session 2, attempt a conflicting update:
UPDATE tstlock SET bar = 'a' WHERE bar = 'a';
This command will hang, waiting for the lock held by Session 1 to be released.
---
Identifying the Blocking Session
Oracle’s DBA_BLOCKERS view shows the sessions causing blocks, but a more detailed and faster method is through the V$LOCK view:
SELECT * FROM v$lock;
Look at the BLOCK column: a value of 1 indicates a session that is blocking others. To determine which session is blocked by which, compare the ID1 and ID2 values for matches, and use this query:
SELECT l1.sid || ' IS BLOCKING ' || l2.sid
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;
For more readability:
SELECT s1.username || '@' || s1.machine || ' (SID=' || s1.sid || ') is blocking ' ||
s2.username || '@' || s2.machine || ' (SID=' || s2.sid || ')' AS blocking_info
FROM v$lock l1
JOIN v$session s1 ON l1.sid = s1.sid
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN v$session s2 ON l2.sid = s2.sid
WHERE l1.block = 1 AND l2.request > 0;
---
Lock Types and the Meaning of ID1/ID2
Understanding the TYPE, ID1, and ID2 columns in V$LOCK helps decode lock behavior:
TX: Row-level transaction lock (common in DML).
TM: DML operation on an object (e.g., a table).
UL: User-defined lock via DBMS_LOCK.
For TX locks, ID1 and ID2 identify rollback and transaction entries. For TM locks, ID1 maps to the object being modified.
---
Lock Modes: LMODE and REQUEST
Oracle lock modes range from 0 (no lock) to 6 (exclusive lock). If a session holds an exclusive TX lock (LMODE=6) and another session wants it, the second session will show REQUEST=6 and LMODE=0.
You might also see TX-4 locks when sessions wait for ITL (Interested Transaction List) entries. This usually indicates a need to increase INITRANS on the affected table.
---
Determining the Locked Object
To find the exact object being locked:
SELECT object_name FROM dba_objects WHERE object_id = <ID1 value>;
This reveals the table or object name associated with the TM lock.
---
Finding the Locked Row
To go deeper and find the specific row that a session is waiting for:
SELECT row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
FROM v$session
WHERE sid = <blocked session SID>;
Combine this with DBMS_ROWID.ROWID_CREATE to reconstruct the actual rowid:
SELECT do.object_name,
dbms_rowid.rowid_create(1, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#) AS row_id
FROM v$session s
JOIN dba_objects do ON s.row_wait_obj# = do.object_id
WHERE s.sid = <blocked session SID>;
You can then query the row directly:
SELECT * FROM tstlock WHERE rowid = '<generated ROWID>';
---
Final Thoughts
This walkthrough demonstrates how to detect blocking locks in Oracle and investigate down to the exact row causing the issue. By combining views like V$LOCK, V$SESSION, and DBA_OBJECTS, you gain powerful tools to resolve and understand locking problems efficiently.
