enq: TX - row lock contention - Find Blocking Sessions

enq: TX - row lock contention

Overview

Wait Event Class: Application (or Concurrency for ITL variant)

Parameters: mode (lock mode requested), id1 (undo segment number in high 16 bits, slot in low 16 bits), id2 (undo sequence number — the transaction ID)

enq: TX - row lock contention fires when one session attempts to modify a row (or acquire a row-level lock) that is already locked by another session’s uncommitted transaction. The waiting session is blocked until:

1. The holding session commits or rolls back (releasing the lock)
2. The holding session is killed by a DBA
3. A deadlock is detected and one session is automatically rolled back

How Oracle Row Locking Works

Oracle uses row-level locking with no lock escalation. When a session modifies a row, Oracle:

1. Sets a lock byte in the row itself (the lock byte in the row header)
2. Records the transaction ID in the row’s ITL (Interested Transaction List) entry in the block header
3. Creates an entry in the undo segment to record the old value

No central lock manager table is used for row locks — the lock is embedded in the block. This is why Oracle row locking is extremely scalable for non-conflicting access patterns, but contention between sessions on the same rows causes these waits.

Mode Variations

The mode parameter identifies the specific type of TX lock:

• Mode 6 (Exclusive): Normal row lock — session 2 wants to modify a row locked by session 1
• Mode 4 (Share): ITL contention — block ran out of ITL slots (see Root Cause #4)
• Mode 4 (Share) — bitmap index: Bitmap index contention (multiple sessions updating rows sharing a bitmap index entry)

---

When This Wait Is a Problem

Normal vs. Concerning

Scenario	Assessment
Occasional short waits (< 1 second) during concurrent updates	Normal — low contention OLTP
Waits > 5 seconds appearing regularly in AWR	Investigate — application or schema design issue
One session blocking many others (blocking tree)	Problem — long-running transaction or stuck session
Waits persisting for minutes	Critical — hung session, deadlock risk, application bug
High enq: TX in AWR top events	Architectural issue — data model or transaction design flaw

Deadlock Scenario

If two sessions each hold a lock the other needs, Oracle detects the deadlock within a few seconds and raises ORA-00060: deadlock detected on one session, rolling back that session’s last statement (not the entire transaction). The other session then proceeds. Repeated deadlocks indicate application logic issues.

---

Diagnostic Queries

1. Find Blocking Sessions Right Now

-- Current blocking session chain
SELECT
    w.sid                                 AS waiting_sid,
    w.serial#                             AS waiting_serial,
    ws.username                           AS waiting_user,
    ws.program                            AS waiting_program,
    ws.sql_id                             AS waiting_sql,
    b.sid                                 AS blocking_sid,
    b.serial#                             AS blocking_serial,
    bs.username                           AS blocking_user,
    bs.program                            AS blocking_program,
    bs.sql_id                             AS blocking_sql,
    bs.last_call_et                       AS blocking_secs_active,
    sw.event                              AS wait_event,
    sw.seconds_in_wait                    AS waiting_secs
FROM v$session       w
JOIN v$session_wait  sw ON w.sid         = sw.sid
JOIN v$session       ws ON w.sid         = ws.sid
JOIN v$session       b  ON w.blocking_session = b.sid
JOIN v$session       bs ON b.sid         = bs.sid
WHERE w.blocking_session IS NOT NULL
  AND sw.event LIKE 'enq: TX%'
ORDER BY sw.seconds_in_wait DESC;

2. Full Lock Waiter/Holder Analysis with SQL Text

-- Comprehensive lock analysis including SQL text
SELECT
    lk.type                               AS lock_type,
    DECODE(lk.lmode,
        0, 'None', 1, 'Null', 2, 'Row-S(SS)', 3, 'Row-X(SX)',
        4, 'Share', 5, 'S/Row-X(SSX)', 6, 'Exclusive', lk.lmode)
                                          AS hold_mode,
    DECODE(lk.request,
        0, 'None', 1, 'Null', 2, 'Row-S(SS)', 3, 'Row-X(SX)',
        4, 'Share', 5, 'S/Row-X(SSX)', 6, 'Exclusive', lk.request)
                                          AS request_mode,
    lk.sid,
    s.serial#,
    s.username,
    s.program,
    s.machine,
    s.status,
    s.last_call_et                        AS secs_in_current_state,
    SUBSTR(sq.sql_text, 1, 100)           AS current_sql,
    lk.id1,
    lk.id2
FROM v$lock   lk
JOIN v$session s  ON lk.sid    = s.sid
LEFT JOIN v$sql  sq ON s.sql_id = sq.sql_id
WHERE lk.type = 'TX'
  AND (lk.lmode > 0 OR lk.request > 0)
ORDER BY lk.id1, lk.id2, lk.lmode DESC;

3. Identify the Locked Row and Object

-- Find which table rows are locked and by whom
SELECT
    lo.oracle_username,
    lo.os_user_name,
    lo.process,
    lo.locked_mode,
    do.object_name,
    do.object_type,
    do.owner,
    s.sid,
    s.serial#,
    s.last_call_et    AS lock_held_secs
FROM v$locked_object  lo
JOIN dba_objects       do ON lo.object_id  = do.object_id
JOIN v$session         s  ON lo.session_id = s.sid
ORDER BY s.last_call_et DESC;

4. ASH History of Lock Waits

-- Historical row lock contention — identify recurring blockers (last 24 hours)
-- Requires Diagnostics Pack license
SELECT
    ash.blocking_session,
    bs.username                           AS blocker_username,
    bs.program                            AS blocker_program,
    ash.sql_id                            AS waiting_sql,
    COUNT(*)                              AS ash_samples,
    COUNT(*) * 10                         AS estimated_wait_secs,
    MIN(ash.sample_time)                  AS first_seen,
    MAX(ash.sample_time)                  AS last_seen
FROM v$active_session_history  ash
JOIN v$session                  bs ON ash.blocking_session = bs.sid
WHERE ash.event         LIKE 'enq: TX%'
  AND ash.sample_time   > SYSDATE - 1
GROUP BY ash.blocking_session, bs.username, bs.program, ash.sql_id
ORDER BY ash_samples DESC
FETCH FIRST 20 ROWS ONLY;

5. Deadlock Detection — Alert Log Check

-- Check for recent ORA-00060 deadlock errors (from V$DIAG_ALERT_EXT or alert log)
-- Find deadlock trace files
SELECT
    originating_timestamp,
    message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-00060%'
   OR message_text LIKE '%deadlock%'
ORDER BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

---

Root Causes

1. Long-Running Transactions Holding Row Locks

The most common cause. A session modifies rows and does not commit, holding the lock for an extended period. Other sessions needing those rows must wait. Common scenarios:

• Interactive transactions: A user opens a form, modifies a record, and leaves for lunch without committing
• Batch jobs without intermediate commits: A long-running UPDATE that locks thousands of rows for the entire duration
• Application bug: Missing COMMIT after DML, or exception handler that doesn’t ROLLBACK properly
• Stuck sessions: A session hung waiting for an external resource (API call, file I/O) while holding a lock

Detection: V$SESSION.LAST_CALL_ET for the blocking session will be large; V$TRANSACTION.START_TIME will show when the transaction began.

2. Application Design — Pessimistic Locking

Some applications use explicit SELECT ... FOR UPDATE to lock rows before displaying them to users, then wait for user input before committing. If the user is slow, the lock is held for the entire think time. In multi-user systems, this is catastrophic for concurrency.

Better pattern: Use optimistic locking — read without locking, then check for modifications at update time using a version column or checksum.

3. Update Hotspot — Multiple Sessions Updating the Same Rows

When the application design causes many concurrent sessions to update the same rows simultaneously (e.g., a running total counter, a sequence-like column updated by application code, a status field that many processes set), contention is architectural and requires redesign.

Examples:

• Multiple sessions all updating WHERE status = 'PENDING' and setting it to ‘PROCESSING’ — each session tries to grab the same rows
• Application-managed sequences: SELECT MAX(id)+1 FROM orders followed by INSERT — hotspot on the table

4. ITL (Interested Transaction List) Contention — Mode 4

When enq: TX appears with mode 4, it is not a row lock conflict — it is an ITL slot shortage. The ITL in a data block stores one entry per concurrent transaction modifying rows in that block. If the ITL fills up (controlled by INITRANS on the table/index), subsequent sessions must wait for an existing transaction to commit before their ITL slot can be reused.

This is separate from row locking — sessions may be updating different rows in the same block, but the block’s ITL is full.

Indicators: V$WAITSTAT showing waits on data block or index block; INITRANS set to 1 (the default minimum).

5. Bitmap Index Contention

Bitmap indexes compress many row values into a single index entry covering a range of rows. When multiple sessions concurrently update rows covered by the same bitmap index entry, they must serialize on that index entry. This makes bitmap indexes fundamentally unsuitable for tables with concurrent DML — they are designed for read-heavy data warehouses.

Detection: The locked object in V$LOCKED_OBJECT is an index (not a table); the index is a bitmap type.

---

Resolution Steps

Kill a Blocking Session

-- Identify the session to kill (use queries from Diagnostic Queries section)
-- Get the blocking session's SID and SERIAL#

-- Kill the blocking session
ALTER SYSTEM KILL SESSION '&blocking_sid,&blocking_serial#' IMMEDIATE;

-- Verify the session is gone or in KILLED status
SELECT sid, serial#, status, username, last_call_et
FROM v$session
WHERE sid = &blocking_sid;

-- If status remains 'KILLED' for a long time, it is rolling back
-- Monitor rollback progress:
SELECT
    s.sid,
    s.serial#,
    s.username,
    t.used_ublk     AS undo_blocks_used,
    t.used_urec     AS undo_records_used
FROM v$session     s
JOIN v$transaction t ON s.taddr = t.addr
WHERE s.sid = &blocking_sid;

Fix ITL Contention — Increase INITRANS

-- Check current INITRANS setting for the hot table
SELECT table_name, ini_trans, max_trans, pct_free
FROM dba_tables
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE';

-- Increase INITRANS to support more concurrent transactions per block
-- Rule of thumb: set to maximum expected concurrent sessions modifying the same block
ALTER TABLE your_schema.your_table INITRANS 10;

-- For indexes on high-concurrency tables:
ALTER INDEX your_schema.your_index INITRANS 10;

-- Note: Change only affects new blocks. Rebuild to apply to existing blocks:
ALTER TABLE your_schema.your_table MOVE ONLINE;
ALTER INDEX your_schema.your_index REBUILD ONLINE;

Fix Application-Level Lock Hotspots

-- BAD: SELECT MAX to generate IDs (causes row lock hotspot)
SELECT MAX(order_id) + 1 INTO v_new_id FROM orders;
INSERT INTO orders (order_id, ...) VALUES (v_new_id, ...);

-- GOOD: Use a proper Oracle SEQUENCE (no locking)
CREATE SEQUENCE orders_seq START WITH 1 INCREMENT BY 1 NOCACHE;
-- Or with CACHE for high-volume:
CREATE SEQUENCE orders_seq START WITH 1 INCREMENT BY 1 CACHE 100;

INSERT INTO orders (order_id, ...) VALUES (orders_seq.NEXTVAL, ...);

Fix Pessimistic Locking — Switch to Optimistic Locking

-- Add a version/timestamp column to detect concurrent modifications
ALTER TABLE accounts ADD (last_modified TIMESTAMP DEFAULT SYSTIMESTAMP);

-- Application reads without locking:
SELECT account_id, balance, last_modified
INTO v_id, v_balance, v_ts
FROM accounts
WHERE account_id = :acct_id;
-- (No FOR UPDATE — no lock held during user think time)

-- Application updates with optimistic lock check:
UPDATE accounts
SET    balance       = :new_balance,
       last_modified = SYSTIMESTAMP
WHERE  account_id    = :acct_id
  AND  last_modified = :original_ts;  -- Fails if someone else modified it

IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Record modified by another user. Please refresh.');
END IF;
COMMIT;

Investigate and Resolve Deadlocks

-- After a deadlock, find the trace file for details
SELECT
    value AS trace_dir
FROM v$diag_info
WHERE name = 'Diag Trace';

-- Deadlock graph is written to: <trace_dir>/orcl_ora_<pid>.trc
-- Also check: SELECT * FROM v$diag_alert_ext WHERE message_text LIKE '%deadlock%';

-- Typical deadlock resolution:
-- 1. Identify the two SQL statements involved from the trace file
-- 2. Ensure both acquire locks in the same order
-- 3. If updating multiple tables, always update Table A before Table B in all code paths
-- 4. Consider using SELECT ... FOR UPDATE OF ... ORDER BY to force consistent lock ordering

---

Prevention & Tuning

1. Keep Transactions Short

The golden rule for minimizing lock contention: do the work as quickly as possible and commit immediately. Design transactions to:

• Minimize rows locked at any given time
• Avoid user interaction inside a transaction
• Not hold locks while calling external services (APIs, file writes)

2. Implement Lock Timeout Handling in Applications

-- Use DBMS_LOCK or NOWAIT to avoid indefinite blocking
-- SKIP LOCKED (12c+) for queue-like patterns:
SELECT order_id, status
FROM order_queue
WHERE status = 'PENDING'
ORDER BY created_date
FETCH FIRST 10 ROWS ONLY
FOR UPDATE SKIP LOCKED;
-- This skips locked rows rather than waiting, enabling parallel workers

3. Monitor Blocking Sessions with Proactive Alerting

-- Script to identify long-running blocking sessions (use in monitoring framework)
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.last_call_et AS lock_held_secs,
    COUNT(w.sid)   AS sessions_blocked
FROM v$session  s
JOIN v$session  w ON w.blocking_session = s.sid
WHERE s.blocking_session IS NULL  -- s is the root blocker
GROUP BY s.sid, s.serial#, s.username, s.program, s.last_call_et
HAVING s.last_call_et > 60  -- Blocking for more than 1 minute
ORDER BY sessions_blocked DESC, lock_held_secs DESC;

4. Use ASSM Tablespaces to Reduce ITL Issues

Automatic Segment Space Management (ASSM) tablespaces allow Oracle to dynamically manage ITL slots more efficiently than manual segment space management. All new tablespaces should use ASSM:

-- Create tablespace with ASSM (default in modern Oracle)
CREATE TABLESPACE app_data
    DATAFILE '/data/app_data01.dbf' SIZE 10G AUTOEXTEND ON
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;  -- ASSM

---

Related Wait Events

• buffer busy waits — Block-level contention, often co-occurs with ITL (TX mode 4) contention
• enq: TM - contention — Table-level lock contention (DDL vs DML conflicts)
• latch free — Latch-level serialization, a different form of concurrency contention
• log file sync — Commit performance affects how quickly lock holders release their locks