enq: TM - contention - Diagnose Oracle Table Lock Waits

enq: TM - contention

Overview

The enq: TM - contention wait event occurs when a session is blocked waiting to acquire a TM (Table Management) enqueue lock on a table. TM locks protect the internal structure of a table — they prevent DDL operations from altering a table while DML is active, and they serialize certain types of concurrent DML that would otherwise conflict.

Oracle acquires TM locks in one of several modes:

• Mode 2 (RS — Row Share): SELECT FOR UPDATE, subquery in DML — allows concurrent DML
• Mode 3 (RX — Row Exclusive): INSERT, UPDATE, DELETE, MERGE — the most common mode for normal DML
• Mode 4 (S — Share): Acquired by some lock-related operations
• Mode 5 (SRX — Share Row Exclusive): Certain DDL holding operations
• Mode 6 (X — Exclusive): ALTER TABLE, DROP TABLE, TRUNCATE — blocks all other DML

Most routine DML (INSERT/UPDATE/DELETE) acquires TM in mode 3 (RX). Multiple sessions can hold TM-RX simultaneously — this is the normal, non-contending case. Contention arises when a mode 6 (DDL) is needed while DML is active, or when a specific anti-pattern called unindexed foreign keys forces Oracle to acquire an elevated TM lock on the parent table during child table DML.

The P1 parameter of enq: TM - contention encodes the enqueue type and mode (‘TM’ plus the requested mode), P2 is the object number (the OBJECT_ID from DBA_OBJECTS), and P3 is 0 for TM enqueues. The P2 value directly identifies the table being contended.

When This Wait Is a Problem

Acceptable: Very brief TM waits (milliseconds) during planned DDL operations are normal. A single ALTER TABLE during off-peak hours causing a momentary pause is expected.

Investigate when:

• enq: TM - contention appears in AWR top wait events during normal OLTP operations
• Average wait time exceeds 1 second
• Sessions accumulate waiting with no DDL activity visible — this strongly suggests unindexed foreign key contention
• Application reports timeout errors during DELETE operations on parent tables

Critical pattern: If you see TM contention without any concurrent DDL, the cause is almost certainly an unindexed foreign key. This is one of the most common Oracle DBA gotchas and causes significant performance degradation on DELETE-heavy workloads.

Diagnostic Queries

1. Identify the Object Being Contended (P2 = Object ID)

-- Decode the P2 value to find the contested table
SELECT
    sw.sid,
    sw.serial#,
    sw.username,
    sw.program,
    sw.event,
    sw.seconds_in_wait,
    sw.p1,
    sw.p2 AS object_id,
    sw.p3,
    -- Decode lock mode from P1
    DECODE(
        BITAND(sw.p1, 255),
        1, 'Null',
        2, 'RS (Row Share)',
        3, 'RX (Row Exclusive)',
        4, 'S (Share)',
        5, 'SRX (Share Row Exclusive)',
        6, 'X (Exclusive)',
        'Unknown'
    ) AS lock_mode_requested,
    o.owner,
    o.object_name,
    o.object_type
FROM
    v$session sw
    JOIN dba_objects o ON sw.p2 = o.object_id
WHERE
    sw.event = 'enq: TM - contention'
ORDER BY
    sw.seconds_in_wait DESC;

2. V$LOCK — Full Lock Chain Analysis

-- Show all TM locks: who holds, who waits, and modes
SELECT
    l.sid,
    s.serial#,
    s.username,
    s.program,
    l.type AS lock_type,
    l.id1 AS object_id,
    o.object_name,
    o.owner,
    o.object_type,
    DECODE(l.lmode,
        0, 'None',
        1, 'Null',
        2, 'RS',
        3, 'RX',
        4, 'S',
        5, 'SRX',
        6, 'X') AS lock_mode_held,
    DECODE(l.request,
        0, 'None',
        1, 'Null',
        2, 'RS',
        3, 'RX',
        4, 'S',
        5, 'SRX',
        6, 'X') AS lock_mode_requested,
    l.block AS is_blocking_others,
    s.status AS session_status,
    s.seconds_in_wait
FROM
    v$lock l
    JOIN v$session s ON l.sid = s.sid
    LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE
    l.type = 'TM'
    AND (l.lmode > 0 OR l.request > 0)
ORDER BY
    l.block DESC,
    l.sid;

3. Identify Unindexed Foreign Keys — The Primary Cause of Unexpected TM Contention

-- Find unindexed foreign keys that cause parent-table TM lock escalation
-- These cause TM-RX on parent during DELETE from parent (full table lock on child)
SELECT
    c.owner,
    c.constraint_name,
    c.table_name AS child_table,
    c.r_owner AS parent_owner,
    rc.table_name AS parent_table,
    c.status,
    'UNINDEXED FOREIGN KEY' AS issue_type,
    -- Columns in the foreign key
    LISTAGG(cc.column_name, ', ')
        WITHIN GROUP (ORDER BY cc.position) AS fk_columns
