handled dead lock step by step
Checked alter log
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/opti/udump/opti_ora_12120076.trc.
Checked the trace log:
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update run_pat set wgt_prod = wgt_prod + :1 where mill_id = :2 and machine_type
= :3 and machine_id = :4 and run_num = :5 and pat_num = :6 and prod_mach_id = :7
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)--------
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00020009-000354a1 33 1970 X 32 1764 X
TX-00120009-0000a101 32 1764 X 33 1970 X
session 1970: DID 0001-0021-00000002 session 1764: DID 0001-0020-00000016
session 1764: DID 0001-0020-00000016 session 1970: DID 0001-0021-00000002
Rows waited on:
Session 1764: obj - rowid = 00013432 - AAATQyAALAAACm/AAg
(dictionary objn - 78898, file - 11, block - 10687, slot - 32)
Session 1970: obj - rowid = 00013435 - AAATQ1AALAAACqMAAF
(dictionary objn - 78901, file - 11, block - 10892, slot - 5)
Information on the OTHER waiting sessions:
Session 1764:
pid=32 serial=1826 audsid=2443435 user: 70/OPTI
O/S info: user: msdmgr, term: CAOAS01, ospid: 26548:9268, machine: BRPPI\CAOAS
01
program: optiProcess.exe
application name: optiProcess.exe, hash value=0
Current SQL Statement:
update run_ords set wgt_prod = wgt_prod + :1 , units_prod = units_prod + :2 wh
ere diam_core = :3 and width_roll = :4 and order_id = :5 and item_num = :6 and d
iam_roll = :7 and machine_id = :8 and mill_id = :9 and machine_type = :10 and ru
n_num = :11
End of information on OTHER waiting sessions.
oracle:bres35 $ oerr ORA 00060
00060, 00000, "deadlock detected while waiting for resource"
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.
Check the lock conflict:
select l1.sid, ' IS BLOCKING ',l2.sid from gv$lock l1,
gv$lock l2 where l1.block =1 and l2.request > 0 and
l1.id1=l2.id1 and l1.id2=l2.id2
2 3 4 ;
no rows selected
from qury result .the deadlock has been handled by oracle automatically
Recommendation:
It 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 relevant sql statement as follows:
update run_pat set wgt_prod = wgt_prod + :1 where mill_id = :2 and machine_type= :3 and machine_id = :4 and run_num = :5 and pat_num = :6 and prod_mach_id = :7
Information on the OTHER waiting sessions:
Current SQL Statement:
update run_ords set wgt_prod = wgt_prod + :1 , units_prod = units_prod + :2 where diam_core = :3 and width_roll = :4 and order_id = :5 and item_num = :6
and diam_roll = :7 and machine_id = :8 and mill_id = :9 and machine_type = :10 and run_num = :11
you need tune the application to avoid this error!