参考： http://www.xifenfei.com/2011/05/ ... AD%BB%E9%94%81.html 原因： 当ORACLE执行insert等DML语句时，会首先自动在所要操作的表上申请一个TM锁，当TM锁获得后，再自动申请TX类型的锁。当两个或多个会话在表的同一条记录上执行DML语言时，第一个会话在记录上加锁，其它的会话处于等待状态，一直到第一个会话提交后TX锁释放，其它的会话才可以加锁。考虑是因为两个insert语句同时试图向一个表中插入PK或unique值相同的数据，而造成其中会话被阻塞，等待其它会话提交或回滚，因而造成死锁。这种情况，只要其中任何一个session提交，另外一个就会报出ORA-00001：违反唯一性约束条件，死锁终止；或者其中一个session回滚，另外一个即可正常执行。(--引惜分飞博客) 模拟： session 1
| insert into t2 values(1,'aaa'); |
session 2
| insert into t2 values(2,'aaa'); |
session 1
| insert into t2 values(2,'aaa'); |
session 2
| insert into t2 values(1,'aaa'); |
错误浮现
|
SQL> insert into t2 values(2,'aaa'); insert into t2 values(2,'aaa') * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource |
trace文件：
|
Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-0001001d-00000310 34 42 X 32 49 S TX-000a0002-0000030b 32 49 X 34 42 S session 42: DID 0001-0022-00000008 session 49: DID 0001-0020-00000008 session 49: DID 0001-0020-00000008 session 42: DID 0001-0022-00000008 Rows waited on: Session 42: no row Session 49: no row ----- Information for the OTHER waiting sessions ----- Session 49: sid: 49 ser: 11 audsid: 240013 user: 80/SCOTT flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC pid: 32 O/S info: user: oracle, term: UNKNOWN, ospid: 1798 image: oracle@sol11node151 (TNS V1-V3) client details: O/S info: user: oracle, term: pts/1, ospid: 1794 machine: sol11node151 program: sqlplus@sol11node151 (TNS V1-V3) application name: SQL*Plus, hash value=3669949024 current SQL: insert into t2 values(1,'aaa') ----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=1qrs504d5jrg1) ----- insert into t2 values(2,'aaa') |
