oracle enq: tm,Tuning enq: TM – contention with foreign key (外键引起的队列)

article/2025/8/28 12:00:23

TM – Enqueue contention 与Table Manipulation相关的入队争用，可以在使用需要锁定表的重组活动显式锁定表时看到。

ID1 ID2的含义

ID1 : 0(LGWR) or object_number，即DBA_OBJECTS.OBJECT_ID

ID2: 0 for a normal table / partition lock ; 1 for a partition-wait lock.

常见原因:

A. Due to Missing Foreign Key (FK) index on the FK constraint columns in the Childtables

通常会发生此事件的等待，因为包含外键约束的列上缺少索引。在这种情况下，Oracle在DELETE，INSERT和UPDATE语句期间被迫在子表上获取TM锁。

B. Enq: TM – contention’ Wait Event (TM Enqueue Contention) Using Parallel DML :

TM locks are Table-level locks that protect the table from being modified mid transaction.

A parallel DML operation’s lock requirement is very different from serial DML. The available locks are controlled by the parameter ‘DML_Locks’.

If you are seeing heavy TM lock contention on objects while using Parallel DML, you should ensure that the number of DML locks specified is realistic for the concurrent workload.

C. Enq: TM – contention’ Wait Event Occurs During ANALYZE INDEX VALIDATE STRUCTURE :

TM (Table Manipulation) enqueue is acquired during : ANALYZE INDEX VALIDATE STRUCTURE If an index is being analyzed using the ANALYZE INDEX VALIDATE STRUCTURE command while a DML operation on the underlying table is being attempted (requiring a TM lock to be placed), then contention on “enq: TM – contention” can occur.

The ANALYZE INDEX command acquires a TM enqueue in share mode on the underlying table; this will block other sessions when they attempt to place a TM lock that is incompatible with a share-mode lock.

SQL>select s.command from v$lock l, v$session s where l.sid = s.sid and l.block = 1 and l.type=’TM’;

演示第一种情况

出门滑雪前，不要忘了给你的外键列加上索引！！有时为了数据的完整性不得不加外键，当然是其它从应用程序中控制完整性，如果有外键而忽略了索引，很可能你的数据库会看到上面的负载图，看下面的两个实验

sys@ANBOB> conn anbob/anbob

Connected.

anbob@ANBOB> create table team(id int,name varchar2(20));

Table created.

anbob@ANBOB> insert into team values(1,'HR');

1 row created.

anbob@ANBOB> insert into team values(2,'IBG');

1 row created.

anbob@ANBOB> insert into team values(3,'OSC');

1 row created.

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> create table memb(id int,name varchar2(20),tid int,

2 constraint fk_mem_team_id foreign key (tid) references team(id) on delete cascade);

constraint fk_mem_team_id foreign key (tid) references team(id) on delete cascade)

ERROR at line 2:

ORA-02270: no matching unique or primary key for this column-list

anbob@ANBOB> alter table team modify id primary key;

Table altered.

anbob@ANBOB> create table memb(id int,name varchar2(20),tid int,

2 constraint fk_mem_team_id foreign key (tid) references team(id) on delete cascade);

Table created.

anbob@ANBOB> select table_name,index_name from user_indexes where TABLE_name='TEAM';

TABLE_NAME INDEX_NAME

------------------------------ ------------------------------

TEAM SYS_C005168

anbob@ANBOB> insert into memb values(1,'anbob',3);

1 row created.

anbob@ANBOB> insert into memb values(2,'adm',1);

1 row created.

anbob@ANBOB> insert into memb values(3,'sal',2);

1 row created.

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> delete team where id=1;

1 row deleted.

anbob@ANBOB> select userenv('sessionid') from dual;

USERENV('SESSIONID')

--------------------

2317

anbob@ANBOB>

########新开一个session 2

[oracle@aix ~]$ sqlplus anbob/anbob

SQL*Plus: Release 10.2.0.1.0 - Production on ????????? 6??? 16 17:21:30 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

anbob@ANBOB> delete team where id=2;

挂起

再新开一个session 3

[oracle@aix ~]$ sqlplus anbob/anbob

SQL*Plus: Release 10.2.0.1.0 - Production on ????????? 6??? 16 17:22:13 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

anbob@ANBOB> insert into memb values(10,'jack',3);

挂起

##########新开一个session 4 ,查看一下lock

sys@ANBOB> col event for a30

sys@ANBOB> /

SID USERNAME BLOCKER EVENT TY LMODE REQUEST OBJECT_NAM OBJECT_TYPE

---------- ---------- ---------- ------------------------------ -- ---------- ---------- ---------- -------------------

141 ANBOB 131 enq: TM - contention TM 3 0 TEAM TABLE

133 ANBOB 141 enq: TM - contention TM 2 0 TEAM TABLE

131 ANBOB SQL*Net message from client TX 6 0

131 ANBOB SQL*Net message from client TM 3 0 MEMB TABLE

141 ANBOB 131 enq: TM - contention TM 0 5 MEMB TABLE

133 ANBOB 141 enq: TM - contention TM 0 3 MEMB TABLE

131 ANBOB SQL*Net message from client TM 3 0 TEAM TABLE

...

166 rdbms ipc message CF 2 0

161 smon timer TS 3 0 I_OBJ# INDEX

168 rdbms ipc message MR 4 0 I_OBJ# INDEX

18 rows selected.

anbob@ANBOB> create index idx_mem_tid on memb(tid);

Index created.

解决方法在子表父字段建索引后，就不再影响其它的insert了

实验2

session1

anbob@ANBOB> insert into team values(4,'HR');

1 row created.

session 2

SQL> insert into memb values(13,'rain',5);

insert into memb values(13,'rain',5)

ERROR at line 1:

ORA-02291: integrity constraint (ANBOB.FK_MEM_TEAM_ID) violated - parent key not found

SQL> insert into memb values(13,'rain',4); 又hang 住了

session 3查一下当前的等待

sys@ANBOB> select sid,serial#,event,audsid from v$session where username='ANBOB';

SID SERIAL# EVENT AUDSID

---------- ---------- ------------------------------ ----------

143 3675 enq: TX - row lock contention 2584

154 875 SQL*Net message from client 2597

回到session 1 ctrl+c 取消操作，查看一下当前的audsid ,确认sid143等待是它

SQL> insert into memb values(13,'rain',4);

insert into memb values(13,'rain',4)

ERROR at line 1:

ORA-02291: integrity constraint (ANBOB.FK_MEM_TEAM_ID) violated - parent key

not found

SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')

--------------------

2584

The following script will show all unindexed columns from foreign key constraints for a specific user and it can also be customized to include only the one table :

SELECT * FROM (

SELECT c.table_name, cc.column_name, cc.position column_position

FROM user_constraints c, user_cons_columns cc

WHERE c.constraint_name = cc.constraint_name

AND c.constraint_type = 'R'

AND c.owner = upper('&&owner') and cc.owner = upper('&&owner')

MINUS

SELECT i.table_name, ic.column_name, ic.column_position

FROM user_indexes i, user_ind_columns ic

WHERE i.index_name = ic.index_name

AND c.owner = upper('&&owner') and cc.owner = upper('&&owner')

)