163: SQL - Writes and Transactions

Learning outcomes

perform safe inserts, updates, and deletes;
group related writes in transactions;
explain rollback, atomicity, and isolation practically.

Practice

Implement task creation with an audit row in one transaction. Force a failure after the task insert and prove neither row remains. Test concurrent edits and decide whether the project needs a version column or conditional update.

Transaction boundary

sql
BEGIN;
INSERT INTO tasks (id, owner_id, title, status) VALUES ($1, $2, $3, 'open');
INSERT INTO task_events (task_id, actor_id, kind) VALUES ($1, $2, 'created');
COMMIT;

If the second insert fails, issue ROLLBACK; do not return success because the first statement worked. A transaction groups writes, but authorization still belongs in the service and ownership predicate. For lost-update protection, update with WHERE id = $1 AND version = $2, then require exactly one affected row.

Test matrix

Test rollback, duplicate keys, a missing owner, two simultaneous edits, client retry after a timeout, and a deadlock/serialization retry policy. Never blindly retry non-idempotent writes without a key or transaction design.

Checkpoint

Explain why a transaction is not a substitute for authorization, and what a client should do after a serialization or deadlock retry.

References

Executable isolation-anomaly lab

Run these in two psql sessions against disposable PostgreSQL data. Use a table with id, owner_id, status, and version, and label sessions A and B. At READ COMMITTED, two reads in one transaction can see different committed snapshots (non-repeatable read), and two transactions can both read "one open task" before inserting another (write skew). The exact visibility depends on the statements and constraints, so prove it with queries rather than memorizing labels.

sql
-- Session A
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT status FROM tasks WHERE id = 1;
-- pause
SELECT status FROM tasks WHERE id = 1;
COMMIT;

-- Session B, after A's first SELECT
BEGIN;
UPDATE tasks SET status = 'done' WHERE id = 1;
COMMIT;

The second read in A can observe B's committed update. At REPEATABLE READ, A keeps one snapshot and PostgreSQL may abort a concurrent writer with a serialization failure; the application must retry the whole transaction, not only the last statement. At SERIALIZABLE, PostgreSQL detects dangerous structures and requires the same whole-transaction retry policy.

Executable lost-update protection:

sql
UPDATE tasks
SET title = $new_title, version = version + 1
WHERE id = $id AND owner_id = $owner_id AND version = $expected_version;
-- application requires row_count = 1; zero means conflict or missing/unauthorized row

Also test a deadlock by updating two rows in opposite order in two sessions. Set a bounded retry for SQLSTATE 40001 (serialization failure) and 40P01 (deadlock detected), with jitter and an idempotency key for uncertain external effects. A transaction provides atomicity and isolation, not authorization: retain the owner predicate and perform permission checks at the service boundary.

Interview questions: which anomalies does snapshot isolation prevent, why can a retry duplicate an external email, and why must a serialization retry restart from BEGIN? Test rollback, duplicate key, lock timeout, deadlock, concurrent version conflict, and a client disconnect before commit.