Experiments and implementations · Intermediate

Observing MVCC snapshots across two sessions

Control updates across two sessions in one disposable local database to compare statement snapshots with transaction snapshots and their limits.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Define the guarantee and disposable environment
  2. Prepare one table shared by two connections
  3. Read twice under Read Committed
  4. Preserve ordering under Repeatable Read
  5. Distinguish BEGIN from snapshot establishment
  6. Identify what stable reads do not establish
  7. Finish transactions and preserve reproducibility
  8. Ask Logeia about this articleCheck your understanding

Define the guarantee and disposable environment

This experiment compares when changes from another PostgreSQL transaction become visible under MVCC. It uses ordinary SELECT statements reading one value twice, separating stable reads from the stronger claim that an entire business workflow is serializable. Prepare a disposable local database and two psql sessions connected to that same database. Production tables and personal data are unnecessary. The values 100 and 120 below are expected outcomes for the specified ordering, not a report of measurements from an actual production system. Record the server version and label the sessions A and B. Two terminals connected to different databases do not share the row whose visibility the procedure is intended to examine.

Prepare one table shared by two connections

Check the database and user in both connections and record the relevant connection context. Initialize the test only once, before either experimental transaction begins, by creating the dedicated lab_balance table and inserting id 1 with amount 100. If the name conflicts with existing material, use a dedicated disposable database or a different lab-only name. With psql autocommit disabled, commit setup explicitly so the other connection can see it. A successful command display is not equivalent to publication of its effects. Throughout the procedure, B’s isolated UPDATE must either run with autocommit enabled or be followed by an explicit COMMIT. Keep that condition consistent across both trials.

Read twice under Read Committed

For the first trial, A begins a Read Committed transaction and reads 100. Leave that transaction open while B changes the amount to 120 and completes its commit. Only after that completion should A execute the same SELECT again; the expected value is 120. Ordinary Read Committed SELECT statements obtain a new snapshot at each statement’s start. Launching the update and second read concurrently would leave their relative ordering ambiguous and weaken the intended comparison. Verify commit completion through the deliberate sequence instead of assuming a chosen sleep duration is enough. Commit A after its second read before resetting the value for the next trial.

sql
-- Only in a disposable LOCAL PostgreSQL database.
-- Setup, before opening the two test transactions:
CREATE TABLE lab_balance(id integer PRIMARY KEY, amount integer NOT NULL);
INSERT INTO lab_balance VALUES (1,100);

-- Session A, first run:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT amount FROM lab_balance WHERE id=1; -- 100

-- Session B:
UPDATE lab_balance SET amount=120 WHERE id=1; -- autocommit

-- Session A:
SELECT amount FROM lab_balance WHERE id=1; -- 120
COMMIT;

-- Reset outside A's transaction:
UPDATE lab_balance SET amount=100 WHERE id=1;

-- Session A, second run:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT amount FROM lab_balance WHERE id=1; -- 100

-- Session B:
UPDATE lab_balance SET amount=120 WHERE id=1; -- autocommit

-- Session A:
SELECT amount FROM lab_balance WHERE id=1; -- 100
COMMIT;
SELECT amount FROM lab_balance WHERE id=1; -- 120
-- Cleanup only the disposable lab table after both sessions finish:
DROP TABLE lab_balance;

Preserve ordering under Repeatable Read

Before the second trial, restore 100 and commit that reset. A begins at Repeatable Read and establishes its snapshot by reading 100. B then changes the row to 120 and commits. A’s next ordinary read is expected to remain 100 because it uses the established snapshot. That result does not indicate failure of B’s commit. After A commits, a new statement should see 120. Do not mix A’s own updates or SELECT FOR UPDATE into this first comparison: they introduce additional visibility and conflict rules and form a different experiment. Check B’s successful commit separately from A’s returned value so that an older visible version is not mistaken for lost durable data.

Distinguish BEGIN from snapshot establishment

In PostgreSQL Repeatable Read, do not memorize BEGIN as the instant at which the relevant snapshot must always be fixed. The first appropriate statement matters. Consider an additional ordering in which A sends BEGIN but has not yet read the row, B commits 120, and A then executes its first SELECT. The initial visible value can now be 120, unlike the earlier trial that established a snapshot at 100. Extra diagnostic statements can also affect the sequence if they acquire a snapshot. Record BEGIN, the first read, B’s commit and the later read as distinct events. A vague note saying that A started first loses the timing information needed to explain the result.

Identify what stable reads do not establish

Repeatedly seeing the same row does not establish that Repeatable Read prevents every serialization anomaly. A write-skew example involving two on-call staff reading each other’s status and updating separate rows differs from direct write conflict on one row. Distinguish PostgreSQL’s Repeatable Read guarantee from Serializable and decide how the application will enforce its invariant. A separate trial with competing updates may also need whole-transaction retry handling. Long-lived snapshots can delay reclamation of older row versions, which is an operational effect beyond the returned value. This small two-read experiment demonstrates one visibility distinction; it does not prove all concurrency properties required by an application or eliminate the need to reason about its update protocol.

Finish transactions and preserve reproducibility

At the end, confirm that neither session retains an unfinished transaction, then remove only the dedicated lab table. After an error, roll back rather than continuing ordinary SQL in a failed transaction, and restart from the documented initial conditions. Preserve version, isolation level, autocommit setting, event order, expected values and actual outputs as separate evidence. If an observation differs, first inspect B’s commit, database identity, reset state and any additional statements. The valuable outcome is an explanation of which version each reader can see at each step. That reasoning helps distinguish an uncommitted or failed update from a correctly committed change that is intentionally absent from an older snapshot.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path