唯一索引导致的死锁

一个事务可以通过回滚段号+槽号+序列号被唯一标示。

V$LOCK中ID1代表的是回滚段号+槽号，ID2代表序列号。

举例：假如ID1为1048576，根据以下方法可以换算成回滚段号和槽号。ID2不需要换算。

QL> select trunc(1048576/65536),mod(1048576,65536) from dual;

TRUNC(1048576/65536) MOD(1048576,65536)
-------------------- ------------------
                  16                  0

SQL> select XIDUSN,XIDSLOT,XIDSQN from v$transaction;--------------------查看事务表，XIDUSN代表回滚段号，XIDSLOT代表槽号。跟以上换算结果一致。

XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
        16          0     690881

事务没结束,会保护它所涉及的所有资源.

 

创建一个表，并在ID上建立唯一索引。

apollo@CRMG>create table wxh_tbd (id number);

 

Table created.

 

apollo@CRMG>create unique index xx_bt on wxh_tbd(id);

 

Index created.

 

1)  ORACLE不会对唯一索引的下一键值加锁。不同的SESSION 如果插入不同的值，可以获得各自的资源。（资源以ID1,ID2唯一标识）。典型的DML操作，一般都要获取SUB-EXCLUSIVE的TM锁，用来保护表结构，这种锁模式之间可以共享，因此可以对表进行并发的DML。还需要获得X模式的TX锁，保护事务。这种锁模式之间是互斥的（得ID1,ID2相同才行，不同资源的X模式的锁不会互斥）。

 

SESSION 1 ,SID=2867，执行如下语句:

 

apollo@CRMG>insert into wxh_tbd values(1);

 

1 row created.

 

apollo@CRMG>select * from v$lock where (sid=(select sid from v$mystat where rownum=1) or sid=1268) and type<>'AE' ORDER BY SID;

 

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

000000009E135F88 000000009E136000       2867 TX       131075     672232          6          0        380          0

0000002A96FF79E8 0000002A96FF7A48       2867 TM       145617          0          3          0        380          0

 

 

SESSION 2,SID=1268，执行如下语句

 

apollo@CRMG>insert into wxh_tbd values(2);

 

1 row created.

 

apollo@CRMG>/

 

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

00000000B41F62D8 00000000B41F6350       1268 TX      1310738     679249          6          0          9          0

0000002A96FF79E8 0000002A96FF7A48       1268 TM       145617          0          3          0          9          0

000000009E135F88 000000009E136000       2867 TX       131075     672232          6          0        486          0

0000002A96FF79E8 0000002A96FF7A48       2867 TM       145617          0          3          0        486          0

 

SESSION 1和SESSION 2都各自获得了自己的资源。因为他们需要SUB-EXCLUSIVE型的TM资源，他们之间不互斥。

需要获得的TX资源，虽然都为X模式，但是不是同一资源（ID1,ID2判断），也不互斥。

 

2）接着上面的继续。

SESSION 1，插入一个值2，这个2，上面已经在SESSOIN 2插入过了，没提交。

 

apollo@CRMG>insert into wxh_tbd values(2);-------------------------------------------会产生等待。

 

apollo@CRMG>select * from v$lock where (sid=2867or sid=1268) and type<>'AE' ORDER BY SID;

 

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

00000000B41F62D8 00000000B41F6350       1268 TX      1310738     679249          6          0        642          1

0000002A96FF6AB0 0000002A96FF6B10       1268 TM       145617          0          3          0        642          0

000000009E135F88 000000009E136000       2867 TX       131075     672232          6          0       1119          0

000000009C79FB80 000000009C79FBD8       2867 TX      1310738     679249          0          4         56          0

0000002A96FF6AB0 0000002A96FF6B10       2867 TM       145617          0          3          0       1119          0

 

SESSION 2拥有资源ID1=1310738,ID2=679249。

由于SESSION 1需要获得SESSION 2的ID1=1310738,ID2=679249的资源而发生等待。而且请求的锁模式是4，即共享模式。

SESSION 1请求的共享模式S和SESSION 2拥有的X模式不兼容，因此发生等待。

这里ORACLE通过什么实现的这种锁，我不清楚，可能象我们想的通过脏读，发现已经插入2了，就等待2的资源。

 

 

3）如果这个时候，SESSION 2再插入1.那么死锁就会发生。

 

查看跟踪文件，这个时候你就会发现一个比较有意思的现象，由于都请求双方的共享锁而发生的死锁。可能很多人一直以为死锁都是X型产生的，死锁与锁模式无关。

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-00020003-000a41e8       551    2867     X            686    1268           S

TX-00140012-000a5d51       686    1268     X            551    2867           S

 

session 2867: DID 0001-0227-0000BDCC    session 1268: DID 0001-02AE-00001EA9

session 1268: DID 0001-02AE-00001EA9    session 2867: DID 0001-0227-0000BDCC

 

Rows waited on:

  Session 2867: obj - rowid = 000238D1 - AAAjjRABFAAANPIAAA

  (dictionary objn - 145617, file - 69, block - 54216, slot - 0)

  Session 1268: obj - rowid = 000238D2 - AAAjjSABFAAANPTAAA

  (dictionary objn - 145618, file - 69, block - 54227, slot - 0)

 

 

4）延伸一下。加入我们退回到步骤2。SESSION 2提交的话，是一个报错，唯一索引冲突

 

如果回滚呢？

会顺利插入。重点看下，回滚前后，V$LOCK的变化。

回滚前：

apollo@CRMG>select * from v$lock where (sid=1547 or sid=1268) and type<>'AE' ORDER BY SID;

 

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

0000002A970730B0 0000002A97073110       1268 TM       145617          0          3          0         52          0

00000000B41F62D8 00000000B41F6350       1268 TX       524319     842480          6          0         52          1

00000000B3EB08E0 00000000B3EB0958       1547 TX       131097     672790          6          0          9          0

0000002A970730B0 0000002A97073110       1547 TM       145617          0          3          0          9          0

00000000B27BC828 00000000B27BC880       1547 TX       524319     842480          0          4          9          0

 

 

回滚后：

apollo@CRMG>select * from v$lock where (sid=1547 or sid=1268) and type<>'AE' ORDER BY SID;

 

ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

00000000B3EB08E0 00000000B3EB0958       1547 TX       131097     672790          6          0         47          0

0000002A96FF6AB0 0000002A96FF6B10       1547 TM       145617          0          3          0         47          0

 

SESSION 1将请求的ID1=524319,ID2=842480的资源获取后，替换为自身的事务信息，这是因为锁所保护的事务资源已经被修改了。

 

11.jpg

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