DeadLocks

Deadlocks

More Information Note:15476.1

What is 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.

Detect a DeadLock

--DISPLAY ALL PROBLEMATIC SESSIONS
SELECT (
SELECT username
FROM gv$session
WHERE sid=a.sid) blocker,
a.sid, ' is blocking ', (
SELECT username
FROM gv$session
WHERE sid=b.sid) blockee, b.sid
FROM gv$lock a, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2;

--SAME WITH MORE DETAILS
SELECT
   (SELECT USERNAME FROM v$session WHERE SID=a.SID) USERNAME_BLOCKER,
   (SELECT MACHINE FROM v$session WHERE SID=a.SID) MACHINE_BLOCKER,
   (SELECT PROGRAM FROM v$session WHERE SID=a.SID) PROGRAM_BLOCKER,
   (SELECT EXTERNAL_NAME FROM v$session WHERE SID=a.SID) EXTERNAL_NAME_BLOCKER,
   a.sid SID_BLOCKER,
   ' is blocking ',
   (SELECT USERNAME FROM v$session WHERE SID=b.SID) USERNAME_BLOCKEE,
   (SELECT MACHINE FROM v$session WHERE SID=b.SID) MACHINE_BLOCKEE,
   (SELECT PROGRAM FROM v$session WHERE SID=b.SID) PROGRAM_BLOCKEE,
   (SELECT EXTERNAL_NAME FROM v$session WHERE SID=b.SID) EXTERNAL_NAME_BLOCKEE,
   b.sid SID_BLOCKEE
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

Example of Deadlock
To reinforce the description the following simple test demonstrates a a row level deadlock scenario. Setup:

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
------------------ ---------- ----------
AAAAv2AAEAAAAqKAAA          1 First
AAAAv2AAEAAAAqKAAB          2 Second

Ses#1: update eg_60 set txt='ses1' where num=1;

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

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

Ses#1: update eg_60 set txt='ses1' where num=2;

This update would cause Ses#1 to wait on the TX lock
held by Ses#2, but Ses#2 is already waiting on this session.
This causes a deadlock scenario so one of the sessions
signals an ORA-60.

Ses#2: ORA-60 error

Ses#1: Still blocked until Ses#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
Although an ORA-60 error does not write information to the alert log the user that gets the ORA-60 error writes information to their trace file. The exact format of this varies between Oracle releases. The trace file will be written to the directory indicated by the USER_DUMP_DEST init.ora parameter.
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 Ses#2:
-----------------------------------------------------------------------
   DEADLOCK DETECTED
   Current SQL statement for this session:
                    update eg_60 set txt='ses2' where num=1

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-00020012-0000025e     12      11     X             11      10           X
   TX-00050013-0000003b     11      10     X             12      11           X
   session 11: DID 0001-000C-00000001      session 10: DID 0001-000B-00000001
   session 10: DID 0001-000B-00000001      session 11: DID 0001-000C-00000001
   Rows waited on:
   Session 10: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAB
   Session 11: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAA
   -----------------------------------------------------------------------

What does the trace information mean ?
In this section we explain each part of the above trace.
-----------------------------------------------------------------------
   DEADLOCK DETECTED
   Current SQL statement for this session:
                    update eg_60 set txt='ses2' where num=1
   -----------------------------------------------------------------------

This shows the statement which was executing which received the ORA-60 error. It is this statement which was rolled back.

-----------------------------------------------------------------------
   Deadlock graph:
                       ---------Blocker(s)-------- ---------Waiter(s)---------
   Resource Name       process session holds waits process session holds waits
   TX-00020012-0000025e     12      11     X             11      10           X
   TX-00050013-0000003b     11      10     X             12      11           X
   -----------------------------------------------------------------------

This shows who was holding each lock, and who was waiting for each lock. 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

So in this example:
SID 11 holds TX-00020012-0000025e in X mode
and wants TX-00050013-0000003b in X mode

SID 10 holds TX-00050013-0000003b in X mode
and wants TX-00020012-0000025e in X 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.

