enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）

liupzmin發表於2016-04-28
ENQ索引
上篇文章簡要介紹了一下當外來鍵無索引時，更新刪除主表的資料會造成子表的鎖定，如果此時子表上有事務，那麼進行更新刪除的session變會等待，等待事件就是enq: TM - contention

外來鍵與 TM enqueue lock 的主要問題是 在早期版本中(9i之前) 當 子表child table上 的外來鍵沒有索引時 ， 若發生 父表 parent table 上記錄被delete 或 update時 ， 會在child table上加 share lock， 這會 阻塞 child table 上的DML。
但是從 9i以後的當 子表child table上 的外來鍵沒有索引時， 父表parent table上的delete 、update 只在 實際這個DML執行的過程中要求share (TM lmode=4) lock，而不會在整個事務中 都要求保持 child table上的 share lock。

還是先了解一下oracle中的鎖模式吧，TM鎖和TX鎖都屬於DML鎖，這裡介紹的是TM的鎖模式

Value   Name(s)                    Table method (TM lock)
    0   No lock                    n/a

    1   Null lock (NL)             Used during some parallel DML operations (e.g. update) by
                                   the pX slaves while the QC is holding an exclusive lock.

    2   Sub-share (SS)             Until 9.2.0.5/6 "select for update"
        Row-share (RS)             Since 9.2.0.1/2 used at opposite end of RI during DML
                                   Lock table in row share mode
                                   Lock table in share update mode

    3   Sub-exclusive(SX)          Update (also "select for update" from 9.2.0.5/6)
        Row-exclusive(RX)          Lock table in row exclusive mode
                                   Since 11.1 used at opposite end of RI during DML

    4   Share (S)                  Lock table in share mode
                                   Can appear during parallel DML with id2 = 1, in the PX slave sessions
                                   Common symptom of "foreign key locking" (missing index) problem

    5   share sub exclusive (SSX)  Lock table in share row exclusive mode
        share row exclusive (SRX)  Less common symptom of "foreign key locking" but likely to be more
                                   frequent if the FK constraint is defined with "on delete cascade."

    6   Exclusive (X)              Lock table in exclusive mode
share lock就是mode為4的S鎖

Summary of Locks Obtained by DML Statements

SQL Statement Row Locks Table Lock Mode RS RX S SRX X
SELECT … FROM table... — none Y Y Y Y Y
INSERT INTO table … Yes SX Y Y N N N
UPDATE table … Yes SX Y* Y* N N N
MERGE INTO table … Yes SX Y Y N N N
DELETE FROM table … Yes SX Y* Y* N N N
SELECT … FROM table FOR UPDATE OF … Yes SX Y* Y* N N N
LOCK TABLE table IN … —
ROW SHARE MODE SS Y Y Y Y N
ROW EXCLUSIVE MODE SX Y Y N N N
SHARE MODE S Y N Y N N
SHARE ROW EXCLUSIVE MODE SSX Y N N N N
EXCLUSIVE MODE X N N N N N
* Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.

TM 鎖在下列場景中被申請：
  1. 在OPS(早期的RAC)中LGWR會以ID1=0 &  ID2=0去申請該佇列鎖來檢查 DML_LOCKS 在所有例項中是全0還是全非0
  2. 當一個單表或分割槽 需要做不同的表/分割槽操作時，ORACLE需要協調這些操作，所以需要申請該佇列鎖。包括：
  3. 啟用參考約束 referential constraints
  4. 修改約束從DIASABLE NOVALIDATE 到DISABLE VALIDATE
  5. 重建IOT
  6. 建立檢視或者修改ALTER檢視時可能需要申請該佇列鎖
  7. 分析表統計資訊或validate structure時
  8. 一些PDML並行DML操作
  9. 所有可能呼叫kkdllk()函式的操作
  10. 太多太多了。。。
下面是各種鎖之間的相容性
enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）

好了，開始動手做個試驗吧，試驗中我會引用KST trace的內容，關於KST，本文不做介紹，只拿來使用

首先，準備環境，本實驗均在11.2.0.4環境下


  1. SQL> conn lp/lp
  2. Connected.
  3. SQL> create table prim(a int,b varchar2(10));

  5. Table created.

  7. SQL> alter table prim add constraint PK_PRIM primary key(a);

  9. Table altered.

  11. SQL> create table child (ca int,cb varchar2(10));

  13. Table created.

  15. SQL> alter table child add constraint FK_CHILD_CA foreign key (ca) references prim(a);

  17. Table altered.

  19. SQL> insert into prim values(1,'asdasd');

  21. 1 row created.

  23. SQL> insert into prim values(2,'asdasd');

  25. 1 row created.

  27. SQL> insert into prim values(3,'asdasd');

  29. 1 row created.

  31. SQL> commit;

  33. Commit complete.

這裡要說一下，在外來鍵是否存在on delete cascade時鎖的獲取還有區別，所以我們分別來測試，首先是沒有索引沒有cascade的情況下，各個語句的鎖獲取情況

一、無索引，無cascade


  1. SQL> select distinct sid from v$mystat;

  3.        SID
  4. ----------
  5.         17

  7. SQL> select pid,spid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));

  9.        PID SPID
  10. ---------- ------------
  11.         36   2761 

  13. SQL> alter system set "_trace_events"='10000-10999:255:36';

  15. System altered.
insert 父表：

  1. insert into prim values(5,'asdasd');
檢視kst資訊

  1. select kst.event,kst.sid,kst.pid,kst.function,kst.data from x$trace kst where pid=36 and sid=17;

  1. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563c-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  2. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  3. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  4. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  5. 10813 17 36 ktubnd ktubnd: Bind usn 3 nax 1 nbx 0 lng 0 par 0
  6. 10813 17 36 ktubnd ktubnd: Txn Bound xid: 3.3.1150
  7. 10704 17 36 ksqgtlctx ksqgtl: acquire TX-00030003-0000047e mode=X flags=GLOBAL|XACT why="contention"
  8. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  9. 10811 17 36 ktbgcl1 3301000100000000 0000000000000000 69dd140000000000 0200000000000000
  10. 10811 17 36 ktbgcl1 3301000100000000 0000000000000000 0de3140000000000 0810c76a177f0000
  11. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=173 seq_num=180 snap_id=1
  12. 10005 17 36 kslwtectx KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=173 seq_num=180 snap_id=1
  13. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=4, exc=4, tot=4
  14. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=174 seq_num=181 snap_id=1
可見父表上的插入會獲取父表和子表mode為3的TM鎖，TM後跟的是object_id的十六進位制，一個TX鎖，讓我們驗證一下

  1. SQL> select * from v$lock where type in('TM','TX');

  1. enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）
我們來commit一下


  1. 10704 17 36 ksqrcli ksqrcl: release TX-00030003-0000047e mode=X
  2. 10813 17 36 ktudnx ktudnx: dec cnt xid:3.3.1150 nax:0 nbx:0
  3. 10704 17 36 ksqrcli ksqrcl: release TM-0001563e-00000000 mode=SX
  4. 10704 17 36 ksqrcli ksqrcl: release TM-0001563c-00000000 mode=SX
  5. 10021 17 36 kcrf_commit_force 2ee3140000000000 2fe3140000000000
  6. 10005 17 36 kslwtbctx KSL WAIT BEG [log file sync] 7416/0x1cf8 1368878/0x14e32e 0/0x0 wait_id=175 seq_num=182 snap_id=1
  7. 10005 17 36 ksliwat KSL FACILITY WAIT fac#=3 time_waited_csecs=1
  8. 10005 17 36 ksliwat KSL POST RCVD poster=11 num=76 loc='ksl2.h LINE:2374 ID:kslpsr' id1=138 id2=0 name=EV type=0 fac#=3 posted=0x3 may_be_posted=1
  9. 10005 17 36 kslwtectx KSL WAIT END [log file sync] 7416/0x1cf8 1368878/0x14e32e 0/0x0 wait_id=175 seq_num=182 snap_id=1
  10. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=11126, exc=11126, tot=11126
  11. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=176 seq_num=183 snap_id=1
  12. 10005 17 36 kslwtectx KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=176 seq_num=183 snap_id=1
  13. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=3, exc=3, tot=3
  14. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=177 seq_num=184 snap_id=1
可見獲得的鎖全部一一釋放

