DB Transaction #

Half-finished corrupted data is one of the hardest bugs to debug and the most expensive to fix. The user already paid but the order status is still pending. The balance was deducted but the product wasn’t shipped. The invoice was created but the items weren’t inventoried. All of these are consequences of database operations running without proper transactions. A transaction is the mechanism ensuring a set of database changes happens as a single unit: either everything succeeds, or nothing changes at all. This article covers transactions from the ACID principles, isolation levels and their implications, correct implementation, what should stay out of transactions, up to patterns for distributed transactions.

The Problems Transactions Solve #

To understand why transactions matter, consider a simple checkout operation involving three steps:

flowchart TD
    Start["Checkout Request"]

    subgraph NoTx["Without a Transaction"]
        N1["1. Deduct product stock ✓"]
        N2["2. Create order record ✓"]
        N3["3. Process payment ✗ FAILED"]
        NResult["State: stock deducted, order exists,\nno payment\n→ HALF-FINISHED CORRUPT DATA"]
        N1 --> N2 --> N3 --> NResult
    end

    subgraph WithTx["With a Transaction"]
        W1["1. Deduct product stock ✓"]
        W2["2. Create order record ✓"]
        W3["3. Process payment ✗ FAILED"]
        Rollback["ROLLBACK all changes"]
        WResult["State: no changes\n→ CONSISTENT"]
        W1 --> W2 --> W3 --> Rollback --> WResult
    end

    Start --> NoTx
    Start --> WithTx

    style NResult fill:#E74C3C,color:#fff
    style WResult fill:#27AE60,color:#fff
    style Rollback fill:#E67E22,color:#fff

Without a transaction, a failure in the third step leaves the system in an inconsistent state that nobody detects automatically. With a transaction, any failure in any step rolls back all changes at once.

ACID Properties — The Foundation of Reliability #

ACID is the four properties defining reliable transaction behavior. Understanding each helps understand why transactions work as expected.

Atomicity — All or Nothing #

All operations within one transaction are treated as a single indivisible unit. If one operation fails, all prior operations in the same transaction are rolled back.

Atomicity example:
  Transaction: Deduct balance A, Add balance B (transfer)

  Scenario 1: Both succeed → COMMIT, both changes saved
  Scenario 2: Deduct A succeeds, add B fails → ROLLBACK, balance A restored
  Scenario 3: Server crashes mid-way → after restart, no changes saved

Consistency — From Valid State to Valid State #

The database is always in a valid state before and after a transaction. Constraints, foreign keys, and business rules defined at the database level can’t be violated by transactions.

Consistency example:
  Constraint: balance must not be negative (CHECK balance >= 0)

  Transaction: deduct the balance from 100 to -50
  → The database rejects the COMMIT because it violates the constraint
  → Automatic rollback
  → The balance stays at 100

Isolation — Transactions Don’t Interfere With Each Other #

Changes made by an uncommitted transaction aren’t visible to other transactions (depending on the isolation level). This prevents various concurrency anomalies.

Without sufficient isolation:

Dirty Read: Transaction A reads data being modified by Transaction B
            but B hasn't committed yet. If B rolls back, A already used wrong data.

Lost Update: Transactions A and B both read a balance of 100.
             A updates to 150, B updates to 80. A's update is lost.

Phantom Read: Transaction A counts the number of records.
              Transaction B inserts a new record.
              Transaction A counts again, the result differs.

Durability — Commits Can’t Be Lost #

Committed data is stored permanently, even if a crash happens right after the commit. Databases use write-ahead logs (WAL) to ensure this.

Isolation Levels — The Choice That Determines Behavior #

This is the most often misunderstood part. Databases provide several isolation levels as a trade-off between consistency and performance. Higher levels provide better isolation but with more aggressive locking.

flowchart LR
    subgraph Levels["Isolation Levels — from lowest to highest"]
        RU["READ UNCOMMITTED\nCan read uncommitted data\nNot used in production"]
        RC["READ COMMITTED\nOnly reads committed data\nDefault in PostgreSQL\nSafe for most use cases"]
        RR["REPEATABLE READ\nRead data stays consistent during the transaction\nDefault in MySQL/InnoDB\nGood for reports and financials"]
        SR["SERIALIZABLE\nTransactions run as if serialized\nSafest, slowest\nOnly for very critical use cases"]
    end

    RU -->|"Stricter"| RC
    RC -->|"Stricter"| RR
    RR -->|"Stricter"| SR

    style RU fill:#E74C3C,color:#fff
    style RC fill:#27AE60,color:#fff
    style RR fill:#E67E22,color:#fff
    style SR fill:#8E44AD,color:#fff

Guidance for choosing isolation levels:

