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