ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > enq: TM - contention解决之道——外键无索引导致锁争用

enq: TM - contention解决之道——外键无索引导致锁争用

Oracle 作者：abin1703 时间：2020-04-29 08:58:12 0 删除 编辑

近日，开发负责人反映 某生产环境业务处理缓慢，主要业务操作就是修改会员信息，登录查询后发现大量的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. 太多太多了。。。

    下面是各种锁之间的兼容性


    好了，开始动手做个试验吧，试验中我会引用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' ) ;



    我们来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.    

    可见语句执行完，已不持有子表上的任何锁

    下面来模拟一下等待，


    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.   

    此时查一下等待链

    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.  

    从上面的分析我们知道，无论插入父表和子表，都会获取两张表上的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' ) ;

      
    查看等待链

      

    二、无索引，有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' ) ;

      

    是不是和没有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' ) ;

     

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

      查询此刻锁的持有情况
    1. SQL >  select  *  from v$ lock  where  type  in ( 'TM' , 'TX' ) ;

      
    可见1167在请求mode为5的锁，且已被阻塞

    查看等待链

      

    因为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' ) ;

        
    四、有索引，有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博客 ” ，链接：https://blog.itpub.net/30345407/viewspace-2689108/ ，如需转载，请注明出处，否则将追究法律责任。

    0
    0
    上一篇： windows 将bat exe 脚本执行文件放到window 当做服务
    下一篇： 将数据库照片大字段下卸到文件系统，照片文件名以某一个字段命名
    请登录后发表评论 登录
    全部评论
    abin1703

    注册时间：2015-07-13

    • 博文量
      204
    • 访问量
      426802

    最新文章

    支持我们 作者招募 用户协议 FAQ Contact Us

    北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

    广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

    违法和不良信息举报电话:18101376593