Enq of Oracle waiting event: TM-contention
During DML, in order to prevent modifications to DML-related objects, the process executing DML must acquire a TM lock on the table.
P1 = name | mode
P2 = object #
P3 = table/partition
SQL > select name,parameter1,parameter2,parameter3 from v$event_name where name like 'enq: TM-contention'
NAME PARAMETER1 PARAMETER2 PARAMETER3
Enq: TM-contention name | mode object # table/partition
Solution: you can query blocker through the following sql
SELECT distinct w.tm, w.p2 OBJECT_ID, l.inst_id, l.sid, l.lmode, l.request,l.block
FROM
(SELECT p2, p3, 'TM-' | | substr (p2rawMarelle 8) | |' -'| | lpad (p3mem8 meme 0') TM
FROM v$session_wait
WHERE event='enq: TM-contention'
And state='WAITING'
) W
Gv$lock L
WHERE l.type (+) = 'TM'
And l.id1 (+) = w.p2
And l.id2 (+) = w.p3
ORDER BY tm, lmode desc, request desc
Generally speaking, TM contention occurs as follows:
L DDL action
L Lock table tab_name in share mode;lock table tab_name in exclusive mode
L INSERT / * + APPEND * / INTO
L direct path load of SQL*Loader
L foreign key constraint no index
Simulated enq: TM-contention
Session 1:
SYS@cdbtest1 (CDB$ROOT) > lock table t in exclusive mode
Table (s) Locked.
Session 2:
SYS@cdbtest1 (CDB$ROOT) > update t set object_id=100 where object_id=100
Query wait event
SYS@cdbtest1 (CDB$ROOT) > select inst_id, event#, event,count (*) from gv$session
2 where wait_class# 6
3 group by inst_id, event#,event
4 order by 1,4 desc
INST_ID EVENT# EVENT COUNT (*)
-
1 278 enq: TM-contention 1
1 414 SQL*Net message to client 1
Query blocker
SELECT distinct w.tm, w.p2 OBJECT_ID, l.inst_id, l.sid, l.lmode, l.request,l.blocklmode, l.request,l.block
FROM
2 3 (SELECT p2, p3, 'TM-' | | substr (p2raw TM-' 8) | |' -'| | lpad (p3Magin8) TM
4 FROM v$session_wait
5 WHERE event='enq: TM-contention'
6 and state='WAITING'
7) W
8 gv$lock L
9 WHERE l.type (+) = 'TM'
10 and l.id1 (+) = w.p2
11 and l.id2 (+) = w.p3
12 ORDER BY tm, lmode desc, request desc
13
TM OBJECT_ID INST_ID SID LMODE REQUEST BLOCK
TM-00013866-00000000 79974 1 113 6 01
TM-00013866-00000000 79974 1 91 0 3 0
The blocker is 113 and the blocker is 91.
