Oracle 'enq: TM - contention': unindexed foreign keys and table-level locks

enq: TM - contention is the wait event a session emits when it wants a DML (table) enqueue on an object and another session already holds a conflicting mode. Unlike enq: TX - row lock contention, which is row-level and usually about uncommitted transactions, TM contention is almost always structural: an unindexed foreign key that forces Oracle to take a full table lock where it would otherwise take a row lock.

The cost is high because the lock is at the table level. One unindexed FK plus one DELETE on the parent can freeze an entire child table. Every session that wants to INSERT, UPDATE, or DELETE any row in that child waits behind the lock holder. The queue grows, connection pools spin up new connections that also block, and you can exhaust PROCESSES while the database itself is healthy.

The fix is almost always the same: index the foreign-key columns on the child table. The diagnostic work is confirming the unindexed FK is the cause and ruling out rarer TM sources: direct-path inserts, UNUSABLE indexes, or materialized view log shrinks.

What this means

The TM (DML) enqueue is acquired by every transaction that performs DML on a table. The mode depends on the operation. The modes that matter for FK contention are:

When a foreign key on the child table has no supporting index, a DELETE or PK UPDATE on the parent must take a mode 4 (S) or mode 5 (SSX) lock on the child. Modes 4 and 5 conflict with mode 3 (SX), which is what every normal INSERT/UPDATE/DELETE holds. So the moment any session is doing DML on the child, the parent DML waits; and the moment the parent DML holds the lock, every subsequent child DML waits. That is the cascade.

From 12.1.0.2 onward, Oracle changed the locking for INSERTs into the parent: an INSERT into the parent acquires only a mode 2 (SS) lock on the unindexed child, which does not block concurrent DML on the child. DELETE and UPDATE of PK columns on the parent still take the full table lock in every version. If you upgraded from 11.2 and the symptom profile changed, this is why; but the DELETE path is unchanged and is still the common trigger.

The wait event row in V$SESSION carries the locked object identifier in p2. That value is the single most useful field for triage because it tells you exactly which table is contended.

flowchart TD
    A["Session deletes parent row"] --> B{"FK on child indexed?"}
    B -- "No" --> C["Acquire mode 4/5 TM lock on child table"]
    C --> D["Child DML sessions hold mode 3 SX"]
    D --> E["enq: TM - contention on child"]
    B -- "Yes" --> F["Row-level lock only, no table lock"]
    F --> G["No TM contention"]
    E --> H["Queue grows, PROCESSES exhausts"]

Common causes

Quick checks

Run these read-only. None of them change the database.

-- Current TM waiters and their blockers
SELECT s.SID, s.SERIAL#, s.USERNAME, s.EVENT, s.SECONDS_IN_WAIT,
       s.SQL_ID, s.BLOCKING_SESSION, s.P2 AS locked_object_id
FROM V$SESSION s
WHERE s.EVENT LIKE 'enq: TM%' AND s.STATE = 'WAITING'
ORDER BY s.SECONDS_IN_WAIT DESC;

-- Resolve the locked object_id from p2
SELECT OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OBJECT_ID IN (
  SELECT DISTINCT s.P2 FROM V$SESSION s
  WHERE s.EVENT LIKE 'enq: TM%' AND s.STATE = 'WAITING'
);

-- Lock modes held and requested for TM locks on that object
SELECT l.SID, l.TYPE, l.LMODE, l.REQUEST, l.BLOCK,
       o.OWNER, o.OBJECT_NAME
FROM V$LOCK l
JOIN DBA_OBJECTS o ON l.ID1 = o.OBJECT_ID
WHERE l.TYPE = 'TM'
  AND o.OBJECT_ID IN (
    SELECT DISTINCT s.P2 FROM V$SESSION s
    WHERE s.EVENT LIKE 'enq: TM%'
  )
ORDER BY l.BLOCK DESC, l.SID;

-- Find unindexed foreign keys (schema-wide)
SELECT cc.OWNER, cc.TABLE_NAME, cc.COLUMN_NAME, cc.POSITION
FROM DBA_CONSTRAINTS c
JOIN DBA_CONS_COLUMNS cc
  ON c.OWNER = cc.OWNER
 AND c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE = 'R'
  AND c.OWNER NOT IN ('SYS','SYSTEM')
  AND cc.POSITION IS NOT NULL
MINUS
SELECT ic.TABLE_OWNER, ic.TABLE_NAME, ic.COLUMN_NAME, ic.COLUMN_POSITION
FROM DBA_IND_COLUMNS ic
WHERE ic.TABLE_OWNER NOT IN ('SYS','SYSTEM');

-- Check for UNUSABLE indexes on the contended table
SELECT OWNER, INDEX_NAME, TABLE_NAME, STATUS
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';

-- What is the blocker actually executing?
SELECT s.SID, s.SQL_ID, sq.SQL_TEXT
FROM V$SESSION s
LEFT JOIN V$SQL sq ON s.SQL_ID = sq.SQL_ID
WHERE s.SID IN (
  SELECT BLOCKING_SESSION FROM V$SESSION
  WHERE EVENT LIKE 'enq: TM%' AND STATE = 'WAITING'
);

How to diagnose it

