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

最新推荐文章于 2023-05-21 21:40:14 发布
最新推荐文章于 2023-05-21 21:40:14 发布
阅读量308 收藏
点赞数
文章标签： 数据库
近日，开发负责人反映某生产环境业务处理缓慢，主要业务操作就是修改会员信息，登录查询后发现大量的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博客 ” ，链接：http://blog.itpub.net/26838672/viewspace-2090105/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26838672/viewspace-2090105/

crc89714
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
BLOG_【AWR分析】Oracle_lhr_队列等待之enq IV - contention案例.pdf
08-08
### Oracle AWR 分析之 enq: IV - Contention 案例解析 #### 一、概述 在Oracle数据库管理中，AWR (Automatic Workload Repository) 是一个非常重要的工具，用于诊断性能瓶颈并帮助优化数据库操作。本文将详细介绍...
enq：TX index contention.txt
11-01
并在数据块上产生一个TX锁，此时另外一个会话也需要向该数据块中插入新的索引条目，于是该事务不得不等待索引分裂完成。这时就会表现为enq: TX - index contention。本例中索引块分裂属于5-5 分裂，此分裂可以通过...
Oracle数据库常见等待事件原因和一般解决方法之(Enq: TM – contention)
rishairu1的博客
03-25 2413
通常我们判断Oracle数据库的性能是否有问题，需要衡量一些指标值。其中很重要的一个要素就是等待事件。 我们通常可以通过AWR报告或者是DBA_HIST_SYSTEM_EVENT视图来找到这些等待事件。 Top 5 Timed Foreground Events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg ...
enq: TM - contention等待事件引起的数据库卡顿分析
还不算晕的专栏
05-21 864
因此结合ORACLE官方文档中（High 'enq: TM - contention' Wait Event Causing Session Hangs in an Oracle Streams Environment (Doc ID 740728.1)），对于OGG附加日志时执行的语句：BEGIN DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => '"AA"."BB"', supplemental_logging => 'none');
oracle enq: tm,enq: TM - contention TM 等待事件的原因及模拟
weixin_31992237的博客
04-12 403
近一个客户反映数据库在执行远程INSERT的时候应用反映很慢，在做AWR后发现enq: TM - contention等待事件很高，应该是外键约束上没有建立索引的问题。下面我来做个实验还原一下当时的现象。CREATE TABLE supplier ( supplier_id number(10) not null, supplier_name v...
没有索引的外键
02-18 170
在更新或删除父表主键时，如果外键上无索引，会导致子表上应用一个mode=4的表级锁（仅在语句执行期间存在，与事务无关）；而如果外键上有索引，只会在子表上应用一个mode=2的表级锁。下面通过10704事件跟踪验证。 1,测试数...
awr报告中显示enq: TM - contention 处理方法
congji3615的博客
07-10 201
DB：oracle 11.1.0.7.0 在做awr报告的时候，发现 enq: TM - contention 占用比例很高 根据网上一哥们提供的帖子，进行了实验 http://blog.csdn.net/robinso...
enq PS - contention.pdf
08-01
当出现 `enq: PS - contention` 错误时，意味着数据库中的并行操作（例如并行查询或DML操作）在争用某些资源，如并行服务器队列、并行执行段或者并行任务。解决这个问题的方法通常包括以下几个方面： - **优化...
BLOG_Oracle_lhr_ 【等待事件】等待事件系列（5.1）--Enqueue(队列等待) .pdf
08-06
- 使用V$ENQUEUE视图来确定哪些数据对象导致了这种等待。 - 分析SQL语句，看是否可以优化查询逻辑，减少对这些对象的操作。 - 考虑是否可以通过增加索引或使用分区技术来降低锁定范围。 综上所述，Enqueue队列等待...
BLOG_【故障处理】Oracle_lhr_队列等待之TX - row lock contention
08-03
### 故障处理：Oracle_lhr_队列等待之TX - row lock contention #### 一、概述 在Oracle数据库管理中，“enq:TX-rowlockcontention”是一种常见的队列等待事件，通常与行级别的锁定冲突有关。这种冲突可能会导致...
enq: TM - contention一例
weixin_33713350的博客
08-13 248
今天下午，有台服务器出现异常，响应特别慢，io等待奇高，awr top 5事件如下： 经回查ash，找到了造成这些事件的sql语句，如下： select * from v$active_session_history where event='enq: TM - contention' select * from v$active_session_history where event='en...
关于外键未加索引引起锁问题
cp26520的博客
06-25 191
测试：session 1：SQL> delete from emp where emp.deptno=10;session 2：SQL> delete from dept where deptno=40;现象：在em...
外键无索引导致oracle数据库锁的经典问题
云计算?
10-29 198
2年前的事情了，现在总结一下，至于深层次的外键索引导致数据库锁的原因网上文章比较多，这里就不再详细介绍了1，数据库响应慢，登录后查看大量enq锁存在SQL&gt; SELECT 2 DECODE (request, 0, 'Holder: ', 'Waiter: ') status, SID, 3 inst_id,ctime, id1, id2, lmode, requ...
外键没有索引，是否会锁表
文档搬运工
10-29 750
RDBMS 19.0.0.0上测试。 在Oracle的concept的第三章，有这么一段话，供参考。 https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/indexes-and-index-organized-tables.html#GUID-D0C53854-D92E-49D0-822A-39F3E757...
enq: TM - contention锁争用的解决
bisal的专栏
07-15 6111
这两天生产上碰见个表锁争用的问题，现象就是04:00夜维一启动，应用就开始处理缓慢，AWR看，enq: TM - contention等待事件占比超过了97%，从“SQL...
事务上的等待事件 —— enq: TX - contention
事后诸葛亮的程序人生（微信：zq9017197）
09-29 4673
TX锁是保护事务的，事务结束时便会释放。因此，为获得TX锁为等待的会话，要等到拥有锁的会话的事务结束为止。 SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name like '%enq: TX%'; NAME PARAMETER1 PARAMETER2
事务上的等待事件 —— enq: TM - contention
事后诸葛亮的程序人生（微信：zq9017197）
09-29 8091
执行DML期间，为防止对与DML相关的对象进行修改，执行DML的进程必须对该表获得TM锁。若在获得TM锁的过程中发生争用，则等待enq: HW - contention 事件。 SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name like 'enq: TM - contention';
enq: TM - contention TM 等待事件
csubx4572的博客
02-03 236
最近一个客户反映数据库在执行远程INSERT的时候应用反映很慢，在做AWR后发现enq: TM - contention等待事件很高，应该是外键约束上没有建立索引的问题。下面我来做个实验还原一下当时的现象。 CREATE TA...
enq: wf - contention
最新发布
07-28
而"enq: wf - contention"表示的是等待在流程（workflow）引擎上发生的这种争用。 流程引擎是 Oracle 数据库中用于执行业务工作流的组件。当多个工作流实例同时竞争互斥资源（如数据库表、行或锁），就会导致这种争...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值