When Oracle rolls back a statement with ORA-00060, it writes a block with who was waiting for whom to the trace of the session that received the error. The report reads that block and shows the wait cycle, the sessions, the contended row, the SQL and the diagnosis by lock type.
What the section shows
The Deadlocks section appears when the trace has the DEADLOCK DETECTED block, in two cases:
- a deadlock-only trace, without the 10046 lines (how to get the deadlock trace): the report has only the Summary, the Diagnosis and the Deadlocks, and the Summary shows Deadlocks (ORA-00060), Detected at, Sessions involved and Diagnosis;
- a 10046 trace in which a deadlock happened along the way: the section comes after the Diagnosis, and the rest of the report is as usual.
The text at the top says where the data comes from: From the DEADLOCK DETECTED block that Oracle writes to the trace of the session that received ORA-00060: who was waiting for whom, on which row and with which statement.
Each deadlock is a Deadlock N card, with the time, the trace line and the type Oracle writes in brackets, such as Transaction Deadlock. The Diagnosis has a high-severity finding for each of the first deadlocks in the trace, with the View deadlock link.
The synthetic trace of the sample has no deadlock, and the sample report does not have this section.
How to read it
The card starts with the diagnosis, highlighted, and with the wait cycle, from the session that received the error: Session 12 waits for Session 34, which waits for Session 12. Then come three blocks.
| Column | What it says |
|---|---|
| Resource | The name of the contended resource, as Oracle writes it, for example TX-00090004-00012345. |
| Type | The lock type: TX (transaction, row), TM (table), UL (application lock) and others. For TM, the object_id of the table comes along, with a link to the object page in the console. |
| Waiting session and Requested mode | The session that requested the lock and the mode it requested, such as X (exclusive) or S (shared). |
| Holding session and Held mode | The session that had the lock and the mode in which it had it. |
In Sessions involved, each session has a block with User, PDB, Machine, Program, Module, Action, Row waited on and the SQL it was running. The one that received the error has the received ORA-00060 mark. Row waited on gives the object_id, the file, the block and the slot of the contended row and, when the trace records it, the rowid.
PL/SQL stack of the session that received the error shows, with the sql_id of the statement, the objects and lines of the PL/SQL being executed, from the innermost call, where the statement was, to the outermost. In the console, each object leads to a page with the code excerpt around the line (object page).
The diagnosis by lock type
| When the graph has | Title in the report | What it usually is |
|---|---|---|
| TM lock | Table lock deadlock (TM) | Unindexed foreign key: when the key is changed or the parent table row is deleted, Oracle locks the whole child table. Create an index on the foreign key columns of the child table. |
| UL lock | User lock deadlock (UL) | Locks requested by the application with DBMS_LOCK, in a different order across sessions. |
| TX lock with a wait in mode S | Transaction deadlock with a wait in mode S | Two sessions inserting the same unique or primary key, lack of ITL slots in the block (low INITRANS) or a bitmap index under concurrent DML. |
| Only TX locks in mode X | Row deadlock (TX in mode X) | Two transactions changing the same rows in different orders. The fix is in the application: always change the rows in the same order, lock them first with SELECT ... FOR UPDATE in the same order, or shorten the transaction. |
| Other types | Deadlock between sessions | Locks of the graph's types requested in a different order across sessions. |
The rows apply in this order: with a TM lock in the graph, the diagnosis is the table one, even if there is TX. Every diagnosis ends with a reminder that Oracle rolled back only the statement of the session that received ORA-00060: its transaction remains open until commit or rollback.
Only the session that received the error writes the block to its trace. The traces of the other sessions in the cycle do not have the deadlock; what is known about them is in Sessions involved.
When it is a concern
- Every deadlock is a high-severity finding: a statement was rolled back, and the application received the error.
- Deadlocks that repeat on the same objects and statements: the application's access order is the problem, and the fix is in the code (deadlock).
- Deadlock on a TM lock: check the indexes on the foreign keys of the child table on the object page.
In a 10046 trace, the error also appears in Errors, on the ORA-00060 card, with the View the deadlock link.
Where to go next
- Deadlock, for the cause and the resolution.
- Deadlock trace, to find and upload the file.
- Objects, for the table and PL/SQL queries in the database.