Oracle多粒度鎖的驗證
創建測試表
[email protected]>create table test (a number,b number);
表已創建。
1 對於未提交的insert操作
[email protected]>insert into test values(10,20);
已創建 1 行。
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
12AB9394 12AB
93A8 17 TM 29512 0 3 0 84 0
12AF9218 12AF9324 17 TX 589841 5506 6 0 84 0
可見,對於未提交的insert操作,會產生兩個鎖,其類型(TYPE)分別為TM和TX,也就是表級意向鎖和事務鎖.
表級意向鎖的模式(LMODE)為:3,表示是row exclusive,即表示此表中的某行獲得了行排他鎖.
事務鎖的模式(LMODE)為:6, 表示是exclusive,即排他鎖,表示此事務獲得了排他鎖.
BLOCK表示此鎖是否阻塞了其它的鎖,即發生死鎖;此處沒有.
2 對於提交的insert操作
[email protected]>commit;
提交完成。
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
此處已沒有記錄,說明在提交後,即完成了鎖的釋放.
3 對於未提交的update操作
[email protected]>update test set a=11 where a=10;
已更新 1 行。
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
12AB9394 12AB
93A8 17 TM 29512 0 3 0 3 0
12AF9218 12AF9324 17 TX 262153 5590 6 0 3 0
可見update操作所引起的鎖的信息完全等同於insert操作..
4 對於提交的update操作
[email protected]>commit;
提交完成。
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
此處已沒有記錄,說明在提交後,即完成了鎖的釋放.
5 對於select操作
[email protected]>select * from test where a=11;
A B
---------- ----------
11 20
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
此處已沒有記錄,說明select操作不會引起任何鎖.
這是與SQL Server等數據庫不同的,這些數據庫select操作也會引起鎖,以取得一致讀;
而oracle是通過回滾機制實現一致讀的,所以不需要引入鎖機制,這極大增強了Oracle的並發度.
6 for update操作
[email protected]>select * from test for update;
A B
---------- ----------
11 20
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
12AB9394 12AB
93A8 17 TM 29512 0 2 0 0 0
12AF9218 12AF9324 17 TX 393224 5558 6 0 0 0
可見,for update操作會引起兩個鎖,分別是表級意向鎖(TM)和事務鎖(TX);
表級意向鎖鎖定模式為:2(row share),這表示屬於此表中的某行獲得了共享鎖;相比較DML操作,此處鎖級別低了一級,DML的是3;其實在Oracle中沒有行級共享鎖.
TX的鎖定模式為6,表示行級排他鎖,這與DML的效果一致.
7 for update操作:commit後
當commit後,就會發現鎖已被釋放.
8 for update與update互鎖問題
1) session 1中:
[email protected]>select * from test for update;
A B
---------- ----------
11 20
2) session 2中:
[email protected]>update test set a=12 where a=11;
此時,這條語句處於阻塞狀態,說明等待鎖;
查看鎖:
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
132D2CCC 132D2CDC 16 TX 196624 5616 0 6 41 0
12AB9418 12AB
942C 16 TM 29512 0 3 0 41 0
12AB9394 12AB
93A8 17 TM 29512 0 2 0 86 0
12AF9218 12AF9324 17 TX 196624 5616 6 0 86 1
發現有兩個會話處於有鎖的活動;
發出for update操作的session 1(sid=17)的有模式為2（row share）的行級共享意向表級鎖;模式為6（exclusive）行級排他鎖;
發出update操作的session 2(sid=16)的模式為3（row exclusive）的行級排他意向鎖;模式為0（None）的行級鎖;
這說明,第二個session(sid=16)由於是後發出的操作,它會首先去檢索將要操作的表是否存在鎖,此處由於存在,故就堵塞了,所以沒有獲得行級鎖;
這也就說,兩個session在檢測操作對象是否處於被鎖狀態時,是首先檢測其表級鎖,這就避免了去檢測沒一行的鎖,這就提升了性能.
像這裡的情況,我們所操作的對象是行,但所利用的檢測鎖機制是在表級.
同時,會發現session 1(sid=17)的TX鎖的BLOCK為1,這表示此鎖堵住了另外的鎖;同時我們會看到session 2(sid=16)的TX鎖等待的對象ID1和ID2與sid=16的相同,這說明sid=17的堵住了sid=16的.
8 rollback第一個會話的for update操作
[email protected]>rollback;
回退已完成。
查看鎖:
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
2AB9418 12AB
942C 16 TM 29512 0 3 0 1296 0
12AE60B0 12AE61BC 16 TX 131089 5605 6 0 6 0
可見,第一鎖的信息已沒有.
此時只有session 2的鎖的信息;而且session 2已獲得鎖.
如果再將session 2進行回滾,就會發現session 2的鎖也沒有了.
9實體完整性引發的鎖阻塞
在具有primary key約束的表中,在兩個session中插入同樣的記錄
[email protected]>alter table test add constraint pk_a primary key(a);
表已更改。
Session 1中:
[email protected]>insert into test(a) values(101);
已創建 1 行。
Session 2中:
[email protected]>insert into test(a) values(101);
session 2處於阻塞狀態.
可見,在session1沒有提交的情況,實體完整性約束就會阻塞住session 2;
查看鎖:
SQL> select * from v$lock;
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
132D2CCC 132D2CDC 16 TX 131081 5627 0 4 164 0
12AE60B0 12AE61BC 16 TX 458759 5513 6 0 164 0
12AB9418 12AB
942C 16 TM 29512 0 3 0 164 0
12AB9394 12AB
93A8 17 TM 29512 0 3 0 188 0
12AE649C 12AE
65A8 17 TX 131081 5627 6 0 188 1
可見,session 1(sid=17)已獲得TM和TX鎖,並且阻塞住了其它的鎖;
session 2(sid=16)被阻塞,
可以發現, session 2已獲得了行排他鎖:
12AE60B0 12AE61BC 16 TX 458759 5513 6 0 164 0
已經完全分配了新的事務;所以session 2不是被堵在和session 1競爭同一個數據塊上(如上面的例子),而是被堵在了完整行約束上:
132D2CCC 132D2CDC 16 TX 131081 5627 0 4 164 0
這個鎖請求的類型為4 （share）;
Sessio 1:
[email protected]>rollback;
回退已完成。
Session 2:
[email protected]>insert into test(a) values(101);
已創建 1 行。
ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK
2AB9418 12AB
942C 16 TM 29512 0 3 0 764 0
12AE60B0 12AE61BC 16 TX 458759 5513 6 0 764 0
可見,session 2所持有的鎖剩余兩個,那個原來等待session 1的鎖已釋放.
10參照完整性引發的鎖阻塞
[email protected]>create table test_child(c number,a number not null constra
int pk_a_ref references test(a));
表已創建。
[email protected]>insert into test(a) values(101);
已創建 1 行。
Session 1:
[email protected]>@showlockedobj
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT
XIDSQN
---------- ---------- --------------- --------------- ---------- ---------- --
------
AAA 17 Row Exclusive TEST 10 21
5678
AAA 17 Row share TEST_CHILD 10 21
5678
可以發現,有兩個對象被鎖住: TEST和TEST_CHILD
[email protected]>@showlocks
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME
BLOCK
---------- ---- ---------- ---------- --------------- ---------- ---------- ----
------
17 TM 29512 0 Row Exclusive 0 75
0
17 TM 29515 0 Row share 0 75
0
17 TX 655381 5678 Exclusive 0 75
0
可見有三個鎖;
SQL> select object_name from dba_objects where object_id=29512;
OBJECT_NAME
---------------
TEST
SQL> select object_name from dba_objects where object_id=29515;
OBJECT_NAME
---------------
TEST_CHILD
可見,除了TEST表需要的TM和TX鎖外,
還同時將TEST_CHILD表鎖住了:其鎖類型為Row share
session 2:
[email protected]>insert into test_child(c,a) values(11,101);
插入外鍵值為101的,語句的執行會停頓.
[email protected]>@showlockedobj
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT
XIDSQN
---------- ---------- --------------- --------------- ---------- ---------- -
------
AAA 16 Row share TEST 7 18
5521
AAA 16 Row Exclusive TEST_CHILD 7 18
5521
AAA 17 Row Exclusive TEST 10 21
5678
AAA 17 Row share TEST_CHILD 10 21
5678
這時會發現,被鎖住的對象有4個;這是因為在子表中的插入同時會鎖住父表和子表.
[email protected]>@showlocks
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME
BLOCK
---------- ---- ---------- ---------- --------------- ---------- ---------- -
------
16 TX 655381 5678 None 4 79
0
16 TM 29512 0 Row share 0 79
0
16 TM 29515 0 Row Exclusive 0 79
0
16 TX 458770 5521 Exclusive 0 79
0
17 TM 29512 0 Row Exclusive 0 508
0
17 TX 655381 5678 Exclusive 0 508
1
17 TM 29515 0 Row share 0 508
0
已選擇7行。
分析鎖的情況.
Session 2(sid=16)有四個鎖:分別是子表的TM和TX鎖:
16 TM 29515 0 Row Exclusive 0 79
0
16 TX 458770 5521 Exclusive 0 79
父表的TM和TX鎖:
16 TX 655381 5678 None 4 79
0
16 TM 29512 0 Row share 0 79
0
這是因為參照完整性需要父表在參照的過程中不能發生改變,所以要對父表加上這些限制.
Session 1:
[email protected]>rollback;
回退已完成。
Session 2:
[email protected]>insert into test_child(c,a) values(11,101);
insert into test_child(c,a) values(11,101)
*
ERROR 位於第 1 行:
ORA-02291: 違反完整約束條件 (AAA.PK_A_REF) - 未找到父項關鍵字
11 更新子表時
[email protected]>update test_child set a=0 where 1=0;
已更新0行。
[email protected]>@showlockedobj
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT
XIDSQN
---------- ---------- --------------- --------------- ---------- ---------- ----
------
AAA 17 Row share TEST 0 0
0
AAA 17 Row Exclusive TEST_CHILD 0 0
0
[email protected]>@showlocks
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME
BLOCK
---------- ---- ---------- ---------- --------------- ---------- ---------- ----
------
17 TM 29512 0 Row share 0 61
0
17 TM 29515 0 Row Exclusive 0 61
0
可見,當更新子表時,會鎖住父子兩個表,即使實際上沒有更新數據
12 當更新父表時:
[email protected]>update test set a=0 where 1=0;
已更新0行。
[email protected]>@showlockedobj
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT
XIDSQN
---------- ---------- --------------- --------------- ---------- ---------- ----
------
AAA 17 Row Exclusive TEST 0 0
0
[email protected]>@showlocks
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME
BLOCK
---------- ---- ---------- ---------- --------------- ---------- ---------- ----
------
17 TM 29512 0 Row Exclusive 0 15
0
>
可見,更新父表只會鎖住父表
13 當父子兩個表同時更新時:
session 1:
[email protected]>update test_child set a=0 where 1=0;
已更新0行。
Session 2
[email protected]>update test set a=1 where 1=0;
session 2會被鎖住.
[email protected]>@showlockedobj
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT
XIDSQN
---------- ---------- --------------- --------------- ---------- ---------- ----
------
AAA 16 Row Exclusive TEST 0 0
0
AAA 16 None TEST_CHILD 0 0
0
AAA 17 Row Exclusive TEST 0 0
0
AAA 17 Row Exclusive TEST_CHILD 0 0
0
[email protected]>@showlocks
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME
BLOCK
---------- ---- ---------- ---------- --------------- ---------- ---------- ----
------
16 TM 29512 0 Row Exclusive 0 72
0
16 TM 29515 0 None 4 72
0
17 TM 29512 0 Row Exclusive 0 358
0
17 TM 29515 0 Row Exclusive 0 95
1
可見,會發生死鎖.是由於第二個session 申請子表的share鎖時發生的.
14對外鍵建立索引
[email protected]>create index idx_child on test_child(a);
索引已創建。
當父子兩個表同時更新時:
session 1:
[email protected]>update test_child set a=0 where 1=0;
已更新0行。
Session 2
[email protected]>update test set a=1 where 1=0;
已更新0行。
可見,不會發生死鎖.
[email protected]>@showlockedobj
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT
XIDSQN
---------- ---------- --------------- --------------- ---------- ---------- ----
------
AAA 16 Row Exclusive TEST 0 0
0
AAA 16 Row share TEST_CHILD 0 0
0
AAA 17 Row share TEST 0 0
0
AAA 17 Row Exclusive TEST_CHILD 0 0
0
[email protected]>@showlocks
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME
BLOCK
---------- ---- ---------- ---------- --------------- ---------- ---------- ----
------
16 TM 29512 0 Row Exclusive 0 15
0
16 TM 29515 0 Row share 0 15
0
17 TM 29512 0 Row share 0 23
0
17 TM 29515 0 Row Exclusive 0 23
0
可以發現,session 1獲得TEST_CHILD行級排他意向表鎖,同時獲得TEST表的行級共享排他意向鎖;
session 2獲得TEST行級排他意向表鎖,同時獲得TEST_CHILD表的行級共享排他意向鎖;
與上個例子相比,區別在於前面的例子中,session 1 獲得TEST_CHILD和TEST行級排他意向表鎖.
也就是說,對外鍵建立索引,可以防止兩個表的死鎖.
15 總結
Oracle通過具有意向鎖的多粒度封鎖機制進行並發控制，保證數據的一致性。其DML鎖（數據鎖）分為兩個層次（粒度）：即表級和行級。通常的DML操作在表級獲得的只是意向鎖（RS或RX），其真正的封鎖粒度還是在行級；另外，在Oracle數據庫中，單純地讀數據（SELECT）並不加鎖，這些都極大地提高了系統的並發程度。
在支持高並發度的同時，Oracle利用意向鎖及數據行上加鎖標志位等設計技巧，減小了Oracle維護行級鎖的開銷，使其在數據庫並發控制方面有著明顯的優勢。
