關於唯一性索引造成堵塞和非唯一性索引造成堵塞的區別

論壇的問題帖子：

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個修改的事物都已經啟動。