模擬RI鎖定導致阻塞的場景
1.建表並驗證對和表DML操作時的鎖定狀態
a是主表，定義了id欄位為主鍵
b是從表，id欄位是引用主表的id欄位
結論：
BYS@dg2>select distinct sid from v$mystat;
SID
----------
17
BYS@dg2>create table a(id int primary key);
Table created.
BYS@dg2>create table b(id references a(id));
Table created.
BYS@dg2>insert into a values(1);
1 row created.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0 insert由於有從屬關係因此會在2個表上都加3號共享鎖
17 TM 75056 0 3 0 0
17 TX 458758 888 6 0 0
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message to client
49 SQL*Net message from client
BYS@dg2>col object_name for a20
BYS@dg2>select object_name from dba_objects where object_id in(75054,75056);
OBJECT_NAME
--------------------
A
B
BYS@dg2>commit; 鎖會隨著事務的結束而釋放
Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
no rows selected
BYS@dg2>select * from a;
ID
----------
1
BYS@dg2>select * from b;
no rows selected
BYS@dg2>update a set id=111 where id=1;
1 row updated.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0 update現在只對主表有鎖定，從表沒有鎖定
17 TX 393219 1031 6 0 0
BYS@dg2>commit;
Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
no rows selected
BYS@dg2>select * from a;
ID
----------
111
BYS@dg2>delete a;
1 row deleted.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0 delete也是隻對主表有鎖定，從表沒有鎖定
17 TX 524310 1244 6 0 0
BYS@dg2>commit;
Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
no rows selected
#########################################################################
BYS@dg2>insert into b values(2); --直接給從表插入記錄，如果主表沒有的話，會報錯違反引用完整性約束
insert into b values(2)
*
ERROR at line 1:
ORA-02291: integrity constraint (BYS.SYS_C0011302) violated - parent key not found
BYS@dg2>insert into a values(2);
1 row created.
BYS@dg2>commit;
Commit complete.
BYS@dg2>select * from a;
ID
----------
2
BYS@dg2>select * from b;
no rows selected
BYS@dg2>insert into b values(2);
1 row created.
BYS@dg2>select * from b;
ID
----------
2
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0
17 TM 75056 0 3 0 0 從表的insert也會對主從表同時加鎖
17 TX 196634 1043 6 0 0
BYS@dg2>commit;
Commit complete.
BYS@dg2>delete b;
1 row deleted.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0
17 TM 75056 0 3 0 0 從表delete也會對主從表同時加鎖
17 TX 393245 1031 6 0 0
#############################################
BYS@dg2>select distinct sid from v$mystat;
SID
----------
46
BYS@dg2>select * from a;
ID
----------
2
BYS@dg2>select * from b;
no rows selected
BYS@dg2>insert into a values(33);
1 row created.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
46 TM 75054 0 3 0 0 insert由於有從屬關係因此會在2個表上都加3號共享鎖
46 TM 75056 0 3 0 0
46 TX 458758 888 6 0 0
此時在會話2上也執行向從表插入的動作會hang住
BYS@dg2>insert into b values(33);
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 589856 1025 0 4 0 主從表插入後產生了2個TX鎖，這說明這是2條不同的獨立的記錄，不是爭用同一條記錄
49 TM 75054 0 3 0 0
49 TM 75056 0 3 0 0
46 TM 75054 0 3 0 0
46 TM 75056 0 3 0 0
49 TX 524298 1245 6 0 0 插入成功，修改值未決
46 TX 589856 1025 6 0 1 46阻塞49，6代表插入成功，修改值未提交是未決狀態
有一個TX鎖正在申請4號鎖，是因為2條記錄的修改值都是未決狀態違反了引用完整性約束從而產生阻塞。導致156會話hang住不能前進。
a是主表，定義了id欄位為主鍵
b是從表，id欄位是引用主表的id欄位
結論：
BYS@dg2>select distinct sid from v$mystat;
SID
----------
17
BYS@dg2>create table a(id int primary key);
Table created.
BYS@dg2>create table b(id references a(id));
Table created.
BYS@dg2>insert into a values(1);
1 row created.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0 insert由於有從屬關係因此會在2個表上都加3號共享鎖
17 TM 75056 0 3 0 0
17 TX 458758 888 6 0 0
BYS@dg2>select sid,event from v$session_wait where sid in (49,17);
SID EVENT
---------- ----------------------------------------------------------------
17 SQL*Net message to client
49 SQL*Net message from client
BYS@dg2>col object_name for a20
BYS@dg2>select object_name from dba_objects where object_id in(75054,75056);
OBJECT_NAME
--------------------
A
B
BYS@dg2>commit; 鎖會隨著事務的結束而釋放
Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
no rows selected
BYS@dg2>select * from a;
ID
----------
1
BYS@dg2>select * from b;
no rows selected
BYS@dg2>update a set id=111 where id=1;
1 row updated.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0 update現在只對主表有鎖定，從表沒有鎖定
17 TX 393219 1031 6 0 0
BYS@dg2>commit;
Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
no rows selected
BYS@dg2>select * from a;
ID
----------
111
BYS@dg2>delete a;
1 row deleted.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0 delete也是隻對主表有鎖定，從表沒有鎖定
17 TX 524310 1244 6 0 0
BYS@dg2>commit;
Commit complete.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
no rows selected
#########################################################################
BYS@dg2>insert into b values(2); --直接給從表插入記錄，如果主表沒有的話，會報錯違反引用完整性約束
insert into b values(2)
*
ERROR at line 1:
ORA-02291: integrity constraint (BYS.SYS_C0011302) violated - parent key not found
BYS@dg2>insert into a values(2);
1 row created.
BYS@dg2>commit;
Commit complete.
BYS@dg2>select * from a;
ID
----------
2
BYS@dg2>select * from b;
no rows selected
BYS@dg2>insert into b values(2);
1 row created.
BYS@dg2>select * from b;
ID
----------
2
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0
17 TM 75056 0 3 0 0 從表的insert也會對主從表同時加鎖
17 TX 196634 1043 6 0 0
BYS@dg2>commit;
Commit complete.
BYS@dg2>delete b;
1 row deleted.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
17 TM 75054 0 3 0 0
17 TM 75056 0 3 0 0 從表delete也會對主從表同時加鎖
17 TX 393245 1031 6 0 0
#############################################
BYS@dg2>select distinct sid from v$mystat;
SID
----------
46
BYS@dg2>select * from a;
ID
----------
2
BYS@dg2>select * from b;
no rows selected
BYS@dg2>insert into a values(33);
1 row created.
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
46 TM 75054 0 3 0 0 insert由於有從屬關係因此會在2個表上都加3號共享鎖
46 TM 75056 0 3 0 0
46 TX 458758 888 6 0 0
此時在會話2上也執行向從表插入的動作會hang住
BYS@dg2>insert into b values(33);
BYS@dg2>select sid,type,id1,id2,lmode,request,block from v$lock where type in('TM','TX');
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
49 TX 589856 1025 0 4 0 主從表插入後產生了2個TX鎖，這說明這是2條不同的獨立的記錄，不是爭用同一條記錄
49 TM 75054 0 3 0 0
49 TM 75056 0 3 0 0
46 TM 75054 0 3 0 0
46 TM 75056 0 3 0 0
49 TX 524298 1245 6 0 0 插入成功，修改值未決
46 TX 589856 1025 6 0 1 46阻塞49，6代表插入成功，修改值未提交是未決狀態
有一個TX鎖正在申請4號鎖，是因為2條記錄的修改值都是未決狀態違反了引用完整性約束從而產生阻塞。導致156會話hang住不能前進。
相關文章
- Oracle LOCK內部機制及最佳實踐系列(二)模擬RI鎖定導致阻塞的場景，並分析v$lockOracle
- MySQL單表模擬鎖的幾個場景MySql
- ORACLE透明加密場景模擬Oracle加密
- ANALYZE導致的阻塞問題分析
- 模擬阻塞會話例項會話
- 5601. 設計有序流 （場景模擬）
- jvm 之 記憶體溢位場景模擬JVM記憶體溢位
- ORA-00060: Deadlock detected（場景模擬)
- 使用IDEA模擬git命令使用的常見場景IdeaGit
- Oracle使用者密碼被鎖定導致的故障Oracle密碼
- 阻塞(block)過程模擬與分析！BloC
- [20181030]模擬分散式事務掛起導致TX鎖爭用.txt分散式
- 連結伺服器查詢導致的阻塞伺服器
- 搭建死鎖場景
- 專案中多次操作SharedPreferences導致ANR場景的解決
- 模擬insert，update和delete造成阻塞的示例delete
- pytorch dataloader num_workers引數設定導致訓練阻塞PyTorch
- 關於oracle死鎖的模擬Oracle
- 資料型別隱式轉換導致的阻塞資料型別
- redis AOF落地策略rewrite導致阻塞問題Redis
- Apache HttpClient 沒有設定time out導致應用長時間阻塞的問題ApacheHTTPclient
- 特定的閂鎖和互斥場景
- JVM 深入筆記（2）記憶體溢位場景模擬JVM筆記記憶體溢位
- MySQL中2個select被阻塞場景的原因MySql
- 【DEADLOCK】Oracle“死鎖”模擬Oracle
- 模擬SQLserver死鎖現象SQLServer
- JavaScript模擬物件導向JavaScript物件
- Jmeter(五十)_效能測試模擬真實場景下的使用者操作JMeter
- oracle鎖阻塞的分析Oracle
- JVM 深入筆記（2）記憶體區溢位場景模擬JVM筆記記憶體
- oracle 使用者user鎖定lock如何知道是什麼原因導致的Oracle
- FLASH場景式導航
- MySQL For Update導致全表排他鎖MySql
- oracle bug 6825287導致DX鎖等待Oracle
- LoadRunner的場景設定
- 【EM】鎖等待故障模擬及排查
- Apollo模擬平臺如何Hold住99.9999%的複雜場景？
- MySQL死鎖系列-常見加鎖場景分析MySql