Deadlock between sessions (ORA-00060)

In a deadlock, two or more sessions wait for each other in a cycle, and none of them moves. Oracle breaks the cycle by returning ORA-00060 to one of them and writes the wait-for graph to that session's trace. The lock type in the graph points to the cause.

Symptoms

In the Diagnosis, one DEADLOCK finding appears for each deadlock in the trace. The title is the diagnosis followed by the sessions in the cycle, such as Row deadlock (TX in mode X) between sessions <a> and <b>. When the trace has more than one deadlock and the time of each one is known, it comes at the end of the title, in parentheses. The severity is always high, with no percentage of the total time.

The text explains the likely cause and the fix and notes that Oracle rolled back only the statement of the session that got ORA-00060: its transaction stays open until the commit or the rollback. When the trace provides it, the text has the sql_id of the statement that got the error. It ends with View deadlock.

The finding comes from the DEADLOCK DETECTED block, in two kinds of file:

• the deadlock-only trace, which Oracle writes for the session that got the error (how to get the deadlock trace). The report has no profile, and the Summary shows Deadlocks (ORA-00060), Detected at, Sessions involved and Diagnosis;
• the 10046 trace of the session that got the error. The report is the full one, and it usually also has the Oracle errors in the trace finding, from the ERROR line of ORA-00060, and the enq: TX - row lock contention or enq: TM - contention wait right before the deadlock.

The Diagnosis shows one finding for each of the first deadlocks in the trace, and the Deadlocks section has their detail.

Cause

The diagnosis comes from the lock type in the wait-for graph and from the requested mode:

The ORA-00060 page in the Oracle documentation summarizes the error.

Resolution

Read the deadlock in the report

1. Click View deadlock.

   Result: the Deadlock n card opens, with the diagnosis, the time and the trace line where the block starts.

2. Follow the cycle: each session waits for the next one, and the last one goes back to the first.
3. In the Wait-for graph, check for each resource the Type, the Waiting session with the Requested mode and the Holding session with the Held mode.
4. In Sessions involved, check User, Machine, Program, Module, Action and the SQL of each session. The one marked received ORA-00060 is the owner of the trace.
5. In Row waited on, check the object_id, the file, the block, the slot and the rowid of the row each session was waiting for. Without Row waited on, the wait was not for a row: that is the case of TM, UL, ITL and unique keys.
6. In PL/SQL stack of the session that received the error, find the PL/SQL unit and the line of code that ran the statement.

With the owner and the name of the table from the object_id, the rowid shows the contended row, if it still exists:

select owner, object_name, object_type
  from dba_objects
 where object_id = &object_id;

select *
  from exemplo.pedido
 where rowid = '&rowid';

Row deadlock (TX in mode X)

• Change the rows always in the same order in all routines, by the key, for example.
• Lock the rows before changing them, with select ... for update, in the same order.
• Shorten the transaction: fewer rows held for less time leave less chance of a cycle.

Transaction with a wait in mode S

• Unique key: check whether two routines insert the same keys in different orders, or whether the same load runs twice at the same time.
• ITL: increase the INITRANS of the table or index in the graph, as in transaction space in the block.
• Bitmap index on a table with concurrent DML: replace it with a B-tree index.

Table lock (TM)

Create an index on the foreign key columns of the child table. The object_id appears in the finding text and in the Type column of the graph; in the console, it opens the object page, with the Table: indexes and their columns block. To find all the unindexed foreign keys in the schema, use the query in index the foreign key.

User lock (UL)

Request the DBMS_LOCK locks always in the same order in all routines and release them early, with release_on_commit or DBMS_LOCK.RELEASE.

Handle ORA-00060 in the application

Oracle rolled back only the statement that got the error. The transaction stays open, with the rows it had already locked: the application needs to roll it back and, if the operation can be repeated, try again. Without that, the session keeps holding the rows and blocks the others.

How to confirm

A deadlock is intermittent: a trace without one does not prove the fix. Watch, during the period when it used to happen, whether ORA-00060 comes back. Each occurrence writes a new trace for the session that got the error and a line in the alert log. In 19c, the v$diag_alert_ext view reads the alert log through the database:

select originating_timestamp, message_text
  from v$diag_alert_ext
 where message_text like '%ORA-00060%'
 order by originating_timestamp desc;

If the error comes back, upload the new trace and compare the diagnosis, the sessions and the statements with those of the previous deadlock.

In the report

The sample report has no deadlock, and the Deadlocks section only appears when the trace has one.

• Deadlocks: one card per deadlock, with the diagnosis, the cycle, the Wait-for graph, the Sessions involved and the PL/SQL stack (deadlocks).
• Diagnosis: one finding per deadlock, with the View deadlock link (Diagnosis).
• Summary: in the deadlock-only trace, the Deadlocks (ORA-00060), Detected at, Sessions involved and Diagnosis panels (summary).
• Errors: in the 10046 trace, the ORA-00060 card has the View the deadlock link (errors).
• SQL statements by contribution: in the 10046 trace, the statement that got the error, by the SQL ID column (SQL statements).

Before the deadlock, the sessions usually wait for each other without closing the cycle; that time appears in lock held by another session.