Transactions in Go and the Defer Rollback Pattern Explained

A transaction is how a database promises that a group of changes happens entirely or not at all. In Go the promise is easy to break by accident, because the failure paths are manual: forget to roll back and the connection is leaked with locks held, use db instead of tx by mistake and a statement runs outside the transaction. This article shows the pattern that avoids both, on Go 1.25.6, with a money transfer that is checked at every step.

The pattern

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"

	_ "modernc.org/sqlite"
)

func transfer(ctx context.Context, db *sql.DB, from, to, cents int, failAfterDebit bool) (err error) {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	// Rollback after Commit returns sql.ErrTxDone and is harmless, so this defer is safe on every path.
	defer func() {
		if rbErr := tx.Rollback(); rbErr != nil && !errors.Is(rbErr, sql.ErrTxDone) {
			err = errors.Join(err, rbErr)
		}
	}()

	if _, err = tx.ExecContext(ctx, `update acct set cents = cents - ? where id = ?`, cents, from); err != nil {
		return err
	}
	if failAfterDebit {
		return errors.New("simulated crash between debit and credit")
	}
	if _, err = tx.ExecContext(ctx, `update acct set cents = cents + ? where id = ?`, cents, to); err != nil {
		return err
	}
	return tx.Commit()
}

func balances(db *sql.DB) string {
	var a, b int
	_ = db.QueryRow(`select cents from acct where id = 1`).Scan(&a)
	_ = db.QueryRow(`select cents from acct where id = 2`).Scan(&b)
	return fmt.Sprintf("a=%d b=%d total=%d", a, b, a+b)
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("sqlite", "file:lab585?mode=memory&cache=shared")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(1)
	_, _ = db.Exec(`create table acct (id int primary key, cents int check (cents >= 0)); insert into acct values (1, 1000), (2, 0)`)
	fmt.Println("start:                ", balances(db))

	fmt.Println("good transfer err:    ", transfer(ctx, db, 1, 2, 300, false))
	fmt.Println("after:                ", balances(db))

	fmt.Println("crash mid-transfer:   ", transfer(ctx, db, 1, 2, 300, true))
	fmt.Println("after (rolled back):  ", balances(db))

	err = transfer(ctx, db, 1, 2, 5000, false)
	fmt.Printf("overdraft err:         %.45s\n", err)
	fmt.Println("after (rolled back):  ", balances(db))

	// Using the transaction after Commit.
	tx, _ := db.BeginTx(ctx, nil)
	_ = tx.Commit()
	_, err = tx.ExecContext(ctx, `select 1`)
	fmt.Println("exec after Commit:    ", err)
	fmt.Println("Rollback after Commit:", tx.Rollback())
}

go run . 2>&1

start:                 a=1000 b=0 total=1000
good transfer err:     <nil>
after:                 a=700 b=300 total=1000
crash mid-transfer:    simulated crash between debit and credit
after (rolled back):   a=700 b=300 total=1000
overdraft err:         constraint failed: CHECK constraint failed: c
after (rolled back):   a=700 b=300 total=1000
exec after Commit:     sql: transaction has already been committed or rolled back
Rollback after Commit: sql: transaction has already been committed or rolled back

The function transfer is the template. Its skeleton is:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return err
}
defer func() {
	if rbErr := tx.Rollback(); rbErr != nil && !errors.Is(rbErr, sql.ErrTxDone) {
		err = errors.Join(err, rbErr)
	}
}()

// statements through tx ...

return tx.Commit()

Four decisions are in those lines.

The defer comes right after BeginTx. From that line on, any return path ends the transaction. A return err from the first statement, a later panic, a return added by someone next year: all roll back. The alternative of writing tx.Rollback() before each return is a pattern that eventually misses one.

Rollback after Commit is harmless. Commit ends the transaction. A later Rollback returns sql.ErrTxDone and does nothing. The program checks it at the end: Rollback after Commit: sql: transaction has already been committed or rolled back. That is why the deferred rollback can be unconditional, and why the closure ignores ErrTxDone. A real rollback error, such as a lost connection, is joined onto the returned error and not lost.

