Lesson 07

Transactions for atomic workflows

Wrap multi-step writes in a transaction so related changes succeed or fail together.

transactions
atomicity
writes

Good Code

approve-review.sql

BEGIN;

UPDATE code_reviews
SET status = 'approved',
    approved_at = now()
WHERE id = $1
  AND status = 'submitted';

INSERT INTO review_events (review_id, event_type, created_at)
VALUES ($1, 'approved', now());

COMMIT;

Bad Code

approve-review.sql

UPDATE code_reviews
SET status = 'approved',
    approved_at = now()
WHERE id = $1;

INSERT INTO review_events (review_id, event_type, created_at)
VALUES ($1, 'approved', now());

Review Notes

What to review

Good Code

The good version treats the status change and audit event as one unit of work.

Bad Code

The bad version can approve a review without recording the event if the second statement fails. It also approves without checking the expected starting state.

Takeaways

If one business action writes multiple rows, review where the transaction begins and ends.