关于唯一性索引造成堵塞和非唯一性索引造成堵塞的区别-CSDN博客

论坛的问题帖子：

http://www.itpub.net/thread-1523814-1-1.html

首先我们要理解TX锁中是通过访问块头的ITL然后来确定事物的，如果事物已经提交则完成，如果事物没有提交则等待.
我们可以查看ID1 ID2来确定事物的信息。
truncate(id1/power(2,16))
bitand(id1,to_number('ffff','xxxx'))+0
id2
分别对应v$transaction中的 XIDUSN，XIDSLOT，XIDSQN，当遭到堵塞的TX时候这里记录的是等待的相关事务。

通过实验发现：在日常的环境堵塞INSERT的情况不多，它不像DELETE UPTDAE，当修改同样的行的时候就会由于TX(X)堵塞，除非加入了唯一性索引，这里你使用的是主键，保证唯一性的实际是一个唯一

性索引。沿用你实验如下：
CREATE TABLE supplier
    (     supplier_id     number(10)     not null,
        supplier_name     varchar2(50)     not null,
        contact_name     varchar2(50),
        CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
    );
然后开启一个会话
INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');
然后再开启一个会话
INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');

当然在唯一性索引的作用下这个造成了堵塞
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
676E1298 676E12B0        130 TM        56010          0          3          0       1357          0
6773C9C4 6773CAE0        130 TX       196633       1456          6          0       1357          0
694531DC 694531F0        130 TX        65544       1088          0          4       1357          0
677309E8 67730B04        152 TX        65544       1088          6          0       1657          1
676E1344 676E135C        152 TM        56010          0          3          0       1657          0

可以发现152堵塞了130会话。这里第三行需要以共享模式(4)访问行数据，而第四行正在以独占模式（6）访问这个数据行。为什么要以共享模式访问数据行？
我个人认为是因为为了保证唯一性索引的唯一性必须要访问这行同时又不愿意别人以独占模式来或者这行，因为独占模式常常意味着要修改数据，如果别的会话正在修改这行，而当前会话却在以null模

式访问的话，那还能保证唯一性索引的唯一性吗？共享模式兼容共享模式，但是肯定是不兼容独占（6）的。同时我们这里还看到第而行，实际上这里修改数据的事物已经获得了行独占的TX锁（6），但

是只是为了保证唯一性索引还在等待中。。这个时候事物视图v$transaction已经开启了2个事物，分别用于2个会话的插入操作，但是130 SESSION的以共享模式获得行数据的事物还在等待。
SQL> select ADDR,XIDUSN,    XIDSLOT ,   XIDSQN from v$transaction;

ADDR         XIDUSN    XIDSLOT     XIDSQN
-------- ---------- ---------- ----------
677309E8          1          8       1088
6773C9C4          3         25       1456
大家可以换算一下
196633       1456 就是6773C9C4          3         25       1456
65544       1088   就是677309E8          1          8       1088

下面可以看看DELETE和UPDATE本身由于不再唯一性约束情况下更改相同行的情况。
SQL> create table ooo
2 as
3 select * from dba_users;

DELETE:

会话1启动delete ooo where USERNAME='DIP';
会话2启动delete ooo where USERNAME='DIP';
会话2堵塞

ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
676E1298 676E12B0        131 TM        56021          0          3          0         24          0
67726E00 67726F1C        131 TX       196655       1457          6          0         24          1
694531DC 694531F0        133 TX       196655       1457          0          6         15          0
676E1344 676E135C        133 TM        56021          0          3          0         15          0

可以看到这里就少了一个TX锁。133会话的TX锁只是在等待131TX锁的事物完成，因为要删除相同的行。通过块头的ITL发现131事物还没有提交。只有等待并且模式都是6.并且此时的事物只有一个，
不像上面的INSERT 事物有2个，因为上面堵塞实际上不是堵塞行的插入，实际上是堵塞唯一性约束的检查，所以2个修改的事物都已经启动。但是这里就是堵塞事物了。所以事物只有一个。

update:
会话1启动update ooo set PASSWORD='' where USERNAME='TEST';
会话1启动update ooo set PASSWORD='' where USERNAME='TEST';
会话2堵塞
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
676E1298 676E12B0        131 TM        56021          0          3          0         12          0
67726E00 67726F1C        131 TX       589829       1515          6          0         12          1
694531DC 694531F0        133 TX       589829       1515          0          6          3          0
676E1344 676E135C        133 TM        56021          0          3          0          3          0

查看事物：
SQL> select ADDR,XIDUSN,    XIDSLOT ,   XIDSQN from v$transaction;

ADDR         XIDUSN    XIDSLOT     XIDSQN
-------- ---------- ---------- ----------
67726E00          9          5       1515

和预想的一样

最后测试一下如果UPDATE遇到唯一性索引堵塞的情况。
CREATE TABLE supplier
    (     supplier_id     number(10)     not null,
        supplier_name     varchar2(50)     not null,
        contact_name     varchar2(50),
        CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
    );

INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');
INSERT INTO supplier VALUES (6, 'Supplier 6', 'Contact 6');
commit;

会话1: update supplier set supplier_id='1' where contact_name='Contact 5';
会话2：update supplier set supplier_id='1' where contact_name='Contact 6';
会话2堵塞
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
676E1298 676E12B0        131 TM        56022          0          3          0         63          0
6774DCC8 6774DDE4        131 TX       589848       1514          6          0         63          1
6774E1E4 6774E300        133 TX       327707       1491          6          0         57          0
694531DC 694531F0        133 TX       589848       1514          0          4         57          0
676E1344 676E135C        133 TM        56022          0          3          0         57          0

事物2个：
SQL> select ADDR,XIDUSN,    XIDSLOT ,   XIDSQN from v$transaction;

ADDR         XIDUSN    XIDSLOT     XIDSQN
-------- ---------- ---------- ----------
6774DCC8          9         24       1514
6774E1E4          5         27       1491

这也证明了我们的猜测。只是要涉及到唯一性索引的堵塞实际上不是堵塞行的操作，实际上是堵塞唯一性约束的检查，所以2个修改的事物都已经启动。

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

转载于:http://blog.itpub.net/7728585/viewspace-713029/