What to do with "ORA-60 Deadlock Detected" Errors [ID 62365.1](oracle deadlock 学习资料-05.1)


 

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.







评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值