Transactions (Isolation, Retries)

Transactions: Where Correctness Is Won or Lost

Transactions protect data consistency. In production systems, improper transaction handling causes silent corruption, deadlocks, and high-latency cascades. Using database/sql transactions correctly requires discipline and explicit boundaries.

ACID in Practice

• Atomicity: All operations succeed or none.
• Consistency: Database constraints preserved.
• Isolation: Concurrent transactions do not see inconsistent state.
• Durability: Committed data survives crashes.

Most production bugs occur in isolation and atomicity misunderstandings.

Basic Transaction Pattern

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

Always defer Rollback. If Commit succeeds, Rollback becomes a no-op.

Executing Queries Inside Transaction

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

Never use db.Exec inside a transaction block. Always use tx.

Commit and Error Handling

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

Commit can fail. Always check its error.

Real Production Failure: Lost Update

Two concurrent transactions:

SELECT balance FROM accounts WHERE id=1;
-- both see 100

UPDATE accounts SET balance = 90 WHERE id=1;

Final balance becomes incorrect due to race.

Mitigation:

• Use SELECT ... FOR UPDATE
• Or optimistic locking (version column)

Isolation Levels

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

Common levels:

• ReadCommitted (default in many DBs)
• RepeatableRead
• Serializable

Higher isolation increases safety but reduces concurrency.

Deadlocks

Deadlocks occur when transactions lock resources in different orders.

Example:

• Tx1 locks row A, waits for row B
• Tx2 locks row B, waits for row A

Database aborts one transaction.

Mitigation

• Access rows in consistent order
• Keep transactions short
• Retry serialization errors

Retry Pattern

for i := 0; i < 3; i++ {
    err := doTransaction()
    if isRetryable(err) {
        continue
    }
    return err
}

Retry only on retryable errors (deadlock, serialization failure).

Transaction Scope Discipline

Do NOT:

• Call external APIs inside transaction
• Perform long computations inside transaction
• Sleep inside transaction

Transactions should be short and deterministic.

Context With Transactions

ctx, cancel := context.WithTimeout(parent, 3*time.Second)
defer cancel()

tx, err := db.BeginTx(ctx, nil)

If context times out, transaction is canceled.

Idempotent Writes

In distributed systems, retries happen. Design transactions to be idempotent.

Example: insert with unique request_id constraint.

INSERT INTO payments (request_id, amount) VALUES ($1, $2)

Duplicate request_id prevents double charge.

Outbox Pattern (Brief Intro)

When publishing events after DB write:

• Write event into outbox table in same transaction
• Separate worker publishes events asynchronously

This prevents partial commit + message loss.

Detecting Transaction Problems

• High lock wait time
• Frequent deadlocks
• Long-running transactions
• Pool saturation

Monitor database lock metrics.

Testing Transaction Logic

go test -race -run=Integration

Simulate concurrent operations to detect lost updates.

Common Anti-Patterns

• Ignoring commit errors
• Mixing db and tx inside transaction
• Long-running transactions
• No retry logic for serialization errors
• External network calls inside transaction

Operational Checklist

• Rollback deferred
• Commit errors handled
• Transactions short-lived
• Isolation level explicit when needed
• Retry strategy implemented
• Row locking order consistent
• No external calls inside transaction

Final Perspective

Transactions are correctness boundaries. In production systems, they must be short, isolated, and deterministic. Proper transaction discipline prevents silent data corruption and ensures predictable behavior under concurrency and failure conditions.