Transactions and Repository Shape

A transfer endpoint debits one account and credits another. The debit succeeds, the credit fails because the receiving row violates a constraint, and the handler returns 500. The customer's money is gone, the retry will take it again, and the support ticket says the dashboard is wrong. Nothing in the handler was written carelessly. It simply ran two statements with no transaction, which is the default state of a database connection.

A transaction is the boundary that says all of these statements happen or none of them do, and in Go it is a small piece of API with a few decisions attached: what context controls it, what to do with the rollback error, which isolation level to ask for, and where the boundary lives in your package structure. Those decisions are what this lesson is about.

BeginTx takes the request context, and that context controls the transaction

The older db.Begin() still works and takes no context. In a service, use BeginTx and pass the context from the request, because that context carries the client's deadline and the client's disconnection into the database. The documentation is specific about what happens next: the context is used until the transaction is committed or rolled back, the package rolls the transaction back if the context is cancelled, and Commit returns an error if the context was cancelled first.

tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return fmt.Errorf("begin: %w", err)
}
defer tx.Rollback()

// ... statements on tx, all with the same ctx

return tx.Commit()

The second argument is where isolation and read-only live, and nil asks for the server's default. That is the right answer far more often than people expect, and the next section shows why the explicit choices deserve scepticism.

defer tx.Rollback() is correct, and the ignored error is the point

The defer looks like a bug the first time you read it, because the function commits on the line before it returns and then calls Rollback after a successful commit. It is not a bug. After Commit or Rollback, the transaction is done, and every later operation on it fails with sql.ErrTxDone; the rollback is a no-op that costs a mutex and a boolean. What the defer buys is the path that skips the commit: an early return, a failed statement, a panic unwinding through the function. Each of those leaves the transaction open and the connection checked out until the deferred rollback runs.

Two consequences follow. Do not return tx.Rollback()'s error from a function that already has a better error, and do not write the ignore as _ = tx.Rollback() without thinking. Check it only if you have somewhere useful to put it. errcheck flags the bare call, and this is one of the cases where the idiomatic answer is to make the ignore deliberate rather than to handle an error that carries no information.

The statement that keeps a transaction honest is commit-once. A transaction has exactly one outcome, so any code path that reaches two calls to Commit is a design error, and any code path that both commits and continues issuing statements is a bug the driver reports as ErrTxDone rather than silently accepting. When a helper function needs to be part of the transaction, pass the *sql.Tx in and let the outermost function commit.

A transfer that rolls back is visible in the rows

The repository is a struct holding the pool, and the transaction lives inside one method. The interface is small and the method names describe business operations rather than SQL:

type Accounts interface {
	Transfer(ctx context.Context, from, to int64, amount int, idem string) error
	Balance(ctx context.Context, id int64) (int, error)
}

type MySQLAccounts struct{ db *sql.DB }

func (m *MySQLAccounts) Transfer(ctx context.Context, from, to int64, amount int, idem string) error {
	tx, err := m.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin: %w", err)
	}
	defer tx.Rollback()

	var balance int
	if err := tx.QueryRowContext(ctx,
		"SELECT balance FROM accounts WHERE id = ? FOR UPDATE", from).Scan(&balance); err != nil {
		return fmt.Errorf("read balance: %w", err)
	}
	if balance < amount {
		return fmt.Errorf("account %d: %w", from, errInsufficientFunds)
	}

	if _, err := tx.ExecContext(ctx,
		"UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, from); err != nil {
		return fmt.Errorf("debit: %w", err)
	}
	if _, err := tx.ExecContext(ctx,
		"UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, to); err != nil {
		return fmt.Errorf("credit: %w", err)
	}
	if _, err := tx.ExecContext(ctx,
		"INSERT INTO ledger (from_id, to_id, amount, created_at, idem_key) VALUES (?, ?, ?, NOW(), ?)",
		from, to, amount, idem); err != nil {
		return fmt.Errorf("ledger: %w", err)
	}

	return tx.Commit()
}

