Oracle Database / Intermediate: application SQL and evidence / Lesson 12

Locking, isolation and undo

Protect capacity invariants and distinguish normal waiting, deadlocks and missing historical versions.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Use conditional writes and explicit locking with bounded expectations.
Explain deadlock recovery and undo-dependent consistent reads.

Protect the condition at the write boundary

Two sessions can both read free_units = 2 and independently decide to reserve two units. A later unconditional overwrite based on the stale value can lose a reservation or oversell capacity. A conditional decrement makes the test and change one SQL operation, while the caller inspects the affected-row count. A CHECK constraint requiring nonnegative capacity is another defensive boundary, but it does not define the user-facing reservation outcome. Combine the business transition, row-count contract and transaction scope. Do not rely on a UI disabling its button to serialize requests from different clients.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

UPDATE capacity
SET free_units = free_units - :requested_units
WHERE slot_id = :slot_id
  AND :requested_units > 0
  AND free_units >= :requested_units;

The application accepts only exactly one affected row and commits it with the related booking. Bound parameters have reviewed numeric types.

Lock only while making the decision

SELECT ... FOR UPDATE locks selected rows for a subsequent controlled modification. Keep that transaction short and avoid waiting for a person or a remote HTTP service while holding it. NOWAIT can report an unavailable lock immediately, while WAIT bounds a wait by seconds in the documented syntax. SKIP LOCKED has specialized queue-like uses and can omit busy rows; it must not be used casually when a report promises all eligible work. Locking options are a contract about behavior under contention, not a general speed switch. The code illustrates a single known capacity row only.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT free_units
FROM capacity
WHERE slot_id = :slot_id
FOR UPDATE WAIT 3;

In an authorized disposable lab, this would wait up to the specified limit for conflicting row locks. No lock or timing was actually exercised here.

A consistent statement is not a frozen transaction

READ COMMITTED supplies statement-level read consistency, so a second ordinary SELECT can see another transaction’s newly committed work. SERIALIZABLE and read-only transactions provide stronger transaction-level snapshot behavior with their own restrictions. A serializable writer may receive ORA-08177 when a conflicting change prevents the expected serialization. Retrying requires restarting the complete business transaction with fresh decisions, not merely resending the last statement against stale application state. Isolation choice should follow the invariant and retry strategy rather than a belief that the strongest-sounding name fixes every concurrency defect.

A deadlock is a cycle rather than ordinary waiting

If transaction A holds capacity row 7 and needs row 8 while transaction B holds row 8 and needs row 7, neither can proceed. Oracle detects a deadlock and reports ORA-00060 to a participant. Review the documented rollback scope and explicitly terminate or retry the application unit rather than assuming every earlier statement has been undone. A consistent resource acquisition order reduces this class of cycle. Preserve the approved diagnostic trace and SQL context; killing arbitrary sessions or increasing timeouts does not remove a contradictory acquisition protocol.

Consistent reads depend on available old versions

Undo supports rollback and reconstruction of older committed block images for consistent reads. A long-running query can need history that is no longer available and fail with ORA-01555. That diagnosis requires query duration, concurrent modification patterns and approved undo evidence; it is not proof that every query needs a larger fixed retention setting. Reducing avoidable work or changing batch design can be more appropriate than a blanket storage prescription. A read-only reporting transaction also consumes historical-version requirements. Plan long extracts with a defined consistency point, duration budget and restart policy instead of assuming that “read-only” means operationally free.

Common errors and fixes

ORA-00054

Why it happens: A NOWAIT resource request encountered conflicting use.
How to fix it: Identify the owning business transaction and apply the documented wait or retry policy.
Prevent it next time: Keep lock-holding work short and make contention a normal API outcome.

ORA-00060

Why it happens: Transactions acquired resources in an order that forms a waiting cycle.
How to fix it: Roll back the intended application unit and inspect the acquisition sequence.
Prevent it next time: Use a consistent ordering for multirow business changes.

ORA-01555

Why it happens: The query cannot reconstruct a required older version from available undo.
How to fix it: Review query duration, modification rate and authorized undo evidence together.
Prevent it next time: Budget long extracts and validate a restartable reporting design.

Check your understanding

1. Why is reading capacity and later writing an absolute replacement unsafe under concurrent reservations?

Show worked solution

Both sessions may base their replacement on the same old value. Use a conditional atomic decrement and inspect its affected-row count, or lock the relevant row within a short reviewed transaction. Include booking and capacity in one unit and define a rejected-reservation response rather than silently accepting zero rows.

2. Two workers need slot 7 and slot 8. How can a shared ordering help?