Everything goes through tx. tx.ExecContext, tx.QueryRowContext. A call on db inside the function takes another connection from the pool and runs outside the transaction. It sees none of the uncommitted changes, can deadlock against the rows the transaction holds, and is not rolled back. Review transaction code for stray db. calls; a helper that accepts an interface with the two methods and is given either db or tx makes the mistake harder, as in rollback tests.

The commit error is returned. tx.Commit() can fail, for example on a serialisation failure or a deferred constraint, so its error is the function's result. Ignoring it hides data loss.

The three outcomes

The program starts with two accounts: a=1000 b=0 total=1000, and a CHECK (cents >= 0) constraint on balances.

A good transfer of 300 returns nil and the balances become a=700 b=300, total still 1000.

A crash between the debit and the credit. The function is told to fail after the debit with the error simulated crash between debit and credit. The debit had already executed inside the transaction. Because the function returned an error before Commit, the deferred Rollback undid it, and the balances read a=700 b=300 total=1000 afterwards: unchanged from before the failed attempt. This is the case transactions exist for. Without one, the debit would stay and 300 cents would vanish.

An overdraft. Transferring 5000 from an account holding 700 breaks the check constraint on the first statement. The error, constraint failed: CHECK constraint failed: c..., comes back and the balances are unchanged again. The database enforced the invariant and the transaction guaranteed nothing partial remained.

Notice how few lines of Go handle the failure. The rollback logic is not repeated at each statement.

Using the transaction after it ends

After Commit, the tx is dead. The program runs a statement on it and gets sql: transaction has already been committed or rolled back. The same error comes from using a transaction after a context cancellation: when the context passed to BeginTx is cancelled, the database/sql package rolls the transaction back by itself, and later calls return ErrTxDone or the context error. Pass a context with a deadline to BeginTx so a stuck transaction cannot hold its connection forever; see query timeouts.

Transaction options

BeginTx takes *sql.TxOptions with two fields: Isolation and ReadOnly. A read-only transaction tells the database it will not write, which allows optimisations and gives an error on an attempted write. The isolation level decides what concurrent transactions can see, and the default depends on the database: READ COMMITTED on PostgreSQL, REPEATABLE READ on MySQL. The differences and their consequences are measured in the isolation article.

Keeping transactions short

A transaction holds a connection from the pool and locks in the database until it ends. Three habits keep that cost low.

• Do the slow work outside. Call external services, render templates and compute before BeginTx or after Commit, not between statements.
• Fetch what you need first, write last. Read in the transaction only what must be consistent, and order the writes so locks are taken late.
• Batch to avoid round trips. A transaction with fifty statements one at a time is fifty round trips while locks are held.

If the transaction wraps a read-modify-write, concurrent updates to the same row can conflict. Either use an atomic SQL statement (update acct set cents = cents - $1 where id = $2 and cents >= $1), a row lock with select ... for update, or a higher isolation level with a retry loop, as described in retrying serialization failures.

A reusable helper

Most programs wrap the pattern once so call sites cannot get it wrong:

func InTx(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) (err error) {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() {
		if rbErr := tx.Rollback(); rbErr != nil && !errors.Is(rbErr, sql.ErrTxDone) {
			err = errors.Join(err, rbErr)
		}
	}()
	if err = fn(tx); err != nil {
		return err
	}
	return tx.Commit()
}

Callers pass a function and return an error; commit and rollback are handled in one place. Compose it with the retry loop for serialisable transactions. Be careful about work with side effects inside fn: if the function is retried, anything it does outside the database, such as sending an email, happens again.

Savepoints and nesting

database/sql has no nested transactions. A second BeginTx takes another connection and starts an independent transaction. Where you need partial rollback inside one transaction, use savepoints with plain SQL: tx.Exec("savepoint s1"), then rollback to savepoint s1 on failure. Both PostgreSQL and MySQL support them. Libraries that offer nested transactions emulate them with savepoints.

Checklist

1. defer the rollback immediately after BeginTx.
2. Every statement through tx.
3. Return the error from Commit.
4. A context with a deadline.
5. Nothing slow between Begin and Commit.
6. Test the failure path as well as the success path: the crash case above takes four lines.

The behaviour is specified in the sql.Tx reference, and the Go team's page on executing transactions shows the same pattern in a tutorial setting.