Transactions on Postgres with Golang

A database transaction groups multiple operations into a single unit of work. Either all operations succeed, or none of them do. Without transactions, a failure halfway through a multi-step operation can leave your data in an inconsistent state.

Consider transferring money between two bank accounts. You need to debit one account and credit another. If the debit succeeds but the credit fails (maybe the server crashes), you have lost money. Transactions prevent this.

Why Transactions Matter

Transactions provide four guarantees, known as ACID:

• Atomicity - All operations succeed or all fail. No partial updates.
• Consistency - The database moves from one valid state to another. Constraints are never violated.
• Isolation - Concurrent transactions do not interfere with each other.
• Durability - Once committed, the data is persisted even if the server crashes.

In Go, the database/sql standard library provides built-in transaction support.

Basic Transaction Pattern

The fundamental flow is: Begin, execute queries, then Commit or Rollback.

func transferFunds(db *sql.DB, fromID, toID int, amount float64) error {
    // Start the transaction
    tx, err := db.Begin()
    if err != nil {
        return fmt.Errorf("begin transaction: %w", err)
    }

    // Debit the sender
    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
        amount, fromID,
    )
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("debit account: %w", err)
    }

    // Credit the receiver
    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
        amount, toID,
    )
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("credit account: %w", err)
    }

    // Commit the transaction
    if err := tx.Commit(); err != nil {
        return fmt.Errorf("commit transaction: %w", err)
    }

    return nil
}

If any query fails, we call Rollback() to undo all changes made within the transaction. Only when everything succeeds do we call Commit().

Using Defer for Safe Rollback

The pattern above works, but calling Rollback() at every error point is repetitive and error-prone. A cleaner approach uses defer:

func transferFunds(db *sql.DB, fromID, toID int, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    // Rollback is a no-op if the tx has already been committed
    defer tx.Rollback()

    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
        amount, fromID,
    )
    if err != nil {
        return err
    }

    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
        amount, toID,
    )
    if err != nil {
        return err
    }

    return tx.Commit()
}

The key insight: calling Rollback() on a transaction that has already been committed is a harmless no-op. So we defer the rollback at the start, and it either runs as a cleanup on error or does nothing if commit already succeeded.

Extracting a Transaction Helper

If you have many functions that need transactions, extract the pattern into a helper:

func withTransaction(db *sql.DB, fn func(tx *sql.Tx) error) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    if err := fn(tx); err != nil {
        return err
    }

    return tx.Commit()
}

// Usage
func transferFunds(db *sql.DB, fromID, toID int, amount float64) error {
    return withTransaction(db, func(tx *sql.Tx) error {
        _, err := tx.Exec(
            "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
            amount, fromID,
        )
        if err != nil {
            return err
        }

        _, err = tx.Exec(
            "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
            amount, toID,
        )
        return err
    })
}

This helper encapsulates the Begin/Commit/Rollback lifecycle. Your business logic only needs to worry about the queries themselves.

Context-Aware Transactions

In production, you should use BeginTx with a context for timeout and cancellation support:

func transferFunds(ctx context.Context, db *sql.DB, fromID, toID int, amount float64) error {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable,
    })
    if err != nil {
        return err
    }
    defer tx.Rollback()

    _, err = tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
        amount, fromID,
    )
    if err != nil {
        return err
    }

    _, err = tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
        amount, toID,
    )
    if err != nil {
        return err
    }

    return tx.Commit()
}

The TxOptions struct lets you set the isolation level. For most operations, the default (Read Committed) is fine. For operations where you need strict ordering guarantees, use LevelSerializable.

Common Mistakes

• Forgetting to rollback - An uncommitted, non-rolled-back transaction holds a database connection open and can cause connection pool exhaustion. Always defer the rollback.
• Using db instead of tx - Once you start a transaction, all queries must go through the tx object. Using db.Exec() instead of tx.Exec() runs the query outside the transaction.
• Long-running transactions - Keep transactions short. Do all your validation and data preparation before calling Begin(), not inside the transaction.
• Ignoring Commit errors - The commit can fail. Always check the error returned by tx.Commit().

Transactions are one of those things that seem simple but have subtle gotchas. The defer-rollback pattern and the transaction helper function handle most of the complexity. Use them consistently, and you will avoid the common pitfalls.