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'
Best viewed in medium text size. Please refresh this page (F5) to view the latest information. This page was create on 15-dec-2000 and last updated on 15-dec-2000.
please forward all queries to amar_padhi@fastmail.fm