Oracle problems

enq: TM - contention: The Unindexed Foreign Key, and the Fix

3 November 2026

enq: TM - contention is a session waiting for a lock on a whole table, and in almost every database it has one cause: a foreign key whose child table has no index on the key, so a delete or a key update on the parent has to lock the child table, and every session inserting into the child queues behind it. Oracle's enqueue list calls TM the "DML Enqueue". Its Concepts guide says a table lock "is acquired by a transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement", and adds the sentence that explains the queue: "A table lock taken on a child table because of an unindexed foreign key is held for the duration of the statement, not the transaction." The wait's parameters name the table, two queries name the holder and the missing index, and the fix is one CREATE INDEX.

What the wait means

Every DML statement takes a TM lock on each table it touches, in a mode that says how much of the table it needs. The reference lists the modes as V$LOCKED_OBJECT prints them: "0 - NONE: lock requested but not yet obtained", "1 - NULL", "2 - ROWS_S (SS): Row Share Lock", "3 - ROW_X (SX): Row Exclusive Table Lock", "4 - SHARE (S): Share Table Lock", "5 - S/ROW-X (SSX): Share Row Exclusive Table Lock", "6 - Exclusive (X): Exclusive Table Lock". Ordinary inserts, updates and deletes take mode 3, and mode 3 does not conflict with mode 3, which is why a thousand sessions change one table all day without a single TM wait. The wait appears when someone asks for mode 4, 5 or 6: the parent-table delete that must take a share lock on an unindexed child, a LOCK TABLE, a direct-path insert, which locks the table exclusively, or DDL, which asks for mode 6 and, by default, does not wait at all. The wait class is Application, because the application's own statements cause it.

Which table, and who holds it

SELECT s.sid, s.blocking_session AS blocker, s.sql_id,
       BITAND(s.p1, 65535) AS mode_requested,
       o.owner, o.object_name, s.seconds_in_wait
FROM   v$session s
JOIN   dba_objects o ON o.object_id = s.p2
WHERE  s.event = 'enq: TM - contention'
ORDER  BY s.seconds_in_wait DESC;

The second parameter of the wait is the object number of the table, and the low two bytes of the first are the mode the session asked for. A session asking for mode 4 on a table it never names in its own statement is the parent-table delete asking for a share lock on the child. Then every lock on that table, held and requested, from V$LOCK, which the reference describes as the view that "lists the locks currently held by the Oracle Database and outstanding requests for a lock":

SELECT l.sid, l.lmode, l.request, l.block, s.status,
       s.username, s.sql_id, s.prev_sql_id, s.program
FROM   v$lock l
JOIN   v$session s ON s.sid = l.sid
WHERE  l.type = 'TM'
AND    l.id1 IN (SELECT p2 FROM v$session
                 WHERE  event = 'enq: TM - contention')
ORDER  BY l.id1, l.request DESC, l.lmode DESC;

The picture is always the same. One session holds mode 3 with an uncommitted transaction, often INACTIVE, its insert done and its commit not yet sent. One session requests mode 4: the delete on the parent, waiting for that transaction to end. And behind it, every new insert into the child requests mode 3 and waits, because the enqueue is a queue and the share request is ahead of them. The block column, "1 - The lock is blocking other processes", marks the first holder; the prev_sql_id of an idle holder is the insert it has not committed.

The foreign keys without an index

SELECT c.owner, c.table_name, c.constraint_name, cc.column_name,
       c.r_constraint_name
FROM   dba_constraints c
JOIN   dba_cons_columns cc ON cc.owner = c.owner
                          AND cc.constraint_name = c.constraint_name
                          AND cc.position = 1
WHERE  c.constraint_type = 'R'
AND    c.owner NOT IN ('SYS', 'SYSTEM')
AND    NOT EXISTS (SELECT 1
                   FROM   dba_ind_columns ic
                   WHERE  ic.table_owner = c.owner
                   AND    ic.table_name  = c.table_name
                   AND    ic.column_name = cc.column_name
                   AND    ic.column_position = 1)
ORDER  BY c.owner, c.table_name;

constraint_type = 'R' is, in the reference's words, "Referential integrity", and r_constraint_name is "Name of the unique constraint definition for the referenced table", which is how the child in the lock query is tied to the parent that the holder deletes from. Every row of this list is a TM wait waiting for the first clean-up job on its parent, and a deadlock waiting for two of them to meet.

The other holders

DDL. ALTER TABLE, DROP, TRUNCATE and a non-online CREATE INDEX ask for mode 6. The reference says DDL_LOCK_TIMEOUT "specifies a time limit for how long DDL statements will wait in a DML lock queue", that "A value of zero indicates a status of NOWAIT", and that zero is the default: by default the DDL fails at once with ORA-00054 instead of waiting. Where the parameter was raised, the DDL waits in the queue on this event, and every new insert, update and delete on the table queues behind it for as long as it waits. Direct-path loads. An INSERT /*+ APPEND */ and parallel DML lock the table exclusively for the length of the transaction, so a load into a table that users insert into all day is a TM queue by design. LOCK TABLE. An application or a tool that locks a table in share or exclusive mode before a batch, and holds it across the batch. Partition maintenance. A drop or exchange of a partition on a busy table is DDL and behaves like DDL.

