LEARN. PRACTICE. GROW.Describe the resource before changing the transaction
The lab represents fictional batch-processing capacity bounded between zero and one hundred units. FREE_UNITS is NUMBER RESERVABLE, ID identifies the row and NOTE is an ordinary column. Two sessions use the same disposable schema. A reserves consumption of 35 and B reserves consumption of 45 before either commits. Both proceed to subsequent observations: the base column still shows 100, while each journal queried by its session shows its own reservation. This matters to L3 support seeing “100 free” in a query but receiving rejection when consuming another 25. There is no contradiction: the additional request must respect commitments already admitted. An isolated read is not a promise of future capacity.
Trace the change in the journal and committed value
After the two reservations, 20 remains for further consumption. B’s additional request for 25 is rejected with ORA-02290. A creates a savepoint and reserves another 10; its quantity moves from 35 to 45. ROLLBACK TO restores 35, retaining the reservation preceding the savepoint. When B commits, the base column becomes 55. A reads 55 despite still holding its reservation of 35. After A commits, the value is 20. Keep a timeline with session, statement, visible journal and base value. Without ordering, a report can confuse reservation with an already applied update or attribute commitment of A’s work to B.
Do not fund consumption with a pending increase
With 20 committed, B reserves an increase of 40. Before B commits, A attempts to consume 30 and receives another CHECK violation. Another transaction’s increase can still roll back; admission does not depend on it. After B commits, the value becomes 60 and A’s reservation of 30 succeeds. A’s rollback releases that reservation and leaves 60. In a subsequent step, A reserves 15 and B reserves 20: A rolls back and B commits, leaving 40. These cases help design independent request cancellations. Do not generalize the conclusion to Sagas or messages sent to external systems. The lab exercised only local transactions, without distributed-compensation integration.
Distinguish statement restrictions and resource waits
The direct assignment SET free_units=25 returned ORA-55746 in the experiment. Consumption intent should use the supported delta form and identify the row correctly. Mixing the reservable column and ordinary NOTE in one statement returned ORA-55735. Separate statements and review atomicity and locking across the complete flow. In another step B updates NOTE and retains its row lock; A obtains a five-unit reservation. A commits only after B rolls back, leaving 35. This demonstrates reservation acquisition in that condition without proving nonwaiting commit while B retained the lock. During incident analysis, associate errors with their statement and phase rather than treating every failure as a timeout.
Prepare maintenance without manipulating the journal
With a reservation pending, DELETE returned ORA-55754 and the row remained. The workflow resolved transactions before subsequent maintenance. Conversion to NOT RESERVABLE removed the last reservable column’s journal but retained the enabled CHECK. Assigning -1 still failed; a valid assignment to 31 committed. Removing the feature therefore does not remove table-defined business bounds. Oracle manages the journal; do not repair it through manual DML. Before an actual change, inventory transactions, dependencies and rollback requirements. Both runs removed the created schema; the dedicated container and network were also removed, retaining the image for other exercises.
Reproduce and report a conclusion bounded by evidence
The SQL below organizes the core exercise by sessions A and B. Use only an authorized disposable schema with a bounded quota. First predict values 100, 55 and 20 and explain why another 25 is rejected. Identify the created journal through metadata without writing directly to it, and compare visibility in both sessions. Add savepoint, rollback and pending replenishment following the described sequence. The runner retained in content/labs/oracle-reservation-journal reproduced two runs of 42 checks in Oracle Free 26ai. It did not measure throughput, lock duration at commit, crash recovery or Saga compensation. Submit a timeline and an eligibility decision for a fictional resource; a production recommendation requires testing actual dependencies.
-- Authorized disposable Oracle Free 26ai schema only.
-- Session A: setup, then reserve. Session B uses the same schema in a separate connection.
CREATE TABLE batch_capacity (
id NUMBER PRIMARY KEY,
free_units NUMBER RESERVABLE CONSTRAINT dr_capacity_bounds CHECK(free_units BETWEEN 0 AND 100),
note VARCHAR2(30));
INSERT INTO batch_capacity VALUES(7,100,'synthetic capacity');
COMMIT;
-- A
UPDATE batch_capacity SET free_units=free_units-35 WHERE id=7;
SELECT free_units FROM batch_capacity WHERE id=7;
-- B, without committing A
UPDATE batch_capacity SET free_units=free_units-45 WHERE id=7;
-- B: expected CHECK violation, capacity already reserved by A and B
UPDATE batch_capacity SET free_units=free_units-25 WHERE id=7;
-- Each session can locate and query its own reservation journal; do not modify it.
SELECT table_name FROM user_tables WHERE table_name LIKE 'SYS_RESERVJRNL_%'
-- A
SAVEPOINT before_extra;
UPDATE batch_capacity SET free_units=free_units-10 WHERE id=7;
ROLLBACK TO before_extra;
-- B
COMMIT;
SELECT free_units FROM batch_capacity WHERE id=7; -- 55
-- A
SELECT free_units FROM batch_capacity WHERE id=7; -- 55
COMMIT;
SELECT free_units FROM batch_capacity WHERE id=7; -- 20
-- Resolve both sessions before removing the disposable exercise objects.100 committed, reservations of 35 and 45: SELECT still shows 100, but another consumption of 25 is rejected.
Common pitfalls
Confusing base value with available capacity; spending pending increases; modifying the journal manually; treating lock-free as a guarantee of nonwaiting commit.
Related topics: Vector search and model migration · Visibility and transaction boundaries · True Cache, reservations, and audit evidence
Reservation admits a bounded delta; commit applies it and rollback releases it. Record each phase and respect the evidence scope.
Reference: Using Lock-Free Reservation · 1Z0-183 public objectives inspected 2026-09-30; revision date not published