LEARN. PRACTICE. GROW.Build a timeline before intervening
During a fictional fund close, operator A changes an amount from 100 to 150. A’s query shows 150, but colleague B still gets 100. Before declaring replication lag, identify the instance, service, user, session, query and commit status. In the lab both sessions connected to the same PDB. The change was still pending: A observed its own write and B the previously committed version. After A committed, a new B query returned 150. This sequence was repeated twice with synthetic data. Diagnosis follows the observed boundaries; it does not require restarting the database or changing parameters to force visibility.
A sequence of queries is not one snapshot
Imagine an APS report that first collects application totals and then transaction details. The business team continues posting movements between the queries. Under READ COMMITTED, using the same connection does not guarantee that both queries observe the same instant. Agree with the business whether the report needs one consistent state or the latest value for each query. For the first objective, a READ ONLY transaction can hold the query view. It must start at the appropriate transaction boundary. Do not add COMMIT to a shared connection without establishing whether pending changes belong to another functional step.
Read lab results with the right user
After the first commit, the sum was 650. A started READ ONLY as a dedicated application user. B changed another amount and committed: its sum became 675 while A continued to get 650. A ended the transaction and the next read returned 675. No update was lost and no cache clearing was needed. The documentation excludes SYS from this READ ONLY behavior, so the lab reserved SYS for creating and removing the fixture. Initial preparation encountered ORA-01466 after recent DDL. Both final runs separated fixture creation from the exercise by two seconds. That local adjustment is not a recovery recipe for real incidents.
DDL changes the recovery boundary
In a maintenance session, an operator updates a record and executes CREATE TABLE to store diagnostics. In the exercise the pending value was 165; B still saw 160 before DDL and saw 165 afterward. A later ROLLBACK retained 165. The operational conclusion is that a reversal plan must distinguish uncommitted transactions from already committed changes. The documented rule includes a commit before syntactically valid DDL even when execution fails, and another after successful DDL. If a deployment mixes DML and DDL, rehearse the boundaries and required compensation. Do not promise whole-file atomicity merely because its error handler contains ROLLBACK.
Accept the change with business evidence
At handover, provide the timeline, expected values, participating sessions and operation ending each transaction. A test finishing with a sum of 690 demonstrates the executed synthetic sequence; it does not establish correctness of every movement in a banking application. Ask the functional team for reconciliation, duplication and external-effect criteria. If a connection drops during commit, absence of a response does not prove that the database rejected the operation. Inspect authorized outcome evidence before repeating a sensitive movement. The lab did not simulate that failure, production load, RAC or distributed transactions. Retain these limitations in knowledge transfer so that a teaching example is not treated as an approved procedure.
-- Dedicated synthetic application session after fixture setup.
SET TRANSACTION READ ONLY;
SELECT SUM(amount) FROM jobs;
-- Another session changes and commits data here.
SELECT SUM(amount) FROM jobs;
COMMIT;
SELECT SUM(amount) FROM jobsA reads 650 during READ ONLY; B commits a total of 675; A observes 675 only after ending the transaction.
Common pitfalls
Using SYS to demonstrate READ ONLY; confusing a connection with a snapshot; introducing DDL into a transaction intended for rollback.
Related topics: Performance diagnosis · Recovery and reconciliation · Deployments and operational acceptance
Document who reads, in which transaction and after which commit before recommending intervention.
Reference: Data Concurrency and Consistency · 1Z0-183 public objectives inspected 2026-09-30; revision date not published