The fix, in order

Index the foreign key. The Development Guide's whole guideline is one sentence: "Index foreign keys unless the matching unique or primary key is never updated or deleted." An index whose leading columns are the foreign key columns, in any order, is enough, and an existing index that starts with them already counts. Create it ONLINE so the build itself does not take the lock it is meant to remove. With the index in place, the parent delete locks the matching child rows instead of the child table, and the queue never forms again.
Until the index exists, keep the parent quiet. Stop the clean-up job that deletes parent rows, or run it in the hour when nobody inserts into the child, and commit in small batches so each share lock is held for a short statement.
DDL: online, and with a short timeout. CREATE INDEX ... ONLINE, ALTER TABLE ... MOVE ONLINE, DBMS_REDEFINITION for a rebuild, and a session-level DDL_LOCK_TIMEOUT of a few seconds, so that the DDL either gets the table at once or fails quickly, instead of parking a mode 6 request that every user statement queues behind for minutes.
Loads: schedule or exchange. A direct-path load runs in the window when the table is idle, or loads a staging table that is exchanged into a partition; conventional inserts during the online hours take the mode 3 lock that conflicts with no one.
An idle holder: commit or end it. The session at the head of the queue holding mode 3 with an uncommitted insert belongs to someone who forgot to commit, or to an application that keeps transactions open across user think time. Ask, or ALTER SYSTEM KILL SESSION it; the fix behind it is in the application, the same one as for a root blocker.
Deadlocks with a TM lock in the trace. ORA-00060 where the trace shows a TM lock is the unindexed foreign key again, two parent deletes meeting two child inserts in opposite orders. The index fixes both the queue and the deadlock.

See it coming

Radar Engine, the detection layer of Scops, samples every session every 5 seconds and follows the chain from each waiter to its blocker, so a share-lock request parked on a child table is a finding with the table, the holder and the statement at the head of the queue while the queue is ten sessions long:

Detector	Clock	When it fires
Sessions blocked	Live. 10 sessions blocked for 3 minutes.	Warning. 30 sessions, or 10 for 5 minutes: Critical.
Application waits surging	Live. Twice the hour before, well above its usual spread.	Watch. With a second sign, such as 5 blocked sessions or commits three times slower: Warning. Above max(4, 4 × usual) active sessions, with an effect: Critical.
Executions stalled	Live. Executions fall while sessions wait on one event or a blocker.	Warning. 10% of normal for 5 minutes, or 30 sessions behind one blocker: Critical.
Deadlocks	Live, against the hour before.	Watch. A burst of five, or a steady stream: Warning. Never Critical.
Long-running transaction	The largest open transaction: 3 hours, 2 GB of undo, and growing.	Warning. 8 hours or 10 GB: Critical. Usual at this hour: Watch.

The finding names the database, the table, the root holder with its user, program and last statement, and the number of sessions behind it. Scops AI, asked why the inserts stopped, reads the same chain and the constraints of the table, and answers with the foreign key and the index that is missing. All of it on any edition, without the Diagnostics Pack.

Questions

What is enq: TM - contention?
A session waiting for a lock on a whole table, the DML enqueue, in a mode that conflicts with a lock another session holds. Ordinary DML takes a row-exclusive table lock that never conflicts with another; the wait appears when a session asks for a share or exclusive lock on the table, almost always a parent-table delete on a foreign key whose child table has no index.

Why does a delete on the parent table block inserts on the child?
Without an index on the foreign key column, Oracle cannot find the child rows of the deleted parent by key, so it takes a share lock on the whole child table for the length of the statement. That lock waits for every uncommitted insert into the child, and every new insert waits behind it. An index on the foreign key lets Oracle lock the matching rows instead.

Which index fixes it?
Any index whose leading columns are the foreign key columns, in any order. Oracle's Development Guide says to index foreign keys unless the matching primary or unique key is never updated or deleted. Create it online.

Why does my ALTER TABLE wait on enq: TM - contention?
DDL needs an exclusive table lock. With DDL_LOCK_TIMEOUT at its default of 0 it does not wait and fails with ORA-00054; with a timeout set, it waits in the lock queue on this event, and every DML statement that arrives meanwhile queues behind it. Use online DDL and a timeout of a few seconds.

Can I kill the session holding the TM lock?
If it is idle with an uncommitted transaction, yes, and the queue clears at once. If it is the parent delete, killing it rolls the delete back and the next run queues again; the index is the fix.

Sources

Oracle: Oracle enqueue names, Concepts: data concurrency and consistency, V$LOCK, V$LOCKED_OBJECT, DDL_LOCK_TIMEOUT, Development Guide: guidelines for indexing foreign keys, ALL_CONSTRAINTS
Scops docs: Detector reference