Subhan Oracle DBA Blog's: What is a Deadlock and how Deadlock situation occurs?

What is a Deadlock?

A deadlock occurs when a session (A) wants a resource held by another session (B) , but that session also wants a resource held by the first session (A). There can be more than 2 sessions involved but the idea is the same.

Example of Deadlock

The following example demonstrates a deadlock scenario.

Setup

create table eg_60 ( num number, txt varchar2(10) );
insert into eg_60 values ( 1, 'First' );
insert into eg_60 values ( 2, 'Second' );
commit;

select rowid, num, txt from eg_60;

ROWID                     NUM TXT
------------------ ---------- ----------
AAASuCAAEAAAAinAAA          1 First
AAASuCAAEAAAAinAAB          2 Second

Session #1:

update eg_60 set txt='ses1' where num=1;

Session #2:

update eg_60 set txt='ses2' where num=2;
update eg_60 set txt='ses2' where num=1;

Session #2 is now waiting for the TX lock held by Session #1

Session #1:

update eg_60 set txt='ses1' where num=2;

Session #1 is now waiting on the TX lock for this row.
The lock is held by Session #2.
However Session #2 is already waiting on Session #1
This causes a deadlock scenario so deadlock detection kicks in and one of the sessions signals an ORA-60.

Session #2:

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Session #1 is still blocked until Session #2 commits or rolls back as ORA-60 only rolls back the current statement and not the entire transaction.

Diagnostic information produced by an ORA-60

ORA-60 error normally writes the error message in the alert.log together with the name of the trace file created. The exact format of this varies between Oracle releases. The trace
file will be written to the directory indicated by the USER_DUMP_DEST or BACKGROUND_DUMP_DEST, depending on the type of process that creates the trace file.

The trace file will contain a deadlock graph and additional information similar to that shown below. This is the trace output from the above example which signaled an ORA-60 to Session #2:

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name          process session holds waits process session holds waits
TX-00050018-000004fa        22     132     X             19     191           X
TX-00070008-00000461        19     191     X             22     132           X

session 132: DID 0001-0016-00000005     session 191: DID 0001-0013-0000000C
session 191: DID 0001-0013-0000000C     session 132: DID 0001-0016-00000005

Rows waited on:
Session 132: obj - rowid = 00012B82 - AAASuCAAEAAAAinAAA
(dictionary objn - 76674, file - 4, block - 2215, slot - 0)
Session 191: obj - rowid = 00012B82 - AAASuCAAEAAAAinAAB
(dictionary objn - 76674, file - 4, block - 2215, slot - 1)

----- Information for the OTHER waiting sessions -----
Session 191:
sid: 191 ser: 5 audsid: 340002 user: 88/DAVID flags: 0x45
pid: 19 O/S info: user: davidx, term: UNKNOWN, ospid: 3163
    image: oracle@quad.xx (TNS V1-V3)
