SQL Transactions

Begin, do the work, commit or roll back

A transaction turns several statements into one all-or-nothing unit. db.BeginTx checks out a connection and pins it; you run every statement through the returned *sql.Tx; then exactly one of Commit or Rollback ends it.

graph LR
B["BeginTx(ctx)"] --> S1["tx.Exec · debit"]
S1 --> S2["tx.Exec · credit"]
S2 --> Q{"all ok?"}
Q -->|yes| C["Commit"]
Q -->|no| R["Rollback"]
C --> DONE["changes persisted"]
R --> UNDO["changes discarded"]

The defer-rollback safety pattern

The idiomatic shape defers Rollback immediately, so every early exit is covered, and only commits on the happy path:

func transfer(ctx context.Context, db *sql.DB, from, to string, cents int) error {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	// Safety net: runs on every return below. After a successful Commit
	// it's a harmless no-op (returns sql.ErrTxDone), so we ignore its error.
	defer tx.Rollback()

	// Every statement goes through tx, NOT db — same pinned connection.
	if _, err := tx.ExecContext(ctx,
		"UPDATE accounts SET balance = balance - $1 WHERE id = $2", cents, from); err != nil {
		return err // defer rolls back
	}
	if _, err := tx.ExecContext(ctx,
		"UPDATE accounts SET balance = balance + $1 WHERE id = $2", cents, to); err != nil {
		return err // defer rolls back
	}

	// Commit is the only thing that persists the work.
	return tx.Commit()
}

Why this works: once Commit succeeds the transaction is over, so the deferred Rollback does nothing. But if any statement fails — or the function panics, or a validation check returns early — the defer fires and the half-finished work is discarded. You never leak an open transaction (which would hold a pool connection hostage).

ACID and isolation levels

A transaction’s guarantees are summarized as ACID: Atomicity (all statements apply or none do), Consistency (the database moves from one valid state to another, honoring constraints), Isolation (concurrent transactions don’t see each other’s partial work), and Durability (once committed, the data survives a crash). Atomicity is what Rollback gives you; durability is what Commit promises.

Isolation is tunable. BeginTx takes a *sql.TxOptions where you set the level — from LevelReadCommitted (you only see committed data, the common default) up to LevelSerializable (transactions behave as if run one at a time). Higher isolation prevents more anomalies (dirty reads, non-repeatable reads, phantom reads) but increases contention and the chance of serialization errors you must retry:

tx, err := db.BeginTx(ctx, &sql.TxOptions{
	Isolation: sql.LevelSerializable,
	ReadOnly:  false,
})

See also

• database/sql — the pool, queries, and scanning that transactions build on.
• Postgres & Redis — drivers and datastore choices.
• graceful shutdown — context cancellation rolls in-flight transactions back.
• context — the cancellation BeginTx honors.

Next: picking real datastores — Postgres & Redis.

Related topics