Oracle 两张表死锁

background_dump_dest		     string	 /oracle/diag/rdbms/oadb/oadb/trace


SQL> select * from t100;

	ID
----------
       100

SQL> select * from t200;

	ID
----------
       200


SESSION 1715:

SQL> update t100 set id=1 where id=100;

已更新 1 行。





另起一个SESSION:

SESSION 1155：


SQL> update t200 set id=2 where id=200;

已更新 1 行。



SESSION 1715:

继续执行:
 update t200 set id=2 where id=200;

此时hang

SESSION 1155：
SQL> update t100 set id=1 where id=100;


此时之前HANG的1715 报

SQL>  update t200 set id=2 where id=200;
 update t200 set id=2 where id=200
*
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁


查看trc：

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00080018-0000068c        19    1715     X             22    1155           X
TX-0007000d-000005bb        22    1155     X             19    1715           X



Rows waited on:
  Session 1715: obj - rowid = 000170DF - AAAXDfAAFAAApWnAAA
  (dictionary objn - 94431, file - 5, block - 169383, slot - 0)
  Session 1155: obj - rowid = 000170DE - AAAXDeAAFAAApWfAAA
  (dictionary objn - 94430, file - 5, block - 169375, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 1155:
  sid: 1155 ser: 7515 audsid: 184910 user: 84/VXSPACE
    flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 22 O/S info: user: oracle, term: UNKNOWN, ospid: 23860
    image: oracle@oadb (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/2, ospid: 23859
    machine: oadb program: sqlplus@oadb (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  update t100 set id=1 where id=100

----- End of information for the OTHER waiting sessions -----



Information for THIS session:

----- Current SQL Statement for this session (sql_id=chj0k88bb341f) -----
 update t200 set id=2 where id=200