Row Locks, Table Locks, ITL, Enqueues, SELECT FOR UPDATE, and Concurrency Semantics

Learning outcomes

Two ServiceHub workers attempt to claim the same work order. One session appears “hung,” another gets an immediate resource-busy error, and an operator sees TX and TM rows in V$LOCK. This lesson connects row locking to Oracle enqueue evidence instead of treating every wait as the same kind of contention.

1. Row locks belong to transactions

INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE can acquire row locks. Those locks normally persist until commit or rollback. Oracle also uses table-level DML enqueue structures, commonly observed as TM, while transaction enqueue waits are commonly observed as TX.

The presence of a TM lock does not mean Oracle has escalated thousands of row locks into a SQL Server-style table lock. Oracle does not perform ordinary lock escalation in that sense. Diagnose the requested/held modes and the actual wait event.

2. Two-session blocker: reproduce it safely

DROP TABLE sh07_lock_queue IF EXISTS PURGE;CREATE TABLE sh07_lock_queue (    work_order_id NUMBER PRIMARY KEY,    status_code   VARCHAR2(12) NOT NULL,    claimed_by    VARCHAR2(30));INSERT INTO sh07_lock_queue VALUES (701,'READY',NULL);INSERT INTO sh07_lock_queue VALUES (702,'READY',NULL);COMMIT;

UPDATE sh07_lock_queueSET claimed_by = 'worker-A'WHERE work_order_id = 701;-- Do not COMMIT or ROLLBACK yet.

UPDATE sh07_lock_queueSET claimed_by = 'worker-B'WHERE work_order_id = 701;-- The prompt waits because Session A holds the conflicting row lock.

This is blocking, not deadlock: Session B waits for A, but A is not waiting for B. End Session B's pending statement if needed, then release A with ROLLBACK before continuing.

3. SELECT FOR UPDATE makes lock intent explicit

Use SELECT FOR UPDATE when the application must read rows and reserve them for a subsequent change in the same transaction. By default Oracle waits for a conflicting lock. NOWAIT fails immediately; WAIT n bounds the wait; SKIP LOCKED deliberately omits locked rows.

-- Session ASELECT work_order_idFROM sh07_lock_queueWHERE work_order_id = 701FOR UPDATE;-- Session B: immediate failure rather than indefinite waitSELECT work_order_idFROM sh07_lock_queueWHERE work_order_id = 701FOR UPDATE NOWAIT;-- Expected: ORA-00054 while A holds the lock.-- Session B: queue-style behavior can deliberately skip locked rowsSELECT work_order_id, status_codeFROM sh07_lock_queueWHERE status_code = 'READY'ORDER BY work_order_idFOR UPDATE SKIP LOCKED;

SKIP LOCKED is useful for competing consumers that may process any available work item. It is inappropriate when the query must return a complete business set, because skipping locked rows is intentionally incomplete.

4. Observe the blocker with V$SESSION and V$LOCK

Use a privileged observer session (for example, a lab DBA account) to inspect dynamic performance views. Do not grant broad catalog access to the application schema merely to make a lab query convenient.

SELECT sid, serial#, username, status,       event, wait_class, blocking_sessionFROM v$sessionWHERE username = 'SERVICEHUB_OWNER'ORDER BY sid;SELECT sid, type, id1, id2, lmode, request, blockFROM v$lockWHERE type IN ('TX','TM')ORDER BY sid, type;

A waiting row-update session commonly reports an event such as enq: TX - row lock contention. LMODE shows the held lock mode and REQUEST the requested mode. These views are evidence of current lock state; they do not tell you the business reason the transaction stayed open.

5. ITL: block-level transaction slots, not another row-lock table

Each data block contains an Interested Transaction List (ITL) that records transactions modifying rows in that block. INITRANS controls initial transaction slots; Oracle can often add slots if block space permits. Under extreme concurrent updates to the same blocks, sessions can wait for an ITL entry, commonly visible as enq: TX - allocate ITL entry.

6. Foreign keys can change locking cost

An unindexed foreign key is legal, but parent-key updates/deletes may require broader child-table work and stronger locking behavior than an indexed child key. In high-concurrency parent maintenance, an index on the foreign-key columns is often important. The decision should follow workload evidence; write-heavy child tables with no parent-key delete/update workload can have different tradeoffs.

7. Blocking is not deadlock, and enqueues are not latches/mutexes

A blocker is a one-way dependency that can resolve when the holder commits or rolls back. A deadlock is a cycle of dependencies; Oracle detects the cycle and raises ORA-00060 to one participant. Latches and mutexes protect internal memory structures and use different wait mechanisms. Treating every “lock” word as the same mechanism leads to incorrect fixes.

-- End both lab transactions first.ROLLBACK;DROP TABLE sh07_lock_queue PURGE;

8. Production judgment and next step

Keep transactions short, lock rows in a consistent business order, bound user-facing waits, and use SKIP LOCKED only for semantics that permit omission. Diagnose with current waits and blockers before killing sessions. ITL or foreign-key changes should be object/workload-specific. No paid pack is required for V$SESSION/V$LOCK, but viewing all sessions normally requires privileged access.

Lesson 5 creates the cycle that distinguishes a deadlock from ordinary blocking, then connects long snapshots to undo pressure and ORA-01555.

Authoritative references

• Managing Transactions — row-lock behavior and 26ai transaction-management context
• Database Development Guide — locking and application SQL processing
• SELECT — FOR UPDATE, NOWAIT, WAIT, and SKIP LOCKED syntax
• V$LOCK — lock mode/request/block evidence
• V$SESSION — wait and blocking-session evidence