本文結合示例簡要的介紹了一下Oracle中鎖的機制。
為了解決多用戶環境下並發操作相同的資源而造成的錯誤修改數據的問題。單用戶環境下不需要考慮鎖，因為所有操作都是串行的。下面的文章簡要的介紹了一下
鎖的分類異常復雜，enqueue、latch、mutex等，都是為了解決並發存在的，自己也有些混亂，所以也不過多解釋了。下面列舉一些對於lock的要點內容。
l 排他鎖：
不允許相關的資源被共享。一個資源在一個時間點內只有一個事務能夠獲取該資源的排他鎖，只有持有該鎖的事務能夠修改相關的資源，
其他想要獲取鎖的事務只能等待該事務因為commit或者rollback而釋放排他鎖。
l 共享鎖：
允許相關的資源被共享。也就是說允許多個事務同時持有某個資源的共享鎖。
對於一個dml操作，會對表以及行加鎖，也就是v$lock中的TM鎖和TX鎖。
l 行級鎖基本原理：
行級鎖的信息是置於數據塊中的，如果要修改某一條記錄的值，其實就是在訪問相應的block，並且分配一個ITL，然後通過rowid訪問
rowpiece header ，如果第二個字節lock byte(lock byte只占用1個字節，最大值為255，這也是為什麼maxtrans最大為255）為0，則將其改為分配的ITL slot number。另外一個事務如果也想要修改數據，就會發現lock byte不為0，如果第一個事務還沒有結束，則第二個事務進入enqueue等待，也就是transaction enqueue。
關於transaction enqueue有一個很有趣的例子，事務不一定是按照時間的先後順序進行的。
具體地址在：
http://docs.oracle.com/cd/E11882_01/server.112/e25789/transact.htm#autoId12
l 對於Table lock來說可以分為以下幾種類型:
1. Row Share (RS|SS)
2. Row Exclusive Table Lock (RX|SX)
3. Share Table Lock (S)
4. Share Row Exclusive Table Lock (SRX|SSX)
5. Exclusive Table Lock (X)
以下是v$lock.LMODE字段中的數字對應的鎖類型
LMODE(Lockmode in which the session holds the lock):
0 -none
1 -null (NULL)
2 -row-S (SS)
3 -row-X (SX)
4 -share (S)
5 -S/Row-X (SSX)
6 -exclusive (X)
為了更好的開展下面的內容，這裡列舉一下各種TM鎖類型的兼容情況。
詳細驗證情況會在4中給出。
RS|SS
RX|SX
S
SRX|SSX
X
RS|SS
√
√
√
√
×
RX|SX
√
√
×
×
×
S
√
×
√
×
×
SRX|SSX
√
×
×
×
×
X
×
×
×
×
×
順便引用一下經典內容：
只有被修改時，行才會被鎖定。
當一條語句修改了一條記錄，只有這條記錄上被鎖定，在Oracle數據庫中不存在鎖升級。
當某行被修改時，它將阻塞別人對它的修改。
當一個事務修改一行時，將在這個行上加上行鎖（TX），用於阻止其它事務對相同行的修改。
讀永遠不會阻止寫。
讀不會阻塞寫，但有唯一的一個例外，就是select ...for update。
寫永遠不會阻塞讀。
當一行被修改後，Oracle通過回滾段提供給數據的一致性讀
當我們更新一個表的記錄的時候，會有兩種鎖產生，一種是DML鎖（TM）也可以稱作table lock 還有一種事務鎖（TX）也可以稱作行鎖
在v$lock中可以查看到。
例如下面的例子當中：
_dexter@FAKE>desc tun2_tab
Name Null? Type
------------------------------------------------------------------------- --------------------------------------------
ID NUMBER(38)
_dexter@FAKE>update tun2_tab set id =2 ;
2 rowsupdated.
_dexter@FAKE>select sid , type , lmode , request , block
from v$lock
where sid =(select sid from v$mystat where rownum<2) ;
SID TYPE LMODE REQUEST BLOCK
-------------- ---------- ---------- ----------
22 AE 4 0 0
22 TM 3 0 0
22 TX 6 0 0
AE是和版本化（Workspace Manager）相關的東西，這裡不再過多描述。
從上面的查詢結果可以看到更新的時候
會添加一個3級的表鎖，也就是 row-X (SX)鎖，保證在事務結束之前，表的結構不會被更改。多個事務可以同時持有相同表的sx鎖。
還有一個6級的行鎖，exclusive (X)，保證在事務結束之前，相關的行信息不會被更改。(鎖信息存放於block中)
ok簡單示例後，來進行這一小節的主要內容，阻塞示例。
insert操作會對表加3級rx鎖，和行排他鎖，但是一般不會發生阻塞，因為讀一致性的關系，在沒提交之前只有當前session才可以操作新插入的行，對於其他事務來說 新增的記錄是不可見的。
下面列舉幾種特殊的阻塞情況。
在11gr2中，可以使用insert /*+ append */ intoselect 的方式執行直接路徑加載。
或者 insert /*+append_values */ into values 的方式。
這裡使用第二種。
Session1session_id=22：
_dexter@FAKE>insert /*+ append_values */ into tun2_tab values (1) ;
1 rowcreated.
_dexter@FAKE>select sid , type , lmode , request , block from v$lock where sid = (select sidfrom v$mystat where rownum<2) ;
SID TYPE LMODE REQUEST BLOCK
-------------- ---------- ---------- ----------
22 AE 4 0 0
22 TM 6 0 0
22 TX 6 0 0
可以看到使用直接路徑加載的時候會對表加6級排他鎖。根據表1，它會阻塞所有試圖在表上加鎖的事務。
Session2session_id=24：
_dexter@FAKE>update tun2_tab set id=3 ;
waiting...
看一下鎖的情況：
_sys@FAKE>select sid , type , id1 , lmode , request , block
2 from v$lock l
3 where sid in (select session_id from v$locked_object)
4 and type in ('TM', 'TX')
5 order by 1 ;
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
22 TM 82618 6 0 1 --session1 包含了表6級鎖，它正在阻塞其他的事務
22 TX 524296 6 0 0
24 TM 82618 0 3 0 --session2 它正在請求表的3級鎖。
Session1
Session2
Description
T1
insert /*+ append_values */ into tun2_tab values (1) ;
直接路徑加載會對表加6級排他鎖
T2
update tun2_tab set id=3 ;
waiting…
update需要對表加3級共享鎖，因為互斥，session2陷入阻塞狀態
所以在直接路徑加載的時候會對表加6級鎖，阻塞其他事務對表加任意類型鎖的操作。
（sqlldr 並行+直接路徑加載的時候會加4級鎖）
_dexter@FAKE>alter table tun2_tab add primary key (id) ;
Table altered.
session1 session_id=22:
_dexter@FAKE>insert into tun2_tab values (1) ;
1 rowcreated.
session2 session_id=24:
_dexter@FAKE>insert into tun2_tab values (1) ;
waiting...
lockstatus :
_sys@FAKE>select sid , type , id1 , lmode , request , block
2 from v$lock l
3 where sid in (select session_id from v$locked_object)
4 and type in ('TM', 'TX')
5 order by 1 ;
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
22 TM 82618 3 0 0
22 TX 196635 6 0 1
24 TX 65548 6 0 0
24 TM 82618 3 0 0
24 TX 196635 0 4 0
_sys@FAKE>select sid,seq#,event from v$session_wait where sid= 24 ;
SID SEQ# EVENT
-------------------- -----------------------------------------------
24 104 enq: TX - row lock contention
這裡發生了row lock等待事件。
可以看到
因為在擁有primary key 列上插入了相同的值，第二個session除了持有自己本事務的6級排他鎖之外，還在請求一個4級共享鎖。這裡發生了阻塞。如果第一個session 提交 。
第二個session會報錯。
_dexter@FAKE>insert into tun2_tab values (1) ;
insert intotun2_tab values (1)
*
ERROR atline 1:
ORA-00001:unique constraint (DEXTER.SYS_C0014094) violated
Session1
Session2
Description
T1
insert into tun2_tab values (1) ;
session1插入數據這裡涉及到了主鍵|唯一鍵
T2
insert into tun2_tab values (1) ;
waiting …
session2插入相同的記錄，會發生阻塞，因為session1的操作是懸而未決的狀態，session2中的事務能否執行取決於session1中的事務是回滾還是提交
T3
commit
session 1 中的事務提交
T4
Raise error：
ORA-00001: unique constraint (DEXTER.SYS_C0014094) violated
這一部分的阻塞比較簡單，只要發生update操作，就會對已有的行加6級排他鎖，表上加3級共享鎖。
_dexter@FAKE>select * from tun2_tab ;
ID NAME
--------------------------------------------------
1 NN
2 NN
3 NN
session1 session_id=22:
_dexter@FAKE>update tun2_tab set name = 'DEXTER' where id=1 ;
1 rowupdated.
session2 session_id=18:
_dexter@FAKE>update tun2_tab set name ='dexter' where id=2 ;
1 rowupdated.
session3 session_id=9:
_dexter@FAKE> update tun2_tab set name ='dexter' where id=1;
waiting...
來看一下鎖的情況：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
9 TX 589850 0 6 0
9 TM 82618 3 0 0
18 TX 196629 6 0 0
18 TM 82618 3 0 0
22 TX 589850 6 0 1 --session1正在阻塞 session 3
22 TM 82618 3 0 0
6 rowsselected.
由上可以看到，對單個表可以加多個3級共享鎖。
session2因為修改的是id=2 的記錄，所以可以正常執行。
session3由於修改的是id=1 的記錄，session1這個時候正在修改，並且對這一行的資源加了6級的排他鎖。所以session3 發生了阻塞
需要等待session 1 釋放後才可以順利執行。
Session1
Session2
Session3
Description
T1
update tun2_tab set name = 'DEXTER' where id=1 ;
session1 update操作會對表加3級共享鎖
T2
update tun2_tab set name ='dexter' where id=2 ;
session2 update操作 也會對表加3級共享鎖，由於更新的記錄不包括ssession1中更新的記錄id=1。所以可以順利執行
T3
update tun2_tab set name ='dexter' where id=1 ;
waiting…
session3 update操作 也會對表加3級共享鎖，由於更新的記錄包括ssession1中更新的記錄id=1。所以無法順利執行
其實對於delete、update、insert操作加鎖操作大致相同，都會對表加3級共享鎖，對修改的行加排他鎖。
所以只要想要並發的修改表中相同的行，在第一個獲取鎖的事務沒有結束前，後面的時候都會發生阻塞。
_dexter@FAKE>select * from tun2_tab ;
ID NAME
--------------------------------------------------
1 dexter
2 dexter
3 NN
session1 session_id=144 :
_dexter@FAKE>delete from tun2_tab where id =1 ;
1 rowdeleted.
session2 session_id=18 :
_dexter@FAKE>delete tun2_tab where id >1 ;
2 rowsdeleted.
session3 session_id=9 :
_dexter@FAKE>delete tun2_tab ;
waiting...
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
9 TX 524317 0 6 0
9 TM 82618 3 0 0
18 TX 655383 6 0 0
18 TM 82618 3 0 0
144 TX 524317 6 0 1
144 TM 82618 3 0 0
6 rowsselected.
發生了阻塞，只有當session 1 和session 2 的事務結束後，session 3 才可以順利完成。
Session1
Session2
Session3
Description
T1
delete from tun2_tab where id =1 ;
T2
delete tun2_tab where id >1 ;
session2 delete 操作因為不包括session 1 中的id=1的記錄，所以可以順利執行
T3
delete tun2_tab ;
waiting …
session3 delete操做，因為需要獲取id=1，id>1記錄的事務鎖，所以發生了等待。可以看到它首先是在等待id=1的事務鎖。
_dexter@FAKE>select * from tun2_tab ;
ID NAME
--------------------------------------------------
1 dexter
2 dexter
3 NN
session1 session_id=22：
_dexter@FAKE>delete from tun2_tab where id =2 ;
1 rowdeleted.
session2 session_id=18：
_dexter@FAKE>update tun2_tab set name ='dexter' where id>1 ;
waiting...
session3 session_id=9：
_dexter@FAKE>delete tun2_tab where id = 3 ;
1 rowdeleted.
查看一下鎖的情況：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
9 TX 393228 6 0 0
9 TM 82618 3 0 0
18 TX 131089 0 6 0
18 TM 82618 3 0 0
22 TX 131089 6 0 1
22 TM 82618 3 0 0
6 rowsselected.
這裡比較有趣了，因為session 2 update 的記錄包括id=2這一行，所以在id=2這一行加鎖的時候，這裡發生了transaction enqueue，它還沒來得及對任何記錄加鎖，就已經進入了等待中。
而session3執行的時候發現id=3 的這一行還沒有鎖標示，所以它順利的對id=3 的記錄加了鎖。
這個時候我們rollback 第一條記錄後
session1 ：
_dexter@FAKE>rollback ;
Rollbackcomplete.
發現session2 依然處於等待狀態中
再看一下鎖的情況：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
9 TX 393228 6 0 1
9 TM 82618 3 0 0
18 TX 589840 6 0 0
18 TX 393228 0 6 0
18 TM 82618 3 0 0
這個時候我們可以看到session2又在等待session3的事務結束以便獲取id=3這條記錄的鎖。
Session1
Session2
Session3
Description
T1
delete from tun2_tab where id =2 ;
T2
update tun2_tab set name ='dexter' where id>1 ;
waiting…
session 2 因為要獲取id=2的記錄的事務鎖所以發生阻塞，等待session1 中的事務釋放。
T3
delete tun2_tab where id = 3 ;
按照正常人的思維，比如說我。這一句應該等待session2中的事務才對。但是事實不是如此。因為session2陷入了阻塞，沒還沒有對id=3的記錄加上事務鎖，所以session3可以順利執行。
T4
commit；
T5
still waiting
因為需要id=3的記錄的事務鎖，所以又被阻塞。
session1session_id=144：
_dexter@FAKE>delete from tun2_tab where id =3 ;
1 rowdeleted.
session2session_id=18：
_dexter@FAKE> update tun2_tab set name ='dexter' whereid>1 ;
waiting..
session3session_id=9：
_dexter@FAKE>delete tun2_tab where id = 2 ;
waiting..
看一下鎖情況：
_sys@FAKE>/
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
9 TX 196635 0 6 0
9 TM 82618 3 0 0
18 TX 196635 6 0 1
18 TM 82618 3 0 0
18 TX 458767 0 6 0
144 TM 82618 3 0 0
144 TX 458767 6 0 1
7 rowsselected.
session 3 也進入了等待中，因為session2 先獲取了id=2 的行鎖，然後等待id=3 的行鎖。
Session1
Session2
Session3
Description
T1
delete from tun2_tab where id =3 ;
T2
update tun2_tab set name ='dexter' where id>1 ;
session 2 因為要獲取id=3的記錄的事務鎖所以發生阻塞，但是在阻塞之前，以及對id=1|2的記錄加了事務鎖
T3
delete tun2_tab where id = 2 ;
waiting…
發生等待。
當block中沒有多余的空間來添加ITL entry的時候，就會發生阻塞。具體可以看下面的例子：
_dexter@FAKE>create table tb_itl (id int , name varchar2(4000)) pctfree 0 initrans 1 ;
Tablecreated.
_dexter@FAKE>insert into tb_itl select level , 'd' from dual connect by level <= 10000 ;
10000 rowscreated.
_dexter@FAKE>commit ;
Commitcomplete.
_dexter@FAKE>update tb_itl set name=lpad('x',2000,name) ;
10000 rowsupdated.
_dexter@FAKE>commit ;
Commitcomplete.
上面的操作保證至少第一個block中不會有多余的空間
selectt.id,
dbms_rowid.rowid_relative_fno(t.rowid)as "FNO#",
dbms_rowid.rowid_block_number(t.rowid)as "BLK#",
dbms_rowid.rowid_row_number(t.rowid) as"ROW#"
from dexter.tb_itl t
whererownum<5 ;
_dexter@FAKE>select t.id,
2 dbms_rowid.rowid_relative_fno(t.rowid) as "FNO#",
3 dbms_rowid.rowid_block_number(t.rowid) as "BLK#",
4 dbms_rowid.rowid_row_number(t.rowid) as "ROW#"
5 from dexter.tb_itl t
6 where id<5 ;
ID FNO# BLK# ROW#
-------------------- ---------- ----------
1 6 187 0
2 6 187 1
3 6 187 2
4 6 187 3
先dump一下看一下block中剩余有幾個itl slot
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.016.00000a60 0x00c000ef.0284.14 C--- 0 scn 0x0000.003d7a84
0x02 0x0003.01c.000009ea 0x00c00153.028c.1c ---- 733 fsc 0x0000.00000000
只有2個事務槽了。
下面內容引用自網絡。
每個ITL entry包括以下的內容：
Transactionid（Xid）: 8bytes。其中包括rollback segment number, transaction table中的slot number等。
Undoblock address（Uba）: 8bytes。其中包括rollback segment block的DBA，sequence number等。
Flags:1nibble。
---- =transaction is active, or committed pending cleanout
C--- =transaction has been committed and locks cleaned out
-B-- =this undo record contains the undo for this ITL entry
--U- =transaction committed (maybe long ago); SCN is an upper bound
---T =transaction was still active at block cleanout SCN
Locks:3nibbles. 也就是所謂的行級鎖（row-level locks）
SCN orfree space credit: 6bytes. 如果這個事務已經clean out，這個值就是SCN；否則，前兩個字節表示由這個事務釋放的此block中的空間數。
我們來嘗試更改一下數據
session1 session_id=144：
_dexter@FAKE>update tb_itl set name=lpad('x',2000,name) where id =1 ;
1 rowupdated.
session2 session_id=18：
_dexter@FAKE> update tb_itl set name=lpad('x',2000,name)where id =2 ;
1 rowupdated.
session3 session_id=21：
_dexter@FAKE>update tb_itl set name=lpad('x',2000,name) where id =3 ;
waiting...
看一下鎖信息
_sys@FAKE>select sid , type , id1 , lmode , request , block
2 from v$lock l
3 where sid in (select session_id from v$locked_object)
4 and type in ('TM', 'TX')
5 order by 1 ;
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
18 TX 393241 6 0 1
18 TM 82846 3 0 0
21 TX 393241 0 4 0
21 TM 82846 3 0 0
144 TX 131088 6 0 0
144 TM 82846 3 0 0
6 rowsselected.
_sys@FAKE>select sid,seq#,event from v$session_wait where sid= 21 ;
SID SEQ# EVENT
---------- ---------------------------------------------------------
21 268 enq: TX - allocate ITL entry
_sys@FAKE>
因為在block 187中無法添加更多的ITL entry（拓展一個只需要24b）而引發的阻塞。
Session1
Session2
Session3
Description
T1
update tb_itl set name=lpad('x',2000,name) where id =1 ;
T2
update tb_itl set name=lpad('x',2000,name) where id =2 ;
T3
update tb_itl set name=lpad('x',2000,name) where id =3 ;
waiting…
常理來說這裡應該順利執行才對，可是事實上，因為block中無法再拓展出ITL entry，所以它被阻塞。
通常情況下不會發生這種情況。
解決辦法：設置表的inittrans 參數為合理值。
_dexter@FAKE>create table tb_bitmap_test (id number , gender varchar2(1)) ;
Tablecreated.
_dexter@FAKE>insert into tb_bitmap_test select level , 'F'from dual connect by level <= 3;
3 rowscreated.
_dexter@FAKE>insert into tb_bitmap_test select level , 'M'from dual connect by level <= 2;
2 rowscreated.
_dexter@FAKE>create bitmap index tb_bitmap_test_btidx1 on tb_bitmap_test(gender) ;
Indexcreated.
_dexter@FAKE>select * from tb_bitmap_test ;
ID GE
------------
1 F
2 F
3 F
1 M
2 M
session1 session_id=144:
_dexter@FAKE>update tb_bitmap_test set gender='M' where id=1 and gender='F' ;
1 rowupdated.
session2 session_id=18:
_dexter@FAKE>delete tb_bitmap_test where gender='M' and id = 1;
waiting...
session3 session_id=9 :
_dexter@FAKE>insert into tb_bitmap_test values (1,'S') ;
1 rowcreated.
鎖情況：
_sys@FAKE>@lock
SID TYPE ID1 LMODE REQUEST BLOCK
-------------- ---------- ---------- ---------- ----------
9 TM 82847 3 0 0
9 TX 196626 6 0 0
18 TX 327710 6 0 0
18 TM 82847 3 0 0
18 TX 589854 0 4 0
144 TX 589854 6 0 1
144 TM 82847 3 0 0
7 rowsselected.
不管是gender='M' 或者 'F' ,只要涉及到這兩個字段的值的dml操作都將進入等待當中（包括insert）
因為第一個session 鎖住了整個bitmap segment。但是只要gender的值不涉及M或者F即可順利執行。所以session3 順利的執行。
Session1
Session2
Session3
Description
T1
update tb_bitmap_test set gender='M' where id=1 and gender='F' ;
因為有了Bitmap索引，所以這個操作會索引表中所有gender=’M’和‘F’的記錄，並且會阻塞相關的insert操作
T2
delete tb_bitmap_test where gender='M' and id = 1;
waiting…
這裡發生了阻塞
T3
insert into tb_bitmap_test values (1,'S') ;
只要gender的值不等於M或者F即可順利執行
_dexter@FAKE>create table tun2_p (id int primary key) ;
Tablecreated.
_dexter@FAKE>create table tun2_c (pid references tun2_p(id)) ;
Tablecreated.
_dexter@FAKE>insert into tun2_c values (1) ;
insert intotun2_c values (1)
*
ERROR atline 1:
ORA-02291:integrity constraint (DEXTER.SYS_C0014143) violated - parent key not found
這裡因為有引用完整性約束，子表中的內容必須與父表中的內容匹配。因為父表中沒有id=1的記錄，所以這裡報錯
_dexter@FAKE>insert into tun2_p values (2) ;
1 rowcreated.
lock status：
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_P 9 TM 82949 3 0 0
9TX 589829 6 0 0
TUN2_C 9 TM 82952 3 0 0
_dexter@FAKE>update tun2_p set id=3 where id=2 ;
1 rowupdated.
lock status：
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
9TX 262144 6 0 0
TUN2_P 9 TM 82949 3 0 0
_dexter@FAKE>delete tun2_p where id=3 ;
1 rowdeleted.
lock status:
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
9TX 524294 6 0 0
TUN2_P 9 TM 82949 3 0 0
如果upadte和delete操作中不包含子表引用的記錄，就不會對子表加鎖。
而insert相對比較復雜一點，它會級聯的將子表鎖定。
如果在子表引用的記錄上發生更改，則會報錯。例如：
_dexter@FAKE>update tun2_p set id=3 where id=1 ;
updatetun2_p set id=3 where id=1
*
ERROR atline 1:
ORA-02292:integrity constraint (DEXTER.SYS_C0014143) violated - child record found
_dexter@FAKE>insert into tun2_c values (2) ;
1 rowcreated.
lock:
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_P 9 TM 82949 3 0 0
9TX 589825 6 0 0
TUN2_C 9 TM 82952 3 0 0
_dexter@FAKE>update tun2_c set pid=1 where pid=2 ;
1 rowupdated.
lock
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_P 9 TM 82949 3 0 0
9TX 655390 6 0 0
TUN2_C 9 TM 82952 3 0 0
_dexter@FAKE>delete from tun2_c where pid=1 ;
2 rowsdeleted.
lock:
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_P 9 TM 82949 3 0 0
9TX 196635 6 0 0
TUN2_C 9 TM 82952 3 0 0
子表的記錄一定會引用到父表的記錄，所以在對子表進行dml操作的時候，都會鎖定父表。
兩個表中現在麼有任何記錄。
session1 session_id=9:
_dexter@FAKE>insert into tun2_p values (1) ;
1 rowcreated.
看一下鎖的情況：
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_C 9 TM 82952 3 0 0
TUN2_P 9 TM 82949 3 0 0
9TX 262149 6 0 0
可以看到，當向父表中插入記錄的時候，會同時鎖定父表和子表，加表的3級共享鎖。
session1沒提交之前其他事務無法看到父表中的id=1的記錄，我們再來嘗試一下向子表中插入pid=1的記錄
session2 session_id=18:
_dexter@FAKE>insert into tun2_c values (1) ;
waiting ...
可以看到session2 進入了阻塞狀態，我們來查看一下鎖的情況
_sys@FAKE>@lock2
OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK
---------------------------------------- ---- ---------- ---------- ---------- ----------
TUN2_C 9 TM 82952 3 0 0
9TX 262149 6 0 1
TUN2_P 9 TM 82949 3 0 0
18TX 262149 0 4 0
TUN2_C 18 TM 82952 3 0 0
18TX 589848 6 0 0
TUN2_P 18 TM 82949 3 0 0
7 rowsselected.
首先我們可以看到，session2也有兩個TM表鎖，分別鎖定了子表和父表。這說明在子表更新數據的時候，也會對引用的對象加鎖。
然後我們還看到，子表陷入了等待當中。
這是因為session2 中的事務是否能夠成功執行，取決於session1 中的事務狀態。而session1 中的事務現在是懸而未決的狀態。
是不是有點和讀一致性搞混了？覺得第二個session中的事務不應該進入阻塞當中，而是直接報錯？
它不像讀一致性，可以在查詢的時候根據undo獲取一個一致性視圖。
在事務執行的時候，只和數據的當前狀態相關。
第一個session的事務rollback後
session2就會報錯
_dexter@FAKE>insert into tun2_c values (1) ;
insert intotun2_c values (1)
*
ERROR atline 1:
ORA-02291:integrity constraint (DEXTER.SYS_C0014143) violated - parent key not found
Session1
Session2
Description
T1
insert into tun2_c values (1) ;
T2
insert into tun2_c values (1) ;
waiting…
正常理解，這裡應該直接報錯，ORA-02291才對，但是這裡沒有，因為父表中id=1的記錄還是懸而未決的狀態。這是智能呢？還是智能呢？還是智能呢？那就是智能吧。
T3
rollback
T4
Raise error
ORA-02291: integrity constraint (DEXTER.SYS_C0014143) violated - parent key not found
一切都明了了，報錯了。
再次引用這張表
RS|SS
RX|SX
S
SRX|SSX
X
RS|SS
√
√
√
√
×
RX|SX
√
√
×
×
×
S
√
×
√
×
×
SRX|SSX
√
×
×
×
×
X
×
×
×
×
×
介紹一些操作
lock table tun2_tab in ROW SHARE mode ;
lmode=2
lock table tun2_tab in ROW EXCLUSIVE mode ;
lmode=3
lock table tun2_tab in SHARE MODE ;
lmode=4
lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;
lmode=5
lock table tun2_tab in EXCLUSIVE MODE ;
lmode=6
下面的示例演示驗證上表的內容
Also called a subshare table lock (SS)
Session1 session_id=35 :
dexter@STARTREK>create table tun2_tab (x int) ;
Tablecreated.
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode nowait ;
Table(s)Locked.
session2 session_id=160:
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in SHARE MODE ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;
Table(s)Locked.
dexter@STARTREK>commit;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in EXCLUSIVE MODE ;
waiting ...
看一下鎖的情況
sys@STARTREK>@lock
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 2 0 1
160 TM 76917 0 6 0
RS|SS鎖和X鎖是不能並發的，但是可以兼容其他類型的鎖。
Also called a subexclusive table lock (SX)
Session1 session_id=35 :
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
Session2 session_id=160：
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in SHARE MODE ;
waiting ...
看一下鎖的情況
sys@STARTREK>/
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 3 0 1
160 TM 76917 0 4 0
RX|SX 與 S 鎖是無法並發的，經測試SRX|SSX鎖也一樣無法與RX|SX鎖並發。
Session1 session_id=35 :
dexter@STARTREK>lock table tun2_tab in SHARE MODE ;
Table(s)Locked.
Session2 session_id=160：
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
waiting ...
鎖情況：
sys@STARTREK>/
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 4 0 1
160 TM 76917 0 3 0
S 鎖比較特殊，它不允許與RX|SX 也就是3級鎖並發，但是允許多個S鎖並發的在多個事務中持有。
例如兩個sessoin同時執行下面的命令
locktable tun2_tab in SHARE MODE ;
可以看到下面的lock信息：
sys@STARTREK>/
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 4 0 0
129 TM 76917 4 0 0
但是S鎖無法與SRX|SSX和X鎖並發。
Also called a share-subexclusive table lock (SSX)
Session1 session_id=35 :
dexter@STARTREK>lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;
Table(s)Locked.
Session2 session_id=129：
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
dexter@STARTREK>commit ;
Commitcomplete.
dexter@STARTREK>lock table tun2_tab in ROW EXCLUSIVE mode ;
waiting ...
鎖情況：
sys@STARTREK>@lock
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 5 0 1
129 TM 76917 0 3 0
SRX|SSX鎖無法與RX|SX以上的鎖並發持有。
Session1 session_id=35 :
dexter@STARTREK>lock table tun2_tab in EXCLUSIVE MODE ;
Table(s)Locked.
Session2 session_id=129：
dexter@STARTREK>lock table tun2_tab in ROW SHARE mode ;
waiting ...
X鎖無法與任何鎖並發。
上面的內容筆者已經使用PLSQL模擬出來，詳情見附錄1
下面列舉幾種常用操作的加鎖情況，是否會因為table lock發生阻塞，只要代入表中即可。
RS|SS
RX|SX
S
SRX|SSX
X
RS|SS
√
√
√
√
×
RX|SX
√
√
×
×
×
S
√
×
√
×
×
SRX|SSX
√
×
×
×
×
X
×
×
×
×
×
insert /*+ append_values */ into values
X
insert /*+ append */ into select
X
insert into values
RX
insert into select
RX
sql*ldr Conventional
RX
sql*ldr append
X
sql*ldr append+parallel
S
update
RX
delete
RX
select for update
RX
下面給出一個最簡單的示例。
_dexter@FAKE>select * from a ;
X
----------
1
2
_dexter@FAKE>select * from b ;
X
----------
1
2
s1 t1:
_dexter@FAKE>update b set x=3 where x= 1 ;
1 rowupdated.
s2 t2:
_dexter@FAKE>update a set x=3 where x=1 ;
1 rowupdated.
s1 t3:
_dexter@FAKE>update a set x=5 where x= 1 ;
s2 t4:
_dexter@FAKE>update b set x=5 where x=1 ;
s1 t5:
_dexter@FAKE>update a set x=5 where x= 1 ;
update aset x=5 where x= 1
*
ERROR atline 1:
ORA-00060:deadlock detected while waiting for resource
raise error00600 deadlock
_dexter@FAKE>select * from b ;
X
----------
3
2
s2 t6:
stillwaiting
直到s1 結束事務
Session1
Session2
Description
T1
update b set x=3 where x= 1 ;
Session1 更新B表記錄
T2
_dexter@FAKE> update a set x=3 where x=1 ;
Session2更新A表記錄
T3
update a set x=5 where x= 1 ;
waiting…
Session1在事務中嘗試修改Sessino2事務中修改的A表的數據
T4
update b set x=5 where x=1 ;
waiting…
Session2在事務中嘗試修改Sessino1事務中修改的B表的數據
T5
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
raise error 00600 deadlock
上面的就是死鎖的情況。Session1會Raise一個00060死鎖的error。Sessino2 still waiting 。
從這裡可以看到，由於邏輯錯誤鎖引發的死鎖。兩個事務都在等待對方釋放鎖資源。
第一個爭搶資源導致死鎖的語句會被取消（只是取消這一個語句，而不是結束整個事務）