READ COMMITTED (PostgreSQL default):
  Best for: The majority of regular CRUD operations
  Trade-off: Non-repeatable reads possible (data can change mid-transaction)
  Use if: No need to read the same data twice
          within one transaction expecting identical results

REPEATABLE READ (MySQL default):
  Best for: Reports, financial calculations, operations needing consistent snapshots
  Trade-off: More locking, slower under concurrent workloads
  Use if: Reading the same data more than once and results must be consistent
          "Read the balance now for calculation, make sure it doesn't change"

SERIALIZABLE:
  Best for: The most critical operations where correctness is absolutely required
  Trade-off: Very aggressive locking, throughput can drop significantly
  Use if: There's truly no alternative
          Example: allocating serial numbers that must be absolutely unique

Never use a higher isolation level than needed “to be safer”. SERIALIZABLE under high traffic can cause many transactions to fail from serialization conflicts, which actually harms system reliability. Choose the lowest level that still provides the guarantees you need.

Correct Transaction Implementation #

The Basic Pattern in Go #

// ANTI-PATTERN: Transaction not handled correctly
func (s *OrderService) CreateOrder(input CreateOrderInput) error {
    tx, _ := s.db.Begin()  // doesn't handle the Begin() error

    s.db.Exec("INSERT INTO orders ...")  // uses db directly, not tx!
    tx.Exec("INSERT INTO order_items ...")

    tx.Commit()  // doesn't handle the Commit() error
    return nil
}

// CORRECT: Transaction with complete error handling
func (s *OrderService) CreateOrder(ctx context.Context, input CreateOrderInput) error {
    tx, err := s.db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelReadCommitted,
    })
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }

    // deferred rollback — no-op if already committed
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)  // re-panic after rollback
        } else if err != nil {
            tx.Rollback()
        }
    }()

    // Use tx, not s.db, for all operations within the transaction
    order := &Order{UserID: input.UserID, Total: input.Total}
    if err = insertOrder(ctx, tx, order); err != nil {
        return fmt.Errorf("insert order: %w", err)
    }

    for _, item := range input.Items {
        if err = insertOrderItem(ctx, tx, order.ID, item); err != nil {
            return fmt.Errorf("insert order item: %w", err)
        }
    }

    if err = deductInventory(ctx, tx, input.Items); err != nil {
        return fmt.Errorf("deduct inventory: %w", err)
    }

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

    return nil
}

The Transaction Helper Function Pattern #

To avoid repeated error-handling boilerplate:

// Helper function to run a function within a transaction
func WithTransaction(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }

    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        }
    }()

    if err := fn(tx); err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return fmt.Errorf("tx err: %v, rollback err: %v", err, rbErr)
        }
        return err
    }

    return tx.Commit()
}

// Clean usage
func (s *OrderService) CreateOrder(ctx context.Context, input CreateOrderInput) error {
    return WithTransaction(ctx, s.db, func(tx *sql.Tx) error {
        order, err := insertOrder(ctx, tx, input)
        if err != nil {
            return err
        }
        if err := insertOrderItems(ctx, tx, order.ID, input.Items); err != nil {
            return err
        }
        return deductInventory(ctx, tx, input.Items)
    })
}

What Belongs and Doesn’t Belong in a Transaction #

This is the most commonly found mistake: putting operations into transactions that shouldn’t be there.

What Belongs in a Transaction #

✓ INSERT, UPDATE, DELETE operations that are logically interdependent
✓ State transitions (pending → processing → completed)
✓ Operations involving multiple tables that must stay consistent together
✓ Interrelated deductions and insertions (deduct stock, add order)
✓ Idempotency checks and inserts (to prevent duplicates)

What Must NOT Be in a Transaction #

✗ HTTP calls to external services (payment gateways, SMS gateways, etc.)
✗ Sending emails or push notifications
✗ File uploads to object storage (S3, GCS)
✗ Large loops or heavy computations
✗ Cache invalidation or cache writes
✗ Publishing events to message queues

Why this matters:

// ANTI-PATTERN: External I/O inside a transaction
func (s *OrderService) CreateOrder(ctx context.Context, input CreateOrderInput) error {
    tx, _ := s.db.BeginTx(ctx, nil)
    defer tx.Rollback()

    // Insert the order into the DB
    order, err := insertOrder(ctx, tx, input)
    if err != nil { return err }

    // DANGER: HTTP call to the payment gateway inside the transaction!
    paymentResult, err := s.paymentGateway.Charge(input.Amount)
    if err != nil {
        // The transaction has been open during the payment gateway request (could be 3-10 seconds!)
        // Database locks are still held during this time
        return err
    }

    return tx.Commit()
}
// Consequence: the transaction stays open 3-10 seconds waiting for the payment response
// Table/row locks block other requests wanting to modify the same data
// Deadlock potential increases significantly

