Dear Readers,

Recently one of my clients facing weird issue, wherein some of application jobs were timing out. He raised a incident with us to check everything is fine from DB end.

After checking active database sessions, I found some of update queries were having wait event “enq: TM – contention”. Also I observed DB alert log was showing muliple deadlocks for the same queries since the issue was started.

What is “enq: TM – contention”:

The TM Enqueue (DML enqueue) is acquired by a transaction when a table is modified by any DML operation. DML operations require table locks to prevent simultaneous DDL operations that would conflict with the transaction.

When your application has referential integrity and attempts to modify the child/parent table, Oracle will get additional locking on the parent/child table when there is no index on the foreign key. It is therefore recommended to add indexes for all foreign key defined in the database.

The most common reason for waits on TM locks tend to involve Foreign Key constraints where the constrained columns are not indexed. Also Oracle waits for the TM lock until it is either granted or the request times out or deadlock detection occurs for TM locks.

One more thing we need to keep in mind, these waits does not indicate a problem with the DB itself. It is mainly due to poor application design.

How to identify & fix foreign key constraint indexes:

There is MOS note: Script to Check for Foreign Key Locking Issues for a Specific User (Doc ID 1019527.6)

You can refer this to create missing foreign key constraint indexes, which help you to get rid of this issue.

Hope u will find this post very useful.

Cheers

Regards,
Adityanath