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

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

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

原创 Oracle 作者：liupzmin 时间：2016-04-28 14:55:41 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
  3. -- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
  5. %This SECONDS AAS
  6. ------ ---------- ----------
  7. WAIT_CHAIN
  8. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  9. 72% 20073 5.6
  10. -> JSCHPROD:(JDBC Thin Client) ON CPU
  12. 8% 2293 .6
  13. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) ON CPU
  15. 8% 2141 .6
  16. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) log file parallel write
  18. 7% 1879 .5
  19. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) LGWR-LNS wait on channel
  21. 2% 654 .2
  22. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) ON CPU
  24. 1% 288 .1
  25. -> 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
  27. 1% 149 0
  28. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) ON CPU
  30. 0% 128 0
  31. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention
  33. 0% 112 0
  34. -> JSCHPROD:(JDBC Thin Client) log file sync
  36. 0% 86 0
  37. -> JSCHPROD:(JDBC Thin Client) db file scattered read
  39. 0% 43 0
  40. -> 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
  42. 0% 37 0
  43. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention
  45. 0% 25 0
  46. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) LGWR wait on LNS
  48. 0% 13 0
  49. -> JSCHPROD:(plsqldev.exe) ON CPU
  51. 0% 11 0
  52. -> SYS:(plsqldev.exe) ON CPU
  54. 0% 10 0
  55. -> JSCHPROD:(JDBC Thin Client) SQL*Net more data from client
  57. 0% 9 0
  58. -> JSCHPROD:(JDBC Thin Client) db file sequential read
  60. 0% 9 0
  61. -> 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
  62. CPU
  64. 0% 6 0
  65. -> JSCHPROD:(JDBC Thin Client) read by other session -> JSCHPROD:(JDBC Thin Client) ON CPU
  67. 0% 4 0
  68. -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention -> JSCHPROD:(JDBC Thin Client) enq: TM - contention
  70. 0% 3 0
  71. -> JSCHPROD:(JDBC Thin Client) SQL*Net more data to client
  73. 0% 3 0
  74. -> JSCHPROD:(JDBC Thin Client) buffer busy waits [data block]
  76. 0% 3 0
  77. -> SYS:(oraagent.bin) Disk file operations I/O
  79. 0% 3 0
  80. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) LGWR wait for redo copy
  82. 0% 2 0
  83. -> JSCHPROD:(JDBC Thin Client) enq: TX - row lock contention
  85. 0% 2 0
  86. -> JSCHPROD:(JDBC Thin Client) log file sync -> SYS:(LGWR) LGWR wait for redo copy -> JSCHPROD:(JDBC Thin Client) ON CPU
  88. 0% 2 0
  89. -> JSCHPROD:(JDBC Thin Client) enq: TX - index contention -> JSCHPROD:(JDBC Thin Client) ON CPU
  91. 0% 1 0
  92. -> JSCHPROD:(plsqldev.exe) log file sync -> SYS:(LGWR) log file parallel write
  94. 0% 1 0
  95. -> SYS:(plsqldev.exe) Disk file operations I/O
  97. 0% 1 0
  98. -> 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大师的语句，外键不加索引也是导致死锁的常见原因之一，因此对于主表经常进行更新删除操作的情况，外键一定要加索引。
至于外键未加索引是如何导致锁定的，以及为何加了索引后争用就消失了?

请关注enq: TM - contention解决之道——外键无索引导致锁争用 （下）

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26838672/viewspace-2090105/ ，如需转载，请注明出处，否则将追究法律责任。

0
1
上一篇： Data Guard ORA-16825、ORA-16661解决一例
下一篇： enq: TM - contention解决之道——外键无索引导致锁争用（下）
请登录后发表评论 登录
全部评论
liupzmin

注册时间：2014-05-17

  • 博文量
    29
  • 访问量
    117423

最新文章

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

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

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

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