enq:TM-contention等待的发生，发生TM锁争用的情况如下：

1、修改无索引外键(foreign key)的父键时
2、DML与DDL之间的TM争用
3、LOCK TABLE 引起的TM锁争用
4、direct load工作引起的TM锁争用
模拟该等待事件
1、创建两张测试表
JZH@jzh>create table jzh1 as select object_id,object_name from user_objects;
Table created.
JZH@jzh>create table jzh2 as select object_id,object_name from user_objects;
Table created.
2、创建主键索引
JZH@jzh>alter table jzh1 add constraint pk_jzh1 primary key(object_id);
Table altered.
3、为jzh2添加外键
JZH@jzh>alter table jzh2 add constraint fk_jzh2 foreign key(object_id) references jzh1 (object_id)on delete cascade;
Table altered.
JZH@jzh>select index_name from user_indexes where table_name='JZH2';
no rows selected
---虽然添加了外键，但没有创建索引
4、多开几个session删除数据
session1:
JZH@jzh>select sid from v$mystat where rownum=1;
SID
----------
1
JZH@jzh>delete from jzh1 where object_NAME='T';
1 row deleted.
session2:
JZH@jzh>delete from jzh1 where object_name='TEST';----hang住了
session3;
JZH@jzh>insert into jzh1 values(1,'JZH');---也被hang住了
5、查看没锁的情况
select a.sid,c.blocking_session blocker,c.event,a.type,a.lmode,a.request,b.object_name,b.object_type from v$lock a,dba_objects b,v$session c where c.username='JZH' and a.id1=b.object_id and a.sid=c.sid;
SID BLOCKER EVENT TYPE LMODE REQUEST OBJECT_NAME OBJECT_TYPE
---------- ---------- -------------------- ---------- ---------- ---------- --------------- --------------------
32 1 enq: TM - contention AE 4 0 ORA$BASE EDITION
37 32 enq: TM - contention AE 4 0 ORA$BASE EDITION
1 SQL*Net message from AE 4 0 ORA$BASE EDITION
client
1 SQL*Net message from TO 3 0 SDO_GEOR_DDL__T TABLE
client ABLE$$
1 SQL*Net message from TM 3 0 JZH2 TABLE
client
SID BLOCKER EVENT TYPE LMODE REQUEST OBJECT_NAME OBJECT_TYPE
---------- ---------- -------------------- ---------- ---------- ---------- --------------- --------------------
32 1 enq: TM - contention TM 0 5 JZH2 TABLE
37 32 enq: TM - contention TM 0 3 JZH2 TABLE
1 SQL*Net message from TM 3 0 JZH1 TABLE
client
32 1 enq: TM - contention TM 3 0 JZH1 TABLE
37 32 enq: TM - contention TM 3 0 JZH1 TABLE
10 rows selected.
终于看到了enq: TM - contention等待事件，且sid:1阻塞了sid:32，而sid:32阻塞了sid:37。
6、在外键列上创建索引
JZH@jzh>create index object_id_idx on jzh2(object_id);
Index created.
再重复上面session1-session3步骤
再次查询：
SYS@jzh>select a.sid,c.blocking_session blocker,c.event,a.type,a.lmode,a.request,b.object_name,b.object_type from v$lock a,dba_objects b,v$session c where c.username='JZH' and a.id1=b.object_id and a.sid=c.sid;
SID BLOCKER EVENT TYPE LMODE REQUEST OBJECT_NAME OBJECT_TYPE
---------- ---------- ---------------------------------------- ---------- ---------- ---------- --------------- --------------------
32 SQL*Net message from client AE 4 0 ORA$BASE EDITION
37 SQL*Net message from client AE 4 0 ORA$BASE EDITION
1 SQL*Net message from client AE 4 0 ORA$BASE EDITION
1 SQL*Net message from client TM 3 0 JZH2 TABLE
37 SQL*Net message from client TM 3 0 JZH2 TABLE
32 SQL*Net message from client TM 3 0 JZH2 TABLE
1 SQL*Net message from client TO 3 0 SDO_GEOR_DDL__T TABLE
ABLE$$
1 SQL*Net message from client TM 3 0 JZH1 TABLE
37 SQL*Net message from client TM 3 0 JZH1 TABLE
SID BLOCKER EVENT TYPE LMODE REQUEST OBJECT_NAME OBJECT_TYPE
---------- ---------- ---------------------------------------- ---------- ---------- ---------- --------------- --------------------
32 SQL*Net message from client TM 3 0 JZH1 TABLE
10 rows selected.
可以看到已经没有enq: TM - contention TM等待。
7、利用lock table模拟enq: TM - contention TM 等待
session1:
JZH@jzh>update jzh1 set object_id=1 where object_name='TEST';
1 row updated.
session2:
JZH@jzh>lock table jzh1 in exclusive mode;----hang住了
SYS@jzh>select a.sid,c.blocking_session blocker,c.event,a.type,a.lmode,a.request,b.object_name,b.object_type from v$lock a,dba_objects b,v$session c where c.username='JZH' and a.id1=b.object_id and a.sid=c.sid;
SID BLOCKER EVENT TYPE LMODE REQUEST OBJECT_NAME OBJECT_TYPE
---------- ---------- ---------------------------------------- ---------- ---------- ---------- --------------- --------------------
32 1 enq: TM - contention AE 4 0 ORA$BASE EDITION
37 SQL*Net message from client AE 4 0 ORA$BASE EDITION
1 SQL*Net message from client AE 4 0 ORA$BASE EDITION
1 SQL*Net message from client TM 3 0 JZH1 TABLE
32 1 enq: TM - contention TM 0 6 JZH1 TABLE
1 SQL*Net message from client TO 3 0 SDO_GEOR_DDL__T TABLE
ABLE$$
1 SQL*Net message from client TX 6 0 /155fe610_Const SYNONYM
raintOperator
7 rows selected.
可以看到enq: TM - contention出来了。

ITPub博客

enq:TM-contention事件等待

最新文章