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.