Database Transactions in Go: Isolation Levels, Locks and Real-World Failures

The problem

Two requests withdraw from the same account at nearly the same moment. Each reads the current balance, computes a new one, and writes it back:

func (r *AccountRepository) Withdraw(ctx context.Context, accountID string, amount int64) error {
	var balance int64
	err := r.db.QueryRow(ctx, `SELECT balance FROM accounts WHERE id = $1`, accountID).Scan(&balance)
	if err != nil {
		return fmt.Errorf("read balance: %w", err)
	}
 
	newBalance := balance - amount
	if newBalance < 0 {
		return ErrInsufficientFunds
	}
 
	_, err = r.db.Exec(ctx, `UPDATE accounts SET balance = $1 WHERE id = $2`, newBalance, accountID)
	return err
}

This looks correct, and it is correct — as long as only one request runs at a time. Here's the interleaving that breaks it:

Both requests read the same starting balance before either write lands. Both compute their own "correct" result independently. The second UPDATE doesn't merge with the first — it overwrites it. The account should end up at 20; it ends up at 50. Nothing errored. Nothing logged a warning. A withdrawal simply disappeared from the account's history. This is a lost update, and it's the single most common way "the database is wrong" bugs actually happen.

Why it matters

This isn't a rare timing coincidence — under real load, two requests for the same account landing within milliseconds of each other is routine (a double-tap on a mobile app, a retried request, two legitimate concurrent actions). Every read-then-write sequence against a shared row has this exposure unless something explicitly prevents the interleaving.

Transactions and row locks

BEGIN / COMMIT / ROLLBACK define a transaction boundary, but a transaction alone doesn't prevent the race above — Postgres's default isolation level lets the second transaction's SELECT see the row as it was before the first transaction's UPDATE, which is exactly the interleaving that caused the problem. What actually prevents it is a row lock: SELECT ... FOR UPDATE.

func (r *AccountRepository) Withdraw(ctx context.Context, accountID string, amount int64) error {
	tx, err := r.db.Begin(ctx)
	if err != nil {
		return fmt.Errorf("begin transaction: %w", err)
	}
	defer tx.Rollback(ctx)
 
	var balance int64
	err = tx.QueryRow(ctx,
		`SELECT balance FROM accounts WHERE id = $1 FOR UPDATE`, accountID,
	).Scan(&balance)
	if err != nil {
		return fmt.Errorf("read balance: %w", err)
	}
 
	newBalance := balance - amount
	if newBalance < 0 {
		return ErrInsufficientFunds
	}
 
	if _, err := tx.Exec(ctx, `UPDATE accounts SET balance = $1 WHERE id = $2`, newBalance, accountID); err != nil {
		return fmt.Errorf("update balance: %w", err)
	}
 
	return tx.Commit(ctx)
}

FOR UPDATE locks the selected row until the transaction ends. Request B's own SELECT ... FOR UPDATE on the same row now blocks until request A commits or rolls back — B's SELECT doesn't even return until it can see A's committed result, so B computes its new balance from the correct, up-to-date starting point. The interleaving that caused the lost update is no longer possible; the two requests are forced to run against that row one at a time.

Isolation levels

Postgres offers three isolation levels that matter in practice:

• Read Committed (the default) — each statement sees data committed before that statement began. This is what allows the lost-update race above when you don't add FOR UPDATE — nothing stops two SELECTs from both seeing the pre-update value.
• Repeatable Read — a transaction sees one consistent snapshot for its entire duration. This prevents some anomalies Read Committed allows, but a transaction that tries to modify a row another concurrent transaction has already changed will fail with a serialization error rather than silently proceeding — the correct response is to retry the whole transaction, not to treat it as a bug.
• Serializable — the strictest: transactions behave as if they ran one at a time, whatever their actual execution order. This eliminates entire classes of concurrency anomalies at the cost of more serialization failures under contention, which your application must be prepared to retry.

The pragmatic default for most CRUD-shaped services is Read Committed plus explicit FOR UPDATE on the specific queries that need it — reach for Serializable only for the transactions where correctness genuinely requires it and you've built retry logic for the serialization failures it will produce, not as a blanket setting applied everywhere out of caution.

A cleaner fix for simple decrements: inventory

Not every read-then-write needs an explicit lock. When the operation is simple enough to express as one statement, PostgreSQL can make it atomic without any lock at all:

