Transactions and Atomic Updates

The wallet service moves 50 cents from account A to B with two statements and no transaction:

// buggy: two statements, no transaction
var bal int64
err := db.QueryRowContext(ctx, `SELECT balance_cents FROM accounts WHERE id = $1`, from).Scan(&bal)
if bal < amount {
	return ErrInsufficientFunds
}
db.ExecContext(ctx, `UPDATE accounts SET balance_cents = balance_cents - $1 WHERE id = $2`, amount, from)
db.ExecContext(ctx, `UPDATE accounts SET balance_cents = balance_cents + $1 WHERE id = $2`, amount, to)

Run two transfers out of the same account concurrently and the interleaving is easy to draw:

T1 reads balance 100        T2 reads balance 100
T1 checks 100 >= 50  ok    T2 checks 100 >= 50  ok
T1 writes 100 - 50 = 50
T2 writes 100 - 50 = 50    <-- T2 overwrote T1's debit
result: 50 left, two withdrawals of 50 paid out

Each statement is individually correct; the sequence is not. This is the read-modify-write race, and no amount of statement-level care fixes it — the reads and writes must execute as one indivisible unit. That is what a transaction is: a boundary the database guarantees will commit all of it or none of it.

func Transfer(ctx context.Context, db *sql.DB, from, to, amount int64) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin transfer: %w", err)
	}
	defer tx.Rollback() // safety net: no-op if Commit ran

	var bal int64
	if err := tx.QueryRowContext(ctx,
		`SELECT balance_cents FROM accounts WHERE id = $1 FOR UPDATE`, from).
		Scan(&bal); err != nil {
		return fmt.Errorf("lock account %d: %w", from, err)
	}
	if bal < amount {
		return ErrInsufficientFunds
	}

	if _, err := tx.ExecContext(ctx,
		`UPDATE accounts SET balance_cents = balance_cents - $1 WHERE id = $2`, amount, from); err != nil {
		return fmt.Errorf("debit %d: %w", from, err)
	}
	if _, err := tx.ExecContext(ctx,
		`UPDATE accounts SET balance_cents = balance_cents + $1 WHERE id = $2`, amount, to); err != nil {
		return fmt.Errorf("credit %d: %w", to, err)
	}
	return tx.Commit()
}

FOR UPDATE is the piece people miss. Begin alone does not stop the race: under the default isolation level, T2's plain SELECT still reads 100 while T1 works — it only blocks when it tries to write. SELECT ... FOR UPDATE takes the row lock at read time, so T2's read waits until T1 commits, then sees 50 and returns ErrInsufficientFunds honestly. Without it you have an atomic-looking transaction that still loses updates.

A simpler alternative exists when the check can live in the database: UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 and inspect RowsAffected — zero rows means insufficient funds, and the race is gone because read and write became one statement. Keep that trick in mind; FOR UPDATE remains necessary whenever the decision needs data the statement itself cannot express.

The transfer function has five ways to return after BeginTx and only one of them should commit. A version that calls tx.Rollback() in each error branch relies on remembering every future branch too — and the first branch someone adds without it holds a connection open, pinning a pool slot until the database's idle timeout reaps it.

The idiom collapses five branches into one:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return fmt.Errorf("begin: %w", err)
}
defer tx.Rollback() // runs on EVERY return path
// ... work ...
return tx.Commit()   // only commit point

Rollback after a successful Commit returns sql.ErrTxDone — and you deliberately ignore it, because defer tx.Rollback() discards the return value anyway. The line is correct on paths where rollback already happened, paths where commit won, and every path in between. That is the whole point: one statement whose meaning is "uncommitted work does not survive," regardless of how the function exits.

Two mechanics make the lifecycle complete. First, BeginTx takes a context — db.BeginTx(ctx, nil) ties the transaction's lifetime to the caller's deadline, so a handler's 2-second budget cancels the transaction rather than letting it dawdle; pass nil only when the work is genuinely unbounded. Second, commit and rollback release server-side resources: an open transaction holds row locks and a reserved connection. A forgotten rollback does not just waste a slot — it blocks other transactions on the locked rows until something external intervenes, which in production looks like an unrelated slowdown.

Errors after work has happened need one more decision: what to return. The convention from error handling applies unchanged — wrap with context on the way out (fmt.Errorf("credit %d: %w", to, err)), never log-and-return at every layer. The caller (or the retry wrapper, next chapter) decides what the failure means; the transaction's job is to leave nothing half-applied.

What transactions do not fix: doing too much inside one. Every statement between Begin and Commit holds locks and pins the connection — so an outbound HTTP call made mid-transaction (waiting on a payment provider for 800 ms) keeps all of that for 800 ms. The rule of thumb: transactions wrap database work only. Fetch external data first, then transact, then notify after commit. Long-held transactions are how atomicity quietly becomes a pool outage — the connection you starve is the one every other request waits for (pool sizing).