The ledger insert carries an idempotency key with a unique index on it, so the same transfer submitted twice cannot be recorded twice. That is the second call in the transcript below, and it fails on the insert after both balances have already been changed inside the transaction:

before:
  account 1 ana    balance 100
  account 2 bo     balance 20
  ledger rows: 0

Transfer(1 -> 2, 25, txn-0001) = nil
after the first transfer:
  account 1 ana    balance 75
  account 2 bo     balance 45
  ledger rows: 1

Transfer(1 -> 2, 25, txn-0001) retry = ledger: Error 1062 (23000): Duplicate entry 'txn-0001' for key 'ledger.uq_ledger_idem'
after the retry that hits the unique key:
  account 1 ana    balance 75
  account 2 bo     balance 45
  ledger rows: 1

The retry moved 25 from the sender, added 25 to the receiver, and then failed. The final state is 75 and 45 with one ledger row, which is the state before the retry. Without the transaction, those balances would read 50 and 70 with one ledger row and no way to tell which transfer had been recorded. Two things are worth noticing: the error message names the index, which is why naming constraints rather than letting MySQL choose is worth the keystrokes, and the whole failure is one line in the log plus a database that never moved.

The FOR UPDATE on the balance read is the other half of getting this right. Reading the balance without it and then updating leaves a window where a concurrent transfer passes the funds check on the same stale value, and both of them proceed. The row lock makes the read and the update one decision.

Go defines eight isolation levels; your driver supports four

sql.TxOptions takes an Isolation value, and database/sql defines eight constants from LevelDefault to LevelLinearizable. That list is the union of what every database has ever called these levels, not a menu you can order from. The check happens inside the driver's BeginTx, and the MySQL driver in this lab maps exactly four:

LevelDefault           ok, server reports REPEATABLE-READ
LevelReadCommitted     ok, server reports REPEATABLE-READ
LevelRepeatableRead    ok, server reports REPEATABLE-READ
LevelSerializable      ok, server reports REPEATABLE-READ
LevelReadUncommitted   ok, server reports REPEATABLE-READ
LevelSnapshot          BeginTx error: mysql: unsupported isolation level: 5
LevelLinearizable      BeginTx error: mysql: unsupported isolation level: 7
LevelWriteCommitted    BeginTx error: mysql: unsupported isolation level: 3
pool after all the failures: {0 1 0 1 0 0s 0 0 0}
Ping still fine

Three of the eight fail before a statement runs, with the level's number in the message. The pool is untouched by those failures, which is worth knowing: a rejected BeginTx returns the connection to the pool, so a configuration that asks for LevelSnapshot on MySQL fails every request cleanly instead of poisoning connections.

The more interesting part is that the session variable still reads REPEATABLE-READ for the four levels that are supported. MySQL's driver issues SET TRANSACTION ISOLATION LEVEL before starting the transaction, and @@transaction_isolation is the session default rather than a live view of the transaction in progress. So the constant you pass is a request, and the only way to know the request was honoured is to test the behaviour. Reading the same row twice with a commit in between does exactly that:

read-committed: session default before BeginTx is REPEATABLE-READ
read-committed: other connection committed balance = 250
read-committed: first read 100, second read 250 -> different value (non-repeatable read)
repeatable-read: session default before BeginTx is REPEATABLE-READ
repeatable-read: first read 100, second read 100 -> same value (repeatable)

Same code, same server, two different answers, and the session variable read identically in both runs. If you set an isolation level because a particular read pattern matters, write that pattern down as a test; the constant and the log line will not tell you which one you got. LevelSerializable is the other side of the same coin: it made the second connection's UPDATE block until it hit the fifty-second lock wait timeout, because serializable reads take shared locks. An isolation level is a concurrency decision with a cost, not a safety switch.

A repository interface earns its keep only where you substitute the store

