Skip to main content

Golang Context Pitfalls: Handling Cancellation in SQL Transactions

 A sudden spike in MaxOpenConnections errors or a PostgreSQL database choking on "Idle in transaction" states is rarely a database tuning issue. In Go services, it is almost exclusively a resource leak caused by mishandling context.Context cancellation during SQL transactions.

When a request is cancelled—via client disconnect or timeout—the Go runtime stops processing the handler. However, if the database transaction (sql.Tx) is not explicitly rolled back, the underlying TCP connection remains reserved and the database session stays active, holding locks on rows and tables until the database's own timeout (often hours) kicks in.

This post analyzes why standard if err != nil handling fails in concurrent environments and provides a closure-based transaction wrapper to guarantee atomicity and connection cleanup.

The Root Cause: Connection Pooling vs. Context Lifecycle

To understand the leak, we must look at how database/sql manages resources.

  1. The Reserve: Calling db.BeginTx(ctx, opts) borrows a dedicated physical connection from the pool. This connection is pinned to the transaction object.
  2. The Detachment: While ExecContext and QueryContext are context-aware (they will stop waiting if the context dies), the transaction object itself is stateful.
  3. The Leak: If your context times out, your Go function returns. If you do not call tx.Rollback() or tx.Commit(), the connection is never returned to the pool. The Go Garbage Collector might eventually reclaim the tx struct, but relying on GC to close network sockets and release database locks is catastrophic for throughput.

Furthermore, a common anti-pattern is attempting to reuse the cancelled context to perform cleanup operations, which can fail depending on the driver implementation.

The Pitfall: Imperative Error Handling

This is the code that causes production outages. It looks correct during code review but fails under load or high-latency scenarios.

// BAD PATTERN: Fragile and prone to leaks
func UpdateUserBalance(ctx context.Context, db *sql.DB, userID int, amount int64) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }

    // Danger 1: If logic panics here, tx is never closed.
    
    // Step 1: Lock row
    _, err = tx.ExecContext(ctx, "SELECT * FROM wallets WHERE user_id = $1 FOR UPDATE", userID)
    if err != nil {
        // Danger 2: Manual rollback. If we forget this in one branch, we leak.
        tx.Rollback() 
        return err
    }

    // Step 2: Update
    _, err = tx.ExecContext(ctx, "UPDATE wallets SET balance = balance + $1 WHERE user_id = $2", amount, userID)
    if err != nil {
        tx.Rollback()
        return err
    }

    // Danger 3: If context cancels RIGHT HERE (before Commit), 
    // Commit might return an error, or logic might return early.
    
    return tx.Commit()
}

The Solution: The WithTransaction Closure Pattern

The only robust way to handle transactions in Go is to abstract the lifecycle management away from the business logic. We use a generic wrapper function that accepts a closure. This guarantees that Rollback is always deferred, and Commit is only attempted if no errors occurred.

This approach solves three problems:

  1. Panics: It catches panics and rolls back.
  2. Forgotten Rollbacks: The defer is set immediately after acquisition.
  3. Context Propagation: It standardizes how the context is used.

The Implementation

Save this in your database package (e.g., internal/store/db.go).

package store

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

// TxFn represents the business logic function signature.
// It receives a transaction handle to perform queries.
type TxFn func(context.Context, *sql.Tx) error

// WithTransaction creates a new transaction and handles rollback/commit automatically.
func WithTransaction(ctx context.Context, db *sql.DB, fn TxFn) (err error) {
    // 1. Begin the transaction using the provided context.
    // We use default isolation level, but you can pass sql.TxOptions if needed.
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin transaction: %w", err)
    }

    // 2. Defer a rollback.
    // If the function returns with a nil error and the Commit is successful,
    // tx.Rollback() will be called on a committed transaction. 
    // This is safe: database/sql returns sql.ErrTxDone, which we ignore.
    defer func() {
        if p := recover(); p != nil {
            // A panic occurred, rollback and re-panic.
            _ = tx.Rollback()
            panic(p)
        } else if err != nil {
            // An error occurred in the business logic, rollback.
            // We ignore the rollback error because the original error is more important.
            _ = tx.Rollback()
        } else {
            // No error, but we still defer rollback as a safety net 
            // in case Commit fails or logic flow is weird.
            _ = tx.Rollback() 
        }
    }()

    // 3. Execute the business logic.
    if err = fn(ctx, tx); err != nil {
        // The defer will handle the rollback.
        return err
    }

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

    return nil
}

Usage in Business Logic

Your service layer becomes clean, declarative, and leak-proof.

func (s *Service) TransferFunds(ctx context.Context, fromID, toID int, amount int64) error {
    // The context handling and transaction lifecycle are now fully encapsulated.
    return store.WithTransaction(ctx, s.db, func(ctx context.Context, tx *sql.Tx) error {
        
        // 1. Deduct
        res, err := tx.ExecContext(ctx, 
            "UPDATE wallets SET balance = balance - $1 WHERE id = $2 AND balance >= $1", 
            amount, fromID)
        if err != nil {
            return err // Rollback happens automatically
        }
        
        rows, _ := res.RowsAffected()
        if rows == 0 {
            return errors.New("insufficient funds") // Rollback happens automatically
        }

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

        return nil // Commit happens automatically
    })
}

Why This Works

1. The defer Safety Net

In database/sql, calling Rollback() on a transaction that has already been committed or rolled back returns sql.ErrTxDone. This is a crucial feature. It allows us to unconditionally defer tx.Rollback() at the top of the function.

If tx.Commit() succeeds, the deferred Rollback does nothing (harmlessly fails). If tx.Commit() fails (e.g., serialization failure or connection loss), the deferred Rollback ensures the driver cleans up local resources.

2. Context Propagation

By passing the ctx into WithTransaction and then down into the closure fn, we ensure that every query respects the request cancellation. If ExecContext fails due to context.Canceled, the error bubbles up, triggers the defer, and the transaction rolls back immediately.

3. Panic Recovery

In Go, a panic unwinds the stack. Without the recover() block in our wrapper, a panic inside the transaction logic would skip a manual Rollback call (unless strictly deferred), potentially leaving the connection in a "poisoned" state depending on the driver implementation. The wrapper catches this, rolls back to release the DB lock, and then re-panics to let the application crash or recover at the middleware level.

Conclusion

Database connection leaks in Go are rarely due to the driver and almost always due to flow control errors in manual transaction management. By moving the Begin, Rollback, and Commit dance into a reusable WithTransaction helper, you eliminate the cognitive load of resource cleanup and ensure that even if your context dies or your code panics, your database connections remain healthy.

Labels: