Day 097 — Transactions & Context

Month 4 · Week 2 · ⬅ Day 096 · Day 098 ➡ · Journal index

🎯 Learning Objective

Run multi-statement work atomically with BeginTx, guarantee cleanup with the deferred-Rollback pattern, wire transactions through context, and pick a sane isolation level.

📚 Topics

• db.BeginTx(ctx, opts), tx.Commit, tx.Rollback; the WithTx helper
• Context cancellation rolling back a transaction; sql.TxOptions
• Isolation levels and serialization-failure retries; sharing a Tx across repos

📖 Reading / Sources

• database/sql — Tx, BeginTx, TxOptions
• pgx — transactions & pgx.BeginFunc
• Postgres — Transaction Isolation
• Go blog — Defer, Panic, and Recover

📝 Notes

• A transaction makes several statements atomic: all commit together or none do. You tx := db.BeginTx(ctx, opts), run statements on tx (not db — the pool would pick other connections), then tx.Commit() → [[transactions]].
• The deferred-Rollback pattern is the rule to memorise: defer tx.Rollback() right after BeginTx. After a successful Commit, Rollback is a no-op (returns sql.ErrTxDone), so an early return, an error, or a panic can never leave a transaction dangling → [[defer]]. I built this stdlib-only in examples/month-04/txn.
• Wrap it in a WithTx(ctx, db, func(tx) error) helper so callers can't forget the rollback. Commit only when fn returns nil; otherwise the deferred rollback fires.
• Context is the kill switch. BeginTx takes ctx; if it's cancelled or its deadline passes, the driver rolls the transaction back and frees the connection. Never start a transaction with context.Background() in a request path — propagate the request's context → [[context-propagation]].
• sql.TxOptions sets Isolation and ReadOnly. Postgres defaults to Read Committed. Stronger levels (Repeatable Read, Serializable) prevent more anomalies but can fail with a serialization error (40001) — those are expected and you retry the whole transaction.
• Keep transactions short. Never hold one open across user input or a slow network call — it pins a connection and holds locks, starving everyone else → [[connection-pooling]].
• Sharing a Tx across repositories solves yesterday's leak: have repo methods accept a Querier/DBTX (satisfied by both the pool and a Tx). WithTx passes the Tx in, so UserRepo and OrderRepo enlist in one atomic unit — a lightweight Unit of Work.
• Don't ignore Commit's error. The write isn't durable until Commit returns nil; a deferred-only rollback with an unchecked commit can silently lose data.

💻 Code Examples

The WithTx helper and atomic Commit/Rollback semantics (including rollback-on-panic and context-checked commit) are runnable: examples/month-04/txn · Run: go run ./examples/month-04/txn.

// WithTx runs fn in a transaction: commit on success, rollback on any error or
// panic. The deferred Rollback is a no-op once Commit has succeeded.
func WithTx(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
    if err != nil {
        return err
    }
    defer tx.Rollback() // safe no-op after a successful Commit

    if err := fn(tx); err != nil {
        return err // deferred Rollback undoes everything
    }
    return tx.Commit() // MUST check this error — data isn't durable until it's nil
}

// Atomic transfer: both updates land, or neither does.
func transfer(ctx context.Context, db *sql.DB, from, to int, cents int64) error {
    return WithTx(ctx, db, func(tx *sql.Tx) error {
        if _, err := tx.ExecContext(ctx,
            `UPDATE accounts SET balance = balance - $1 WHERE id = $2`, cents, from); err != nil {
            return err
        }
        _, err := tx.ExecContext(ctx,
            `UPDATE accounts SET balance = balance + $1 WHERE id = $2`, cents, to)
        return err
    })
}

🏋️ Exercises / Practice

Exercise	Status	Link
Transactional store: Begin/Commit/Rollback, WithTx, rollback-on-panic	✅	exercises/month-04/week-2/txstore
Transaction semantics demo (atomicity, cancelled-context commit)	✅	examples/month-04/txn

🐛 Mistakes Made

• Ran statements on db instead of tx inside a "transaction" — they each grabbed a different pooled connection and weren't atomic at all.
• Returned after an error without rolling back; the connection leaked until the pool deadlocked. The defer tx.Rollback() fixes this once and for all.
• Ignored Commit's error and "lost" a write under a constraint violation. Always check it.

❓ Open Questions

• A clean, generic retry wrapper for 40001 serialization failures — how many attempts, and how to back off without holding locks?

🧠 Active Recall (answer without looking)

1. Q: Why is defer tx.Rollback() safe even when the transaction commits successfully?

A

After a successful `Commit`, the transaction is already finished, so `Rollback` is a no-op returning `sql.ErrTxDone`. The defer guarantees cleanup on every other path (error, panic, early return).

1. Q: What happens to a transaction if the context passed to BeginTx is cancelled?

A

The driver rolls the transaction back and releases the connection. That's why you propagate the request context, never `context.Background()`, into transactional work.

🪶 Feynman Reflection

A transaction is a "save point with an undo button". You open it, make a batch of changes on that one connection, and either press Commit (everything sticks) or Rollback (everything vanishes). The deferred Rollback is a dead-man's switch: if anything goes wrong — error, panic, timeout — the undo fires automatically, so you can never leave the database half-changed.

🕳️ Knowledge Gaps

• Practical serialization-failure retry loops and how isolation level choice changes throughput.

✅ Summary

I can run atomic multi-statement work with BeginTx, guarantee cleanup with deferred Rollback inside a WithTx helper, propagate context to cancel/rollback, check Commit, and reason about isolation levels.

⏭️ Next Steps / Prep for Tomorrow

• Day 098: week review + closed-book recall of the data-access stack.

---

Time spent	Difficulty	Confidence
90 min	🟦🟦🟦⬜⬜	🟦🟦🟦⬜⬜

Suggested commit: docs(journal): transactions and context (day 097)