Transactions
BeginTx groups statements so they all take effect or none do; commit on success, roll back on every other path.
tx, err := db.BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)Some changes only make sense together. Taking money off one account and adding it to another must either both happen or neither happen. A transaction is how you ask the database for that: statements inside it are invisible to everyone else until you commit, and a rollback throws all of them away.
Begin, commit, roll back
db.BeginTx(ctx, opts) returns a *sql.Tx. Pass nil for the options to accept the database's default behaviour, or a *sql.TxOptions to set ReadOnly or an isolation level such as sql.LevelSerializable. A Tx is bound to exactly one connection taken out of the pool, and it keeps that connection until it ends. A transaction that is never committed or rolled back never gives the connection back.
So the first line after a successful BeginTx is defer tx.Rollback(). Every early return then ends the transaction. After a successful Commit the deferred rollback does nothing: it returns sql.ErrTxDone, which is why its error is ignored here and nowhere else.
Use tx, not db
Every statement inside the transaction goes through tx.ExecContext or tx.QueryRowContext. A call on db takes a different connection and runs outside the transaction, so it cannot see the uncommitted changes and will not be rolled back with them.
Keep them short
An open transaction holds a pool connection and often holds row locks. Do no HTTP calls, no file reads and no waiting on a queue inside one. Gather what you need first, open the transaction, run the statements, commit.
Shorthand
db.Begin() is exactly db.BeginTx(context.Background(), nil).
Example
package main
import (
"context"
"database/sql"
"fmt"
"log"
_ "modernc.org/sqlite"
)
// transfer moves money between two accounts, or changes nothing at all.
func transfer(ctx context.Context, db *sql.DB, from, to, cents int) error {
tx, err := db.BeginTx(ctx, nil) // nil options: the database's default isolation level
if err != nil {
return err
}
defer tx.Rollback() // ends the transaction on any early return; a no-op after Commit
// Both statements go through tx, never through db.
if _, err := tx.ExecContext(ctx, "UPDATE accounts SET cents = cents - ? WHERE id = ?", cents, from); err != nil {
return err
}
if _, err := tx.ExecContext(ctx, "UPDATE accounts SET cents = cents + ? WHERE id = ?", cents, to); err != nil {
return err
}
return tx.Commit()
}
func main() {
db, err := sql.Open("sqlite", ":memory:")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.SetMaxOpenConns(1) // one connection, so every statement sees the same in-memory database
if _, err := db.Exec("CREATE TABLE accounts (id INTEGER PRIMARY KEY, cents INTEGER)"); err != nil {
log.Fatal(err)
}
if _, err := db.Exec("INSERT INTO accounts VALUES (1, 1000), (2, 0)"); err != nil {
log.Fatal(err)
}
if err := transfer(context.Background(), db, 1, 2, 250); err != nil {
log.Fatal(err)
}
var a, b int
q := "SELECT (SELECT cents FROM accounts WHERE id = 1), (SELECT cents FROM accounts WHERE id = 2)"
if err := db.QueryRow(q).Scan(&a, &b); err != nil {
log.Fatal(err)
}
fmt.Println("account 1:", a)
fmt.Println("account 2:", b)
}
// Output:
// account 1: 750
// account 2: 250When to use it
- Move a value between two rows so a crash can never leave the pair half updated.
- Insert an order and its line items as a single unit of work.
- Delete a user and everything that references them, or nothing if any step fails.
More examples
One helper, every transaction
Begin, defer rollback and commit are the same four lines every time, so write them once. The closure cannot forget to commit, and a reviewer only has to check the body.
package storage
import (
"context"
"database/sql"
"fmt"
)
// WithTx runs fn inside a transaction. fn gets the tx and must use only the tx.
func WithTx(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return fmt.Errorf("begin: %w", err)
}
defer tx.Rollback()
if err := fn(tx); err != nil {
return err // the deferred Rollback undoes everything fn did
}
if err := tx.Commit(); err != nil {
return fmt.Errorf("commit: %w", err)
}
return nil
}
func (s *Store) Rename(ctx context.Context, id int64, name string) error {
return WithTx(ctx, s.db, func(tx *sql.Tx) error {
_, err := tx.ExecContext(ctx, "UPDATE users SET name = $1 WHERE id = $2", name, id)
return err
})
}A read-only transaction at a stricter isolation level
Transactions are not only for writes. When two reads must describe the same moment, a read-only transaction gives you that, and ReadOnly makes an accidental write fail in the database rather than in review. Not every driver supports every isolation level; an unsupported one is reported by BeginTx.
package storage
import (
"context"
"database/sql"
)
// Both counts come from one consistent view of the data, even if rows change
// between the two queries.
func (s *Store) Totals(ctx context.Context) (users, orders int, err error) {
opts := &sql.TxOptions{
Isolation: sql.LevelRepeatableRead, // the two reads agree with each other
ReadOnly: true, // the database rejects any write here
}
tx, err := s.db.BeginTx(ctx, opts)
if err != nil {
return 0, 0, err
}
defer tx.Rollback() // nothing to commit; rollback is the normal ending
if err := tx.QueryRowContext(ctx, "SELECT count(*) FROM users").Scan(&users); err != nil {
return 0, 0, err
}
if err := tx.QueryRowContext(ctx, "SELECT count(*) FROM orders").Scan(&orders); err != nil {
return 0, 0, err
}
return users, orders, nil
}Reporting a failed commit honestly
Use this when a rollback failure matters, for instance because it signals a dropped connection. It also shows the other reason to use a transaction: deciding partway through that nothing should happen, and getting that for free.
package storage
import (
"context"
"database/sql"
"errors"
"fmt"
"log/slog"
)
func (s *Store) DeleteUser(ctx context.Context, id int64) error {
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer func() {
// ErrTxDone just means Commit already ran. Anything else is worth a log line.
if err := tx.Rollback(); err != nil && !errors.Is(err, sql.ErrTxDone) {
slog.Error("rollback failed", "error", err)
}
}()
if _, err := tx.ExecContext(ctx, "DELETE FROM sessions WHERE user_id = $1", id); err != nil {
return fmt.Errorf("delete sessions: %w", err)
}
res, err := tx.ExecContext(ctx, "DELETE FROM users WHERE id = $1", id)
if err != nil {
return fmt.Errorf("delete user: %w", err)
}
n, err := res.RowsAffected()
if err != nil {
return err
}
if n == 0 {
return ErrNotFound // the deferred Rollback discards the session deletes too
}
return tx.Commit()
}
Discussion