The reason to put an interface in front of a store is that something else implements it. A test that runs against SQLite, an in-memory fake for handler tests, or a second backend during a migration are all good reasons, and the interface above exists for the first of those. What does not pay for itself is an interface that mirrors the store one method at a time and is implemented in exactly one place, because the extra indirection buys nothing and the SQL type still leaks through the method signatures the moment a query needs a transaction.

The tell is the method set. Transfer is one business operation that happens to need four statements, and it hides the transaction correctly: callers cannot start a transaction that spans two calls, because they never see a *sql.Tx. An interface with GetAccount, UpdateAccount and InsertLedger pushes the transaction question back to every caller and cannot answer it, since the methods each get their own connection. If a fake for that interface passes your tests, it is because the fake is not modelling the one thing that is hard.

Two tests are worth writing against the real store rather than a fake. The first drives a transfer that must fail and asserts the balances are unchanged, which is the behaviour in the transcript above. The second fires two concurrent transfers through the real store, under the race detector, and asserts the balances still add up. The second is the one that has a reason to exist: an in-memory fake will happily pass a test suite that never exercises FOR UPDATE.

A cancelled request cancels the query, and the connection does not come back

Context propagation in a transaction is not decoration. When the client disconnects, the request context is cancelled, the driver notices, and the statement running on the connection is interrupted. What happens to the connection afterwards is the part worth knowing:

after 501ms the query returned: context deadline exceeded
errors.Is(err, context.DeadlineExceeded) = true
now the request context is already done; every further statement fails immediately
second statement on the same tx: context deadline exceeded (in 501ms)
tx.Rollback() = invalid connection

The query was interrupted about a millisecond after its 500 millisecond deadline, so the work stopped rather than running to completion. The next statement on the same transaction failed without reaching the server, because the context was already done. database/sql had rolled the transaction back on its own when the context was cancelled, and the driver had marked the connection unusable because it could not know how much of the response had arrived. That is why the deferred tx.Rollback() returned invalid connection instead of ErrTxDone: the connection was discarded rather than returned to the pool. Checking that error would tell you nothing you could act on, and the pool is smaller by one connection, which it will replace on demand.

Two habits keep this useful. Always pass the request context into every ExecContext, QueryContext and BeginTx, rather than reaching for the non-context versions. And give the request a deadline somewhere, because a transaction with no deadline holds row locks for as long as the client is willing to wait, and the client that broke is not the one paying for the lock.

Show the solution and the reasoning

The third print matches the second because the transaction never committed. The unique index on the idempotency key is the last statement, so the debit and the credit have already been written inside the transaction when it fails; the rollback discards both, and the balances return to 75 and 45 with the ledger row from the first call intact. That ordering is deliberate in the exercise. A constraint checked at the first statement proves nothing about the other three, and the point of a transaction is the work that happens before the failure.

Deleting the deferred rollback is the demonstration that the defer is not ceremony. The failing call returns its error with the transaction still open and its connection checked out. The next request that needs a connection waits for one; with SetMaxOpenConns(1) it waits forever, and with a larger pool it waits until it has leaked one connection per failure, which takes as many failures as the pool has connections. db.Stats() shows InUse climbing and never falling, and the database's own counters show nothing wrong at all.

With the deadline in place, the sleeping query returns context deadline exceeded at the deadline, errors.Is matches it, and the deferred rollback returns invalid connection rather than ErrTxDone, because the driver discarded the connection when it interrupted the statement. The pool replaces it. If you want to see the difference in a live service, watch OpenConnections after a burst of cancelled requests: it dips and recovers, which is the pool doing its job rather than a problem to fix.

On the interface question, there is no single right answer and the trade-off is worth stating plainly. An interface with Transfer and Balance lets a handler test run without a database and keeps the transaction inside the implementation. A concrete struct would do the same job with less code until the first fake is needed. The version that is always wrong is the one whose methods each map to one statement, because it advertises composability it cannot deliver: two calls on that interface are two transactions, and any invariant that spans them is yours to maintain by hand.