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.