FROM
    dba_constraints c
    JOIN dba_constraints rc
        ON c.r_constraint_name = rc.constraint_name
        AND c.r_owner = rc.owner
    JOIN dba_cons_columns cc
        ON c.constraint_name = cc.constraint_name
        AND c.owner = cc.owner
WHERE
    c.constraint_type = 'R'  -- Foreign key
    AND c.status = 'ENABLED'
    AND NOT EXISTS (
        -- Check if a matching index exists for the FK columns
        SELECT 1
        FROM dba_ind_columns ic
        WHERE ic.table_owner = c.owner
          AND ic.table_name = c.table_name
          AND ic.column_name IN (
              SELECT cc2.column_name
              FROM dba_cons_columns cc2
              WHERE cc2.constraint_name = c.constraint_name
                AND cc2.owner = c.owner
          )
          AND ic.column_position = 1
    )
GROUP BY
    c.owner, c.constraint_name, c.table_name,
    c.r_owner, rc.table_name, c.status
ORDER BY
    c.owner, c.table_name;

4. V$SYSTEM_EVENT — TM Contention Historical Totals

-- TM enqueue wait statistics
SELECT
    event,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2) AS total_wait_secs,
    ROUND(average_wait / 100, 4) AS avg_wait_secs,
    ROUND(max_wait / 100, 2) AS max_wait_secs
FROM
    v$system_event
WHERE
    event LIKE 'enq: TM%'
    OR event LIKE 'enq: TX%'
ORDER BY
    total_wait_secs DESC;

5. ASH — Historical TM Contention Pattern

-- Active Session History: TM contention events with object context
SELECT
    TO_CHAR(ash.sample_time, 'YYYY-MM-DD HH24:MI') AS sample_minute,
    ash.current_obj#,
    o.object_name,
    o.owner,
    COUNT(*) AS ash_samples,
    COUNT(DISTINCT ash.session_id) AS sessions_affected,
    MAX(ash.blocking_session) AS blocking_session
FROM
    v$active_session_history ash
    LEFT JOIN dba_objects o ON ash.current_obj# = o.object_id
WHERE
    ash.event = 'enq: TM - contention'
    AND ash.sample_time > SYSDATE - 1
GROUP BY
    TO_CHAR(ash.sample_time, 'YYYY-MM-DD HH24:MI'),
    ash.current_obj#,
    o.object_name,
    o.owner
ORDER BY
    ash_samples DESC
FETCH FIRST 30 ROWS ONLY;

6. Find Sessions Running DDL That Holds Mode-6 TM Locks

-- Sessions executing DDL that would hold exclusive TM locks
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.status,
    s.event,
    s.seconds_in_wait,
    s.sql_id,
    q.sql_text
FROM
    v$session s
    LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE
    (
        UPPER(q.sql_text) LIKE '%ALTER TABLE%'
        OR UPPER(q.sql_text) LIKE '%DROP TABLE%'
        OR UPPER(q.sql_text) LIKE '%TRUNCATE%'
        OR UPPER(q.sql_text) LIKE '%CREATE INDEX%'
    )
    AND s.status = 'ACTIVE'
ORDER BY
    s.seconds_in_wait DESC;

Root Causes

1. Unindexed Foreign Keys (Most Common for Unexpected TM Contention)

This is the single most common cause of enq: TM - contention during normal DML operations with no DDL running. When a foreign key on a child table lacks a supporting index, Oracle must perform a full scan of the child table when rows are deleted from the parent table to verify referential integrity. To prevent concurrent DML on the child table from interfering with this scan, Oracle acquires a mode-4 (Share) or mode-5 (Share Row Exclusive) TM lock on the child table for the duration of the parent table DELETE.

This means: a DELETE from the parent table takes a table-level lock on the child table. In a system where the child table has high concurrent DML activity, this serializes everything — all child table DML must wait until the parent table DELETE completes.

Adding an index on the foreign key columns allows Oracle to perform a targeted index range scan instead of a full table scan, eliminating the need for the elevated TM lock.

2. DDL Executing Concurrently with DML (ALTER TABLE, TRUNCATE, DROP INDEX)

DDL statements require an exclusive (mode-6) TM lock on the target table. Any concurrent DML (INSERT/UPDATE/DELETE in mode-3 RX) must wait for the exclusive DDL lock to be released. This is expected behavior, but it becomes a problem when DDL is executed during peak hours while the table is heavily used.

Conversely, DDL that arrives while DML is active must wait for all existing DML transactions to complete and release their TM locks before the exclusive DDL lock can be granted.

3. Online Index Creation Contention

CREATE INDEX ... ONLINE generally allows concurrent DML during index build, but there is a brief period at the end of the build where the index must be finalized. During this window, Oracle acquires a brief mode-4 TM lock. If the table is under very high DML, even this brief exclusive moment can generate TM contention waits.

