和外來鍵相關的阻塞和死鎖問題總結
外來鍵上沒有index操作主表資料時常會引起阻塞和deadlock問題
[@more@]外來鍵引起的阻塞：
session 1:
SQL> create table p(id int primary key ) tablespace users;
表已建立。
SQL> create table r(id int references p on delete cascade) tablespace users;
表已建立。
SQL> insert into p values(1);
已建立 1 行。
SQL> insert into p values(2);
已建立 1 行。
SQL> insert into p values(3);
已建立 1 行。
SQL> commit;
提交完成。
SQL> select distinct sid from v$mystat;
SID
----------
159
SQL> select * from p;
ID
----------
1
2
3
SQL> delete from p where id=1;
已刪除 1 行。
SQL>
--=======================
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
159 TM 13017 0 3 0 9 0
159 TX 589854 449 6 0 9 0
159 TM 13020 0 3 0 9 0
SQL> col object_name format a10
SQL> select object_id,object_name from dba_objects where object_id in (13017,130
20);
OBJECT_ID OBJECT_NAM
---------- ----------
13017 P
13020 R
--=========================
很明顯session 1中的語句delete from p where id=1;在字表r上加了鎖，mode為3；
--=========================
session 2：
SQL> select distinct sid from v$mystat;
SID
----------
128
SQL> delete from p where id=2;
等待、被session 1阻塞了
--=========================
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 3 0 9 0
128 TM 13020 0 0 5 9 0
159 TX 589854 449 6 0 90 0
159 TM 13020 0 3 0 90 1
159 TM 13017 0 3 0 90 0
SQL>
很明顯session 2中的語句delete from p where id=2;請求鎖mode為5被
session 1中的語句delete from p where id=1加在r上的鎖mode為3阻塞了;
由於mode 3對應的RX(row exclusive)和mode 5對應的SRX(share row exclusive)不能相容，因此
session 2被阻塞了。
--=========================
外來鍵引起的死鎖：
rollback掉上面session 1和session 2中的sql
--=========================
session 1:
SQL> insert into r values(2);
已建立 1 行。
SQL>
--=======================
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
159 TM 13017 0 2 0 15 0
159 TX 393228 371 6 0 15 0
159 TM 13020 0 3 0 15 0
SQL>
--=======================
session 2:
SQL> insert into r values(2);
已建立 1 行。
SQL>
--=======================
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 2 0 14 0
128 TX 262168 360 6 0 14 0
128 TM 13020 0 3 0 14 0
159 TX 393228 371 6 0 55 0
159 TM 13020 0 3 0 55 0
159 TM 13017 0 2 0 55 0
已選擇6行。
SQL>
--=======================
當我們向子表r中分別透過session 1和2插入資料時，此時發現在r表上家了鎖mode是3，在主表
p上加的鎖mode是2(RS: row share),此時別沒有阻塞，接下來操作主表...
session 1：
SQL> delete from p where id=1;
阻塞...
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 2 0 332 0
128 TX 262168 360 6 0 332 0
128 TM 13020 0 3 0 332 1
159 TX 393228 371 6 0 373 0
159 TM 13020 0 3 5 373 0
159 TM 13017 0 3 0 29 0
已選擇6行。
SQL>
從最後一個欄位block=1發現session 1(sid:159)請求的鎖mode 5被session 2(sid:128)加在子表(r)
上的3鎖阻塞...(上面已經說了mode 3和5不能相容)；
--=========================
session 2:
SQL> delete from p where id=3;
阻塞...
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 3 0 30 0
128 TX 262168 360 6 0 569 0
128 TM 13020 0 3 5 569 0
159 TX 393228 371 6 0 610 0
159 TM 13020 0 3 0 610 1
159 TM 13017 0 2 0 30 0
已選擇6行。
SQL>
從最後一個欄位block=1發現session 2(sid:128)請求的鎖mode 5被session 1(sid:159)加在子表(r)
上的3鎖阻塞...(上面已經說了mode 3和5不能相容)；這樣session 1和session 2相互阻塞最終形成了死鎖
，當然死鎖oracle會自動偵測並且解除，於是session 1中出現了deadlock被解除的提示資訊：
session 1：
SQL> delete from p where id=1;
delete from p where id=1
*
第 1 行出現錯誤:
ORA-00060: 等待資源時檢測到死鎖
SQL>
--=========================
如何避免使用外來鍵而引起的死鎖：
rollback掉上面session 1和session 2中的sql：
session 1：
SQL> select * from p;
ID
----------
1
2
3
SQL> select * from r;
未選定行
SQL> create index idx_r on r(id) tablespace users;
索引已建立。
SQL> insert into r values(2);
已建立 1 行。
SQL>
--========================
session 3：
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
159 TM 13017 0 2 0 78 0
159 TX 131078 387 6 0 78 0
159 TM 13020 0 3 0 78 0
SQL>
--=======================
session 2:
SQL> insert into r values(2);
已建立 1 行。
SQL>
--========================
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 2 0 22 0
128 TX 393219 372 6 0 22 0
128 TM 13020 0 3 0 22 0
159 TX 131078 387 6 0 124 0
159 TM 13020 0 3 0 124 0
159 TM 13017 0 2 0 124 0
已選擇6行。
SQL>
--==========================
session 1:
SQL> delete from p where id=1;
已刪除 1 行。
SQL>
--========================
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 2 0 121 0
128 TX 393219 372 6 0 121 0
128 TM 13020 0 3 0 121 0
159 TX 131078 387 6 0 223 0
159 TM 13020 0 3 0 223 0
159 TM 13017 0 3 0 27 0
已選擇6行。
SQL>
--=======================
我們發現在子表r上建立了index之後，session 1(sid:159)中的操作delete from p where id=1執行之後
加在主表p上的鎖mode由原來的2變成了3，而且沒有index之前請求的子表r上的鎖mode是5，現在變成了3，
鎖mode 3和session 2裡面insert操作引起的鎖mode 3在行級鎖不發生衝突(因為2個session操作的r表裡的資料不是同一行)
的情況下是可以相容的，因此session 2(sid:128)不在阻塞session 1;
--====================
session 2：
SQL> delete from p where id=3;
已刪除 1 行。
SQL>
--====================
session 3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in
2 (159,128) order by sid;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
128 TM 13017 0 3 0 30 0
128 TX 393219 372 6 0 541 0
128 TM 13020 0 3 0 541 0
159 TX 131078 387 6 0 643 0
159 TM 13020 0 3 0 643 0
159 TM 13017 0 3 0 447 0
已選擇6行。
SQL>
同理r上有了index之後，session 2(sid:128)中的操作delete from p where id=3執行之後
加在主表p上的鎖mode由原來的2變成了3，而且沒有index之前請求的子表r上的鎖mode是5，現在變成了3，
鎖mode 3和session 1裡面insert操作引起的鎖mode 3在行級鎖不發生衝突(因為2個session操作的r表裡的資料不是同一行)
的情況下是可以相容的，因此session 1(sid:159)不再阻塞session 2;沒有了相互阻塞，死鎖也就不會發生了，
因此在外來鍵上建立index不僅會提高由於操作(比如delete主表資料)主表主鍵
資料而引起的掃描子表的效率而且也可以避免操作主表資料而引起的阻塞和死鎖問題
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1039681/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 和外來鍵相關的一點效能問題總結！
- 死鎖問題總結
- Oracle 外來鍵索引影響阻塞問題Oracle索引
- OC UI總結之--tableview相關用法和問題UIView
- mysql相關問題總結MySql
- PostgreSQL死鎖相關SQL
- AdornerDecorator的CacheMode繫結和windows鎖屏導致TableControl鎖死問題Windows
- Django（15）外來鍵和表關係Django
- 多執行緒-死鎖問題概述和使用執行緒
- MySQL的主鍵和欄位型別問題總結MySql型別
- oracle外來鍵索引解決父表鎖定問題Oracle索引
- oracle外來鍵約束的總結Oracle
- 死鎖（ora-00060）以及死鎖相關的知識點
- Hadoop/Spark相關面試問題總結HadoopSpark面試
- HTTPS總結+相關面試問題解答HTTP面試
- MySQL中鎖的相關問題DTQUMySql
- ImageView相關------ ScaleType講解和Metrix控制(總結用來速查)View
- ZooKeeper和Curator相關經驗總結
- 演算法問題總結-連結串列相關演算法
- MySQL死鎖問題MySql
- 關於外來鍵的理解和實驗步驟
- 與堆和堆排序相關的問題排序
- sql server死鎖的問題SQLServer
- MySQL 死鎖和鎖等待MySql
- Laravel 學習總結二：get () 和 first () 的區別、@each () 的用法和新增外來鍵約束Laravel
- mysql啟動和關閉外來鍵約束MySql
- MySQL死鎖系列-線上死鎖問題排查思路MySql
- SQL的主鍵和外來鍵約束SQL
- 關於資料庫死鎖問題的解釋資料庫
- Java,InputStream,Socket阻塞.(關於HTTP請求的IO問題自我總結)JavaHTTP
- Redis學習總結和相關資料Redis
- 新的主鍵和外來鍵的語法
- 【原】關於SQLServer死鎖的診斷和定位SQLServer
- vim 命令 快捷鍵以及相關總結
- [zt] sql server 死鎖總結SQLServer
- TCP相關面試題總結TCP面試題
- MySQL 死鎖問題分析MySql
- Sqlserver分析死鎖問題SQLServer