Unraveling Oracle ITL Deadlocks: Concepts, Detection, and Hands‑On Simulations

1. Concept of Deadlock and Its Trace File

A deadlock occurs when two or more processes wait indefinitely for resources held by each other; Oracle detects and records such events in the alert log and generates a trace file. In RAC, the LMD (Lock Manager Daemon) creates the trace file.

2. Deadlock Trace Files

Both single‑instance and RAC environments include the keyword Deadlock in the alert log. In RAC, the LMD process generates the trace file, while in a single instance the trace file is produced directly by the instance.

3. Detection Interval

The hidden parameter _lm_dd_interval controls how often Oracle checks for deadlocks. Its default is 10 seconds in 11g and 60 seconds in 10g.

4. Classification of Deadlocks

Oracle deadlocks are mainly divided into row‑level and block‑level (ITL) deadlocks.

Row‑Level Deadlock – includes primary‑key/unique‑index deadlocks, foreign‑key without index, bitmap‑index deadlocks, common transaction deadlocks, and autonomous‑transaction deadlocks.

Block‑Level Deadlock – refers to ITL (Interested Transaction List) deadlocks.

4.1 Row‑Level Deadlock Examples (Oracle 11.2.0.4)

Primary‑Key / Unique‑Index Deadlock : Create a table with a primary key, insert a row in session 1 without committing, insert another row in session 2, then swap the values. Both sessions lock each other, producing ORA‑00060.

Session 2 (SID 156) blocks session 1 (SID 191) and vice‑versa, as shown in the alert logs.

When session 2 commits, session 1 receives a unique‑constraint violation.

Foreign‑Key Without Index : Updating or deleting a parent‑table row locks the entire child table if the foreign key lacks an index.

Update parent primary key → child table full lock.

Delete parent row → child table full lock.

Solution: create an index on the foreign‑key column.

Bitmap Index Deadlock : Concurrent updates on a bitmap index lock the entire bitmap, not just the affected rows, leading to deadlock when different sessions lock different bitmap partitions.

Session 1 locks bitmap for ID = 1, session 2 locks bitmap for ID = 2; each then requests the other's bitmap, causing a deadlock.

Common Transaction Deadlock : Two sessions update different tables in opposite order, then try to update the other's table, creating a classic deadlock scenario.

When the second update is issued, Oracle rolls back the offending statement and reports ORA‑00060.

Autonomous Transaction Deadlock : An autonomous transaction inside a trigger tries to lock a row already held by the main transaction, causing a deadlock.

4.2 Simulation Steps for Row‑Level Deadlocks

All simulations use Oracle 11.2.0.4. Example commands are shown in SQL*Plus sessions.

create table A(id number primary key);

insert into A values(1); commit;

Session 1 updates row 1 without commit, session 2 updates row 2 without commit, then each session attempts to update the other row, leading to blocking and finally a deadlock error.

5. Block‑Level (ITL) Deadlock

ITL (Interested Transaction List) is a structure inside each Oracle data block that records active transactions. Each block has a default of two ITL slots (controlled by INITRANS), which can be expanded if free space exists. When all ITL slots are occupied, sessions wait for an ITL entry, which can lead to ITL deadlocks under high concurrency.

Key fields in an ITL entry include:

Itl : slot number.

Xid : transaction identifier (undo segment, slot, wrap).

Uba : address of the undo block containing the before‑image.

Flag : transaction state.

Lck : number of rows locked by the transaction.

Scn/Fsc : commit SCN or fast‑commit credit.

Example query to view undo information:

SELECT UBAFIL, UBABLK, UBASQN, UBAREC FROM v$transaction;

5.1 ITL Count and Configuration

The number of ITL slots per block is set by INITRANS (default 1 for tables, 2 for indexes). The maximum number of ITL slots (pre‑11g MAXTRANS) is now 255. Insufficient ITL slots cause the wait event TX - allocate ITL entry.

5.2 Simulating ITL Deadlock

Create a table with PCTFREE=0 and INITRANS=1 to force ITL allocation to consume all free space. Insert enough rows to fill three blocks, then start four sessions, each updating different rows in the fully‑filled blocks to consume all ITL slots. create table T_ITL_LHR(id number) PCTFREE 0 INITRANS 1; After loading 2000 rows, two blocks are completely full. Four sessions perform updates that lock different ITL slots, leading to a situation where each session waits for another’s ITL slot.

When the hidden parameter _lm_dd_interval expires, Oracle detects the deadlock and writes an entry to the alert log.

The resulting Global Wait‑For‑Graph (WFG) shows the circular wait among the four sessions.

Conclusion

Deadlocks in Oracle are usually caused by inconsistent lock acquisition order, missing indexes (especially foreign‑key indexes), bitmap index contention, or autonomous transactions that conflict with the main transaction. Proper indexing, adequate ITL slot configuration, and careful transaction design eliminate most deadlock scenarios.