insert子表：

  1. insert into child values(2,'sadsada');

  1. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563c-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  2. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  3. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  4. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  5. 10813 17 36 ktubnd ktubnd: Bind usn 7 nax 1 nbx 0 lng 0 par 0
  6. 10813 17 36 ktubnd ktubnd: Txn Bound xid: 7.17.835
  7. 10704 17 36 ksqgtlctx ksqgtl: acquire TX-00070011-00000343 mode=X flags=GLOBAL|XACT why="contention"
  8. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  9. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=186 seq_num=193 snap_id=1
  10. 10005 17 36 kslwtectx KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=186 seq_num=193 snap_id=1
  11. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=3, exc=3, tot=3
  12. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=187 seq_num=194 snap_id=1

可見子表上的插入也會獲取父表和子表mode為3的TM鎖

update父表：

  1. update prim set a=1 where a=1;

  1. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563c-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  2. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  3. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=S flags=GLOBAL|XACT why="contention"
  4. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  5. 10704 17 36 ksqrcli ksqrcl: release TM-0001563e-00000000 mode=S
  6. 10704 17 36 ksqrcli ksqrcl: SUCCESS
  7. 10811 17 36 ktbgcl1 2c01000100000000 0000000000000000 0fe7140000000000 0200000000000000
  8. 10811 17 36 ktbgcl1 2c01000100000000 0000000000000000 27e9140000000000 b80fb86a177f0000
  9. 10813 17 36 ktubnd ktubnd: Bind usn 4 nax 1 nbx 0 lng 0 par 0
  10. 10813 17 36 ktubnd ktubnd: Txn Bound xid: 4.10.851
  11. 10704 17 36 ksqgtlctx ksqgtl: acquire TX-0004000a-00000353 mode=X flags=GLOBAL|XACT why="contention"
  12. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  13. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=204 seq_num=211 snap_id=1
  14. 10005 17 36 kslwtectx KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=204 seq_num=211 snap_id=1
  15. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=5, exc=5, tot=5
  16. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=205 seq_num=212 snap_id=1
可以很清楚的看到在執行語句期間，注意僅僅是語句的執行期間，會附加一個mode為4的S鎖到子表上，很快便釋放了

delete父表：

  1. delete from prim where a=4;

  1. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563c-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  2. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  3. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=S flags=GLOBAL|XACT why="contention"
  4. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  5. 10704 17 36 ksqrcli ksqrcl: release TM-0001563e-00000000 mode=S
  6. 10704 17 36 ksqrcli ksqrcl: SUCCESS
  7. 10813 17 36 ktubnd ktubnd: Bind usn 2 nax 1 nbx 0 lng 0 par 0
  8. 10813 17 36 ktubnd ktubnd: Txn Bound xid: 2.0.1117
  9. 10704 17 36 ksqgtlctx ksqgtl: acquire TX-00020000-0000045d mode=X flags=GLOBAL|XACT why="contention"
  10. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  11. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=S flags=GLOBAL|XACT why="contention"
  12. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  13. 10704 17 36 ksqrcli ksqrcl: release TM-0001563e-00000000 mode=S
  14. 10704 17 36 ksqrcli ksqrcl: SUCCESS
  15. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 bae9140000000000
  16. 10812 17 36 ktrgcm 0200000000000000 0000000000000000 5d04000000000000
  17. 10812 17 36 ktrgcm 9b12c00000000000 4401000000000000 0200000000000000
  18. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 0000000000000000
  19. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 bae9140000000000
  20. 10812 17 36 ktrgcm 0200000000000000 0000000000000000 5d04000000000000
  21. 10812 17 36 ktrgcm 9b12c00000000000 4401000000000000 0200000000000000
  22. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 0000000000000000
  23. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 bae9140000000000
  24. 10812 17 36 ktrgcm 0200000000000000 0000000000000000 5d04000000000000
  25. 10812 17 36 ktrgcm 9b12c00000000000 4401000000000000 0200000000000000
  26. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 0000000000000000
  27. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 bae9140000000000
  28. 10812 17 36 ktrgcm 0200000000000000 0000000000000000 5d04000000000000
  29. 10812 17 36 ktrgcm 9b12c00000000000 4401000000000000 0200000000000000
  30. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 0000000000000000
  31. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 bae9140000000000
  32. 10812 17 36 ktrgcm 0200000000000000 0000000000000000 5d04000000000000
  33. 10812 17 36 ktrgcm 9b12c00000000000 4401000000000000 0200000000000000
  34. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 0000000000000000
  35. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=227 seq_num=234 snap_id=1
  36. 10005 17 36 kslwtectx KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=227 seq_num=234 snap_id=1
  37. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=3, exc=3, tot=3
  38. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=228 seq_num=235 snap_id=1
delete跟update比多了一次S鎖的獲取和釋放，為何呢，是否和刪除的行數有關？我們再多刪一行試試

  1. SQL> delete from prim where a=4 or a=5;

  1. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563c-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  2. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  3. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=S flags=GLOBAL|XACT why="contention"
  4. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  5. 10704 17 36 ksqrcli ksqrcl: release TM-0001563e-00000000 mode=S
  6. 10704 17 36 ksqrcli ksqrcl: SUCCESS
  7. 10813 17 36 ktubnd ktubnd: Bind usn 6 nax 1 nbx 0 lng 0 par 0
  8. 10813 17 36 ktubnd ktubnd: Txn Bound xid: 6.15.1290
  9. 10704 17 36 ksqgtlctx ksqgtl: acquire TX-0006000f-0000050a mode=X flags=GLOBAL|XACT why="contention"
  10. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  11. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=S flags=GLOBAL|XACT why="contention"
  12. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  13. 10704 17 36 ksqrcli ksqrcl: release TM-0001563e-00000000 mode=S
  14. 10704 17 36 ksqrcli ksqrcl: SUCCESS
  15. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 5eea140000000000
  16. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  17. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2200000000000000
  18. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 0000000000000000
  19. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 5eea140000000000
  20. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  21. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2200000000000000
  22. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 0000000000000000
  23. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 5eea140000000000
  24. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  25. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2200000000000000
  26. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 0000000000000000
  27. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 5eea140000000000
  28. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  29. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2200000000000000
  30. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 0000000000000000
  31. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 5eea140000000000
  32. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  33. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2200000000000000
  34. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 0000000000000000
  35. 10812 17 36 ktrgcm 3301000100000000 0000000000000000 5eea140000000000
  36. 10812 17 36 ktrgcm 0000000000000000 0000000000000000 0000000000000000
  37. 10812 17 36 ktrgcm 0000000000000000 0000000000000000 0000000000000000
  38. 10811 17 36 ktbgcl1 3301000100000000 0000000000000000 5eea140000000000 0100000000000000
  39. 10811 17 36 ktbgcl1 3301000100000000 0000000000000000 5eea140000000000 e00fc76a177f0000
  40. 10812 17 36 kturCRBackoutOneChg 0100000000000000 bc06c00000000000 0e01000000000000 2200000000000000
  41. 10812 17 36 ktrgcm 3301000100000000 0100000000000000 0100000000000000
  42. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=S flags=GLOBAL|XACT why="contention"
  43. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  44. 10704 17 36 ksqrcli ksqrcl: release TM-0001563e-00000000 mode=S
  45. 10704 17 36 ksqrcli ksqrcl: SUCCESS
  46. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 5fea140000000000
  47. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  48. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2400000000000000
  49. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 0000000000000000
  50. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 5fea140000000000
  51. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  52. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2400000000000000
  53. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 0000000000000000
  54. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 5fea140000000000
  55. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  56. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2400000000000000
  57. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 0000000000000000
  58. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 5fea140000000000
  59. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  60. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2400000000000000
  61. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 0000000000000000
  62. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 5fea140000000000
  63. 10812 17 36 ktrgcm 0600000000000000 0f00000000000000 0a05000000000000
  64. 10812 17 36 ktrgcm bc06c00000000000 0e01000000000000 2400000000000000
  65. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 0000000000000000
  66. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=236 seq_num=243 snap_id=1
  67. 10005 17 36 kslwtectx KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=236 seq_num=243 snap_id=1
  68. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=4, exc=4, tot=4
  69. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=237 seq_num=244 snap_id=1
可以發現除了語句執行時需要獲取一次S鎖之外，刪多少行就要獲取多少次S鎖，從之前的鎖相容列表就可發現S鎖和SX（RX）鎖是不相容的，而SX（RX）是insert update delete獲取的鎖模式，可以想象如果此時子表上有事務，或者S鎖獲得了尚未釋放的時候，子表要進行事務獲取mode為3的SX（RX）鎖時，session都會產生等待。

看一下此時session獲取的鎖，記住這次結果，後面會有對比。

  1. SQL> select * from v$lock where type in('TM','TX');

  3.    enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）
可見語句執行完，已不持有子表上的任何鎖

下面來模擬一下等待，


  1. sid:31
  2. SQL> insert into child values(2,'12312');

  4. 1 row created.

  6. SQL> select distinct sid from v$mystat;

  8.        SID
  9. ----------
  10.         31
  11.         
  12. sid:1169

  14. SQL> update prim set a=1 where a=1;--hang住了

  16. SQL> select * from v$lock where type in('TM','TX');

  18.   enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）
此時查一下等待鏈

  1. SQL> --鎖源頭查詢,帶物件和sql以及event
  2. SQL> WITH sessions AS
  3.   2 (SELECT /*+materialize*/
  4.   3 sid,
  5.   4 blocking_session,
  6.   5 blocking_instance,
  7.   6 row_wait_obj#,
  8.   7 sql_id,
  9.   8 inst_id,
  10.   9 event
  11.  10 FROM gv$session)
  12.  11 SELECT LPAD(' ', 4 * (level - 1)) || s.inst_id || '.' || sid sid,
  13.  12 object_name,
  14.  13 substr(sql_text, 1, 40) sql_text,
  15.  14 event
  16.  15 FROM sessions s
  17.  16 LEFT OUTER JOIN dba_objects d
  18.  17 ON (object_id = row_wait_obj#)
  19.  18 LEFT OUTER JOIN gv$sql q
  20.  19 ON (s.sql_id = q.SQL_ID and s.inst_id = q.INST_ID)
  21.  20 WHERE sid IN (SELECT blocking_session FROM sessions)
  22.  21 OR blocking_session IS NOT NULL
  23.  22 CONNECT BY PRIOR sid = blocking_session
  24.  23 START WITH blocking_session IS NULL;

  1.  enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）
從上面的分析我們知道，無論插入父表和子表，都會獲取兩張表上的mode為3的鎖，而mode為3的鎖和mode為4的鎖是不相容的，也就是說此時父表上連插入都無法進行


再開第三個session


  1. sid:1167
  2. SQL> insert into prim values(7,'dasd');--hang住了

  4. SQL> select * from v$lock where type in('TM','TX');
  enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）
檢視等待鏈

  enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）

二、無索引，有cascade


  1. SQL> alter table child drop constraint FK_CHILD_CA;

  3. Table altered.

  5. SQL> alter table child add constraint FK_CHILD_CA foreign key (ca) references prim(a) on delete cascade;

  7. Table altered.
有cascade的時候，僅在delete語句上有所區別，下面僅列出delete語句


  1. SQL> delete from prim where a=2 or a=4;

  3. 2 rows deleted.


  1. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563c-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  2. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  3. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=SSX flags=GLOBAL|XACT why="contention"
  4. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  5. 10704 17 36 ksqcnv ksqcnv: convert TM-0001563e-00000000 from=SSX to=SX flags=
  6. 10704 17 36 ksqcnv ksqcnv: SUCCESS
  7. 10813 17 36 ktubnd ktubnd: Bind usn 4 nax 1 nbx 0 lng 0 par 0
  8. 10813 17 36 ktubnd ktubnd: Txn Bound xid: 4.20.852
  9. 10704 17 36 ksqgtlctx ksqgtl: acquire TX-00040014-00000354 mode=X flags=GLOBAL|XACT why="contention"
  10. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  11. 10704 17 36 ksqcnv ksqcnv: convert TM-0001563e-00000000 from=SX to=SSX flags=
  12. 10704 17 36 ksqcnv ksqcnv: SUCCESS
  13. 10704 17 36 ksqcnv ksqcnv: convert TM-0001563e-00000000 from=SSX to=SX flags=
  14. 10704 17 36 ksqcnv ksqcnv: SUCCESS
  15. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 bef2140000000000
  16. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  17. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0a00000000000000
  18. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 0000000000000000
  19. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 bef2140000000000
  20. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  21. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0a00000000000000
  22. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 0000000000000000
  23. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 bef2140000000000
  24. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  25. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0a00000000000000
  26. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 0000000000000000
  27. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 bef2140000000000
  28. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  29. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0a00000000000000
  30. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 0000000000000000
  31. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 bef2140000000000
  32. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  33. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0a00000000000000
  34. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 0000000000000000
  35. 10812 17 36 ktrgcm 3301000100000000 0000000000000000 bef2140000000000
  36. 10812 17 36 ktrgcm 0000000000000000 0000000000000000 0000000000000000
  37. 10812 17 36 ktrgcm 0000000000000000 0000000000000000 0000000000000000
  38. 10811 17 36 ktbgcl1 3301000100000000 0000000000000000 bef2140000000000 0100000000000000
  39. 10811 17 36 ktbgcl1 3301000100000000 0000000000000000 bff2140000000000 e00fc76a177f0000
  40. 10812 17 36 kturCRBackoutOneChg 0100000000000000 ef00c00000000000 2801000000000000 0a00000000000000
  41. 10812 17 36 ktrgcm 3301000100000000 0100000000000000 0100000000000000
  42. 10704 17 36 ksqcnv ksqcnv: convert TM-0001563e-00000000 from=SX to=SSX flags=
  43. 10704 17 36 ksqcnv ksqcnv: SUCCESS
  44. 10704 17 36 ksqcnv ksqcnv: convert TM-0001563e-00000000 from=SSX to=SX flags=
  45. 10704 17 36 ksqcnv ksqcnv: SUCCESS
  46. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 c0f2140000000000
  47. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  48. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0e00000000000000
  49. 10812 17 36 ktrgcm 3b01000100000000 0000000000000000 0000000000000000
  50. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 c0f2140000000000
  51. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  52. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0e00000000000000
  53. 10812 17 36 ktrgcm 3c01000100000000 0000000000000000 0000000000000000
  54. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 c0f2140000000000
  55. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  56. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0e00000000000000
  57. 10812 17 36 ktrgcm 3d01000100000000 0000000000000000 0000000000000000
  58. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 c0f2140000000000
  59. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  60. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0e00000000000000
  61. 10812 17 36 ktrgcm 3e01000100000000 0000000000000000 0000000000000000
  62. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 c0f2140000000000
  63. 10812 17 36 ktrgcm 0400000000000000 1400000000000000 5403000000000000
  64. 10812 17 36 ktrgcm ef00c00000000000 2801000000000000 0e00000000000000
  65. 10812 17 36 ktrgcm 3f01000100000000 0000000000000000 0000000000000000
  66. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=425 seq_num=432 snap_id=1
  67. 10005 17 36 kslwtectx KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=425 seq_num=432 snap_id=1
  68. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=4, exc=4, tot=4
  69. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=426 seq_num=433 snap_id=1
此時會申請一個mode為5的SSX鎖，隨後即轉換為mode為3的SX鎖，這也是在語句執行期間獲取和轉換的，並非事務期間，同樣刪除多少行就涉及到多少次獲取轉換，看一下此時鎖獲得情況

  1. SQL> select * from v$lock where type in('TM','TX');
  enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）

是不是和沒有cascade的時候不同了，這次最終會持有子表上的mode為3的鎖，我們再深入的思考一點，SSX鎖和SX鎖是不相容的，這樣是否就意味著後進行的delete會被先進行的delete阻塞（不同session），好，現在就來模擬一下：

  1. sid:1169

  3. SQL> delete from prim where a=1;

  5. 0 rows deleted.

  7. SQL> select * from v$lock where type in('TM','TX');
 enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）

  1. sid:1167
  2. SQL> delete from prim where a=2;--session hang住了

  查詢此刻鎖的持有情況
  1. SQL> select * from v$lock where type in('TM','TX');
  enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）
可見1167在請求mode為5的鎖，且已被阻塞

檢視等待鏈

  enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）

因為delete完畢會持有子表上的SX鎖，而SX鎖與S鎖不相容，所以delete父表的session也會阻塞update父表的session，因為update回去請求子表的S鎖，而此時子表上有SX鎖，類似於子表上有事務在進行，這裡就不在論述了，徒佔篇幅。

三、有索引，無cascade

    我看到有資料說，如果有索引時,對父表的操作,會級聯加一個TM RS鎖(level 2)到子表上。但我在試驗中並未看到，也許是版本差異，我也未去求證，有索引時insert與無索引時在獲取鎖方面沒有區別，這裡僅列出update和delete
    建立索引：

  1. SQL> alter table child drop constraint FK_CHILD_CA;

  3. Table altered.

  5. SQL> alter table child add constraint FK_CHILD_CA foreign key (ca) references prim(a);

  7. Table altered.

  9. SQL> create index ind_child_ca on child(ca);

  11. Index created.

update父表：

  1. SQL> update prim set a=6 where a=6;

  3. 1 row updated.

  1. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563c-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  2. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  3. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  4. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  5. 10811 17 36 ktbgcl1 2b01000100000000 0000000000000000 fadc140000000000 0200000000000000
  6. 10811 17 36 ktbgcl1 2b01000100000000 0000000000000000 cdf9140000000000 10dfb76a177f0000
  7. 10813 17 36 ktubnd ktubnd: Bind usn 3 nax 1 nbx 0 lng 0 par 0
  8. 10813 17 36 ktubnd ktubnd: Txn Bound xid: 3.28.1117
  9. 10704 17 36 ksqgtlctx ksqgtl: acquire TX-0003001c-0000045d mode=X flags=GLOBAL|XACT why="contention"
  10. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  11. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=471 seq_num=478 snap_id=1
  12. 10005 17 36 kslwtectx KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=471 seq_num=478 snap_id=1
  13. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=3, exc=3, tot=3
  14. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=472 seq_num=479 snap_id=1
可見有了索引之後，不再需要在語句級別獲取子表上的S鎖了

delete父表：

  1. SQL> delete from prim where a=9;

  3. 1 row deleted.

  1. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563c-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  2. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  3. 10704 17 36 ksqgtlctx ksqgtl: acquire TM-0001563e-00000000 mode=SX flags=GLOBAL|XACT why="contention"
  4. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  5. 10811 17 36 ktbgcl1 2c01000100000000 0000000000000000 66f1140000000000 0200000000000000
  6. 10811 17 36 ktbgcl1 2c01000100000000 0000000000000000 58fa140000000000 78efb76a177f0000
  7. 10813 17 36 ktubnd ktubnd: Bind usn 4 nax 1 nbx 0 lng 0 par 0
  8. 10813 17 36 ktubnd ktubnd: Txn Bound xid: 4.18.854
  9. 10704 17 36 ksqgtlctx ksqgtl: acquire TX-00040012-00000356 mode=X flags=GLOBAL|XACT why="contention"
  10. 10704 17 36 ksqgtlctx ksqgtl: SUCCESS
  11. 10811 17 36 ktbgcl1 3301000100000000 0000000000000000 c0f2140000000000 0200000000000000
  12. 10811 17 36 ktbgcl1 3301000100000000 0000000000000000 58fa140000000000 90eec66a177f0000
  13. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=476 seq_num=483 snap_id=1
  14. 10005 17 36 kslwtectx KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=476 seq_num=483 snap_id=1
  15. 10005 17 36 kslwtectx KSL WAIT END wait times (usecs) - snap=4, exc=4, tot=4
  16. 10005 17 36 kslwtbctx KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 wait_id=477 seq_num=484 snap_id=1
與update相同，都持有了子表上的SX鎖，而SX與SX是相容的，所以不會再產生鎖定問題

  1. SQL> select * from v$lock where type in('TM','TX');
    enq: TM - contention解決之道——外來鍵無索引導致鎖爭用（下）
四、有索引，有cascade

    表現與無cascade時相同

五、結論

  1.     外來鍵無索引鎖無cascade時，update/delete父表，會在語句級別級聯一個mode為4的S鎖到子表，其中delete多少行就會級聯多少次
  2.     外來鍵無索引有cascade時，update父表仍會在語句級別級聯mode為4的S鎖到子表，delete時會先獲取mode為5的SSX鎖，在將其轉換成mode為3的SX鎖，而且刪除多少行就會涉及到多少次轉換
  3.     外來鍵有索引無cascade時，update/delete不會在語句級級聯鎖到子表，最終會持有父表和子表上的mode為3的SX鎖（無索引時只有有cascade的delete時最終會持有子表上的SX鎖）
  4.     外來鍵有索引有cascade時，與無cascade表現相同
    敬請期待番外篇：外來鍵無索引刪除父表導致子表全掃描

參考文獻：

http://www.askmaclean.com/archives/know-more-about-oracle-row-lock.html

http://www.askmaclean.com/archives/enq-tm-contention.html

http://www.askmaclean.com/archives/know-oracle-lock-mode.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26838672/viewspace-2090237/，如需轉載，請註明出處，否則將追究法律責任。

相關文章