enq: TM – contention等待事件

介绍

TM enqueue ，这里的TM 是指Table Manipulation,最常见的enqueue 之一， enq: TM – contention 最常见的enqueue 并发争用等待之一。

使用

TM 锁在下列场景中被申请：

1.  在OPS(早期的RAC)中LGWR会以ID1=0 &  ID2=0去申请该队列锁来检查 DML_LOCKS 在所有实例中是全0还是全非0
2. 当一个单表或分区 需要做不同的表/分区操作时，ORACLE需要协调这些操作，所以需要申请该队列锁。包括：
3. 启用参考约束 referential constraints
4. 修改约束从DIASABLE NOVALIDATE 到DISABLE VALIDATE
5. 重建IOT
6. 创建视图或者修改ALTER视图时可能需要申请该队列锁
7. 分析表统计信息或validate structure时
8. 一些PDML并行DML操作
9. 所有可能调用kkdllk()函数的操作
10. 太多太多了。。。

减少ENQ: TM – Contention

我们可以通过ID1作为object_id来确认大家争用的到底是什么对象，之后我们可以确定具体如何减少这些争用，具体到SQL。

ID1 ID2的含义

ID1 要么是0(LGWR)  要么是object_number， 即DBA_OBJECTS.OBJECT_ID，对应锁住对象的对象号。Id2 is 0 for a normal table / partition lock and 1 for a partition-wait lock.

Waits on this event typically occur because an index is missing on the column(s) containing a foreign key constraint. In this case Oracle is forced to acquire a TM lock on the child table during DELETE, INSERT and UPDATE statements. However, there are other cases where this can occur, e.g. a LOCK TABLE command is being used.

Solutions

Review all foreign key constraints to ensure corresponding indexes are in place. Script displays the problem table in the Objects tab for the SQL statement. Also review the Blockers tab to see what the blocker is doing. 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’)
)
ORDER BY table_name, column_position;

1. Bug 4153376 documents a change in the mode that this enqueue takes out for SELECT FOR
UPDATE statements.
2. Bug 5849679 documents an undetected deadlock case with RAC.
3. Bug 6053354 documents a possible area of contention if using indexes that reside in a read-only
tablespace.