监控到客户系统下午3点过AAS突然从20多猛增到300多。检查发现主要是TM等待SQL> select blocking_session,P2,COUNT(*) 2 from v$active_session_history 3 where sample_time between to_date('20110602 1515', 'yyyymmdd hh24:mi') and 4 to_date('20110602 1520', 'yyyymmdd hh24:mi') 5 AND event='enq: TM - contention' 6 GROUP BY blocking_session,P2 7 ; BLOCKING_SESSION P2 COUNT(*)---------------- ---------- ---------- 1456 78324 9 979 78324 2 2806 78324 3 797 78324 23054 1889 78324 1 1878 78324 24 2212 78324 2 3507 78324 1 1536 78324 4 831 78324 1 3252 78324 4 3179 78324 3020 1192 78324 3 4512 78324 109 2465 78324 2 3642 78324 5 78324 71 2175 78324 1 大量会话被797这个SESSION柱塞一般情况下,INSERT需要TM2,UPDATE/DELETE需要TM3,那么能柱塞的一般是TM4及以上SQL> select distinct chr(bitand(p1, -16777216) / 16777215) || 2 chr(bitand(p1, 16711680) / 65535) lock_type, 3 mod(p1, 16) lock_mode, 4 sql_id, 5 session_id, 6 p2 7 from v$active_session_history a 8 where sample_time between to_date('20110602 1515', 'yyyymmdd hh24:mi') and 9 to_date('20110602 1520', 'yyyymmdd hh24:mi') 10 and event = 'enq: TM - contention' 11 and mod(p1, 16) > 3 12 ; LOCK_TYPE LOCK_MODE SQL_ID SESSION_ID P2--------- ---------- ------------- ---------- ----------TM 4 57yrdsxqngs94 797 78324TM 4 57yrdsxqngs94 3179 78324果然,797/3179这2个会话在请求默认TM4,其会柱塞其后请求TM2/3的INSERT/UPDATE/DELETE语句SQL> SELECT sql_text FROM v$sql WHERE sql_id='57yrdsxqngs94'; SQL_TEXT--------------------------------------------------------------------------------DELETE FROM crm.our_staff WHERE ROWID = :PLSQLDEV_ROWIDSQL> select owner,object_name from dba_objects where object_id=78324; OWNER OBJECT_NAME------------------------------ --------------------------------------------------------------------------------SO BUSI_ORDER检查发现SO.BUSI_ORDER有指向crm.our_staff的外键,而该外键上没有索引。删除父表crm.our_staff的数据导致了在子表SO.BUSI_ORDER上加TM4的锁虽然有的文章说9I以后ORACLE修改了算法,不会在无索引的情况下,当父表数据修改时,在子表加TM4或者TM5而且做实验也没模拟成功过但是实际情况下,TM模式的死锁,还是很常见,无论是9i还是10g
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-697005/,如需转载,请注明出处,否则将追究法律责任。