Troubleshooting “ORA-00060 Deadlock Detected” Errors

What is a 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.

The following example demonstrates a deadlock scenario.

Devamını oku: Troubleshooting “ORA-00060 Deadlock Detected” Errors

— Create test table
create table test ( num number, txt varchar2(10) );
insert into test values ( 1, ‘First’ );
insert into test values ( 2, ‘Second’ );

commit;
Session #1:

update test set txt=’ses1′ where num=1;

Session #2:

update test set txt=’ses2′ where num=2;
update test set txt=’ses2′ where num=1;

Session #2 is now waiting for the TX lock held by Session #1

Session #1:

update test set txt=’ses1′ where num=2;
Session #1 is now waiting on the TX lock for this row.

The lock is held by Session #2. However Session #2 is already waiting on Session #1

This causes a deadlock scenario so deadlock detection kicks in and one of the sessions signals an ORA-60.

Session #2:

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

If we want to check more details for our example we need to enable 10027 trace and re-run our scenario again.It will create trc file(detailed one)

SQL> ALTER SYSTEM SET EVENTS ‘10027 trace name context forever, level 2’;
System altered.

For more details please check:
Troubleshooting “ORA-00060 Deadlock Detected” Errors (Doc ID 62365.1)
How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace (Doc ID 1507093.1)



Yorum bırakın