oracle鎖阻塞的分析

此文章列舉三個關於鎖阻塞的例子，並對此作詳細的說明，話不多說，直接開題。

一：外來鍵沒有索引，引起阻塞
外來鍵沒有建立索引而引起的阻塞應該是最常見到，下面舉例對此詳細分析
SQL> DELETE EMP WHERE EMPNO=7900;
已刪除1行。
SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       10 TX      458791      1813         6         0       416         0
       10 TM       30139         0         3         0       416         0
       10 TM       30137         0         2         0       416         0

SQL> select trunc(458791/power(2,16)) ,mod(458791,power(2,16)) from dual;
TRUNC(458791/POWER(2,16)) MOD(458791,POWER(2,16))
------------------------- -----------------------
7                       39
再來查v$transaction，看看sid=10會話當前使用的undo segment和slot
SQL> select addr,xidusn,xidslot,xidsqn from v$transaction
2 where addr in (select taddr from v$session where sid=10);

ADDR        XIDUSN   XIDSLOT    XIDSQN
-------- ---------- ---------- ----------
67BAB0CC         7        39      1813

對於tx鎖，v$lock中的id1即為持有該鎖的事務的回滾段號，事務槽號的組合
再來看
SQL> select owner,object_name from dba_objects where object_id in (30139,30137);
OWNER                         OBJECT_NAME
------------------------------ --------------------------------------------------------------------------------
SCOTT                         DEPT
SCOTT                         EMP
對於tm鎖，id1即為持有該鎖的物件的id，結合上一個查詢，可以看到，該會話對30139即emp表加了lmode=3的鎖，即rx鎖，同時，對30137即dept加了lmode=2的鎖，即rs鎖。
接下來在另外一會話中執行如下語句
delete dept where1=0；
該會話被阻塞
檢視v$lock
SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');

      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       10 TX      458791      1813         6         0       416         0
       10 TM       30139         0         3         0       416         1
       10 TM       30137         0         2         0       416         0
       12 TM       30139         0         0         4       372         0
       12 TM       30137         0         3         0       372         0

可以看到，另一個會話(sid=12)對30137即dept加了lmode=3的鎖，即rx鎖。同時請求對30139即emp表加了lmode=4的鎖，即s鎖(request=4)，但該請求被阻塞(s和rx不相容)，可以通過id1,id2,,block欄位看到，此會話sid=10會話阻塞。

接下來，在emp的外來鍵欄位deptno建立索引
SQL> create index idx_emp_dept on emp(deptno);
Index created
按原步驟重複上面的實驗
SQL> DELETE EMP WHERE EMPNO=7900;
已刪除1行。
SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');
      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       10 TX      196632      2251         6         0       386         0
       10 TM       30139         0         3         0       386         0
       10 TM       30137         0         2         0       386         0

可以看到，是否建立索引，對emp表的鎖情況沒有影響。
在另外一個會話中執行如下語句
SQL> DELETE DEPT WHERE 1=0;
已刪除0行。
該語句沒有被阻塞，可以執行。看看當前的鎖資訊
SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');
      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       10 TX      196632      2251         6         0       435         0
       10 TM       30139         0         3         0       435         0
       10 TM       30137         0         2         0       435         0
       12 TM       30139         0         2         0         9         0
       12 TM       30137         0         3         0         9         0

可以看到，在外來鍵上建立索引後，刪除父表，對子表加的是rs鎖，比沒有索引加s的強度要低，這樣避免了阻塞的發生，提高了系統的並行性。

二：點陣圖索引帶來的阻塞
點陣圖索引適合建在低基數列上面，在資料倉儲比較常用，如果是在併發性要求較高的oltp系統就要慎重了，不當的應用，可能會帶來嚴重的阻塞。
SQL> create bitmap index bidx_emp_job on emp(job);
索引已建立。
SQL> DELETE EMP WHERE EMPNO=7369;
已刪除1行。
在另外一會話中執行sql語句
SQL> DELETE EMP WHERE EMPNO=7876;
該語句阻塞
SQL> Select sid,type,id1,id2,lmode,request,ctime,block From v$lock Where Type In ('TM','TX');
      SID TYPE       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       10 TX      524312      2362         6         0       116         1
       10 TM       30139         0         3         0       116         0
       10 TM       30137         0         2         0       116         0
       12 TX      589864      2399         6         0        98         0
       12 TM       30139         0         3         0        98         0
       12 TM       30137         0         2         0        98         0
       12 TX      524312      2362         0         4        98         0
可以看到，sid=12的會話等待sid=10持有的tx鎖，雖然刪除的是不同記錄，但因為
empno等於7876，7369的job欄位值都是’CLERK’,而我們恰恰在job欄位建立了點陣圖索引，第一個dml語句引起了對job=CLERK點陣圖段的鎖定，進而阻塞了第二個會話對該點陣圖段其他記錄的dml操作（注：如此時對job<>CLERK的記錄做dml是不會阻塞的）。

三：Maxtrans帶來的阻塞
在建立一個物件的時候，可以指定initrans，maxtrans引數，這二個引數指定了分配給該物件中每個block初始和最大允許併發事務數的，對每個事務，在其受影響的block內都對應一個itl，受其影響的行都會在行的lock byte（lb）位置此itl號（dump一個dml過的但還沒有commit或者rollback的block可以清楚的看到），如果在一個併發性非常高的系統中，甚至超過了maxtrans的值(或者block的free space用完)，那麼oracle就無法增加itl，因此過低的maxtrans同樣會引起sql語句的阻塞。
SQL> drop index bidx_emp_job;
索引已丟棄。
SQL> alter table emp maxtrans 3;
表已更改。
SQL>select distinct dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from emp;
FILE_ID  BLOCK_ID
---------- ---------
1               50466
所有的記錄都在1號檔案的50466塊，開三個session，分別刪除三條不同的記錄，三個session都可以正常執行，現在再開第四個session，刪除一個不同的記錄，該session被阻塞