溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
Oracle中的鎖機制及實驗的分析，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。
使用鎖的目的是什么：
為了解決多用戶環境下并發操作相同的資源而造成的錯誤修改數據的問題。單用戶環境下不需要考慮鎖，因為所有操作都是串行的。下面的文章簡要的介紹了一下
要點:
鎖的分類異常復雜，enqueue、latch、mutex等，都是為了解決并發存在的，自己也有些混亂，所以也不過多解釋了。下面列舉一些對于lock的要點內容。
排他鎖：
不允許相關的資源被共享。一個資源在一個時間點內只有一個事務能夠獲取該資源的排他鎖，只有持有該鎖的事務能夠修改相關的資源,其他想要獲取鎖的事務只能等待該事務因為commit或者rollback而釋放排他鎖。
共享鎖：允許相關的資源被共享。也就是說允許多個事務同時持有某個資源的共享鎖。對于一個dml操作，會對表以及行加鎖，也就是v$lock中的TM鎖和TX鎖。
行級鎖基本原理：
行級鎖的信息是置于數據塊中的，如果要修改某一條記錄的值，其實就是在訪問相應的block，并且分配一個ITL，然后通過rowid訪問rowpiece header ，如果第二個字節lock byte(lock byte只占用1個字節，最大值為255，這也是為什么maxtrans最大為255）為0，則將其改為分配的ITL slot number。另外一個事務如果也想要修改數據，就會發現lock byte不為0，如果第一個事務還沒有結束，則第二個事務進入enqueue等待，也就是transaction enqueue。
對于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中給出。
順便引用一下經典內容：
只有被修改時，行才會被鎖定。
當一條語句修改了一條記錄，只有這條記錄上被鎖定，在Oracle數據庫中不存在鎖升級。
當某行被修改時，它將阻塞別人對它的修改。
當一個事務修改一行時，將在這個行上加上行鎖（TX），用于阻止其它事務對相同行的修改。
讀永遠不會阻止寫。
讀不會阻塞寫，但有唯一的一個例外，就是select ...for update。
寫永遠不會阻塞讀。
當一行被修改后，Oracle通過回滾段提供給數據的一致性讀
1.分別模擬insert，update和delete造成阻塞，
一個update更新語句的簡單描述
當我們更新一個表的記錄的時候，會有兩種鎖產生，一種是DML鎖（TM）也可以稱作table lock 還有一種事務鎖（TX）也可以稱作行鎖
在v$lock中可以查看到。
例如下面的例子當中：
SQL> select * from tt;
ID NAME
---------- --------------------
1 aaa
2 aaa
3 aaa
4 aaa
5 aaa
SQL> update tt set id=7;
5 rows updated.
SQL> select sid,type,lmode,request,block from v$lock where sid =(select sid from v$mystat where rownum<2);
SID TY LMODE REQUEST BLOCK
---------- -- ---------- ---------- ----------
1 TO 3 0 0
1 AE 4 0 0
1 TM 3 0 0
1 TX 6 0 0
AE是和版本化（Workspace Manager）相關的東西，這里不再過多描述。
從上面的查詢結果可以看到更新的時候會添加一個3級的表鎖，也就是 row-X (SX)鎖，保證在事務結束之前，表的結構不會被更改。多個事務可以同時持有相同表的sx鎖。還有一個6級的行鎖，exclusive (X)，保證在事務結束之前，相關的行信息不會被更改。(鎖信息存放于block中)
ok簡單示例后，來進行這一小節的主要內容，阻塞示例。
insert 阻塞
insert操作會對表加3級rx鎖，和行排他鎖，但是一般不會發生阻塞，因為讀一致性的關系，在沒提交之前只有當前session才可以操作新插入的行，對于其他事務來說 新增的記錄是不可見的。
下面列舉幾種特殊的阻塞情況。
直接路徑加載引發的阻塞
在11gr2中，可以使用insert /*+ append */ intoselect 的方式執行直接路徑加載。
或者 insert /*+append_values */ into values 的方式。
這里使用第二種。
Session1 session_id=1
SQL> select sid from v$mystat where rownum<2;
SID
----------
1
SQL> insert /*+ append_values */ into tt values (8,'b');
1 row created.
SQL> select sid , type , lmode , request , block from v$lock where sid = (select sid from v$mystat where rownum<2) ;
SID TY LMODE REQUEST BLOCK
---------- -- ---------- ---------- ----------
1 TO 3 0 0
1 AE 4 0 0
1 TM 6 0 0
1 TX 6 0 0
可以看到使用直接路徑加載的時候會對表加6級排他鎖。根據表1，它會阻塞所有試圖在表上加鎖的事務。
Session2 session_id=30
SQL> update tt set id=9;
waiting...
看一下鎖的情況：
select sid , type , id1 , lmode , request , block
from v$lock l
where sid in (select session_id from v$locked_object)
and type in ('TM', 'TX')
order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
1 TM 89451 6 0 1 --session1 包含了表6級鎖，它正在阻塞其他的事務
1 TX 262155 6 0 0
30 TM 89451 0 3 0 --session2 它正在請求表的3級鎖。
所以在直接路徑加載的時候會對表加6級鎖，阻塞其他事務對表加任意類型鎖的操作。
（sqlldr 并行+直接路徑加載的時候會加4級鎖）
因為主鍵|唯一鍵引發的阻塞
SQL> truncate table tt;
Table truncated.
SQL> insert into tt values (1,'a');
1 row created.
SQL> insert into tt values (2,'b');
1 row created.
SQL> commit;
Commit complete.
session1 session_id=1:
SQL> alter table tt add primary key (id);
Table altered.
SQL>
SQL> insert into tt values (3,'c');
1 row created.
session2 session_id=30:
SQL> insert into tt values (3,'d');
waiting...
SQL> select sid , type , id1 , lmode , request , block
from v$lock l
where sid in (select session_id from v$locked_object)
and type in ('TM', 'TX')
order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
1 TX 458773 6 0 1
1 TM 89451 3 0 0
30 TX 524308 6 0 0
30 TX 458773 0 4 0
30 TM 89451 3 0 00
SQL> set lines 200 pages 999
SQL> select sid,seq#,event from v$session_wait where sid=30;
SID SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
30 24 enq: TX - row lock contention
這里發生了row lock等待事件。
可以看到因為在擁有primary key 列上插入了相同的值，第二個session除了持有自己本事務的6級排他鎖之外，還在請求一個4級共享鎖。這里發生了阻塞。如果第一個session 提交 。
第二個session會報錯。
SQL> insert into tt values (3,'d');
insert into tt values (3,'d')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C0011620) violated
Update阻塞
這一部分的阻塞比較簡單，只要發生update操作，就會對已有的行加6級排他鎖，表上加3級共享鎖。
session1 session_id=1:
SQL> select * from tt;
ID NAME
---------- --------------------
1 a
2 b
3 c
SQL> update tt set name='AA' where id=1;
1 row updated.
session2 session_id=30:
SQL> update tt set name='BB' where id=2;
1 row updated.
session3 session_id=32:
SQL> update tt set name='ABC' where id=1;
waiting...
來看一下鎖的情況：
SQL> 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 TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
1 TX 196629 6 0 1 --session1正在阻塞 session 3
1 TM 89451 3 0 0
30 TX 327706 6 0 0
30 TM 89451 3 0 0
32 TX 196629 0 6 0
32 TM 89451 3 0 0
6 rows selected.
由上可以看到，對單個表可以加多個3級共享鎖。
session2因為修改的是id=2 的記錄，所以可以正常執行。
session3由于修改的是id=1 的記錄，session1這個時候正在修改，并且對這一行的資源加了6級的排他鎖。所以session3 發生了阻塞需要等待session 1 釋放后才可以順利執行。
Delete阻塞
其實對于delete、update、insert操作加鎖操作大致相同，都會對表加3級共享鎖，對修改的行加排他鎖。
所以只要想要并發的修改表中相同的行，在第一個獲取鎖的事務沒有結束前，后面的時候都會發生阻塞。
SQL> select * from tt;
ID NAME
---------- --------------------
1 ABC
2 BB
3 c
session1 session_id=1 :
delete from tt where id=1;
1 row deleted.
session2 session_id=30 :
SQL> delete from tt where id >1;
2 rows deleted.
session3 session_id=32
SQL> delete tt;
waiting...
SQL> select sid , type , id1 , lmode , request , block
from v$lock l
where sid in (select session_id from v$locked_object)
and type in ('TM', 'TX')
order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
1 TX 262174 6 0 1
1 TM 89451 3 0 0
30 TX 655368 6 0 0
30 TM 89451 3 0 0
32 TX 262174 0 6 0
32 TM 89451 3 0 0
6 rows selected.
發生了阻塞，只有當session 1 和session 2 的事務結束后，session 3 才可以順利完成。
下面有兩個有趣的實驗
有趣小實驗1
SQL> insert into tt values (1,'a');
1 row created.
SQL> insert into tt values (2,'b');
1 row created.
SQL> insert into tt values (3,'c');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tt;
ID NAME
---------- --------------------
1 a
2 b
3 c
session1 session_id=1
SQL> delete from tt where id=2;
1 row deleted.
session2 session_id=32
SQL> update tt set name='wang' where id>1;
waiting...
session3 session_id=32
SQL> delete from tt where id=3;
1 row deleted.
查看一下鎖的情況：
select sid , type , id1 , lmode , request , block
from v$lock l
where sid in (select session_id from v$locked_object)
and type in ('TM', 'TX')
order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
1 TX 655382 6 0 1
1 TM 89451 3 0 0
30 TX 655382 0 6 0
30 TM 89451 3 0 0
32 TX 196631 6 0 0
32 TM 89451 3 0 0
6 rows selected.
這里比較有趣了，因為session 2 update 的記錄包括id=2這一行，所以在id=2這一行加鎖的時候，這里發生了transaction enqueue，它還沒來得及對任何記錄加鎖，就已經進入了等待中。而session3執行的時候發現id=3 的這一行還沒有鎖標示，所以它順利的對id=3 的記錄加了鎖。
這個時候我們rollback 第一條記錄后
session1 ：
SQL> rollback;
Rollback complete.
發現session2 依然處于等待狀態中
再看一下鎖的情況：
SQL> select sid , type , id1 , lmode , request , block
from v$lock l
where sid in (select session_id from v$locked_object)
and type in ('TM', 'TX')
order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
30 TX 196631 0 6 0
30 TM 89451 3 0 0
30 TX 327712 6 0 0
32 TX 196631 6 0 1
32 TM 89451 3 0 0
這個時候我們可以看到session2又在等待session3的事務結束以便獲取id=3這條記錄的鎖。
有趣小實驗2
SQL> select * from tt;
ID NAME
---------- --------------------
1 a
2 wang
SQL> insert into tt values (3,'c');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tt;
ID NAME
---------- --------------------
1 a
3 c
2 wang
session1 session_id=1
SQL> delete from tt where id =3;
1 row deleted.
session2 session_id=30
SQL> update tt set name='dddddddddd' where id >1;
waiting..
session3 session_id=32
SQL> delete from tt where id =2;
1 row deleted.
SQL> 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 TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
1 TX 131087 6 0 1
1 TM 89451 3 0 0
30 TX 458774 6 0 1
30 TX 131087 0 6 0
30 TM 89451 3 0 0
32 TX 458774 0 6 0
32 TM 89451 3 0 0
7 rows selected.
session 3 也進入了等待中，因為session2 先獲取了id=2 的行鎖，然后等待id=3 的行鎖。
ITL 引起的阻塞
當block中沒有多余的空間來添加ITL entry的時候，就會發生阻塞。具體可以看下面的例子：
SQL> create table tb_itl (id int , name varchar2(4000)) pctfree 0 initrans 1 ;
Table created.
SQL> insert into tb_itl select level , 'd' from dual connect by level <= 10000 ;
10000 rows created.
SQL> commit;
Commit complete.
SQL> update tb_itl set name=lpad('x',2000,name) ;
10000 rows updated.
SQL> commit;
Commit complete.
上面的操作保證至少第一個block中不會有多余的空間
select t.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 tb_itl t
where rownum<5 ;
SQL> 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 tb_itl t
6 where rownum<5 ;
ID FNO# BLK# ROW#
---------- ---------- ---------- ----------
1 1 94905 0
2 1 94905 1
3 1 94905 2
4 1 94905 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=1
SQL>update tb_itl set name=lpad('x',2000,name) where id =1 ;
1 rowupdated.
session2 session_id=30
SQL> update tb_itl set name=lpad('x',2000,name)where id =2 ;
1 rowupdated.
session3 session_id=30
SQL>update tb_itl set name=lpad('x',2000,name) where id =3 ;
waiting...
看一下鎖信息:
select sid , type , id1 , lmode , request , block
from v$lock l
where sid in (select session_id from v$locked_object)
and type in ('TM', 'TX')
order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
1 TX 327705 6 0 1
1 TM 89470 3 0 0
30 TX 131081 6 0 0
30 TM 89470 3 0 0
32 TX 327705 0 4 0 ---申請4級別鎖
32 TM 89470 3 0 0
6 rows selected.
SQL> set lines 200
SQL> select sid,seq#,event from v$session_wait where sid=32;
SID SEQ# EVENT
---------- ---------- ----------------------------------------------------------------
32 67 enq: TX - allocate ITL entry
因為在block 94905中無法添加更多的ITL中無法添加更多的ITL entry（拓展一個只需要24b）而引發的阻塞。
通常情況下不會發生這種情況。
解決辦法：設置表的inittrans 參數為合理值。
Bitmap 引起的阻塞
SQL> create table tb_bitmap_test (id number , gender varchar2(1)) ;
Table created.
SQL> insert into tb_bitmap_test select level , 'F'from dual connect by level <= 3;
3 rows created.
SQL> insert into tb_bitmap_test select level , 'M'from dual connect by level <= 2;
2 rows created.
SQL> create bitmap index tb_bitmap_test_btidx1 on tb_bitmap_test(gender) ;
Index created.
SQL> select * from tb_bitmap_test ;
ID G
---------- -
1 F
2 F
3 F
1 M
2 M
session1 session_id=1:
SQL> update tb_bitmap_test set gender='M' where id=1 and gender='F' ;
1 row updated.
session2 session_id=30:
SQL> delete tb_bitmap_test where gender='M' and id = 1;
waiting...
session3 session_id=32
SQL> insert into tb_bitmap_test values (1,'S') ;
1 row created.
--鎖情況：
select sid , type , id1 , lmode , request , block
from v$lock l
where sid in (select session_id from v$locked_object)
and type in ('TM', 'TX')
order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ----------
1 TM 89471 3 0 0
1 TX 262147 6 0 1
30 TX 589837 6 0 0
30 TM 89471 3 0 0
30 TX 262147 0 4 0
32 TM 89471 3 0 0
32 TX 196608 6 0 0
7 rows selected.
不管是gender='M' 或者 'F' ,只要涉及到這兩個字段的值的dml操作都將進入等待當中（包括insert）
因為第一個session 鎖住了整個bitmap segment。但是只要gender的值不涉及M或者F即可順利執行。所以session3 順利的執行。
2.模擬RI鎖定導致阻塞的場景。
--初始化環境
SQL> create table tun2_p (id int primary key) ;
Table created.
SQL> create table tun2_c (pid references tun2_p(id));
Table created.
SQL> insert into tun2_c values (1);
insert into tun2_c values (1)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.SYS_C0011622) violated - parent key not found
這里因為有引用完整性約束，子表中的內容必須與父表中的內容匹配。因為父表中沒有id=1的記錄，所以這里報錯
--主表插入
SQL> insert into tun2_p values (2) ;
1 row created.
SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d
2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM', 'TX')
3 order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME
---------- -- ---------- ---------- ---------- ---------- --------------------------------
1 TX 589833 6 0 0 TUN2_P
1 TM 89475 3 0 0 TUN2_P
1 TM 89473 3 0 0 TUN2_C
1 TX 589833 6 0 0 TUN2_C
1 TM 89475 3 0 0 TUN2_C
1 TM 89473 3 0 0 TUN2_P
6 rows selected.
SQL> select * from TUN2_P;
ID
----------
2
SQL> select * from TUN2_C;
no rows selected
--主表更新（子表中沒有引用的記錄）
update tun2_p set id=3 where id=2 ;
1 row updated.
SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d
2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM', 'TX')
3 order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME
---------- -- ---------- ---------- ---------- ---------- -------------------
1 TM 89473 3 0 0 TUN2_P
1 TX 655386 6 0 0 TUN2_P
---主表刪除（子表中沒有引用的記錄）
SQL> delete tun2_p where id=3 ;
1 row deleted.
SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d
2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM', 'TX')
3 order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME
---------- -- ---------- ---------- ---------- ---------- ----------------------
1 TM 89473 3 0 0 TUN2_P
1 TX 655386 6 0 0 TUN2_P
SQL> commit;
Commit complete.
如果upadte和delete操作中不包含子表引用的記錄，就不會對子表加鎖。而insert相對比較復雜一點，它會級聯的將子表鎖定。
如果在子表引用的記錄上發生更改，則會報錯。例如：
updatetun2_p set id=3 where id=1
ERROR atline 1:
ORA-02292:integrity constraint (DEXTER.SYS_C0014143) violated - child record found
子表插入
--查詢：
SQL> select * from tun2_p;
ID
----------
2
插入子表：
SQL> insert into tun2_c values (2) ;
1 row created.
SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d
2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM', 'TX')
3 order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME
---------- -- ---------- ---------- ---------- ---------- ------------------------
1 TX 524302 6 0 0 TUN2_P
1 TM 89475 3 0 0 TUN2_P
1 TM 89473 3 0 0 TUN2_C
1 TX 524302 6 0 0 TUN2_C
1 TM 89475 3 0 0 TUN2_C
1 TM 89473 3 0 0 TUN2_P
6 rows selected.
--子表更新：
SQL> update tun2_c set pid=1 where pid=2 ;
update tun2_c set pid=1 where pid=2
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.SYS_C0011622) violated - parent key not found
--增加父鍵
SQL> insert into tun2_p values(1);
1 row created.
SQL> commit;
Commit complete.
--更新子表
SQL> update tun2_c set pid=1 where pid=2 ;
1 row updated.
SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d
2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM', 'TX')
3 order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME
---------- -- ---------- ---------- ---------- ---------- ------------------
1 TX 196632 6 0 0 TUN2_P
1 TM 89475 3 0 0 TUN2_P
1 TM 89473 3 0 0 TUN2_C
1 TX 196632 6 0 0 TUN2_C
1 TM 89475 3 0 0 TUN2_C
1 TM 89473 3 0 0 TUN2_P
6 rows selected.
--子表刪除
SQL> delete from tun2_c where pid=1 ;
1 row deleted.
SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d
2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM', 'TX')
3 order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME
---------- -- ---------- ---------- ---------- ---------- ----------------------------
1 TX 196632 6 0 0 TUN2_P
1 TM 89475 3 0 0 TUN2_P
1 TM 89473 3 0 0 TUN2_C
1 TX 196632 6 0 0 TUN2_C
1 TM 89475 3 0 0 TUN2_C
1 TM 89473 3 0 0 TUN2_P
6 rows selected.
子表的記錄一定會引用到父表的記錄，所以在對子表進行dml操作的時候，都會鎖定父表。
復雜示例
兩個表中現在么有任何記錄。
session1 session_id=1
SQL> select sid from v$mystat where rownum<2;
SID
----------
1:
SQL> commit;
Commit complete.
SQL> select * from tun2_p;
ID
----------
1
2
SQL> select * from tun2_c;
no rows selected
SQL> insert into tun2_p values (3);
1 row created.
SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d
2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM', 'TX')
3 order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME
---------- -- ---------- ---------- ---------- ----------
1 TX 524309 6 0 0 TUN2_P
1 TM 89475 3 0 0 TUN2_P
1 TM 89473 3 0 0 TUN2_C
1 TX 524309 6 0 0 TUN2_C
1 TM 89475 3 0 0 TUN2_C
1 TM 89473 3 0 0 TUN2_P
6 rows selected.
可以看到，當向父表中插入記錄的時候，會同時鎖定父表和子表，加表的3級共享鎖。
session1沒提交之前其他事務無法看到父表中的id=1的記錄，我們再來嘗試一下向子表中插入pid=1的記錄
session2 session_id=30:
SQL>insert into tun2_c values (3);
waiting ...
可以看到session2 進入了阻塞狀態，我們來查看一下鎖的情況
SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d
2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM', 'TX')
3 order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME
---------- -- ---------- ---------- ---------- ---------- -------------------------
1 TX 524309 6 0 1 TUN2_P
1 TM 89473 3 0 0 TUN2_P
1 TM 89475 3 0 0 TUN2_P
1 TM 89473 3 0 0 TUN2_C
1 TM 89475 3 0 0 TUN2_C
1 TX 524309 6 0 1 TUN2_C
30 TM 89473 3 0 0 TUN2_C
30 TM 89475 3 0 0 TUN2_C
30 TX 262146 6 0 0 TUN2_C
30 TX 524309 0 4 0 TUN2_C
30 TM 89473 3 0 0 TUN2_P
30 TM 89475 3 0 0 TUN2_P
30 TX 262146 6 0 0 TUN2_P
30 TX 524309 0 4 0 TUN2_P
14 rows selected.
首先我們可以看到，session2也有兩個TM表鎖，分別鎖定了子表和父表。這說明在子表更新數據的時候，也會對引用的對象加鎖。
然后我們還看到，子表陷入了等待當中。
這是因為session2 中的事務是否能夠成功執行，取決于session1 中的事務狀態。而session1 中的事務現在是懸而未決的狀態。
是不是有點和讀一致性搞混了？覺得第二個session中的事務不應該進入阻塞當中，而是直接報錯？
它不像讀一致性，可以在查詢的時候根據undo獲取一個一致性視圖。
在事務執行的時候，只和數據的當前狀態相關。
第一個session的事務rollback后session2就會報錯
SQL>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
3.從mode 2-6 的TM鎖相互間的互斥示例
介紹一些操作：
下面的示例演示驗證上表的內容
Row Share (RS)
Also called a subshare table lock (SS)
Session1 session_id=1 :
SQL>create table tun2_tab (x int) ;
Tablecreated.
SQL>lock table tun2_tab in ROW SHARE mode nowait ;
Table(s)Locked.
session2 session_id=30:
SQL> lock table tun2_tab in ROW SHARE mode ;
Table(s) Locked.
SQL> commit;
Commit complete.
SQL> lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s) Locked.
SQL> commit;
Commit complete.
SQL> lock table tun2_tab in SHARE MODE ;
Table(s) Locked.
SQL> commit ;
Commit complete.
SQL> lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;
Table(s) Locked.
SQL> commit;
Commit complete.
SQL> lock table tun2_tab in EXCLUSIVE MODE ;
waiting ...
看一下鎖的情況
SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d
2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in ('TM', 'TX')
3 order by 1 ;
SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME
---------- -- ---------- ---------- ---------- ---------- ------------
1 TM 89479 2 0 1 TUN2_TAB
30 TM 89479 0 6 0 TUN2_TAB
RS|SS鎖和X鎖是不能并發的，但是可以兼容其他類型的鎖。
Row Exclusive TableLock （RX|SX）
Also called a subexclusive table lock (SX)
Session1 session_id=1
SQL>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
Session2 session_id=30
SQL>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
SQL>commit ;
Commitcomplete.
SQL>lock table tun2_tab in ROW EXCLUSIVE mode ;
Table(s)Locked.
SQL>commit ;
Commitcomplete.
SQL>lock table tun2_tab in SHARE MODE ;
waiting ...
看一下鎖的情況
SQL>/
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鎖并發。
Share Table Lock（S）
Session1 session_id=1 :
SQL>lock table tun2_tab in SHARE MODE ;
Table(s)Locked.
Session2 session_id=30
SQL>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
SQL>commit ;
Commitcomplete.
SQL>lock table tun2_tab in ROW EXCLUSIVE mode ;
waiting ...
鎖情況：
SQL>/
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 4 0 1
160 TM 76917 0 3 0
S 鎖比較特殊，它不允許與RX|SX 也就是3級鎖并發，但是允許多個S鎖并發的在多個事務中持有。
例如兩個sessoin同時執行下面的命令
lock table tun2_tab in SHARE MODE ;
可以看到下面的lock信息：
SQL>/
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 4 0 0
129 TM 76917 4 0 0
但是S鎖無法與SRX|SSX和X鎖并發。
Share Row ExclusiveTable Lock (SRX|SSX）
Also called a share-subexclusive table lock (SSX)
Session1 session_id=1 :
SQL>lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;
Table(s)Locked.
Session2 session_id=129：
SQL>lock table tun2_tab in ROW SHARE mode ;
Table(s)Locked.
SQL>commit ;
Commitcomplete.
SQL>lock table tun2_tab in ROW EXCLUSIVE mode ;
waiting ...
鎖情況：
SQL>@lock
SID TY ID1 LMODE REQUEST BLOCK
------------ ---------- ---------- ---------- ----------
35 TM 76917 5 0 1
129 TM 76917 0 3 0
SRX|SSX鎖無法與RX|SX以上的鎖并發持有。
Exclusive Table Lock(X)
Session1 session_id=1 :
SQL>lock table tun2_tab in EXCLUSIVE MODE ;
Table(s)Locked.
Session2 session_id=129：
SQL>lock table tun2_tab in ROW SHARE mode ;
waiting ...
X鎖無法與任何鎖并發。
4.導致死鎖的SQL示例。
下面給出一個最簡單的示例
SQL> create table a (x int);
Table created.
SQL> create table b(x int);
Table created.
SQL> insert into a values(1);
1 row created.
SQL> insert into a values(2);
1 row created.
SQL> insert into b values(1);
1 row created.
SQL> insert into b values(2);
1 row created.
SQL> commit;
Commit complete.
SQL>select * from a ;
X
------------
1
2
SQL>select * from b ;
X
----------
1
2
s1 t1:
SQL>update b set x=3 where x= 1 ;
1 row updated.
s2 t2:
SQL>update a set x=3 where x=1 ;
1 row updated.
s1 t3:
SQL>update a set x=5 where x= 1 ;
s2 t4:
SQL>update b set x=5 where x=1 ;
s1 t5:
SQL>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
SQL>select * from b ;
X
----------
3
2
s2 t6:
still waiting
直到s1 結束事務
從這里可以看到，由于邏輯錯誤鎖引發的死鎖。兩個事務都在等待對方釋放鎖資源。
第一個爭搶資源導致死鎖的語句會被取消（只是取消這一個語句，而不是結束整個事務）
看完上述內容，你們掌握Oracle中的鎖機制及實驗的分析的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
