Checked for relevance on 8-16-2010
Introduction
~~~~~~~~~~~~
This short article describes how to determine the cause of ORA-60
"deadlock detected while waiting for resource" errors.
Note that 99% of the time deadlocks are caused by application or
configuration issues. This article attempts to highlight the most
common deadlock scenarios.
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.
Example of Deadlock
~~~~~~~~~~~~~~~~~~~
To reinforce the description the following simple test demonstrates a
a deadlock scenario. This is on Oracle 8.0.4 so if you are used to Oracle7
the ROWIDs may look a little strange:
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-60 error normally writes to the alert log.
And 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 and sometimes
to the background_dump_dest.
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.
Note that not all this information is produced in all Oracle releases.
-----------------------------------------------------------------------
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
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 Mode
Type 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) This is usually related to the existence of
or foreign key constraints where the columns
S (mode 4) 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.
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.
1. Given the TM lock id in the form TM-AAAAAAAA-BBBBBBBB
convert AAAAAAAA from hexadecimal to a decimal number
2. Locate the object using DBA_OBJECTS:
SELECT * FROM dba_objects WHERE object_id= NNNN;
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.
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"
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 enabled
to show the order in which each session issues its commands in order
to get into a deadlock scenario.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