client details:
    O/S info: user: davidx, term: pts/3, ospid: 3097
    machine: quad.xx program: sqlplus@quad.xx (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
current SQL:
update eg_60 set txt='ses1' where num=2

Information for THIS session:

----- Current SQL Statement for this session (sql_id=13b96yk6y5zny) -----
update eg_60 set txt='ses2' where num=1
===================================================
PROCESS STATE
-------------
.....

How Deadlock situation occurs?

Deadlock is a situation that occurs when two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock. The example below demonstrates how deadlock occurs.

Suppose there is a table test with two rows.

create table test (

row_row_num row_number,

txt varchar2(10) );

insert into test values ( 1, 'abcd' );

insert into test values ( 2, 'xyz' );

commit;

SQL> Select * from test ;

ROW_NUM TXT

1 abcd

2 xyz

Ses#1: Issue the following command:

SQL> update test set txt='ses1' where row_num=1;

Ses#2: Issue the following command:

SQL> update test set txt='ses2' where row_num=2;

SQL> update test set txt='ses2' where row_num=1;

Ses#2 is now waiting for the lock held by Ses#1

Ses#1: Issue the following command:

SQL> update test set txt='ses1' where row_num=2;

This update would cause Ses#1 to wait on the lock held by Ses#2, but Ses#2 is already waiting on this session. This causes a deadlock.

Deadlock Detection

Transaction deadlocks occur when two or more transactions are attempting to access an object with incompatible lock modes. The following script can be used to identify deadlocks in the database. The query depends upon objects that are created by the script $ORACLE_HOME/rdbms/admin/dbmslock.sql. Log on as SYS or with SYSDBA authority and run this script in all databases. You may have to run the deadlock monitoring script below numerous times before you identify the transaction that is causing the problem.

What does the trace information mean ?

In this section we explain each part of the above trace.
Note that not all this information is produced in all Oracle releases.

Section 1: Deadlock Graph

Deadlock graph:
                       ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name          process session holds waits process session holds waits
TX-00050018-000004fa        22     132     X             19     191           X
TX-00070008-00000461        19     191     X             22     132           X

session 132: DID 0001-0016-00000005     session 191: DID 0001-0013-0000000C
session 191: DID 0001-0013-0000000C     session 132: DID 0001-0016-00000005

This shows which process was holding each lock, and which process was waiting for each lock.
For each resource there are 2 parts each giving information on the relevant process:

Blocker(s)
Waiters(s)

The columns in the graph indicate:

Resource Name: Lock name being held / waited for.
process V$PROCESS.PID of the Blocking / Waiting session
session V$SESSION.SID of the Blocking / Waiting session
holds Mode the lock is held in
waits Mode the lock is requested in (waiting for)

So in this example:
SID 132 (Process 22) is holding TX-00050018-000004fa in eXclusive mode and is requesting TX-00070008-00000461 in eXclusive mode.

SID 191 (Process 19) is holding TX-00070008-00000461 in eXclusive mode and is requesting TX-00050018-000004fa in eXclusive mode.

The important things to note here are the LOCK TYPE, the MODE HELD and the MODE REQUESTED for each resource as these give a clue as to the reason for the deadlock.

Section 2: Rows waited on

Rows waited on:
Session 132: obj - rowid = 00012B82 - AAASuCAAEAAAAinAAA
(dictionary objn - 76674, file - 4, block - 2215, slot - 0)
Session 191: obj - rowid = 00012B82 - AAASuCAAEAAAAinAAB
(dictionary objn - 76674, file - 4, block - 2215, slot - 1)

If the deadlock is due to row-level locks being obtained in different orders then this section of the trace file indicates the exact rows that each session is waiting to lock for themselves. Ie: If the lock requests are TX mode X waits then the 'Rows waited on' may show useful information.
For any other lock type / mode the 'Rows waited on' is not relevant and usually shows as "no row".

In the above example:

SID 132 was waiting for ROWID 'AAASuCAAEAAAAinAAA' of object 76674
SID 191 was waiting for ROWID 'AAASuCAAEAAAAinAAB' of object 76674

This can be decoded to show the exact row/s.
Eg: SID 132 can be shown to be waiting thus:

SELECT owner, object_name, object_type FROM dba_objects WHERE object_id = 76674;

OWNER      OBJECT_NAM OBJECT_TYP
---------- ---------- ----------
DAVID       EG_60      TABLE

SELECT * FROM david.eg_60 WHERE ROWID='AAASuCAAEAAAAinAAA';

       NUM TXT
---------- ----------
         1 ses1

Section 3: Information on OTHER waiting session(s)

----- Information for the OTHER waiting sessions -----
Session 191:
sid: 191 ser: 5 audsid: 340002 user: 88/DAVID flags: 0x45
pid: 19 O/S info: user: davidx, term: UNKNOWN, ospid: 3163
    image: oracle@quad.xx (TNS V1-V3)
client details:
    O/S info: user: davidx, term: pts/3, ospid: 3097
    machine: quad.xx program: sqlplus@quad.xx (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
current SQL:
update eg_60 set txt='ses1' where num=2

This section displays information regarding the other sessions (apart from the session that produced the ORA-60 deadlock trace) that are involved in the deadlock. The information includes:

session details
client details
Current SQL
In this case: update eg_60 set txt='ses1' where num=2

Section 4: Information for this session

Information for THIS session:

----- Current SQL Statement for this session (sql_id=13b96yk6y5zny) -----
update eg_60 set txt='ses2' where num=1
===================================================
PROCESS STATE
-------------
.....

Displays the current sql for the session that creates the ORA-60 trace as well as a complete PROCESS STATE for the session.

Avoiding Deadlock

The above deadlock example occurs because the application which issues the update statements has no strict ordering of the rows it updates. Applications can avoid row-level lock deadlocks by enforcing some ordering of row updates. This is purely an application design issue.
Eg: If the above statements had been forced to update rows in ascending 'num' order then:

Session #1:          update eg_60 set txt='ses1' where num=1;
Session #2:          update eg_60 set txt='ses2' where num=1;
> Session #2 is now waiting for the TX lock held by Ses#1
Session #1:          update eg_60 set txt='ses1' where num=2;
> Succeeds as no-one is locking this row
commit;
                         > Session #2 is released as it is no longer waiting for this TX
Session #2: update eg_60 set txt='ses2' where num=2;
                           commit;

The strict ordering of the updates ensures that a deadly embrace cannot occur. This is the simplest deadlock scenario to identify and resolve. Note that the deadlock need not be between rows of the same table - it could be between rows in different tables. Hence it is important to place rules on the order in which tables are updated as well as the order of the rows within each table.Other deadlock scenarios are discussed below.

Different Lock Types and Modes

The most common lock types seen in deadlock graphs are TX and TM locks. These may appear held / requested in a number of modes. It is the lock type and modes which help determine what situation has caused the deadlock.

Lock Mode	Mode Requested	Probable Cause
TX	X (mode 6)	Application row level conflict. Avoid by recoding the application to ensure rows are always locked in a particular order.
TX	S (mode 4)	There are a number of reasons that a TX lock may be requested in S mode.
TM	SSX (mode 5) or S (mode 4)	This is usually related to the existence of foreign key constraints where the columns are not indexed on the child table.

Although other deadlock scenarios can happen the above are the most common.

TM locks - which object ?

ID1 of a TM lock indicates which object is being locked. This makes it very simple to isolate the object involved in a deadlock when a TM lock is involved.

The TM lock id is in the form TM-00012B85-00000000 where 00012B85 is the object number in hexadecimal format.

Convert 00012B85 from hexadecimal to a decimal number
Hexadecimal 00012B85 is Decimal 76677
Locate the object using DBA_OBJECTS

SELECT owner,object_name,object_type FROM dba_objects WHERE object_id= 76677;

OWNER OBJECT_NAM OBJECT_TYP
---------- ---------- ----------
DAVID EMP TABLE

This is the object id that the TM lock covers.
Note that with TM locks it is possible that the lock is already held in some mode in which case the REQUEST is to escalate the lock mode.

How to obtain Additional Information

If you are still having problems identifying the cause of a deadlock Oracle Support may be able to help. Additional information can be collected by adding the following to the init.ora parameters:

event="60 trace name errorstack level 3;name systemstate level 266"

or by setting events using alter system in which case the event will be set for the life of the Oracle instance and only for new sessions:

ALTER SYSTEM SET events'60 trace name errorstack level 3;name systemstate level 266';

Note that this can generate a very large trace file which may get
truncated unless MAX_DUMP_FILE_SIZE is large enough to accommodate the output.

When this is set any session encountering an ORA-60 error will write information about all processes on the database at the time of the error. This may help show the cause of the deadlock as it can show information about both users involved in the deadlock. Oracle Support will need
all the information you have collected in addition to the new trace file to help identify where in the application you should look for problems.

It may be necessary to run the offending jobs with SQL_TRACE or 10046 event enabled to show the order in which each session issues its commands in order to get into a deadlock scenario.

References

Note:62354.1 TX lock waits and why they occur
Note:33453.1 TM locks and Foreign Key Constraints
Note:38373.1 Example TM locks During Referential Integrity Enforcement

Which views can be used to detect locking problems?

A number of Oracle views permits to detect locking problems.

V$SESSION_WAIT
When a session is waiting on a resource, it can be found waiting on the enqueue wait event
Example:

SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'enqueue';

SID identifier of session holding the lock
P1, P2, P3 determine the resource when event = 'enqueue'
SECONDS_IN_WAIT gives how long the wait did occurs

V$SESSION
Session information and row locking information

SID, SERIAL# identifier of the session
EVENT event waited on
P1, P2, P3 determine the resource when event = 'enqueue'
# SECONDS_IN_WAIT gives how long the wait did occurs
LOCKWAIT address of the lock waiting, otherwise null
ROW_WAIT_OBJ# object identified of the object we are waiting on (object_id of dba_objects)
ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#
file_id , block_id and row location within block of the locked row

V$LOCK
List of all the locks in the system

SID identifier of session holding the lock
TYPE, ID1 and ID2 determine the resource
LMODE and REQUEST indicate which queue the session is waiting on, as follows:
LMODE > 0, REQUEST = 0 owner
LMODE = 0, REQUEST > 0 acquirer
LMODE > 0, REQUEST > 0 converter
CTIME time since current mode was converted
BLOCK are we blocking another lock
BLOCK = 0 non blocking
BLOCK = 1 blocking others

DBA_LOCK or DBA_LOCKS
Formatted view on V$LOCK (created via $ORACLE_HOME/rdbms/admin/catblock.sql)

SESSION_ID == SID in V$LOCK
LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
LAST_CONVERT == CTIME of V$LOCK
BLOCKING_OTHERS formatted value of BLOCK from V$LOCK

V$TRANSACTION_ENQUEUE
Subset of V$LOCK for the blocking TX resources only
(same description as for the V$LOCK view)

V$ENQUEUE_LOCK
Subset of V$LOCK for the system resources only and
blocked TX resources only. (same description as for the V$LOCK view)

DBA_DML_LOCKS
Subset of the V$LOCK for the DML (TM) locks only
Created via $ORACLE_HOME/rdbms/admin/catblock.sql
Same description as the DBA_LOCK view

V$LOCKED_OBJECT
Same info as DBA_DML_LOCKS, but linked with the rollback and session information

XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION
OBJECT_ID object being locked
SESSION_ID session id
ORACLE_USERNAME oracle user name
OS_USER_NAME OS user name
PROCESS OS process id
LOCKED_MODE lock mode

V$RESOURCE
List of all the currently locked resources in the system.
Each row can be associated with one or more rows in V$LOCK

TYPE, ID1 and ID2 determine the resource

DBA_DDL_LOCKS
Has a row for each DDL lock that is being held, and one row for each outstanding request for a DDL lock.
It is subset of DBA_LOCKS
Same description as the DBA_LOCK view

DBA_WAITERS
View that retrieve information for each session waiting on a
lock (created via $ORACLE_HOME/rdbms/admin/catblock.sql)