// CORRECT: Separate external I/O from the transaction
func (s *OrderService) CreateOrder(ctx context.Context, input CreateOrderInput) error {
    // Step 1: Create the order with status "pending" — in a fast transaction
    var orderID string
    err := WithTransaction(ctx, s.db, func(tx *sql.Tx) error {
        order, err := insertOrder(ctx, tx, input)
        if err != nil { return err }
        orderID = order.ID
        return insertOrderItems(ctx, tx, order.ID, input.Items)
    })
    if err != nil {
        return err
    }

    // Step 2: Charge the payment — outside the transaction
    paymentResult, err := s.paymentGateway.Charge(input.Amount)
    if err != nil {
        // Update the order status to "payment_failed" — a new, fast transaction
        _ = s.updateOrderStatus(ctx, orderID, "payment_failed")
        return err
    }

    // Step 3: Confirm the order — a new short transaction
    return WithTransaction(ctx, s.db, func(tx *sql.Tx) error {
        return confirmOrderWithPayment(ctx, tx, orderID, paymentResult.ID)
    })
}

Deadlocks — Causes and Prevention #

Deadlocks happen when two or more transactions wait for locks held by the other transactions.

sequenceDiagram
    participant T1 as Transaction 1
    participant TA as Row A
    participant TB as Row B
    participant T2 as Transaction 2

    T1->>TA: Lock Row A ✓
    T2->>TB: Lock Row B ✓

    T1->>TB: Request lock on Row B... wait
    T2->>TA: Request lock on Row A... wait

    Note over T1,T2: DEADLOCK — both wait forever
    Note over T1,T2: The database detects it and aborts one

How to prevent deadlocks:

1. Access resources in a consistent order
   // If you need to lock users A and B, always lock the smaller ID first
   userIDs := []string{recipientID, senderID}
   sort.Strings(userIDs)  // sort first
   for _, id := range userIDs {
       lockUser(ctx, tx, id)
   }

2. Keep transactions short
   → Short transactions = locks held briefly = less chance of conflicts

3. Use SELECT FOR UPDATE wisely
   → Only lock rows that will actually be modified
   → Don't lock rows that are only read

4. Retry on deadlocks
   → The database detects and rolls back one of the transactions
   → Implement retries with exponential backoff
   for attempt := 0; attempt < maxRetries; attempt++ {
       err := WithTransaction(ctx, db, fn)
       if isDeadlockError(err) {
           time.Sleep(backoffDuration(attempt))
           continue
       }
       return err
   }

The Saga Pattern — For Distributed Transactions #

When a business operation spans several different services (microservices), a single database transaction can’t cover them all. The saga pattern is the solution.

flowchart TD
    Start["Checkout Request"]

    subgraph Saga["Saga: Checkout Flow"]
        S1["Step 1: Order Service\nCreate order → status: pending"]
        S2["Step 2: Inventory Service\nDeduct stock"]
        S3["Step 3: Payment Service\nProcess payment"]
        S4["Step 4: Order Service\nUpdate status → completed"]
    end

    subgraph Compensate["Compensating Transactions\n(if a failure occurs)"]
        C3["Cancel the payment\n(if step 4 fails)"]
        C2["Restore stock\n(if step 3 fails)"]
        C1["Cancel the order\n(if step 2 fails)"]
    end

    Start --> S1 --> S2 --> S3 --> S4
    S4 -->|"Fails"| C3
    S3 -->|"Fails"| C2
    S2 -->|"Fails"| C1

    style C1 fill:#E74C3C,color:#fff
    style C2 fill:#E74C3C,color:#fff
    style C3 fill:#E74C3C,color:#fff

Two saga implementations:

1. Choreography-based Saga:
   Every service publishes events and subscribes to other services' events
   → No central coordinator
   → Simpler but harder to debug

2. Orchestration-based Saga:
   A central orchestrator calls each service in sequence
   and handles compensating transactions on failures
   → Easier to trace and debug
   → Has a single point of failure (the orchestrator)

Saga principles:
  → Every step must be idempotent (retryable without double effects)
  → Every step must have a clear compensating transaction
  → Eventual consistency, not immediate consistency

Transaction Anti-Patterns to Avoid #

Overly Long Transactions #

// ✗ ANTI-PATTERN: Transaction opened at request start, committed at the end
func (h *Handler) HandleRequest(w http.ResponseWriter, r *http.Request) {
    tx, _ := h.db.Begin()

    // ... lots of logic, several minutes ...
    // ... input validation (doesn't need to be in a transaction) ...
    // ... fetching data from various tables ...
    // ... complex calculations ...
    // ... external API calls (VERY WRONG) ...

    tx.Exec("INSERT INTO results ...")
    tx.Commit()
}
// Database locks held during the entire request processing!

