Deadlocks, Blocking Sessions, ORA-01555 Snapshot Too Old, Undo Sizing, and Retry Design

Learning outcomes

A ServiceHub incident shows two very different failures under the label “locking”: one request simply waits behind another transaction, while another receives ORA-00060. Separately, a long report fails with ORA-01555 even though no row is locked for reading. Correct diagnosis starts by separating wait cycles from missing undo history.

1. Blocking timeline: one waiter, no cycle

DROP TABLE sh07_deadlock_demo IF EXISTS PURGE;CREATE TABLE sh07_deadlock_demo (    work_order_id NUMBER PRIMARY KEY,    status_code   VARCHAR2(12) NOT NULL,    version_no    NUMBER DEFAULT 0 NOT NULL);INSERT INTO sh07_deadlock_demo VALUES (701,'OPEN',0);INSERT INTO sh07_deadlock_demo VALUES (702,'OPEN',0);COMMIT;

UPDATE sh07_deadlock_demoSET version_no = version_no + 1WHERE work_order_id = 701;-- Keep transaction open.

UPDATE sh07_deadlock_demoSET status_code = 'HOLD'WHERE work_order_id = 701;-- Waits. There is no cycle yet.

When Session A commits or rolls back, Session B can continue. That is ordinary blocking. Killing the blocker may be operationally necessary sometimes, but it is not the first diagnostic conclusion.

2. Deadlock timeline: create a cycle deliberately

Reset both sessions with ROLLBACK, then use two different rows. The cycle appears only when each transaction holds one row and requests the other.

UPDATE sh07_deadlock_demoSET version_no = version_no + 1WHERE work_order_id = 701;UPDATE sh07_deadlock_demoSET status_code = 'HOLD'WHERE work_order_id = 702;-- This second statement waits after Session B has locked 702.

UPDATE sh07_deadlock_demoSET version_no = version_no + 1WHERE work_order_id = 702;UPDATE sh07_deadlock_demoSET status_code = 'CLOSED'WHERE work_order_id = 701;-- One participant should receive ORA-00060 after the cycle is detected.

Oracle resolves the deadlock by rolling back one statement involved in the cycle and signaling that session. Earlier successful work in that transaction is not automatically rolled back. Production code should normally treat the transaction as suspect and explicitly roll it back unless the business protocol has a carefully proven recovery path.

3. Deadlock evidence: trace first, folklore last

Oracle's error help directs operators to the trace file. The alert log also records ORA-00060 occurrences. From the victim session, a privileged query can locate its default trace file.

SELECT name, valueFROM v$diag_infoWHERE name IN ('Default Trace File','Diag Trace');

The deadlock trace describes the resources and sessions participating in the cycle. Use it to determine lock order and SQL involved. Do not “fix” deadlocks by increasing timeouts; a cycle cannot resolve by waiting longer.

4. ORA-01555 is not a row-lock timeout

ORA-01555: snapshot too old means a reader needed undo records to reconstruct an older consistent image, but those undo records had been overwritten. The key dimensions are query age, undo generation rate, available undo space, tuned retention, and whether retention is guaranteed—not the number of row locks held by the query.

5. Diagnose undo with V$UNDOSTAT

V$UNDOSTAT provides 10-minute interval statistics. Useful columns include undo blocks consumed, transaction count, longest query duration, tuned undo retention, and the snapshot-too-old error count. Use a privileged observer because the application schema should not receive broad performance-view access solely for troubleshooting.

SELECT begin_time,       end_time,       undoblks,       txncount,       maxquerylen,       tuned_undoretention,       ssolderrcnt,       nospaceerrcntFROM v$undostatORDER BY begin_time DESCFETCH FIRST 12 ROWS ONLY;

If MAXQUERYLEN approaches or exceeds effective retention while the system generates undo heavily, the risk becomes plausible. UNDO_RETENTION is not a universal guarantee: Oracle documentation explains that under space pressure unexpired undo can be reused unless the undo tablespace uses retention guarantee, which itself trades query protection against the possibility that DML runs out of undo space.

6. Deliberately wrong fixes

Wrong fix 1: commit inside a fetch loop. Repeated commits do not make a long-running query's required snapshot younger; they can complicate cursor processing and increase undo churn. Wrong fix 2: set an enormous UNDO_RETENTION blindly. Retention depends on tablespace sizing and workload, and forcing retention can shift the failure from long queries to writers. Wrong fix 3: retry every ORA error forever. Some errors are deterministic data/logic bugs, and unbounded retries amplify load.

7. Retry design: bounded, classified, and idempotent

For transient concurrency failures that are explicitly classified as retryable, use a bounded retry count, exponential or jittered backoff, fresh transaction state, and an idempotency key or optimistic version check so replay does not duplicate business effects. For ORA-00060, roll back the failed transaction before retrying the business unit unless the design proves a narrower safe recovery.

for attempt in 1..MAX_ATTEMPTS:    begin new transaction    try:        apply business change using idempotency_key        commit        return success    except DEADLOCK_OR_RETRYABLE_LOCK_ERROR:        rollback        if attempt == MAX_ATTEMPTS: raise        sleep(backoff_with_jitter(attempt))    except OTHER_ERROR:        rollback        raise

ORA-01555 is usually not solved by immediately retrying the same long query under the same undo pressure. Diagnose query duration, undo consumption, retention, schema/query design, and workload timing first.

8. Cleanup, chapter summary, and bridge

ROLLBACK;DROP TABLE sh07_deadlock_demo PURGE;

Chapter 07 has connected DML to its concurrency machinery: transactions own locks, SCNs define read views, undo reconstructs older versions, blockers are wait dependencies, deadlocks are cycles, and ORA-01555 is a missing-history problem. No AWR/ASH/ADDM or Diagnostics/Tuning Pack is required for the mandatory labs; those licensing-sensitive tools are deferred to later performance chapters. Chapter 08 now builds on this foundation by examining how Oracle indexes change row access paths and DML cost.

Authoritative references

• Data Concurrency and Consistency — blocking, deadlock detection, and statement-level rollback
• ORA-00060 — current 26ai deadlock error guidance
• ORA-01555 — snapshot-too-old cause and action
• Managing Undo — retention tuning and undo sizing
• V$UNDOSTAT — MAXQUERYLEN, tuned retention, and ORA-01555 counters
• V$DIAG_INFO — ADR and current trace-file paths