A m Z o n e

The cause of ORA-00060 deadlock

How is ORA-00060 deadlock encountered? What is the cause?

ORA-00060: deadlock detected while waiting for resource

Deadlock occurs when two sessions get interlaced regarding the resources they want to acquire. This results in a conflict of resources acquired and to be acquired.
For example, Session A locks the first record in table ONE.
Session B locks the fifth record in table TWO.
Session A now tries to update the fifth record of table TWO (already locked by session B).
Session B also tries to update the first record of table ONE (already locked by session A).
Both sessions wait for each other to release the wanted resource, resulting in a deadlock.

Autonomous transactions can also result in deadlocks, like the below example

 SQL>create or replace trigger am07 
   2  after insert or update or delete on am07 
   3  for each row 
   4  declare 
   5     l_chk pls_integer; 
   6     pragma autonomous_transaction; 
   7  begin 
   8    --update am07 set col1 = 1 where col1 = 1; 
   9    --insert into am07 values (2); 
  10    --select 1 into l_chk from am07 where col1 = 3; 
  11    delete from am07 where col1 = 3; 
  12  exception 
  13     when no_data_found then null; 
  14* end; 
 SQL>/ 

 Trigger created. 

 SQL>delete from am07 where col1 = 3; 
 delete from am07 where col1 = 3 
             * 
 ERROR at line 1: 
 ORA-00060: deadlock detected while waiting for resource 
 ORA-06512: at "RAPID.AM07", line 8 
 ORA-04088: error during execution of trigger 'RAPID.AM07' 

Press the Back button of you Browser to go to previous page
Home