Transactions, conditional changes and rollback evidence

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

Transactions, conditional changes and rollback evidence

Concept and contract

Verify an atomic unit by inspecting intermediate state and the state after rollback.

A transaction groups database changes under an atomic boundary. This local exercise intentionally rolls back a conditional status change. The worked script changes active role 1 to archived, reads that intermediate state and then rolls back before checking the original state again.

The expected evidence has two checkpoints: inside the transaction active is zero, and after rollback active is one. Returning only the final row would not prove the change ever occurred, while returning only the intermediate row would not prove recovery.

Mechanism and worked example

BEGIN starts the unit, UPDATE applies a predicate-bounded change and ROLLBACK discards it.

BEGIN starts the unit, UPDATE applies a predicate-bounded change and ROLLBACK discards it. The conditional active=1 clause makes the intended source state explicit. The identity predicate prevents an accidental table-wide change in this teaching example.

Atomicity does not mean every concurrency problem is solved. Isolation levels, conflicting writers and retry policies differ between engines. The embedded PostgreSQL runner here uses one connection, so it does not claim to rehearse a two-connection deadlock or a production recovery procedure.

Complete worked example

sql

BEGIN;
UPDATE roles SET active=0 WHERE id=1 AND active=1;
SELECT 'inside' AS checkpoint,id,active FROM roles WHERE id=1;
ROLLBACK;
SELECT 'after' AS checkpoint,id,active FROM roles WHERE id=1;

Reasoning: Both intermediate change and final restoration are observed.

Counterexamples and common mistakes

The counterexample commits instead of rolling back.

The counterexample commits instead of rolling back. Its after checkpoint remains archived, violating the exercise's promise to restore the original active state. The script parses and changes exactly one row, so a check focused only on successful execution would miss the wrong final state.

Another mistake omits the identity predicate and alters every active role. Always verify affected membership and postconditions. Constraints help protect data invariants, but application intent and the chosen transaction boundary still require explicit review and testing.

Counterexample: a plausible but wrong query

sql

BEGIN;
UPDATE roles SET active=0 WHERE id=1 AND active=1;
SELECT 'inside' AS checkpoint,id,active FROM roles WHERE id=1;
COMMIT;
SELECT 'after' AS checkpoint,id,active FROM roles WHERE id=1;

Reasoning: COMMIT preserves the change and violates this rollback contract.

Independent practice: small query

Perform the same reversible status change for role 2.

Perform the same reversible status change for role 2. Verify zero inside the transaction and one after rollback, preserving the identity in both checkpoint rows. This small task changes the target identity without adding concurrency or a new transaction mechanism.

Use only the disposable companion fixture. State why the post-rollback assertion is necessary and why a success message from UPDATE is insufficient evidence. A separate real multi-connection lab would be needed to establish isolation or lock-wait behavior.

Step 1: Target role 2 with its expected active state. Step 2: Read inside, roll back and read again.

Practice starter: solve before reading the solution

sql

BEGIN;
-- Change only active role 2 and observe it.
ROLLBACK;
-- Check role 2 after restoration.

Reasoning: Keep the conditional predicate and change the target identity only.

Hint 1: Keep the identity predicate on both UPDATE and checkpoint reads.
Hint 2: The final active value should be one.

Solution, tests and trade-offs

The solution returns inside, 2, 0 and after, 2, 1.

The solution returns inside, 2, 0 and after, 2, 1. Both engines execute the transaction and restoration checks on fresh local databases. No production database, vacancy, user record or backup is changed by this teaching verification.

A stronger extension can add another related write and prove both are rolled back together. Keep that extension optional until the two-checkpoint core works. Do not confuse a database rollback with reversal of an external email or payment side effect outside the transaction.

Practice solution and acceptance checks

sql

BEGIN;
UPDATE roles SET active=0 WHERE id=2 AND active=1;
SELECT 'inside' AS checkpoint,id,active FROM roles WHERE id=2;
ROLLBACK;
SELECT 'after' AS checkpoint,id,active FROM roles WHERE id=2;

Reasoning: Role 2 is archived inside and active again after rollback.

Interview reasoning and next step

In interviews, separate atomicity, consistency constraints, isolation and durability rather than using transaction as a universal correctness answer.

In interviews, separate atomicity, consistency constraints, isolation and durability rather than using transaction as a universal correctness answer. Explain the actual unit of change and show a failure or rollback path with observable postconditions.

The final capstone returns to read-only analytics and combines a deterministic per-company selection with empty-company retention. It requires the same explicit population and identity reasoning that makes a bounded update understandable.

Optional stretch — after the core task works: Add a second related local write and prove both changes are discarded by the same rollback.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Verify an atomic unit by inspecting intermediate state and the state after rollback.