Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.