// ✓ Solution: Open the transaction right before the writes, commit after the writes finish
func (h *Handler) HandleRequest(w http.ResponseWriter, r *http.Request) {
    // Validate and fetch data without a transaction
    input := parseAndValidate(r)
    data := h.fetchRequiredData(ctx, input)

    // Only the write part is in a transaction, kept as short as possible
    err := WithTransaction(ctx, h.db, func(tx *sql.Tx) error {
        return h.writeResults(ctx, tx, data)
    })
}

Ignoring Errors from Commit #

// ✗ ANTI-PATTERN: Commit errors ignored
tx.Commit()  // if this fails, we don't know

// ✓ Solution: Commit errors must be handled
if err := tx.Commit(); err != nil {
    // Commits can fail due to serialization conflicts, network issues, etc.
    log.Error("transaction commit failed", "error", err)
    return fmt.Errorf("failed to commit: %w", err)
}

Ambiguous Nested Transactions #

// ✗ ANTI-PATTERN: Nested transactions with unclear boundaries
func (s *Service) OperationA(ctx context.Context) error {
    tx, _ := s.db.Begin()
    // ...
    s.OperationB(ctx)  // OperationB also opens its own transaction!
    tx.Commit()
}

// ✓ Solution: Pass the transaction as a parameter, or use the UoW pattern
func (s *Service) OperationA(ctx context.Context) error {
    return WithTransaction(ctx, s.db, func(tx *sql.Tx) error {
        if err := s.doPartA(ctx, tx); err != nil { return err }
        return s.doPartB(ctx, tx)  // use the same tx
    })
}

DB Transaction Checklist #

TRANSACTION DESIGN:
  □ Every transaction represents one clear unit of work
  □ Transactions as short as possible — open late, commit early
  □ External I/O (HTTP calls, emails, file uploads) OUTSIDE transactions
  □ Isolation level chosen by need, not by default

IMPLEMENTATION:
  □ All errors from Begin() and Commit() handled
  □ All operations within transactions use tx, not db directly
  □ defer Rollback() installed immediately after Begin() succeeds
  □ No naked recover() hiding transaction errors

ERROR HANDLING:
  □ Rollback called on errors mid-transaction
  □ Rollback errors also logged (even if little can be done about them)
  □ Deadlock errors handled with retries and backoff

CONCURRENCY:
  □ Consistent lock ordering to prevent deadlocks
  □ SELECT FOR UPDATE only on rows to be modified
  □ Concurrent scenarios tested (not just sequential unit tests)

DATABASE CONSTRAINTS:
  □ UNIQUE constraints for data that must be unique
  □ FOREIGN KEY constraints for relations that must be valid
  □ CHECK constraints for value rules that must always hold
  □ NOT NULL for fields that can't be empty

OBSERVABILITY:
  □ Transaction errors logged with sufficient context
  □ Long-running transactions alerted (queries exceeding thresholds)
  □ Deadlock rates monitored

Summary #

• Transactions ensure all or nothing — if one operation fails mid-way, all changes in the same transaction are rolled back. This prevents partial updates causing inconsistent data.
• ACID is a guarantee, not a feature — Atomicity, Consistency, Isolation, and Durability are properties to understand, not just memorize. Each solves a different class of problems.
• Choose the right isolation level, not the highest — READ COMMITTED for the majority of use cases, REPEATABLE READ for financial calculations needing consistent snapshots. SERIALIZABLE is almost always overkill and harms throughput.
• External I/O must never be inside a transaction — HTTP calls, emails, and file uploads inside transactions hold locks longer, cause more deadlocks, and reduce throughput. Separate external I/O from database operations.
• Open late, commit early — don’t open a transaction at request start and commit at the end. Open it right before writes begin, commit right after all writes finish.
• Always handle Commit() errors — commits can fail from serialization conflicts or network issues. Ignoring Commit errors means possibly not realizing data wasn’t saved.
• defer Rollback() right after Begin() — this pattern ensures rollback is always called on errors, even unexpected panics.
• Deadlocks are conditions to handle, not fully avoid — implement retries with exponential backoff for serialization errors and deadlocks. Keep lock acquisition order consistent.
• Database constraints are transaction partners — transactions maintain atomicity, constraints maintain consistency. They complement each other and can’t replace one another.
• Distributed transactions need the saga pattern — when operations span multiple services, use sagas with compensating transactions instead of trying to make one transaction span many databases.

#

← Previous: Validation   Next: N+1 Query