参考: 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') |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28572479/viewspace-2168935/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28572479/viewspace-2168935/