1. Confirm the wait is actually TM. Filter V$SESSION by EVENT LIKE 'enq: TM%'. If you see enq: TX - row lock contention or enq: TX - allocate ITL entry instead, the diagnosis is different; see the related guide on TX row lock contention.
2. Identify the contended object. Take P2 from the waiter row in V$SESSION and join to DBA_OBJECTS. The locked object is the child table in the unindexed-FK case.
3. Pull the lock modes. Query V$LOCK for TYPE = 'TM' on that object. A row with REQUEST = 4 or 5 waiting on a holder with LMODE = 3 is the signature of unindexed-FK contention. REQUEST = 6 points at direct-path INSERT. REQUEST = 5 against LMODE = 3 is ON DELETE CASCADE without an index.
4. Confirm the FK is unindexed. Run the MINUS query above. If the contended child table appears with a constraint of type R (foreign key) and no matching index entry, you have the root cause.
5. Confirm the parent operation. Look at the blocking session’s SQL. It should be a DELETE, a PK UPDATE, or a MERGE that touches parent rows. If it is a direct-path INSERT, the diagnosis shifts to the APPEND path, not the FK path.
6. Rule out UNUSABLE indexes. An FK index that exists but is UNUSABLE is treated by Oracle as no index at all. Run the DBA_INDEXES STATUS check and rebuild any UNUSABLE index on the contended table. For partitioned indexes, the top-level status may be N/A while individual partitions are UNUSABLE; check DBA_IND_PARTITIONS.

Metrics and signals to monitor

Fixes

Index the foreign-key columns (default fix)

In almost every case the correct fix is to create an index on the FK columns of the child table. This lets Oracle use a row lock on the child instead of a full table lock.

-- Create an index on the FK column(s) of the child table
CREATE INDEX child_table_fk_idx ON schema.child_table (fk_column_id)
  ONLINE;

Use ONLINE so the index build does not itself take an exclusive TM lock and add to the contention. For multi-column FKs, the index must lead with the FK columns; a composite index whose leading columns are those FK columns qualifies. Their order can differ from the constraint definition, but choose the order that also serves application predicates.

After the index is built, re-run the MINUS query to confirm the FK no longer appears. Existing blocked sessions will begin to drain once the holder commits or rolls back; the index does not retroactively break the current lock chain.

Rebuild UNUSABLE indexes

If the FK index exists but is UNUSABLE, the table-lock behavior returns. Rebuild it:

ALTER INDEX schema.child_table_fk_idx REBUILD ONLINE;

For partitioned indexes, check DBA_IND_PARTITIONS for per-partition STATUS and rebuild the affected partitions.

Reduce direct-path TM exposure

If the cause is INSERT /*+ APPEND */ taking a mode 6 lock on the target table, the structural fix is to avoid concurrent OLTP DML on the same table during the direct-path load. If that is not possible, drop the APPEND hint or move the load to a window where the table is quiescent. There is no index fix for this case; mode 6 is by design for direct-path loads.

Materialized view log SHRINK

If ASH or the blocker SQL shows a SHRINK SPACE on an MLOG$_ table during an MV refresh, the SHRINK holds a TM lock that cascades to parent DML. The structural fix is to disable or reschedule the SHRINK for the MV refresh. For persistent errors, Oracle documents _mv_refresh_shrink_log=FALSE as disabling MV-log shrink during refresh; because this is an underscore parameter and its default is version-specific, check the current instance and engage Oracle Support before changing it.

ITL contention is a different event

If you actually see enq: TX - allocate ITL entry, the fix is to raise INITRANS on the table and its indexes, then rebuild affected segments. MAXTRANS is deprecated and effectively fixed at 255 in modern releases. This is not a TM issue and indexing FKs will not help. See the related guide on TX row lock contention.

Prevention

• Audit FK columns at build time. Every foreign key whose parent may receive DELETE or PK UPDATE should have an index on the child. Make this a code-review checkpoint for schema migrations.
• Alert on enq: TM - contention. Any non-zero rate during OLTP hours is a structural problem, not a load problem. Treat it as a ticket.
• Alert on ORA-00060 with TM resources in the deadlock graph. Unindexed FKs can produce deadlocks that row-lock monitoring alone misses.
• Watch UNUSABLE indexes after partition maintenance. Partition operations can leave local indexes UNUSABLE. If one of them is on an FK column, TM contention returns.
• Avoid ON DELETE CASCADE unless the FK is indexed. Cascade deletes without an index take mode 5 SSX, the most restrictive mode and the most likely to deadlock.
• Re-run the MINUS query after schema changes. Adding a constraint without an index, or dropping an index that supports an FK, silently reintroduces the problem.

How Netdata helps

• The Oracle collector surfaces V$SYSTEM_EVENT wait time by event, so enq: TM - contention appears as its own series. A spike in this event with no corresponding spike in log file sync or db file sequential read points squarely at a locking problem rather than I/O.
• Per-second active session counts broken down by wait class let you see TM contention land under the Application wait class and correlate it with a TPS drop in the same window.
• PROCESSES and SESSIONS utilization from V$RESOURCE_LIMIT shows the cascade effect: as the TM chain grows, process utilization climbs toward the limit, giving early warning before ORA-00020.
• ML anomaly detection on wait-event time and active session counts catches the onset of a TM cascade even when no static threshold would have fired, which matters because the normal rate of TM waits on a healthy system is effectively zero.
• The enqueue deadlocks statistic from V$SYSSTAT is collected as a counter, so an ORA-00060 spike from an unindexed FK shows up as a step change correlated with the TM wait spike.
• Correlating the TM wait spike with the alert log stream (ORA-00060 entries and deadlock graphs) lets you confirm the structural cause from a single timeline instead of cross-referencing multiple tools.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle ’enq: TX - row lock contention’: blocking sessions and uncommitted DML
• Oracle Database monitoring checklist: the signals every production instance needs
• Oracle Database monitoring maturity model: from survival to expert
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls