Transactions pin connections: isolation levels from Go, and retrying 40001 and 40P01

One transaction, one pinned connection

Everything else in this lesson hangs off one mechanical fact: BeginTx checks a single connection out of the pool and pins it until Commit or Rollback. Every statement on the *sql.Tx rides that one connection — which is what makes the transaction see its own uncommitted writes, and what makes the pool poorer by one for the transaction’s entire lifespan. The Hook’s arithmetic follows directly: a 20-connection pool where each transaction encloses 9 seconds of HTTP yields a hard ceiling of about 2.2 transactions per second for the whole process, with every unrelated query queueing behind them. The rule that prevents it: between Begin and Commit, only database statements and cheap CPU — no HTTP calls, no Kafka publishes, no file I/O. If a workflow needs external calls plus atomicity, it becomes a state machine: commit a pending row, make the call, then a second short transaction to finalize.

The canonical shape:

func transfer(ctx context.Context, db *sql.DB, from, to int64, amt int64) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback() // after a successful Commit this returns sql.ErrTxDone — a deliberate no-op

	if _, err := tx.ExecContext(ctx,
		`UPDATE accounts SET balance = balance - $1 WHERE id = $2`, amt, from); err != nil {
		return err // any early return: the defer rolls back and frees the pinned connection
	}
	if _, err := tx.ExecContext(ctx,
		`UPDATE accounts SET balance = balance + $1 WHERE id = $2`, amt, to); err != nil {
		return err
	}
	return tx.Commit()
}

defer tx.Rollback() immediately after BeginTx is the pattern worth internalizing. Rollback after a successful Commit is not a bug — the Tx is already resolved, so it returns sql.ErrTxDone and does nothing. That idempotence is the design: one deferred line covers every early return and every panic between Begin and Commit, and the happy path pays nothing for it. The alternative — tracking a committed flag, or rolling back manually on each error branch — is exactly the code that forgets a branch and leaks a pinned connection plus an open server-side transaction holding locks.

Isolation: what you request vs what Postgres gives

Before you pick an isolation level, ask what concurrency bug you are actually trying to prevent — the answer tells you which level you need and what retry obligation comes with it. database/sql lets you request an isolation level portably, and the driver translates or refuses:

tx, err := db.BeginTx(ctx, &sql.TxOptions{
	Isolation: sql.LevelSerializable, // pgx issues: BEGIN ISOLATION LEVEL SERIALIZABLE
	ReadOnly:  true,                  // lets Postgres skip write machinery; a planner hint for free
})

What arrives on the other side is Postgres semantics, and they are worth knowing precisely:

• Read committed (the default). Each statement sees a fresh snapshot. Two SELECTs in one transaction can disagree; a read-modify-write done as SELECT-then-UPDATE silently loses concurrent updates. The fixes live in SQL: a single atomic UPDATE ... SET x = x + 1, or SELECT ... FOR UPDATE to lock the row first.
• Repeatable read. One snapshot for the whole transaction — reads are stable. But if a concurrent transaction commits a write to a row you then try to update, yours aborts with SQLSTATE 40001 (serialization_failure). Already at this level, retrying stops being optional.
• Serializable. Postgres runs SSI on top of snapshots, tracking read/write dependencies between concurrent transactions. It delivers true serializability — and reserves the right to abort transactions that individually did nothing wrong with the same 40001. The documentation is blunt about the contract: applications using this level must be prepared to retry transactions. Serializable without a retry loop is a latent 500 generator that fires only under concurrency, i.e. only in production.

Lock waits, deadlocks, and the retry loop

Two distinct slow-transaction pathologies get conflated. A lock wait is silent queueing: your UPDATE blocks until the transaction holding the row lock commits — no error, just latency (bound it with lock_timeout; surface it with log_lock_waits). A deadlock is a cycle: transaction A holds row 1 and wants row 2, B holds row 2 and wants row 1. Postgres detects the cycle after deadlock_timeout (1 s by default), picks a victim, and kills it with SQLSTATE 40P01 — the survivor proceeds normally. Both 40001 and 40P01 are retryable by design: the database is saying “this interleaving lost; try again”, not “this operation is wrong”. Which makes the retry helper the load-bearing pattern of this unit:

func withTx(ctx context.Context, db *sql.DB, opts *sql.TxOptions, fn func(tx *sql.Tx) error) error {
	var err error
	for attempt := 1; attempt <= 4; attempt++ {
		err = func() error {
			tx, err := db.BeginTx(ctx, opts)
			if err != nil {
				return err
			}
			defer tx.Rollback()
			if err := fn(tx); err != nil {
				return err
			}
			return tx.Commit()
		}()
		if !retryable(err) || ctx.Err() != nil {
			return err
		}
		// jittered backoff: deadlock partners retrying in lockstep collide again
		time.Sleep(time.Duration(attempt*attempt) * 25 * time.Millisecond)
	}
	return fmt.Errorf("transaction gave up after 4 attempts: %w", err)
}

func retryable(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) &&
		(pgErr.Code == "40001" || pgErr.Code == "40P01") // serialization_failure, deadlock_detected
}

Three details carry the weight. Classification goes through errors.As on the driver’s typed error and matches SQLSTATE codes — never substring-matching error text, which breaks across driver versions and locales. The whole closure re-runs, so fn must be safe to repeat: no side effects beyond the transaction itself (an email sent inside fn goes out once per attempt). And the backoff is jittered for a reason — two deadlock partners that retry on identical schedules meet each other again at the same rows.