轉載--oracle DML鎖
Oracle多粒度封鎖機制研究
1 引言—資料庫鎖的基本概念
為了確保併發使用者在存取同一資料庫物件時的正確性（即無丟失修改、可重複讀、不讀“髒”資料），資料庫中引入了鎖機制。基本的鎖型別有兩種：排它鎖（Exclusive locks記為X鎖）和共享鎖（Share locks記為S鎖）。
排它鎖：若事務T對資料D加X鎖，則其它任何事務都不能再對D加任何型別的鎖，直至T釋放D上的X鎖；一般要求在修改資料前要向該資料加排它鎖，所以排它鎖又稱為寫鎖。
共享鎖：若事務T對資料D加S鎖，則其它事務只能對D加S鎖，而不能加X鎖，直至T釋放D上的S鎖；一般要求在讀取資料前要向該資料加共享鎖，所以共享鎖又稱為讀鎖。
2 Oracle 多粒度封鎖機制介紹
根據保護物件的不同，Oracle資料庫鎖可以分為以下幾大類：
(1) DML lock（data locks，資料鎖）：用於保護資料的完整性；
(2) DDL lock（dictionary locks，字典鎖）：用於保護資料庫物件的結構（例如表、檢視、索引的結構定義）；
(3) internal locks 和l a t c h es（內部鎖與閂）：保護內部資料庫結構；
(4) distributed locks（分散式鎖）：用於OPS（並行伺服器）中；
(5) PCM locks（並行快取記憶體管理鎖）：用於OPS（並行伺服器）中。
本文主要討論DML（也可稱為data locks，資料鎖）鎖。從封鎖粒度（封鎖物件的大小）的角度看，Oracle DML鎖共有兩個層次，即行級鎖和表級鎖。
2.1 Oracle的TX鎖（行級鎖、事務鎖）
許多對Oracle不太瞭解的技術人員可能會以為每一個TX鎖代表一條被封鎖的資料行，其實不然。TX的本義是Transaction（事務），當一個事務第一次執行資料更改（Insert、Update、Delete）或使用SELECT… FOR UPDATE語句進行查詢時，它即獲得一個TX（事務）鎖，直至該事務結束（執行COMMIT或ROLLBACK操作）時，該鎖才被釋放。所以，一個TX鎖，可以對應多個被該事務鎖定的資料行。
在Oracle的每行資料上，都有一個標誌位來表示該行資料是否被鎖定。Oracle不象其它一些DBMS（資料庫管理系統）那樣，建立一個連結串列來維護每一行被加鎖的資料，這樣就大大減小了行級鎖的維護開銷，也在很大程度上避免了其它資料庫系統使用行級封鎖時經常發生的鎖數量不夠的情況。資料行上的鎖標誌一旦被置位，就表明該行資料被加X鎖，Oracle在資料行上沒有S鎖。
2.2 TM鎖（表級鎖）
2.2.1 意向鎖的引出
表是由行組成的，當我們向某個表加鎖時，一方面需要檢查該鎖的申請是否與原有的表級鎖相容；另一方面，還要檢查該鎖是否與表中的每一行上的鎖相容。比如一個事務要在一個表上加S鎖，如果表中的一行已被另外的事務加了X鎖，那麼該鎖的申請也應被阻塞。如果表中的資料很多，逐行檢查鎖標誌的開銷將很大，系統的效能將會受到影響。為了解決這個問題，可以在表級引入新的鎖型別來表示其所屬行的加鎖情況，這就引出了“意向鎖”的概念。
意向鎖的含義是如果對一個結點加意向鎖，則說明該結點的下層結點正在被加鎖；對任一結點加鎖時，必須先對它的上層結點加意向鎖。如：對錶中的任一行加鎖時，必須先對它所在的表加意向鎖，然後再對該行加鎖。這樣一來，事務對錶加鎖時，就不再需要檢查表中每行記錄的鎖標誌位了，系統效率得以大大提高。
2.2.2 意向鎖的型別
由兩種基本的鎖型別（S鎖、X鎖），可以自然地派生出兩種意向鎖：
意向共享鎖（Intent Share Lock，簡稱IS鎖）：如果要對一個資料庫物件加S鎖，首先要對其上級結點加IS鎖，表示它的後裔結點擬（意向）加S鎖；
意向排它鎖（Intent Exclusive Lock，簡稱IX鎖）：如果要對一個資料庫物件加X鎖，首先要對其上級結點加IX鎖，表示它的後裔結點擬（意向）加X鎖。
另外，基本的鎖型別（S、X）與意向鎖型別（IS、IX）之間還可以組合出新的鎖型別，理論上可以組合出4種，即：S+IS，S+IX，X+IS，X+IX，但稍加分析不難看出，實際上只有S+IX有新的意義，其它三種組合都沒有使鎖的強度得到提高（即：S+IS=S，X+IS=X，X+IX=X，這裡的“=”指鎖的強度相同）。所謂鎖的強度是指對其它鎖的排斥程度。
這樣我們又可以引入一種新的鎖的型別
共享意向排它鎖（Shared Intent Exclusive Lock,簡稱SIX鎖） ：如果對一個資料庫物件加SIX鎖，表示對它加S鎖，再加IX鎖，即SIX=S+IX。例如：事務對某個表加SIX鎖，則表示該事務要讀整個表（所以要對該表加S鎖），同時會更新個別行（所以要對該表加IX鎖）。
這樣資料庫物件上所加的鎖型別就可能有5種：即S、X、IS、IX、SIX。
具有意向鎖的多粒度封鎖方法中任意事務T要對一個資料庫物件加鎖，必須先對它的上層結點加意向鎖。申請封鎖時應按自上而下的次序進行；釋放封鎖時則應按自下而上的次序進行；具有意向鎖的多粒度封鎖方法提高了系統的併發度，減少了加鎖和解鎖的開銷。
2.2.3 Oracle的TM鎖（表級鎖）
Oracle的DML鎖（資料鎖）正是採用了上面提到的多粒度封鎖方法，其行級鎖雖然只有一種（即X鎖），但其TM鎖（表級鎖）型別共有5種，分別稱為共享鎖（S鎖）、排它鎖（X鎖）、行級共享鎖（RS鎖）、行級排它鎖（RX鎖）、共享行級排它鎖（SRX鎖），與上面提到的S、X、IS、IX、SIX相對應。需要注意的是，由於Oracle在行級只提供X鎖，所以與RS鎖（通過SELECT … FOR UPDATE語句獲得）對應的行級鎖也是X鎖（但是該行資料實際上還沒有被修改），這與理論上的IS鎖是有區別的。
下表為Oracle資料庫TM鎖的相容矩陣（Y=Yes，表示相容的請求； N=No，表示不相容的請求；-表示沒有加鎖請求）：
|
|
1 NULL |
2 SS |
3 SX |
4 S |
5 SSX |
6 X |
|
1 NULL |
YES |
YES |
YES |
YES |
YES |
YES |
|
2 SS(RS) |
YES |
YES |
YES |
YES |
YES |
NO |
|
3 SX(RX) |
YES |
YES |
YES |
NO |
NO |
NO |
|
4 S |
YES |
YES |
NO |
YES |
NO |
NO |
|
5 SSX(SRX) |
YES |
YES |
NO |
NO |
NO |
NO |
|
6 X |
YES |
NO |
NO |
NO |
NO |
NO |
表一：Oracle資料庫TM鎖的相容矩陣
一方面，當Oracle執行SELECT…FOR UPDATE、INSERT、UPDATE、DELETE等DML語句時，系統自動在所要操作的表上申請表級RS鎖（SELECT…FOR UPDATE）或RX鎖（INSERT、UPDATE、DELETE），當表級鎖獲得後，系統再自動申請TX鎖，並將實際鎖定的資料行的鎖標誌位置位（指向該TX鎖）；另一方面，程式或操作人員也可以通過LOCK TABLE語句來指定獲得某種型別的TM鎖。下表總結了Oracle中各SQL語句產生TM鎖的情況：
|
SQL語句 |
表鎖模式 |
允許的鎖模式 |
|
Select * from table_name…… |
無 |
RS、RX、S、SRX、X |
|
Insert into table_name…… |
RX |
RS、RX |
|
Update table_name…… |
RX |
RS、RX |
|
Delete from table_name…… |
RX |
RS、RX |
|
Select * from table_name for update |
RS |
RS、RX、S、SRX |
|
lock table table_name in row share mode |
RS |
RS、RX、S、SRX |
|
lock table table_name in row exclusive mode |
RX |
RS、RX |
|
lock table table_name in share mode |
S |
RS、S |
|
lock table table_name in share row exclusive mode |
SRX |
RS |
|
lock table table_name in exclusive mode |
X |
無 |
表二：Oracle資料庫TM鎖小結
我們可以看到，通常的DML操作（SELECT…FOR UPDATE、INSERT、UPDATE、DELETE），在表級獲得的只是意向鎖（RS或RX），其真正的封鎖粒度還是在行級；另外，Oracle資料庫的一個顯著特點是，在預設情況下，單純地讀資料（SELECT）並不加鎖，Oracle通過回滾段（Rollback segment）來保證使用者不讀“髒”資料。這些都極大地提高了系統的併發程度。
由於意向鎖及資料行上鎖標誌位的引入，極大地減小了Oracle維護行級鎖的開銷，這些技術的應用使Oracle能夠高效地處理高度併發的事務請求。
TM鎖的5級別的解釋
0-- none
1-- null
2--行級共享鎖（Row Shared，簡稱RS鎖）
通常是通過select …from for update語句新增的，同時該方法也是我們用來手工鎖定某
些記錄的主要方法。比如，當我們在查詢某些記錄的過程中，不希望其他使用者對查詢的記
錄進行更新操作，則可以發出這樣的語句。當資料使用完畢以後，直接發出rollback命令
將鎖定解除。當表上新增了RS鎖定以後，不允許其他事務對相同的表新增排他鎖，但是允
許其他的事務通過DML語句或lock命令鎖定相同表裡的其他資料行。
3 --行級排他鎖（Row Exclusive，簡稱RX鎖）
Dml Insert Update Delete
當我們進行DML時會自動在被更新的表上新增RX鎖，或者也可以通過執行lock命令顯式的
在表上新增RX鎖。在該鎖定模式下，允許其他的事務通過DML語句修改相同表裡的其他數
據行，或通過lock命令對相同表新增RX鎖定，但是不允許其他事務對相同的表新增排他鎖（X鎖）。
4--共享鎖（Share，簡稱S鎖）
通過lock table in share mode命令新增該S鎖。在該鎖定模式下，不允許任何使用者更新表。
但是允許其他使用者發出select …from for update命令對錶新增RS鎖。
5--共享行級排他鎖（Share Row Exclusive，簡稱SRX鎖）
通過lock table in share row exclusive mode命令新增SRX鎖。該鎖定模式比行級排他鎖
和共享鎖的級別都要高，這時不能對相同的表進行DML操作，也不能新增共享鎖。
6--排他鎖（Exclusive，簡稱X鎖）
通過lock table in exclusive mode命令新增X鎖。在該鎖定模式下，其他使用者不能對錶進
行任何的DML和DDL操作，該表上只能進行查詢。
3 Oracle 多粒度封鎖機制的監控
3.1 系統檢視介紹
為了監控Oracle系統中鎖的狀況，我們需要對幾個系統檢視有所瞭解：
3.1.1 v$lock檢視
v$lock檢視列出當前系統持有的或正在申請的所有鎖的情況，其主要欄位說明如下：
|
欄位名稱 |
型別 |
說明 |
|
SID |
NUMBER |
會話（SESSION）標識； |
|
TYPE |
VARCHAR（2） |
區分該鎖保護物件的型別； |
|
ID1 |
NUMBER |
鎖標識1； |
|
ID2 |
NUMBER |
鎖標識2； |
|
LMODE |
NUMBER |
鎖模式：0（None），1（null）,2（row share）, 3（row exclusive）,4 （share）,5（share row exclusive）,6（exclusive） |
|
REQUEST |
NUMBER |
申請的鎖模式：具體值同上面的LMODE |
|
CTIME |
NUMBER |
已持有或等待鎖的時間； |
|
BLOCK |
NUMBER |
是否阻塞其它鎖申請； |
表三：v$lock檢視主要欄位說明
如果某個request列是一個非0值，那麼它就是在等待一個鎖。 如果block列是1，這個SID 就持有了一個鎖，並且阻塞別人獲得這個鎖。
其中在TYPE欄位的取值中，本文只關心TM、TX兩種DML鎖型別；
關於ID1、ID2，TYPE取值不同其含義也有所不同：
|
TYPE |
ID1 |
ID2 |
|
TM |
被修改表的標識（object_id） |
0 |
|
TX |
以十進位制數值表示該事務所佔用的回滾段號與該事務在該回滾段的事務表（Transaction table）中所佔用的槽號（slot number，可理解為記錄號）。其組成形式為： 0xRRRRSSSS ( RRRR = RBS number, SSSS = slot )。 |
以十進位制數值表示環繞（wrap）次數，即該槽（slot）被重用的次數； |
表四：v$lock檢視中ID1與ID2欄位取值說明
3.1.2 v$locked_object檢視
v$locked_object檢視列出當前系統中哪些物件正被鎖定，其主要欄位說明如下：
|
欄位名稱 |
型別 |
說明 |
|
XIDUSN |
NUMBER |
回滾段號； |
|
XIDSLOT |
NUMBER |
槽號； |
|
XIDSQN |
NUMBER |
序列號； |
|
OBJECT_ID |
NUMBER |
被鎖物件標識； |
|
SESSION_ID |
NUMBER |
持有鎖的會話（SESSION）標識； |
|
ORACLE_USERNAME |
VARCHAR2（30） |
持有該鎖的使用者的Oracle使用者名稱； |
|
OS_USER_NAME |
VARCHAR2（15） |
持有該鎖的使用者的作業系統使用者名稱； |
|
PROCESS |
VARCHAR2（9） |
作業系統的程式號； |
|
LOCKED_MODE |
NUMBER |
鎖模式，取值同表三中的LMODE； |
表五：v$locked_object檢視欄位說明
3.2 監控指令碼
根據上述系統檢視，可以編制指令碼來監控資料庫中鎖的狀況。
3.2.1 showlock.sql
第一個指令碼showlock.sql，該指令碼通過連線v$locked_object與all_objects兩檢視，顯示哪些物件被哪些會話鎖住：
select rpad(oracle_username,10) o_name,session_id sid,
decode(locked_mode,0,'None',1,'Null',2,'Row share',
3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,
object_name ,xidusn,xidslot,xidsqn
from v$locked_object,all_objects
where v$locked_object.object_id=all_objects.object_id;
3.2.2 showalllock.sql
第二個指令碼showalllock.sql，該指令碼主要顯示當前所有TM、TX鎖的資訊；
/* showalllock.sql */
select sid,type,id1,id2,
decode(lmode,0,'None',1,'Null',2,'Row share',
3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')
lock_type,request,ctime,block
from v$lock
where TYPE IN('TX','TM');
4 Oracle 多粒度封鎖機制示例
以下示例均執行在Oracle 8.1.7上，資料庫版本不同，其輸出結果也可能有所不同。首先建立3個會話，其中兩個（以下用SESS#1、SESS#2表示）以SCOTT使用者連入資料庫，以操作Oracle提供的示例表（DEPT、EMP）；另一個（以下用SESS#3表示）以SYS使用者連入資料庫，用於監控；
4.1 操作同一行資料引發的鎖阻塞
SESS#1：
SQL> select * from dept for update;
DEPTNO DNAME LOC
---------- -------------- -------------
10 account 70
20 research 8
30 sales 8
40 operations 8
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 17 Row share DEPT 8 2 5861
SQL> @showalllock
SID TY ID ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
17 TX 524290 5861 Exclusive 0 761 0
17 TM 32970 0 Row share 0 761 0
如第一個指令碼showlock所示，執行完SELECT…FOR UPDATE 語句後， SESS#1（SID為17）在DEPT表上獲得Row share鎖；如第二個指令碼showalllock所示，SESS#1獲得的TX鎖為Exclusive，這些都驗證了上面的理論分析。另外，我們可以將TX鎖的ID1按如下方法進行分解：
SQL> select trunc(524290/65536) xidusn,mod(524290,65536) xidslot from dual;
XIDUSN XIDSLOT
------ -------
8 2
分解結果與第一個指令碼直接查出來的XIDUSN與XIDSLOT相同，而TX鎖的ID2（5861）與XIDSQN相同，可見當LOCK TYPE為TX時，ID1實際上是該事務所佔用的回滾段段號與事務表中的槽（SLOT）號的組合，ID2即為該槽被重用的次數，而這三個值實際上可以唯一地標識一個事務，即TRANSACTION ID，這三個值從系統表v$transaction中也可查到。
另外， DEPT表中有4條記錄被鎖定，但TX鎖只有1個，這也與上面的理論分析一致。繼續進行操作：
SESS#2：
SQL> update dept set loc=loc where deptno=20;
該更新語句被阻塞，此時再檢視系統的鎖情況：
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 17 Row share DEPT 8 2 5861
SCOTT 19 Row Exclusive DEPT 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
17 TX 524290 5861 Exclusive 0 3462 1
17 TM 32970 0 Row share 0 3462 0
19 TM 32970 0 Row Exclusive 0 7 0
19 TX 524290 5861 None 6 7 0
在DEPT表上除了SESS#1（SID為17）持有Row share鎖外，又增加了SESS#2（SID為19）持有的Row Exclusive鎖，但還沒有為SESS#2分配回滾段（XIDUSN、XIDSLOT、XIDSQN的值均為0）；而從第二個指令碼看到，SESS#2的TX鎖的LOCK_TYPE為None，其申請的鎖型別（REQUEST）為6（即Exclusive），而其ID1、ID2的值與SESS#1所持有的TX鎖的ID1、ID2相同，SESS#1的TX鎖的阻塞域（BLOCK）為1，這就說明了由於SESS#1持有的TX鎖，阻塞了SESS#2的更新操作（SESS#2所更新的行與SESS#1所鎖定的行相沖突）。還可以看出，SESS#2先申請表級的TM鎖，後申請行（事務）級的TX鎖，這也與前面的理論分析一致。
下面，將SESS#1的事務進行回滾，解除對SESS#2的阻塞，再對系統進行監控。
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 19 Row Exclusive DEPT 2 10 5803
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
19 TX 131082 5803 Exclusive 0 157 0
19 TM 32970 0 Row Exclusive 0 333 0
可以看到，SESS#1的事務所持有的鎖已經釋放，系統為SESS#2的事務分配了回滾段，而其TX鎖也已經獲得，並且ID1、ID2是其真正的Transaction ID。再將會話2的事務進行回滾。
SESS#2：
SQL> rollback;
Rollback complete.
檢查系統鎖的情況：
SESS#3：
SQL> @showlock
no rows selected
SQL> @showalllock
no rows selected
可以看到，TM與TX鎖已全部被釋放。
4.2 實體完整性引發的鎖阻塞
DEPT（部門）表有如下欄位DEPTNO（部門編號），DNAME（部門名稱），LOC（部門位置）；其中DEPTNO列為主鍵。
SESS#1
SQL> INSERT INTO DEPT(DEPTNO) VALUES(50);
1 row created.
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive DEPT 6 88 29
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TX 393304 29 Exclusive 0 6 0
7 TM 3574 0 Row Exclusive 0 6 0
向DEPT表中插入一條DEPTNO為50的記錄後，SESS#1（SID為7）在DEPT表上獲得Row Exclusive鎖，並且由於進行了資料插入，該事務被分配了回滾段，獲得TX鎖。
SESS#2
INSERT INTO DEPT(DEPTNO) VALUES(50);
這時，SESS#2（SID為8）也向DEPT表中插入一條DEPTNO為50的記錄，該語句被阻塞，檢查鎖情況：
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 8 Row Exclusive DEPT 7 75 30
SCOTT 7 Row Exclusive DEPT 6 88 29
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TX 393304 29 Exclusive 0 92 1
7 TM 3574 0 Row Exclusive 0 92 0
8 TX 458827 30 Exclusive 0 22 0
8 TM 3574 0 Row Exclusive 0 22 0
8 TX 393304 29 None 4 22 0
SESS#2在DEPT表上也獲得了Row Exclusive鎖，同樣也獲得了回滾段的分配，得到TX鎖，但是由於其插入的記錄與SESS#1插入的記錄的DEPTNO均為50，該語句成功與否取決於SESS#1的事務是提交還是回滾，所以SESS#2被阻塞，表現為SESS#2以Share方式（REQUEST=4）等待SESS#1所持有的TX鎖的釋放。
這時，如果SESS#1進行回滾：
SESS#1
SQL> ROLLBACK;
Rollback complete.
SESS#2
1 row created.
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 8 Row Exclusive DEPT 7 75 30
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
8 TX 458827 30 Exclusive 0 136 0
8 TM 3574 0 Row Exclusive 0 136 0
SESS#2的阻塞將被解除，SESS#2只持有原先已有的TM與TX鎖，其等待的TX鎖（由SESS#1持有）也消失了。
如果SESS#1提交而不是回滾，在SESS#2上將會出現如下提示：
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated錯誤。
即發生主鍵衝突，SESS#1與SESS#2的所有鎖資源均被釋放。
4.3 參照完整性引發的鎖阻塞
EMP（員工）表有如下欄位：EMPNO（員工編號），ENAME（員工姓名），DEPTNO（員工所在部門編號），其中DEPTNO列為外來鍵，其父表為DEPT。
SESS#1
SQL> insert into dept(deptno) values(60);
1 row created.
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive DEPT 2 6 33
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TX 131078 33 Exclusive 0 148 0
7 TM 3574 0 Row Exclusive 0 148 0
SESS#1（SID為7）在DEPT表中先插入一條DEPTNO為60的記錄，SESS#1獲得了DEPT表上的Row Exclusive鎖，及一個TX鎖。
SESS#2
insert into emp(empno,deptno) values(2000,60)；
被阻塞
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive DEPT 2 6 33
SCOTT 8 Row Exclusive EMP 3 20 31
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TX 131078 33 Exclusive 0 228 1
7 TM 3574 0 Row Exclusive 0 228 0
8 TX 196628 31 Exclusive 0 9 0
8 TM 3576 0 Row Exclusive 0 9 0
8 TX 131078 33 None 4 9 0
SESS#2（SID為8）向EMP表中出入一條新記錄，該記錄DEPT值為60（即SESS#1剛插入，但還未提交的記錄的DEPTNO值），SESS#2獲得了EMP表上的Row Exclusive鎖，另外由於插入記錄，還分配了回滾段及一個TX鎖，但由於SESS#2的插入語句是否成功取決於SESS#1的事務是否進行提交，所以它被阻塞，表現為SESS#2以Share（REQUEST=4）方式等待SESS#1釋放其持有的TX鎖。這時SESS#1如果提交，SESS#2的插入也將執行成功，而如果SESS#1回滾，由於不符合參照完整性，SESS#2將報錯：
SESS#2
insert into emp(empno,deptno) values(2000,60)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not
Found
SESS#2持有的鎖也被全部釋放。
這裡再插入一種情況進來，當修改子表的時候沒提交，
無法修改父表的參考列的值
會話1：
select Userenv('SID') From dual --508
Delete From emp Where empno=7369
不提交
會話2：
select Userenv('SID') From dual --488
Update dept Set deptno=99 Where deptno=80
會話阻塞了
會話3：
select * From v$lock Where Sid In (508,488)
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
327713DC 327713F4 488 TM 139627 0 3 0 30 0
327714A0 327714B8 488 TM 139629 0 0 4 30 0
318B350C 318B3628 508 TX 655364 99274 6 0 30 0
32771254 3277126C 508 TM 139627 0 2 0 30 0
32771318 32771330 508 TM 139629 0 3 0 30 1
4.4 外來鍵未加索引引發的鎖阻塞
EMP表上的DEPTNO列為外來鍵，但沒有在該列上建索引。
SESS#1
SQL> delete emp where 0=1;
0 rows deleted.
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive EMP 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TM 3576 0 Row Exclusive 0 10 0
首先SESS#1（SID為7）做了一個刪除操作，但由於條件（0=1）為永假，所以實際上並沒有一行被刪除，從監控指令碼可以看出SESS#1在EMP表上獲得Row Exclusive鎖，但由於沒有實際的行被刪除，所以並沒有TX鎖，也沒有為SESS#1分配回滾段。
SESS#2：
SQL> delete dept where 0=1;
該語句雖然也不會刪除實際資料，但卻被阻塞，檢視系統的鎖情況：
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 8 None EMP 0 0 0
SCOTT 7 Row Exclusive EMP 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TM 3576 0 Row Exclusive 0 31 1
8 TM 3576 0 None 4 12 0
SESS#2申請在EMP表上加SHARE鎖（REQUEST=4），但該申請被SESS#1阻塞，因為SESS#1已經在EMP表上獲得了Row Exclusive鎖，與SHARE鎖不相容。
下面我們對SESS#1進行回滾後，再進行監控。
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 8 Share EMP 0 0 0
SCOTT 8 Row Exclusive DEPT 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
8 TM 3574 0 Row Exclusive 0 16 0
8 TM 3576 0 Share 0 16 0
SESS#2在EMP表上獲得Share鎖後，又在DEPT表上獲得Row Exclusive鎖，由於沒有實際的行被修改，SESS#2並沒有獲得TX鎖。
在Oracle8中，如果子表的外來鍵上沒有加索引，當在父表上刪除記錄時，會先在子表上申請獲得Share鎖，之後再在父表上申請Row Exclusive鎖。由於表級Share鎖的封鎖粒度較大，所以容易引起阻塞，從而造成效能問題。
當在外來鍵上建立索引後，在父表上刪除資料將不再對子表上加Share鎖，如下所示：
SESS#1：
SQL> create index i_emp_deptno on emp(deptno);
Index created.
SQL> delete dept where 0=1;
0 rows deleted.
SQL>
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive DEPT 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TM 3574 0 Row Exclusive 0 9 0
可以看到，在EMP表DEPTNO列上建立索引後，在DEPT表上執行DELETE操作，不再要求在EMP表上加Share鎖，只是在DEPT表上加Row Exclusive鎖，封鎖的粒度減小，引起阻塞的可能性也減小。
5 Oracle 多粒度封鎖機制總結
Oracle通過具有意向鎖的多粒度封鎖機制進行併發控制，保證資料的一致性。其DML鎖（資料鎖）分為兩個層次（粒度）：即表級和行級。通常的DML操作在表級獲得的只是意向鎖（RS或RX），其真正的封鎖粒度還是在行級；另外，在Oracle資料庫中，單純地讀資料（SELECT）並不加鎖，這些都極大地提高了系統的併發程度。
在支援高併發度的同時，Oracle利用意向鎖及資料行上加鎖標誌位等設計技巧，減小了Oracle維護行級鎖的開銷，使其在資料庫併發控制方面有著明顯的優勢。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10678398/viewspace-716566/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE DML鎖定機制Oracle
- ORACLE鎖機制-轉載Oracle
- 轉載oracle資料庫鎖Oracle資料庫
- [轉載]oracle手動鎖表Oracle
- ORACLE 11g新特性-允許DDL鎖等待DML鎖Oracle
- oracle 監控 DML DDL 鎖 的4個檢視Oracle
- 轉載：深入淺出oracle鎖 釋放鎖---原理篇Oracle
- ORACLE DML語句鎖機制導致事務等待示例Oracle
- db2_dml鎖分析DB2
- Oracle Parallel DMLOracleParallel
- MySQL鎖詳解！(轉載)MySql
- 對鎖的理解(轉載)
- sqlserver鎖機制（轉載）SQLServer
- oracle鎖的管理（轉）Oracle
- 並行dml操作所需的TM鎖並行
- Oracle分批提交DMLOracle
- 關於主外來鍵關係DML父表和DML子表加鎖方式
- oracle 鎖（轉自網路）Oracle
- SAP IQ DML操作產生表鎖(不是行鎖)，同時DML同一個表預設直接報錯
- oracle lock鎖_v$lock_轉Oracle
- Oracle 鎖簡單介紹(轉)Oracle
- Oracle鎖簡單介紹(轉)Oracle
- Oracle DML NOLOGGINGOracle
- oracle dml與索引index(一)Oracle索引Index
- 轉載oracle awrOracle
- 轉：Oracle 解鎖Record is locked by another useOracle
- 轉載Oracle AWR速查Oracle
- oracle rowid (轉載)Oracle
- DML的鎖，修改表經常遇到的的場景
- 【鎖】Oracle鎖系列Oracle
- oracle lock轉換及oracle deadlock死鎖系列一Oracle
- 原創：oracle DML介紹與使用Oracle
- Oracle並行操作——並行DML操作Oracle並行
- oracle support nologging ddl dmlOracle
- Oracle DDL,DML,DCL,TCL 基礎概念Oracle
- Oracle文件轉載 部落格Oracle
- 【轉載】oracle更新語法Oracle
- [轉載] Oracle EBS 入門Oracle