-----------------------------------------------------------------------
   Rows waited on:
   Session 10: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAB
   Session 11: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAA
   -----------------------------------------------------------------------

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 10 was waiting for ROWID 'AAAAv2AAEAAAAqKAAB' of object 0xBF6
(which is 3062 in decimal)

SID 11 was waiting for ROWID 'AAAAv2AAEAAAAqKAAA' of object 0xBF6

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

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

Owner Object_Name Object_Type
------- --------------- ---------------
SYSTEM EG_60 TABLE

SELECT * FROM system.eg_60 WHERE ROWID='AAAAv2AAEAAAAqKAAB';

NUM TXT
---------- ----------
2 Second

Block Level Locks
Everybody knows that Oracle was the first database to implement row-level locking instead of page locking or block locking, but in real life we still encounter page locking in very rare cases.
Application logic is not the only cause for Deadlocks ,Database Design is also responsible for that sometimes, it might be related to the way transaction layer storage parameters were set for the database objects.
Any database block has a transaction layer and a data layer, a good design will take into consideration both the expected number of records to be accommodated in one block and the maximum number of concurrent transactions accessing this block. If the data portion was increased by accommodating more records then this will
decrease the possibility of having the required number of concurrent transactions that might access a certain block and some of the transactions have to wait until others are completed , in this case a transaction layer lock will occur and ora-60 is likely to be reported.
Two parameters are responsible about that , INITRANS which is the initial number of concurrent transactions that access one block and MAXTRANS which is the maximum number of concurrent transactions that may access the same block. For example, if a table is very large and only a small number of users simultaneoulsy access the table, the chances of multiple concurrent transactions requiring access to the same data block is low. Therefore, INITRANS can be set low, especially if space is an issue with the database.
Alternatively, assume that a small table is usually accessed by many users at the same time. In this case, you might consider preallocating transaction entry space by using a high INITRANS (to eliminate the overhead of having to allocate transaction entry space, as required when the object is in use)and allowing a higher MAXTRANS so that no users have to wait to access certain hot blocks.
Its possible to get an ora-60 deadlock when more than one transaction does DML on a block if INITRANS was set too low. If there are more simultaneous transactions than slots then the transaction layer needs to grow from INITRANS towards MAXTRANS, but it can't if the rest of the block is packed with data. In
this case the transaction that could not get a slot will either hang or get an ora-60 depending on the particular circumstances. This is an issue with tables, indexes, and clusters.

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:

Ses#1: update eg_60 set txt='ses1' where num=1;

Ses#2: update eg_60 set txt='ses2' where num=1;
Ses#2 is now waiting for the TX lock held by Ses#1

Ses#1: update eg_60 set txt='ses1' where num=2;
Succeeds as no-one is locking this row
commit;
Ses#2 is released as it is no longer waiting for this TX

Ses#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 Type

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. See [NOTE:62354.1] for a list of when TX locks are requested in mode 4.

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.See [NOTE:33453.1] for how to locate such constraints. See below for locating the OBJECT being waited on.

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

Not Indexed FK
Suppose I have the following tables:
PARENT (
colp number not null primary key,
col2 number);

CHLD(
colp number,
colx number,
constraint fk_colp foreign key (colp) references PARENT(colp));

Assume that I do not have aindex defined on "colp" of CHILD.

Now when I delete/insert/update a record from CHILD table, what kind of Lock it acquires on the parent table?
What is the case when the operation is on the PARENT table?

It's important to ensure that the foreign key of the child (detail) table is indexed. Without this index, any inserts, deletes or updates (foreign key or otherwise) made to the child table will result in a share lock being taken out on the parent table

Check to see if any parent/child relationships are involved. An SSX lock is acquired on the parent table when a child is being inserted into or updated, and there is no index on the foreign key.
To reduce deadlocking on parent/child tables, create an index on the columns that make up the foreign key. This will avoid extraneous locking on the tables involved.

Lock Type	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. See [NOTE:62354.1] for a list of when TX locks are requested in mode 4.
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.See [NOTE:33453.1] for how to locate such constraints. See below for locating the OBJECT being waited on.