OERR: ORA 60 "deadlock detected while waiting for resource" [ID 18251.1]（oracle deadlock 学习资料-03 死锁分析步骤）

Error:  ORA 60



Text:   deadlock detected while waiting for resource



-------------------------------------------------------------------------------



Cause:  Transactions deadlock one another waiting for resources



Action: Look at the trace file to see the transactions and resources



        involved. Retry if necessary.



 



*** Important: The notes below are for experienced users - See Note:22080.1










** For CUSTOMER issues with ORA-60 send them:      Note:62365.1






   This details the most common ORA-60 scenarios.







Explanation:



        A deadlock has been detected so your statement has been rolled



        back to allow the other party to continue.







Diagnosis:



        Note the lock TYPES and MODES in the DEADLOCK graph.



	   The most common deadlocks involve one of the following:



		Lock type  Mode  Problem



		   TX	     X   Application row lock deadlock.See below.



		   TX        S   Many reasons - see Note:62354.1






		   TM	    SSX	 Usually unindex foreign key constraints.











	TX  requested X mode:



	~~~~~~~~~~~~~~~~~~~~~



        This is usually an application design issue in that transactions



        are modifying tables A and B in opposite orders.



        Eg: 1 updates A



            2 updates B



            1 tries to update B but blocks



            2 tries to update A but blocks



            ** Deadlock as neither 1 nor 2 will ever complete.







        a) Has ANYTHING in the schema been changed (Eg: A foreign key INDEX



           may have been dropped) as this could affect locking of tables.







        b) There should be a user trace with the deadlock TX information



           in USER_DUMP_DEST. This will indicate who was involved in the



           deadlock







        c) Is the insert into a clustered table ?  If so see Bug:197942










        d) What are MAXTRANS / INITRANS for the tables in the deadlock ?











Articles / Known Issues:



       ORA-60 against UET$	(Fixed 7.2)		      Bug:231455






       ORA-60 from  ANALYZE ... VALIDATE ... command:



		NOTE: It is possible to get this if the dictionary has



			been analyzed and has statistics present. Delete



			the statistics.







	V6: If this occurs on startup it is likely that a file may be missing. 



        There should be a trace file produced and an entry in the alert log 



        which will tell you the location of the problem file. Either:



		a) restore access to the datafiles



	OR	b) offline the datafiles and drop the tablespace when it 



		   opens to clean up the data dictionary.



		   (obviously you have lost all the data in this case)