溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
Oracle中的鎖機(jī)制及實驗的分析，相信很多沒有經(jīng)驗的人對此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。
使用鎖的目的是什么：
為了解決多用戶環(huán)境下并發(fā)操作相同的資源而造成的錯誤修改數(shù)據(jù)的問題。單用戶環(huán)境下不需要考慮鎖，因為所有操作都是串行的。下面的文章簡要的介紹了一下
要點:
鎖的分類異常復(fù)雜，enqueue、latch、mutex等，都是為了解決并發(fā)存在的，自己也有些混亂，所以也不過多解釋了。下面列舉一些對于lock的要點內(nèi)容。
排他鎖：
不允許相關(guān)的資源被共享。一個資源在一個時間點內(nèi)只有一個事務(wù)能夠獲取該資源的排他鎖，只有持有該鎖的事務(wù)能夠修改相關(guān)的資源,其他想要獲取鎖的事務(wù)只能等待該事務(wù)因為commit或者rollback而釋放排他鎖。
共享鎖：允許相關(guān)的資源被共享。也就是說允許多個事務(wù)同時持有某個資源的共享鎖。對于一個dml操作，會對表以及行加鎖，也就是v$lock中的TM鎖和TX鎖。
行級鎖基本原理：
行級鎖的信息是置于數(shù)據(jù)塊中的，如果要修改某一條記錄的值，其實就是在訪問相應(yīng)的block，并且分配一個ITL，然后通過rowid訪問rowpiece header ，如果第二個字節(jié)lock byte(lock byte只占用1個字節(jié)，最大值為255，這也是為什么maxtrans最大為255）為0，則將其改為分配的ITL slot number。另外一個事務(wù)如果也想要修改數(shù)據(jù)，就會發(fā)現(xiàn)lock byte不為0，如果第一個事務(wù)還沒有結(jié)束，則第二個事務(wù)進(jìn)入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字段中的數(shù)字對應(yīng)的鎖類型
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)
為了更好的開展下面的內(nèi)容，這里列舉一下各種TM鎖類型的兼容情況。
詳細(xì)驗證情況會在4中給出。
順便引用一下經(jīng)典內(nèi)容：
只有被修改時，行才會被鎖定。
當(dāng)一條語句修改了一條記錄，只有這條記錄上被鎖定，在Oracle數(shù)據(jù)庫中不存在鎖升級。
當(dāng)某行被修改時，它將阻塞別人對它的修改。
當(dāng)一個事務(wù)修改一行時，將在這個行上加上行鎖（TX），用于阻止其它事務(wù)對相同行的修改。
讀永遠(yuǎn)不會阻止寫。
讀不會阻塞寫，但有唯一的一個例外，就是select ...for update。
寫永遠(yuǎn)不會阻塞讀。
當(dāng)一行被修改后，Oracle通過回滾段提供給數(shù)據(jù)的一致性讀
1.分別模擬insert，update和delete造成阻塞，
一個update更新語句的簡單描述
當(dāng)我們更新一個表的記錄的時候，會有兩種鎖產(chǎn)生，一種是DML鎖（TM）也可以稱作table lock 還有一種事務(wù)鎖（TX）也可以稱作行鎖
在v$lock中可以查看到。
例如下面的例子當(dāng)中：
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）相關(guān)的東西，這里不再過多描述。
從上面的查詢結(jié)果可以看到更新的時候會添加一個3級的表鎖，也就是 row-X (SX)鎖，保證在事務(wù)結(jié)束之前，表的結(jié)構(gòu)不會被更改。多個事務(wù)可以同時持有相同表的sx鎖。還有一個6級的行鎖，exclusive (X)，保證在事務(wù)結(jié)束之前，相關(guān)的行信息不會被更改。(鎖信息存放于block中)
ok簡單示例后，來進(jìn)行這一小節(jié)的主要內(nèi)容，阻塞示例。
insert 阻塞
insert操作會對表加3級rx鎖，和行排他鎖，但是一般不會發(fā)生阻塞，因為讀一致性的關(guān)系，在沒提交之前只有當(dāng)前session才可以操作新插入的行，對于其他事務(wù)來說 新增的記錄是不可見的。
下面列舉幾種特殊的阻塞情況。
直接路徑加載引發(fā)的阻塞
在11gr2中，可以使用insert /*+ append */ intoselect 的方式執(zhí)行直接路徑加載。
或者 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級排他鎖。根據(jù)表1，它會阻塞所有試圖在表上加鎖的事務(wù)。
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級鎖，它正在阻塞其他的事務(wù)
1 TX 262155 6 0 0
30 TM 89451 0 3 0 --session2 它正在請求表的3級鎖。
所以在直接路徑加載的時候會對表加6級鎖，阻塞其他事務(wù)對表加任意類型鎖的操作。
（sqlldr 并行+直接路徑加載的時候會加4級鎖）
因為主鍵|唯一鍵引發(fā)的阻塞
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
這里發(fā)生了row lock等待事件。
可以看到因為在擁有primary key 列上插入了相同的值，第二個session除了持有自己本事務(wù)的6級排他鎖之外，還在請求一個4級共享鎖。這里發(fā)生了阻塞。如果第一個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阻塞
這一部分的阻塞比較簡單，只要發(fā)生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 的記錄，所以可以正常執(zhí)行。
session3由于修改的是id=1 的記錄，session1這個時候正在修改，并且對這一行的資源加了6級的排他鎖。所以session3 發(fā)生了阻塞需要等待session 1 釋放后才可以順利執(zhí)行。
Delete阻塞
其實對于delete、update、insert操作加鎖操作大致相同，都會對表加3級共享鎖，對修改的行加排他鎖。
所以只要想要并發(fā)的修改表中相同的行，在第一個獲取鎖的事務(wù)沒有結(jié)束前，后面的時候都會發(fā)生阻塞。
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.
發(fā)生了阻塞，只有當(dāng)session 1 和session 2 的事務(wù)結(jié)束后，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這一行加鎖的時候，這里發(fā)生了transaction enqueue，它還沒來得及對任何記錄加鎖，就已經(jīng)進(jìn)入了等待中。而session3執(zhí)行的時候發(fā)現(xiàn)id=3 的這一行還沒有鎖標(biāo)示，所以它順利的對id=3 的記錄加了鎖。
這個時候我們rollback 第一條記錄后
session1 ：
SQL> rollback;
Rollback complete.
發(fā)現(xiàn)session2 依然處于等待狀態(tài)中
再看一下鎖的情況：
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的事務(wù)結(jié)束以便獲取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 也進(jìn)入了等待中，因為session2 先獲取了id=2 的行鎖，然后等待id=3 的行鎖。
ITL 引起的阻塞
當(dāng)block中沒有多余的空間來添加ITL entry的時候，就會發(fā)生阻塞。具體可以看下面的例子：
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個事務(wù)槽了。
下面內(nèi)容引用自網(wǎng)絡(luò)。
每個ITL entry包括以下的內(nèi)容：
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. 如果這個事務(wù)已經(jīng)clean out，這個值就是SCN；否則，前兩個字節(jié)表示由這個事務(wù)釋放的此block中的空間數(shù)。
我們來嘗試更改一下數(shù)據(jù)
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）而引發(fā)的阻塞。
通常情況下不會發(fā)生這種情況。
解決辦法：設(shè)置表的inittrans 參數(shù)為合理值。
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操作都將進(jìn)入等待當(dāng)中（包括insert）
因為第一個session 鎖住了整個bitmap segment。但是只要gender的值不涉及M或者F即可順利執(zhí)行。所以session3 順利的執(zhí)行。
2.模擬RI鎖定導(dǎo)致阻塞的場景。
--初始化環(huán)境
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
這里因為有引用完整性約束，子表中的內(nèi)容必須與父表中的內(nèi)容匹配。因為父表中沒有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相對比較復(fù)雜一點，它會級聯(lián)的將子表鎖定。
如果在子表引用的記錄上發(fā)生更改，則會報錯。例如：
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.
子表的記錄一定會引用到父表的記錄，所以在對子表進(jìn)行dml操作的時候，都會鎖定父表。
復(fù)雜示例
兩個表中現(xiàn)在么有任何記錄。
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.
可以看到，當(dāng)向父表中插入記錄的時候，會同時鎖定父表和子表，加表的3級共享鎖。
session1沒提交之前其他事務(wù)無法看到父表中的id=1的記錄，我們再來嘗試一下向子表中插入pid=1的記錄
session2 session_id=30:
SQL>insert into tun2_c values (3);
waiting ...
可以看到session2 進(jìn)入了阻塞狀態(tài)，我們來查看一下鎖的情況
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表鎖，分別鎖定了子表和父表。這說明在子表更新數(shù)據(jù)的時候，也會對引用的對象加鎖。
然后我們還看到，子表陷入了等待當(dāng)中。
這是因為session2 中的事務(wù)是否能夠成功執(zhí)行，取決于session1 中的事務(wù)狀態(tài)。而session1 中的事務(wù)現(xiàn)在是懸而未決的狀態(tài)。
是不是有點和讀一致性搞混了？覺得第二個session中的事務(wù)不應(yīng)該進(jìn)入阻塞當(dāng)中，而是直接報錯？
它不像讀一致性，可以在查詢的時候根據(jù)undo獲取一個一致性視圖。
在事務(wù)執(zhí)行的時候，只和數(shù)據(jù)的當(dāng)前狀態(tài)相關(guān)。
第一個session的事務(wù)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鎖相互間的互斥示例
介紹一些操作：
下面的示例演示驗證上表的內(nèi)容
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鎖是不能并發(fā)的，但是可以兼容其他類型的鎖。
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 鎖是無法并發(fā)的，經(jīng)測試SRX|SSX鎖也一樣無法與RX|SX鎖并發(fā)。
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級鎖并發(fā)，但是允許多個S鎖并發(fā)的在多個事務(wù)中持有。
例如兩個sessoin同時執(zhí)行下面的命令
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鎖并發(fā)。
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以上的鎖并發(fā)持有。
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鎖無法與任何鎖并發(fā)。
4.導(dǎo)致死鎖的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 結(jié)束事務(wù)
從這里可以看到，由于邏輯錯誤鎖引發(fā)的死鎖。兩個事務(wù)都在等待對方釋放鎖資源。
第一個爭搶資源導(dǎo)致死鎖的語句會被取消（只是取消這一個語句，而不是結(jié)束整個事務(wù)）
看完上述內(nèi)容，你們掌握Oracle中的鎖機(jī)制及實驗的分析的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
