ITPub博客

首页 > Linux操作系统 > Linux操作系统 > handled deadlock step bu step

handled deadlock step bu step

原创 Linux操作系统 作者:lorikyo 时间:2019-06-19 10:48:09 0 删除 编辑

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!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/92530/viewspace-497942/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-07-18

  • 博文量
    211
  • 访问量
    155548