TM - Row Lock Contention Wait Event

This afternoon, a user reported that he’s experienced performance issue. He described that his PC seemed hung without response while he ran one of oracle jobs.

I quickly checked the the sessions this user lunched and found that his sessions kept waiting for event “enq: TM Lock Contention”, not normal one “SQL*Net message from client”.

Typically, “enq: TM Lock Contention” is defined as below.

Problem

Several sessions in your database are taking a very long time to process some insert statements. As a result, the “active” sessions count is very high and the database is unable to accept new session connections. Upon checking, you find that the database is experiencing a lot of enq: TM – contention wait events.

Solution

The enq: TM – contention event is usually due to missing foreign key constraints on a table that’s part of an Oracle DML operation. Once you fix the problem by adding the foreign key constraint to the relevant table, the enq: TM – contention event will go away.

The waits on the enq: TM – contention event for the sessions that are waiting to perform insert operations are almost always due to an unindexed foreign key constraint.. This happens when a dependent or child table’s foreign key constraint that references a parent table is missing an index on the associated key. Oracle acquires a table lock on a child table if it’s performing modifications on the primary key column in the parent table that’s referenced by the foreign key of the child table. Note that these are full table locks (TM), and not row-level locks (TX)—thus, these locks aren’t restricted to a row but to the entire table. Naturally, once this table lock is acquired, Oracle will block all other sessions that seek to modify the child table’s data. Once you create an index in the child table performing on the column that references the parent table, the waits due to the TM contention will go away.

From definition above, I checked related tables and found one of tables has two disabled foreign keys. And then, I tried to enable them. however, it failed by receiving ora-00054 error.

1
ORA-00054: resource busy and acquire with NOWAIT specified
To prevent from getting this error and then be able to enable foreign keys, I then tried to locate the blocking session.

1
2
3
4
5
6
7
8
9
SQL> select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='EMP';

SID    SERIAL#
----   --------
753     8910
Then, I killed this blocking session.

1
2
3
ALTER SYSTEM KILL SESSION '753,8910';

session killed.
After above, I simply got success to enable these two foreign keys and then the contention issue was solved.