[20180801]insert导致死锁.txt

[20180801]insert导致死锁.txt --//链接http://www.itpub.net/thread-2104135-2-1.html的讨论,自己有点疏忽了,插入主键相同也会导致死锁. --//自己按照链接http://www.xifenfei.com/2011/05/insert%E5%BC%95%E8%B5%B7%E7%9A%84%E6%AD%BB%E9%94%81.html --//自己测试看看: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0 2.测试: --//session 1: SCOTT@test01p> SCOTT@test01p> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------         86         73 6388:6512                DEDICATED 6448                      57          6 alter system kill session '86,73' immediate; CREATE TABLE t1(ID NUMBER); ALTER TABLE t1 ADD primary key (ID); INSERT INTO t1 VALUES(1); --//session 2: SCOTT@test01p> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------        237         59 6988:424                 DEDICATED 5872                      15          6 alter system kill session '237,59' immediate; INSERT INTO t1 VALUES(2); --//现在没事没有冲突. --//回到session 1: INSERT INTO t1 VALUES(2); --//挂起,但是没有死锁. --//回到session 2: INSERT INTO t1 VALUES(1); --//session 1出现如下提示: SCOTT@test01p> INSERT INTO t1 VALUES(2); INSERT INTO t1 VALUES(2) * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource --//但是session 2一样会挂起.因为主键冲突还存在. --//session 1: SCOTT@test01p> commit ; Commit complete. --//session 2: SCOTT@test01p> INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(1) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SYS_C0011640) violated --//提示主键冲突.感觉应该在应用很少出现这样的情况. 3.看看转储: *** 2018-08-01 20:24:23.459 *** SESSION ID:(86.73) 2018-08-01 20:24:23.459 *** CLIENT ID:() 2018-08-01 20:24:23.459 *** SERVICE NAME:(test01p) 2018-08-01 20:24:23.459 *** MODULE NAME:(SQL*Plus) 2018-08-01 20:24:23.459 *** ACTION NAME:() 2018-08-01 20:24:23.459 *** CONTAINER ID:(3) 2018-08-01 20:24:23.459   *** 2018-08-01 20:24:23.459 DEADLOCK DETECTED ( ORA-00060 )   [Transaction Deadlock]   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 *** 2018-08-01 20:24:23.460 information may aid in determining the deadlock:   Deadlock graph:                                           ---------Blocker(s)--------  ---------Waiter(s)--------- Resource Name                             process session holds waits  process session holds waits TX-00100015-0000060F-00000000-00000000         57      86     X             15     237           S TX-00070013-00005F4E-00000000-00000000         15     237     X             57      86           S   session 86: DID 0001-0039-00000014    session 237: DID 0001-000F-0000000A session 237: DID 0001-000F-0000000A    session 86: DID 0001-0039-00000014   Rows waited on:   Session 86: no row   ~~~~~~~~~~~~~~~~~~~   Session 237: no row  ~~~~~~~~~~~~~~~~~~~~ ----- Information for the OTHER waiting sessions ----- Session 237:   sid: 237 ser: 59 audsid: 26211525 user: 109/SCOTT   pdb: 3/TEST01P     flags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-     flags2: (0x40009) -/-/INC   pid: 15 O/S info: user: oracle, term: XXX, ospid: 5872     image: ORACLE.EXE (SHAD)   client details:     O/S info: user: XXX\Administrator, term: XXX, ospid: 6988:424     machine: WORKGROUP\XXX program: sqlplus.exe     application name: SQL*Plus, hash value=3669949024   current SQL:   INSERT INTO t1 VALUES(1)   ----- End of information for the OTHER waiting sessions -----   Information for THIS session:   ----- Current SQL Statement for this session (sql_id=0s3pjym30ya3w) ----- INSERT INTO t1 VALUES(2) =================================================== --//太长.. --//链接http://www.itpub.net/thread-2104135-1-1.html比较牛,有4个会话串在一起. --//实际上我自己有点疑惑的地方: Rows waited on:   Session 86: no row   Session 237: no row --//no row,按照道理应该有行记录.没想到主键冲突会出现这样的情况. --//链接的错误应该类似这样: --//sesion 1: SCOTT@test01p(86,73)> INSERT INTO t1 VALUES(1); 1 row created. --//sesion 2: SCOTT@test01p(237,59)> INSERT INTO t1 VALUES(2); 1 row created. --//sesion 3: SCOTT@test01p(161,69)> INSERT INTO t1 VALUES(3); 1 row created. --//sesion 4: SCOTT@test01p(156,71)> INSERT INTO t1 VALUES(4); 1 row created. --//然后分别插入,就会分别挂起!! --//sesion 1: SCOTT@test01p(86,73)> INSERT INTO t1 VALUES(2); --//sesion 2: SCOTT@test01p(237,59)> INSERT INTO t1 VALUES(3); --//sesion 3: SCOTT@test01p(161,69)> INSERT INTO t1 VALUES(4); --//sesion 4: SCOTT@test01p(156,71)> INSERT INTO t1 VALUES(1); *** 2018-08-01 20:50:46.479 DEADLOCK DETECTED ( ORA-00060 )   [Transaction Deadlock]   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)--------  ---------Waiter(s)--------- Resource Name                             process session holds waits  process session holds waits TX-000B0005-000013E2-00000000-00000000         57      86     X             58     156           S TX-000A0007-000065E1-00000000-00000000         58     156     X             38     161           S TX-00090002-00006203-00000000-00000000         38     161     X             15     237           S TX-000F001B-00000835-00000000-00000000         15     237     X             57      86           S   session 86: DID 0001-0039-00000014    session 156: DID 0001-003A-0000000A session 156: DID 0001-003A-0000000A    session 161: DID 0001-0026-0000005B session 161: DID 0001-0026-0000005B    session 237: DID 0001-000F-0000000A session 237: DID 0001-000F-0000000A    session 86: DID 0001-0039-00000014   Rows waited on:   Session 86: no row   Session 156: no row   Session 161: no row   Session 237: no row --//session 1(86)  阻塞 session 4(156) --//session 4(156) 阻塞 session 3(161) --//session 3(161) 阻塞 session 2(237) --//session 2(237) 阻塞 session 1(86) --//这样就形成环. --//不过好像对方的情况更复杂!!