advertisements
_____________________________________________________________________________________________________________________
Overview:
This error is due to the resource contention between two sessions. It indicates that a dead lock happened due to the resource contention with other session. Oracles itself does a rollback on your current session and resolve the error. Other session can proceed as usual. The alert.log will record the error with all details. Current session will be rolled back and need to resubmitted once the required resources are available.
Deadlock detected while waiting for resource. In most of the cases the deadlock are caused by application errors.
Deadlock detected while waiting for resource. In most of the cases the deadlock are caused by application errors.
Dead lock
Dead lock happens when a session (sess1) wants resource locked by another session (sess2), But that session also wants the resource which is locked by sess1. These can be happened between more than two sessions also with same kind of scenario.
Dead Lock Example
create table temp ( num number, txt varchar2(10) );
insert into temp values ( 1, 'First' );
insert into temp values ( 2, 'Second' );
commit;
select rowid, num, txt from temp;
ROWID NUM TXT
------------------ ---------- ----------
AAAAv2AAEAAAAqKABC 1 First
AAAAv2AAEAAAAqKABD 2 Second
Sess1: update temp set txt='ses1' where num=1;
Sess2: update temp set txt='ses2' where num=2;
update temp set txt='ses2' where num=1;
> Sess2 is now waiting for the TX lock held by Sess1
Sess1: update temp set txt='sess1' where num=2;
This update would cause Sess1 to wait on the TX lock held by Sess2, but Sess2 is already waiting on this session. This causes a deadlock scenario so one of the sessions signals an ORA-60.
Sess2: ORA-60 error
Sess1: Still blocked until Sess2 completes the transaction (either commits or rolls back) as ORA-60 only rolls back the current statement and not the entire transaction.
How to check the error details
Ora-00060 error normally records the details in alert.log and also in the trace file. The trace file will be created in USER_DUMP_DEST and sometimes in background_dump_dest. Trace file will contain a deadlock graph and additional information.
-----------------------------------------------------------------------
DEADLOCK DETECTED
Current SQL statement for this session:
update temp set txt='ses2' where num=1
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00020012-0000025e 12 11 X 11 10 X
TX-00050013-0000003b 11 10 X 12 11 X
session 11: DID 0001-000C-00000001 session 10: DID 0001-000B-00000001
session 10: DID 0001-000B-00000001 session 11: DID 0001-000C-00000001
Rows waited on:
Session 10: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKABC
Session 11: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKABD
-----------------------------------------------------------------------
So in this example:
SID 11 holds TX-00020012-0000025e in X mode
and wants TX-00050013-0000003b in X mode
SID 10 holds TX-00050013-0000003b in X mode
and wants TX-00020012-0000025e in X mode
The important things to note here are the LOCK TYPE, the MODE HELD and the MODE REQUESTED for each resource as these give a clue as to the reason for the deadlock.
-----------------------------------------------------------------------
Rows waited on:
Session 10: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKABC
Session 11: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKABD
-----------------------------------------------------------------------
In the above example:
SID 10 was waiting for ROWID 'AAAAv2AAEAAAAqKAAB' of object 0xBF6
(which is 3062 in decimal)
SID 11 was waiting for ROWID 'AAAAv2AAEAAAAqKAAA' of object 0xBF6
How to avoid DeadLock
In the above example the error occurs because of the application which issues the update statements has no strict ordering of the rows it updates. Applications can avoid row-level lock deadlocks by enforcing some ordering of row updates. This is purely an application design issue. Strict ordering of the updates avoids the deadlock situation. Dead lock need not be between the rows between the same tables.
In case the deadlock cannot be identified after checking the application thoroughly, you can enable tracing with event="60 trace name errorstack level 3;name systemstate level 266"
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment