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

abin1703發表於2020-04-29
ENQ索引

近日，開發負責人反映 某生產環境業務處理緩慢，主要業務操作就是修改會員資訊，登入查詢後發現大量的session正在等待 enq: TM - contention，且waiting的語句幾乎都是update ，

session的即時資訊沒有保留，現在附上ash檢視的一些統計資訊，可以大概瞭解一下當時爭用的場景


  1. SQL> @ash_wait_chains.sql username||':'||program2||event2 session_type='FOREGROUND' sysdate-6/24 sysdate-5/24
  2. -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
  3. %This SECONDS AAS
  4. ------ ---------- ----------
  5. WAIT_CHAIN
  6. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  7. 72% 20073 5.6
  8. -> JSCHPROD:(JDBC Thin Client) ON CPU
  9. 8% 2293 .6
  10. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) ON CPU
  11. 8% 2141 .6
  12. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) log file parallel write
  13. 7% 1879 .5
  14. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) LGWR-LNS wait on channel
  15. 2% 654 .2
  16. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) ON CPU
  17. 1% 288 .1
  18. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) ON CPU
  19. 1% 149 0
  20. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) ON CPU
  21. 0% 128 0
  22. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention
  23. 0% 112 0
  24. -> JSCHPROD:(JDBC Thin Client) log file sync
  25. 0% 86 0
  26. -> JSCHPROD:(JDBC Thin Client) db file scattered read
  27. 0% 43 0
  28. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) ON CPU
  29. 0% 37 0
  30. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention
  31. 0% 25 0
  32. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) LGWR wait on LNS
  33. 0% 13 0
  34. -> JSCHPROD:(plsqldev.exe) ON CPU
  35. 0% 11 0
  36. -> SYS:(plsqldev.exe) ON CPU
  37. 0% 10 0
  38. -> JSCHPROD:(JDBC Thin Client) SQL*Net more data from client
  39. 0% 9 0
  40. -> JSCHPROD:(JDBC Thin Client) db file sequential read
  41. 0% 9 0
  42. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) ON
  43. CPU
  44. 0% 6 0
  45. -> JSCHPROD:(JDBC Thin Client) read by other session -> JSCHPROD:(JDBC Thin Client) ON CPU
  46. 0% 4 0
  47. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention
  48. 0% 3 0
  49. -> JSCHPROD:(JDBC Thin Client) SQL*Net more data to client
  50. 0% 3 0
  51. -> JSCHPROD:(JDBC Thin Client) buffer busy waits [data block]
  52. 0% 3 0
  53. -> SYS:(oraagent.bin) Disk file operations I/O
  54. 0% 3 0
  55. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) LGWR wait for redo copy
  56. 0% 2 0
  57. -> JSCHPROD:(JDBC Thin Client) enq: TX - row lock contention
  58. 0% 2 0
  59. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) LGWR wait for redo copy -> JSCHPROD:(JDBC Thin Client) ON CPU
  60. 0% 2 0
  61. -> JSCHPROD:(JDBC Thin Client) enq: TX - index contention -> JSCHPROD:(JDBC Thin Client) ON CPU
  62. 0% 1 0
  63. -> JSCHPROD:(plsqldev.exe) log file sync -> SYS:(LGWR) log file parallel write
  64. 0% 1 0
  65. -> SYS:(plsqldev.exe) Disk file operations I/O
  66. 0% 1 0
  67. -> JSCHPROD:(JDBC Thin Client) enq: TX - row lock contention -> JSCHPROD:(JDBC Thin Client) ON CPU

可以看到，TM鎖的爭用很多，再看一份當時awr報告的top10

Top 10 Foreground Events by Total Wait Time


    Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
    DB CPU
    		20K
    		87.6
    log file sync 467,706 2795.1 6 12.2 Commit
    enq: TM - contention 2,042 401.7 197 1.8 Application
    db file scattered read 593,258 99 0 .4 User I/O
    db file sequential read 274,234 10.7 0 .0 User I/O
    SQL*Net more data from client 370,989 9.7 0 .0 Network
    read by other session 26,041 6.9 0 .0 User I/O
    SQL*Net message to client 3,955,830 6.1 0 .0 Network
    log file switch completion 19 3.3 176 .0 Configuration
    SQL*Net more data to client 30,490 2 0 .0 Network


    雖然佔DBTIME不多，但本來是很快的操作，短時間內給人的感覺就是業務處理緩慢，訊息都堆在redis裡

    查一下當時等待事件的p1,p2,p3的值


    1. select ash .SAMPLE_TIME ,
    2.        ash .EVENT ,
    3.        ash .SESSION_ID ,
    4.        ash .BLOCKING_SESSION ,
    5.        ash .P1TEXT ,
    6.        ash .P1 ,
    7.        ash .P2TEXT ,
    8.        ash .p2 ,
    9.        ash .p3text ,
    10.        ash .p3 ,
    11.        ash .SESSION_STATE ,
    12.        ash .SQL_OPNAME ,
    13.        ash .SQL_ID
    14.         --ash.*
    15.    from v$active_session_history ash
    16.   where ash .SAMPLE_TIME  >
    17.         to_date ( '20160425 10:00:00' ,  'yyyymmdd HH24:MI:SS' )
    18.     and ash .SAMPLE_TIME  <
    19.         to_date ( '20160425 12:10:00' ,  'yyyymmdd HH24:MI:SS' )
    20.     and ash .WAIT_CLASS  < >  'Idle'
    21.     and ash .EVENT  like  'enq: TM - contention'
    22.   order  by sample_time  desc;

    下面是部分結果


    1. enq : TM  - contention    391    2457    name | mode    1414332419     object #    110434     table /partition    0    WAITING     INSERT    7w0tma5up32wt
    2. enq : TM  - contention    2213    297    name | mode    1414332420     object #    110433     table /partition    0    WAITING     UPDATE    ak25v8q8p6fzd
    3. enq : TM  - contention    2457    2402    name | mode    1414332420     object #     110434     table /partition    0    WAITING     UPDATE    ak25v8q8p6fzd
    4. enq : TM  - contention    2641    297    name | mode    1414332420     object #     110433     table /partition    0    WAITING     UPDATE    ak25v8q8p6fzd
    5. enq : TM  - contention    194    297    name | mode    1414332420     object #    110433     table /partition    0    WAITING     UPDATE    9gd6xhd0xyhph
    6. enq : TM  - contention    297    2402    name | mode    1414332419     object #    110433     table /partition    0    WAITING     INSERT    7w0tma5up32wt
    7. enq : TM  - contention    341    2402    name | mode    1414332419     object #    110433     table /partition    0    WAITING     INSERT    7w0tma5up32wt
    8. enq : TM  - contention    391    2213    name | mode    1414332420     object #    110433     table /partition    0    WAITING     UPDATE    6nmp0bp3uuqtr
    9. enq : TM  - contention    2402    2213    name | mode    1414332420     object #    110433     table /partition    0    WAITING     UPDATE    44nzxnp9wwk3z
    10. enq : TM  - contention    2457    2213    name | mode    1414332420     object #    110433     table /partition    0    WAITING     UPDATE    ak25v8q8p6fzd
    11. enq : TM  - contention    297    2213    name | mode    1414332420     object #    110433     table /partition    0    WAITING     UPDATE    ak25v8q8p6fzd
    12. enq : TM  - contention    391    2213    name | mode    1414332420     object #    110433     table /partition    0    WAITING     UPDATE    ak25v8q8p6fzd
    13. enq : TM  - contention    4    2504    name | mode    1414332421     object #     110415     table /partition    0    WAITING     UPDATE    ak25v8q8p6fzd
    14. enq : TM  - contention    148    2504    name | mode    1414332421     object #    110415     table /partition    0    WAITING     UPDATE    9gd6xhd0xyhph

    可以看到紅色的p2的值為產生TM爭用的物件id，經過查證，這些object均是session正在更新的表的子表，而且透過v$sql檢視update語句均更改了主表的主鍵，問題到這裡已經很明朗了，由於外來鍵沒加索引，導致了主表在更新主表主鍵或刪除主表記錄時對子表的鎖定，而且這張主表被大量的子表引用，此時子表上也同時進行事務處理，所以造成了更新主表的session 不時hang住。

    透過對所有子表的外來鍵加索引，消除了爭用，檢測未加索引的外來鍵語句：

    1. SELECT TABLE_NAME ,
    2.        CONSTRAINT_NAME ,
    3.        CNAME1  | | NVL2 (CNAME2 ,  ','  | | CNAME2 ,  NULL )  | |
    4.        NVL2 (CNAME3 ,  ','  | | CNAME3 ,  NULL )  | |
    5.        NVL2 (CNAME4 ,  ','  | | CNAME4 ,  NULL )  | |
    6.        NVL2 (CNAME5 ,  ','  | | CNAME5 ,  NULL )  | |
    7.        NVL2 (CNAME6 ,  ','  | | CNAME6 ,  NULL )  | |
    8.        NVL2 (CNAME7 ,  ','  | | CNAME7 ,  NULL )  | |
    9.        NVL2 (CNAME8 ,  ','  | | CNAME8 ,  NULL ) COLUMNS
    10.    FROM  ( SELECT B .TABLE_NAME ,
    11.                B .CONSTRAINT_NAME ,
    12.                 MAX ( DECODE (POSITION , 1 , COLUMN_NAME ,  NULL ) ) CNAME1 ,
    13.                 MAX ( DECODE (POSITION , 2 , COLUMN_NAME ,  NULL ) ) CNAME2 ,
    14.                 MAX ( DECODE (POSITION , 3 , COLUMN_NAME ,  NULL ) ) CNAME3 ,
    15.                 MAX ( DECODE (POSITION , 4 , COLUMN_NAME ,  NULL ) ) CNAME4 ,
    16.                 MAX ( DECODE (POSITION , 5 , COLUMN_NAME ,  NULL ) ) CNAME5 ,
    17.                 MAX ( DECODE (POSITION , 6 , COLUMN_NAME ,  NULL ) ) CNAME6 ,
    18.                 MAX ( DECODE (POSITION , 7 , COLUMN_NAME ,  NULL ) ) CNAME7 ,
    19.                 MAX ( DECODE (POSITION , 8 , COLUMN_NAME ,  NULL ) ) CNAME8 ,
    20.                 COUNT ( * ) COL_CNT
    21.            FROM  ( SELECT  SUBSTR (TABLE_NAME , 1 , 30 ) TABLE_NAME ,
    22.                         SUBSTR (CONSTRAINT_NAME , 1 , 30 ) CONSTRAINT_NAME ,
    23.                         SUBSTR (COLUMN_NAME , 1 , 30 ) COLUMN_NAME ,
    24.                        POSITION
    25.                    FROM USER_CONS_COLUMNS ) A ,
    26.                USER_CONSTRAINTS B
    27.           WHERE A .CONSTRAINT_NAME  = B .CONSTRAINT_NAME
    28.             AND B .CONSTRAINT_TYPE  =  'R'
    29.           GROUP  BY B .TABLE_NAME , B .CONSTRAINT_NAME ) CONS
    30.   WHERE COL_CNT  >  ALL
    31.   ( SELECT  COUNT ( * )
    32.            FROM USER_IND_COLUMNS I
    33.           WHERE I .TABLE_NAME  = CONS .TABLE_NAME
    34.             AND I .COLUMN_NAME  IN  (CNAME1 , CNAME2 , CNAME3 , CNAME4 , CNAME5 ,
    35.                 CNAME6 , CNAME7 , CNAME8 )
    36.             AND I .COLUMN_POSITION  < = CONS .COL_CNT
    37.           GROUP  BY I .INDEX_NAME ) ;


    這是摘自TOM大師的語句， 外來鍵不加索引也是導致死鎖的常見原因之一，因此對於主表經常進行更新刪除操作的情況，外來鍵一定要加索引。
    至於外來鍵未加索引是如何導致鎖定的，以及為何加了索引後爭用就消失了?


    上篇文章簡要介紹了一下當外來鍵無索引時，更新刪除主表的資料會造成子表的鎖定，如果此時子表上有事務，那麼進行更新刪除的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表現相同

         敬請期待番外篇：外來鍵無索引刪除父表導致子表全掃描

    參考文獻：




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

    相關文章