4. LOCK TABLE Statements

Explicit LOCK TABLE table_name IN SHARE MODE or IN EXCLUSIVE MODE statements from application code or DBA scripts directly acquire TM locks at the requested mode. These block concurrent DML and are a common cause of unexpected TM contention in applications that use table-level locking for “pessimistic” concurrency control.

5. Import Operations (Data Pump, SQL*Loader)

Data Pump import and direct-path SQL*Loader operations acquire elevated TM locks during their load operation. Running concurrent OLTP DML against the same tables during an import or load generates TM contention.

Resolution Steps

Step 1: Create Indexes for Unindexed Foreign Keys

-- Create indexes for unindexed foreign keys identified by the diagnostic query
-- Example: FK from ORDERS to CUSTOMERS on CUSTOMER_ID column

-- First, check that the index doesn't already exist under a different name
SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_owner = 'HR'
  AND table_name = 'ORDERS'
  AND column_name = 'CUSTOMER_ID';

-- Create the index (use ONLINE to avoid locking during creation)
CREATE INDEX hr.idx_orders_customer_id
ON hr.orders(customer_id)
ONLINE
TABLESPACE idx_tbs;

Step 2: Identify and Kill Blocking DDL Sessions (Emergency)

-- Identify the blocking session from V$LOCK (block = 1)
SELECT sid, serial#, username, program, event, seconds_in_wait
FROM v$session s
WHERE s.sid IN (
    SELECT l.sid FROM v$lock l WHERE l.type = 'TM' AND l.block = 1
);

-- Kill the blocking session if appropriate
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Step 3: Validate and Fix All Foreign Keys Without Indexes

-- After identifying unindexed FKs (query #3 above), generate CREATE INDEX DDL
SELECT
    'CREATE INDEX ' ||
    LOWER(c.owner) || '.idx_' ||
    LOWER(SUBSTR(c.table_name, 1, 20)) || '_' ||
    LOWER(SUBSTR(c.constraint_name, 1, 10)) ||
    ' ON ' || LOWER(c.owner) || '.' || LOWER(c.table_name) ||
    '(' ||
    LISTAGG(cc.column_name, ', ')
        WITHIN GROUP (ORDER BY cc.position) ||
    ') ONLINE TABLESPACE idx_tbs;' AS create_index_ddl
FROM
    dba_constraints c
    JOIN dba_cons_columns cc
        ON c.constraint_name = cc.constraint_name
        AND c.owner = cc.owner
WHERE
    c.constraint_type = 'R'
    AND c.status = 'ENABLED'
    AND c.owner = 'HR'  -- Target schema
    -- Add the NOT EXISTS subquery from diagnostic query #3
GROUP BY
    c.owner, c.constraint_name, c.table_name
ORDER BY
    c.table_name;

Step 4: Move DDL to Maintenance Windows

Implement a policy enforced in deployment pipelines: no DDL involving ALTER TABLE, DROP, TRUNCATE, or CREATE INDEX (non-online) during business hours. Use Oracle Scheduler for timed maintenance operations.

Step 5: Replace Table-Level LOCKs with Row-Level Locking

Review application code for explicit LOCK TABLE statements. Replace with SELECT ... FOR UPDATE or SELECT ... FOR UPDATE SKIP LOCKED for row-level locking that does not block concurrent DML on unrelated rows.

Prevention & Tuning

Index every foreign key: Make this a development standard enforced by code review and automated checks. The diagnostic query above should return zero rows in a well-configured schema.

Use ONLINE DDL operations: CREATE INDEX ... ONLINE, online table redefinition, and Oracle 23ai’s lockless ADD COLUMN minimize TM lock contention during schema changes.

Automate foreign key index checking: Include the unindexed FK check in your daily DBA health check script. Alert on any unindexed FK added by developers before it causes production contention.

-- Scheduled health check: alert on new unindexed FKs
-- Run via DBMS_SCHEDULER nightly, alert if result count > 0
SELECT COUNT(*) FROM dba_constraints c
WHERE c.constraint_type = 'R'
  AND c.status = 'ENABLED'
  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_position = 1
        AND ic.column_name IN (
            SELECT cc.column_name FROM dba_cons_columns cc
            WHERE cc.constraint_name = c.constraint_name
        )
  );

Related Wait Events

• enq: TX - row lock contention — Row-level transaction lock; a session is waiting for another session to commit or rollback a specific row
• enq: TX - allocate ITL entry — Insufficient ITL (Interested Transaction List) slots in a block header; need to increase INITRANS
• enq: TT - contention — Tablespace lock contention; can co-occur with DDL operations
• library cache lock — DDL also acquires library cache locks alongside TM enqueues
• row cache lock — Dictionary cache lock; may co-occur during DDL execution