Two transfer requests cross in opposite directions: T1 locks A then wants B, T2 locks B then wants A. The database detects the cycle and kills one of them with a deadlock error (SQLSTATE 40P01 in Postgres, error 1213 in MySQL). Nothing is corrupted — the victim's transaction never applied — but the caller received an error for work that should have succeeded. Under isolation levels above read-committed, 40001 serialization failures say the same thing: try again.

The critical structural insight: the retry wraps the whole transaction, not the failed statement. A dead transaction is dead — you cannot re-run the UPDATE on it. The unit that failed is the unit that retries:

func withTxRetry(ctx context.Context, attempts int, fn func(tx *sql.Tx) error) error {
	var err error
	for i := 0; i < attempts; i++ {
		if i > 0 {
			backoff := time.Duration(1<<uint(i)) * 50 * time.Millisecond // 100ms, 200ms...
			backoff += time.Duration(rand.Int63n(int64(backoff / 2)))     // + jitter
			select {
			case <-time.After(backoff):
			case <-ctx.Done():
				return ctx.Err() // budget outranks persistence
			}
		}

		err = func() error {
			tx, err := db.BeginTx(ctx, nil)
			if err != nil {
				return err
			}
			defer tx.Rollback()
			if err := fn(tx); err != nil {
				return err
			}
			return tx.Commit()
		}()

		if err == nil {
			return nil
		}
		if !isRetryableTx(err) {
			return err // constraint violation: retrying changes nothing
		}
	}
	return fmt.Errorf("transaction failed after %d attempts: %w", attempts, err)
}

Each clause guards a distinct mistake. isRetryableTx inspects the driver's error — *pgconn.PgError with code 40P01 or 40001, MySQL 1213/1205 — using errors.As, never message matching (error identity). A unique-constraint violation is permanent: retrying it forever is a bug that converts one 409 into a timeout. Backoff with jitter stops two deadlocked clients from colliding again in lockstep, and the ctx.Done() arm keeps retries inside the caller's deadline.

Retrying only helps if the work is safe to repeat — the function runs from BeginTx fresh each time, so the database sees clean attempts; but if fn also sends an email after the UPDATE, attempt two sends it twice. Keep side effects outside the retried unit or make them idempotent.

Deadlocks also invite prevention: lock rows in a consistent order across all code paths (sorted ids), keep transactions short, and prefer the single-statement conditional UPDATE when it fits. Prevention lowers the rate; the retry loop handles the remainder — and knowing which errors are which separates a resilient service from one that hammers a failing database.

The order service needs three statements to place an order: insert the order, decrement stock, record the audit row. Someone puts BeginTx in the handler and passes *sql.Tx through four layers; someone else opens a transaction inside each repository method — three transactions, and a failure at statement three leaves statements one and two committed. Both designs failed the same test: they let function boundaries decide atomicity.

The principle: the transaction boundary follows the invariant, not the code structure. The invariant "an order exists only if stock was decremented and audit written" spans three tables, so one transaction spans the three statements, living in the service function that owns the invariant — not in handlers (HTTP concerns) and not in individual repository methods (each would be its own unit).

Making that ergonomic needs one interface, because the code inside must run against either a bare *sql.DB (single statement, no transaction) or a *sql.Tx (the atomic unit):

// satisfied by both *sql.DB and *sql.Tx
type querier interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

func (r *OrderRepo) insertOrder(ctx context.Context, q querier, o Order) error {
	_, err := q.ExecContext(ctx,
		`INSERT INTO orders (id, user_id, total_cents) VALUES ($1, $2, $3)`,
		o.ID, o.UserID, o.Total)
	return err
}

// one call runs everything in a transaction;
// the same method runs single-statement paths without one
func (r *OrderRepo) PlaceOrder(ctx context.Context, o Order) error {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if err := r.insertOrder(ctx, tx, o); err != nil {
		return fmt.Errorf("insert order: %w", err)
	}
	if err := r.decrementStock(ctx, tx, o); err != nil {
		return fmt.Errorf("decrement stock: %w", err)
	}
	if err := r.recordAudit(ctx, tx, o); err != nil {
		return fmt.Errorf("audit: %w", err)
	}
	return tx.Commit()
}

Reads that need no atomicity — a product list, a profile fetch — skip BeginTx entirely and take *sql.DB directly; opening a transaction for a single SELECT only reserves a connection and adds lock bookkeeping. And note what this design does to concurrency: the three statements run back-to-back in microseconds, so lock hold time stays short and the chance of the deadlock the previous chapter retries stays low.

The questions that settle any boundary argument: what must be all-or-nothing (invariant), what must block concurrently (locks), and how long can the connection be pinned (pool). Answer those three and the transaction lands in the right function — everything else is taste. The schema those statements run against has its own consistency story, and it is the subject of schema migrations.