引言：Oracle 鎖在我一開始接觸的時候會有一種高深莫測的感覺，就像是遙遠的外星人看不見摸不著但是能感覺到，我在實際的工作中就遇到過ORA-00054: resource busy acquire with nowait specified錯誤不能插入表，當時知道是被鎖定了，根據V$LOCK也定位出阻塞的會話了，但不知道如何長久的解決它，究其原因就是不清楚內部機制與釋放原理，下面根據例子來揭開鎖的面紗，走進Oracle鎖的世界。

分別模擬insert，update和delete造成阻塞的示例，並對v$lock中的相應的資訊進行說明，給出SQL演示。

> create user leo2 identified by leo2 default tablespace leo1;    新建立一個LEO2使用者
User created.
> grant connect,resource to leo2;                                           授予基本許可權
Grant succeeded.
> select owner,table_name,tablespace_name from dba_tables where wner='LEO1';
OWNER                         TABLE_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
LEO1                            LEO_WAGE                      LEO1

> create table t1 (id int primary key);    建立t1表，設定id列為主鍵
Table created.
> insert into t1 values(1);
1 row created.
> select * from t1;
      ID
----------
      1
> insert into leo1.t1 values(1);          當沒有提交，在插入同樣的values時就發生了對會話的阻塞，hang在這裡不能前進
> commit;                                        必須提交後，阻塞才終止，也就是說commit可以釋放阻塞
Commit complete.
> insert into leo1.t1 values(1);          因為已經有了1值，故違反了主鍵約束
insert into leo1.t1 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (LEO1.SYS_C0010817) violated

update 鎖阻塞

> select * from t1;
      ID
----------
      1
      2
   100
> update t1 set id=200 where id=100;    更新一行沒有提交，沒有提交的事物對別人是不可見的，但在物理塊上真真切切的修改了，他人只能訪問undo回滾段中映象
1 row updated.
> update leo1.t1 set id=300 where id=100; 我們在會話leo2上也更新同一個表裡的同一行，此時hang住了不動了，因為2個會話在爭用同一條記錄的修改權
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;       一般影響業務效能的就 TM and TX 鎖
   SID TY       ID1       ID2    LMODE REQUEST    BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
   138 TM    73449       0       3       0       0
   138 TX    196615    912       6       0       1
   156 TM    73449       0       3       0       0
   156 TX    196615    912       0       6       0

SID：會話id號
TYPE：鎖的型別
ID1：會話操作物件的id號
ID2：ID1+ID2 定位回滾段上的一個地址（即修改之前資料映象地址），由於138和156會話是一樣的說明指向的是同一個地址，換句話說操作的是同一行資料
LMODE：鎖模式，不同的數字代表不同的鎖模式  例如  0 現在沒有申請到鎖 3  共享鎖模式（段級共享鎖） 6  排他鎖模式鎖的級別越高限制越多
REQUEST：目前會話沒有鎖，正在申請的鎖模式  例如  0 沒有正在申請的鎖，說明已經有鎖了 6  現在正在申請6號鎖，目前因為沒有才申請
BLOCK：當前正在阻塞幾個會話  例如  1  當前正在阻塞一個會話  2  當前正在阻塞兩個會話
鎖的實質：是維護一個事務完整性的，鎖的資訊是資料塊的一個屬性，是物理的，並不是邏輯上屬於某個表或者某幾行的。

> select distinct sid from v$mystat;       這個會話當前id是138，我們怎麼區分呢？=> LEO1使用者=138    LEO2使用者=156
   SID
----------
   138
> select object_name from dba_objects where object_id=73449;                   138會話操作的物件是T1表
OBJECT_NAME
--------------------------------------------------------------------------------
T1
說明：138會話在T表上加了TM和TX鎖，TM鎖模式為3（共享鎖） TX鎖模式6（排他鎖），目前TX鎖正在阻塞一個會話（就是156會話）。
156會話就是當前被阻塞的會話，156會話操作物件也是T1表（ID1都一樣的），TM鎖模式也為3（共享鎖就是有幾個會話就可以建立幾個共享鎖，同時存在），TX現在還沒有申請到鎖，正在申請6號鎖，而這個6號鎖就是138會話所持有的（因為2個會話操作的是同一行資料）
> select sid,event from v$session_wait where sid in (138,156);       從會話等待檢視上可以看出，有哪些會話由於什麼原因導致等待事件不能前進
   SID EVENT
---------- ----------------------------------------------------------------
   138 SQL*Net message to client
   156 enq: TX - row lock contention
156會話由於TX鎖爭用原因導致hang住不能前進，enq=enqueues佇列鎖（通常和業務有關，為了保護業務的鎖）
小結：現在我們應該很晴朗的看出138會話阻塞156會話，以及阻塞的原因和會話數和鎖型別

