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出來了。

enq:TM-contention事件等待

相關文章