mysql等待事件类型_enq: TM – contention等待事件

介绍

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

使用

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

在OPS(早期的RAC)中LGWR会以ID1=0 &  ID2=0去申请该队列锁来检查 DML_LOCKS 在所有实例中是全0还是全非0

当一个单表或分区 需要做不同的表/分区操作时，ORACLE需要协调这些操作，所以需要申请该队列锁。包括：

启用参考约束 referential constraints

修改约束从DIASABLE NOVALIDATE 到DISABLE VALIDATE

重建IOT

创建视图或者修改ALTER视图时可能需要申请该队列锁

分析表统计信息或validate structure时

一些PDML并行DML操作

所有可能调用kkdllk()函数的操作

太多太多了。。。

减少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.