UPDATE inventory
SET quantity = quantity - $1
WHERE id = $2 AND quantity >= $1;

This single statement reads and writes atomically as part of the same operation — there's no window between "read the current quantity" and "write the new one" for another transaction to land in, because there's no separate read step in application code at all. Check RowsAffected(): zero means there wasn't enough stock, no separate SELECT needed to find that out. This is a genuinely better fix than FOR UPDATE whenever the operation reduces to a single conditional update — reach for FOR UPDATE when you need to read the value, make a decision that isn't expressible as a single WHERE clause, and then write.

Deadlocks

A deadlock is two transactions each holding a lock the other one wants: transaction A locks row 1 and then tries to lock row 2; transaction B has already locked row 2 and is waiting on row 1. Neither can proceed. PostgreSQL detects this cycle and kills one of the two transactions with a deadlock error rather than letting both hang forever.

The fix isn't handling the error better — it's not creating the cycle in the first place, by always locking rows in a consistent order across every code path that touches more than one row (e.g., always lock by ascending primary key). If a transfer between two accounts always locks the lower account ID first, two concurrent transfers between the same pair of accounts can never form the opposing wait-cycle that causes a deadlock.

Why application-level locking isn't enough

A sync.Mutex in Go only coordinates goroutines inside one process. The moment a service runs multiple replicas, each replica has its own, completely independent mutex — two replicas can each acquire their own lock and proceed as if they were exclusive, because neither one's lock has any relationship to the other's. The database transaction and its row locks are the one thing every replica actually shares, which is why correctness for shared rows has to live at the database layer, not in application code. For coordination needs that genuinely aren't just database rows — a scheduled job that must run on exactly one replica, for example — see Distributed Locks in Go for the general pattern.

Production considerations

Keep transactions short. A transaction that holds a row lock across a slow external API call or an unrelated slow query blocks every other transaction waiting on that row for the full duration — do the slow work outside the transaction wherever possible.

Retry serialization failures automatically for any code path using Repeatable Read or Serializable — a transaction that fails with a serialization error and isn't retried is a transaction that silently didn't happen, from the caller's point of view, unless the retry logic is actually there.

Monitor lock waits, not just query latency — pg_stat_activity with a non-null wait_event_type of Lock tells you which queries are currently blocked waiting for a lock, which is a different (and often more actionable) signal than slow-query logs alone.

Common mistakes

• Read-then-write without FOR UPDATE on a value multiple requests can modify concurrently — the exact bug this article opens with.
• Holding a transaction open across a slow external call, turning a single slow dependency into lock contention for every other transaction touching the same rows.
• Inconsistent lock ordering across different code paths that touch the same two tables, creating the exact cycle that produces deadlocks.

Testing

The fix only means something if it survives real concurrency, not just a single-threaded test:

func TestWithdraw_ConcurrentRequests_NoLostUpdate(t *testing.T) {
	db := setupTestDB(t)
	seedAccount(t, db, "acct_1", 100)
	repo := NewAccountRepository(db)
 
	var wg sync.WaitGroup
	amounts := []int64{30, 50}
	for _, amt := range amounts {
		wg.Add(1)
		go func(amount int64) {
			defer wg.Done()
			_ = repo.Withdraw(context.Background(), "acct_1", amount)
		}(amt)
	}
	wg.Wait()
 
	var balance int64
	err := db.QueryRow(context.Background(),
		`SELECT balance FROM accounts WHERE id = $1`, "acct_1",
	).Scan(&balance)
	if err != nil {
		t.Fatalf("read final balance: %v", err)
	}
	if balance != 20 {
		t.Fatalf("expected final balance 20 (100 - 30 - 50), got %d — a lost update occurred", balance)
	}
}

Run this against the unprotected version first — it fails intermittently, which is exactly the point: a lost update doesn't reproduce every run, it reproduces under the right timing, which is why it's easy to miss in code review and hard to catch without a concurrency test like this one.

Summary

A lost update isn't a database bug — it's what happens when application code performs a read-then-write against a shared row with nothing preventing two of them from interleaving. SELECT ... FOR UPDATE closes that window by making the read-then-write atomic at the row level; a single conditional UPDATE statement closes it even more cheaply when the operation is simple enough to express that way. Isolation levels above the default trade correctness guarantees for serialization failures your code must retry, and none of this is optional once a service runs more than one replica against the same database — the transaction is the only coordination mechanism every replica actually shares.