insert 鎖阻塞

> select * from leo1.t1;
      ID
----------
      1
      2
   200
> insert into leo1.t1 values(3);    插入一行但沒有提交，這是一個未決狀態，還不清楚是否真正插入
1 row created.
> insert into leo1.t1 values(3);    我們在會話leo2上也插入同樣的資料，此時hang住了不動了，這裡實際上是插入了2條獨立的記錄，不能認為是同一條記錄，只是值一樣
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TY       ID1       ID2    LMODE REQUEST    BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
   138 TM    73449       0       3       0       0
   138 TX    65555       681       6       0       1       138正在阻塞另一個會話
   156 TM    73449       0       3       0       0
   156 TX    458766    669       6       0       0       這也顯示了在插入第二條記錄時沒有被阻塞
   156 TX    65555       681       0       4       0       而在插入的修改值相同後被阻塞了，鎖的級別是4
insert時v$lock檢視裡面多了一個TX鎖（就是最後一行），首先說明一下insert和update delete 操作的不同，後兩者都是對同一條記錄的修改權爭用產生阻塞（這裡不涉及修改值的問題），而insert操作實際上插入了2條不同的記錄，由於這2條不同的記錄的修改值一樣違反了主鍵約束從而產生阻塞，實際是對修改值的相同產生了阻塞。鎖的級別為4，這種鎖比update的鎖級別要低，鎖的級別越低限制越少。

delete 鎖阻塞

> select * from leo1.t1;                t1表中有5條記錄，我們計劃刪除的是最後1條
      ID
----------
      1
      2
      4
      5
   200
> delete from leo1.t1 where id=200;    138會話正在刪除id=200的記錄，但是沒有提交，此時就是加上一個TM TX鎖
1 row deleted.
> delete from leo1.t1 where id=200;    這時158會話也做同樣的動作，就被hang住了不能動了，下面我們來看看鎖定情況
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TY       ID1       ID2    LMODE REQUEST    BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
   138 TM    73449       0       3       0       0
   138 TX    524316    935       6       0       1             138會話持有一個6級排他鎖，正在阻塞一個會話
   156 TM    73449       0       3       0       0
   156 TX    524316    935       0       6       0             156會話被阻塞住了沒有獲得鎖，正在申請一個6級鎖
> select object_name from dba_objects where object_id=73449;             現在可知鎖定的就是t1表
OBJECT_NAME
--------------------------------------------------------------------------------
T1
> select sid,event from v$session_wait where sid in (138,156);          從會話等待檢視上也可以看出138阻塞了156會話
   SID EVENT
---------- ----------------------------------------------------------------
   138 SQL*Net message from client
   156 enq: TX - row lock contention
156會話由於TX鎖爭用原因導致hang住不能前進，enq=enqueues佇列鎖
小結：我們在瞭解鎖的同時，也要在業務設計的流程上儘量去避免它們的發生，比如說2個人的工作沒有協調好，在同一時間去做了同一件事，這就有可能產生鎖。

select...for update 鎖阻塞                                  這是一種對結果集修改的保護機制

場景：一次性修改多條記錄的時候會用到這個命令，起到鎖定結果集的效果，這也是結果集修改引起的阻塞
> select * from leo1.t1;
      ID
----------
      1
      2
      3
   200
> select * from leo1.t1 where id<=3 for update;       如果我們想對查詢出的結果集進行獨佔，並且此時不允許其他會話進行修改，可以這麼來寫
      ID
----------
      1
      2
      3
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TYPE             ID1       ID2    LMODE REQUEST    BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
   138 TM             73449       0       3       0       0
   138 TX          589839    915       6       0       0          這3行記錄都已經被TX鎖鎖定了，在沒有提交之前別人不能修改
> update leo1.t1 set id=4 where id=1;
> update leo1.t1 set id=4 where id=2;
> update leo1.t1 set id=4 where id=3;                我們在會話leo2上測試更新結果集中的每條記錄，都會hang住了不能前進，說明這
個結果集已經整體被鎖定
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TY       ID1       ID2    LMODE REQUEST    BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
   138 TM    73449       0       3       0       0
   138 TX    589839    915       6       0       1    138正在阻塞另一個會話
   156 TM    73449       0       3       0       0
   156 TX    589839    915       0       6       0

LEO1=138會話 LEO2=156會話，我們可以看出138會話阻塞156會話，156會話TX在請求一個6號排他鎖，因為2個會話都在修改同一個結果集。這種方法可以一次性鎖定n行記錄。
重點：一個表上只能有一個6號鎖

2012.11.28
天津&winter
分享技術~成就夢想
Blog：

Oracle LOCK內部機制及最佳實踐系列(一)分別模擬insert|update|delete造成阻塞及說明

相關文章