LEARN. PRACTICE. GROW.Available reads, blocked writes
An application can query a transaction normally, but the worker trying to update it is held up. This combination is compatible with another transaction holding a row lock. In the lab A changed row 1 without committing. B could read the old value; SELECT FOR UPDATE NOWAIT on that row returned ORA-00054, accompanied by ORA-40097 in this version. The secondary diagnostic was retained in logs without inventing semantics from documentation that had not been inspected. Record the failing statement, the owning session identified through authorized tools and the functional scope. A responding read does not establish write-path availability; a waiting write does not establish total database unavailability either.
Choose a wait policy appropriate to the work
NOWAIT lets the caller receive the conflict without waiting for release of the requested lock. WAIT can give the owner time to finish; the application still needs failure handling when that limit does not resolve the conflict. SKIP LOCKED is useful when consumers can handle other eligible jobs, but changes the returned set. In the three-row exercise B obtained only identifiers 2 and 3 while A held 1 locked. An ordinary query counted all three. For a close requiring every transaction, do not replace complete selection with SKIP LOCKED merely to remove an alert. Define how omitted work will be revisited, which metrics expose backlog and who decides acceptable delay.
What remains after ROLLBACK TO
In the second part A changed row 1 to 160, created a savepoint and changed row 2 to 245. Rolling back to the savepoint retained 160 and restored 225 on row 2. B then started a new lock request on 2 and acquired it; a new request on 1 still failed. Only after A committed did the committed total reach 685. This was not a full rollback. Documentation distinguishes new requests from transactions already waiting when partial rollback occurs: those can continue waiting for transaction completion. The lab confirmed new requests; it did not measure the behavior of previously suspended waiters.
Coordinate the APS response without losing context
During a fictional overnight processing incident, the application team knows the batch and the database team identifies the lock. Connect those facts before deciding to terminate a session. Establish whether the transaction is progressing, the close deadline, which operations would be rolled back and how the application would resume work. An administrative action can require rollback and reconciliation; do not assume instant release or no impact. Communicate functional consequences: pending operations, remaining window, duplication risk and the next decision point. If an authorized retry policy exists, apply bounds and monitor outcomes, avoiding concurrent attempts that merely add pressure to the same row.
Acceptance and limits of a concurrency demonstration
Both runs passed 28 checks each, using two SQLPlus sessions and one synthetic user per run. Schemas, container and temporary network were removed; code and sanitized logs remain available for review. This evidence supports the distinctions taught but does not measure capacity, consumer fairness or incident recovery time. SKIP LOCKED semantics also do not justify promising absence of every wait: an exclusive table lock is a documented limitation. To accept an actual worker, add concurrency, retry, ordering, recovery and reconciliation exercises matching the functional contract. Record separately what was observed, what comes from documentation and what remains an operational hypothesis.
-- Synthetic session B while A holds row 1.
SELECT id FROM jobs WHERE id=1 FOR UPDATE NOWAIT;
SELECT id FROM jobs ORDER BY id FOR UPDATE SKIP LOCKED;
ROLLBACK;
-- Compare with an ordinary read, not a locking read.
SELECT COUNT(*) FROM jobsAfter rollback to the savepoint, B acquires row 2 in a new request but still cannot acquire row 1.
Common pitfalls
An empty result as an empty queue; partial rollback as transaction completion; unbounded retries; a synthetic test as capacity evidence.
Related topics: Performance diagnosis · Recovery and reconciliation · Deployments and operational acceptance
Relate each lock to its transaction and each mitigation to work that could be omitted or repeated.
Reference: SELECT · 1Z0-183 public objectives inspected 2026-09-30; revision date not published