Retry Serialization Failures in Go Transactions Safely

When you run transactions at a strict isolation level, the database does not make them wait and always succeed. Sometimes it decides that two concurrent transactions cannot both be allowed to commit, and it aborts one with an error that means "try again". Application code that does not expect this treats a normal event as a failure. This article shows the error, the retry wrapper and an experiment with eight workers, on Go 1.25.6 and PostgreSQL 18.

The reasons for the error are in the isolation article, and the transaction shape is in the transaction article.

What to retry

PostgreSQL signals retryable conflicts with specific SQLSTATE codes:

Both mean the transaction was rolled back and that running it again may succeed. Do not retry other errors: a constraint violation (23505), a syntax error or a lost connection need different handling. With the pgx driver, the error is a *pgconn.PgError, even when you reach it through database/sql with the pgx/stdlib adapter, and errors.As finds it through wrapping:

var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
	return pgErr.Code == "40001" || pgErr.Code == "40P01"
}

MySQL's equivalents are error 1213 (deadlock) and 1205 (lock wait timeout), found with *mysql.MySQLError and its Number field.

The wrapper

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"sync"
	"sync/atomic"

	"github.com/jackc/pgx/v5/pgconn"
	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

// retryable reports whether Postgres asked the client to try again: serialization failure or deadlock.
func retryable(err error) bool {
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) {
		return pgErr.Code == "40001" || pgErr.Code == "40P01"
	}
	return false
}

// WithRetry runs fn in a serializable transaction and retries the whole thing on a retryable error.
func WithRetry(ctx context.Context, db *sql.DB, max int, fn func(*sql.Tx) error, attempts *atomic.Int64) error {
	var err error
	for i := 0; i < max; i++ {
		attempts.Add(1)
		var tx *sql.Tx
		tx, err = db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
		if err != nil {
			return err
		}
		if err = fn(tx); err == nil {
			err = tx.Commit()
		} else {
			_ = tx.Rollback()
		}
		if err == nil || !retryable(err) {
			return err
		}
	}
	return fmt.Errorf("gave up after %d attempts: %w", max, err)
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	_, _ = db.Exec(`drop table if exists counter587; create table counter587 (id int primary key, n int); insert into counter587 values (1, 0)`)

	var attempts atomic.Int64
	var wg sync.WaitGroup
	var failures atomic.Int64
	const workers = 8
	for range workers {
		wg.Add(1)
		go func() {
			defer wg.Done()
			err := WithRetry(ctx, db, 20, func(tx *sql.Tx) error {
				var n int
				if err := tx.QueryRow(`select n from counter587 where id = 1`).Scan(&n); err != nil {
					return err
				}
				_, err := tx.Exec(`update counter587 set n = $1 where id = 1`, n+1)
				return err
			}, &attempts)
			if err != nil {
				failures.Add(1)
			}
		}()
	}
	wg.Wait()
	var n int
	_ = db.QueryRow(`select n from counter587 where id = 1`).Scan(&n)
	fmt.Printf("workers=%d final counter=%d failures=%d\n", workers, n, failures.Load())
	fmt.Printf("attempts > workers (some were retried): %v\n", attempts.Load() > workers)

	// Without retry the same work loses updates or errors.
	_, _ = db.Exec(`update counter587 set n = 0`)
	var noRetryErrs atomic.Int64
	for range workers {
		wg.Add(1)
		go func() {
			defer wg.Done()
			tx, _ := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
			var n int
			_ = tx.QueryRow(`select n from counter587 where id = 1`).Scan(&n)
			if _, err := tx.Exec(`update counter587 set n = $1 where id = 1`, n+1); err != nil {
				noRetryErrs.Add(1)
				_ = tx.Rollback()
				return
			}
			if err := tx.Commit(); err != nil {
				noRetryErrs.Add(1)
			}
		}()
	}
	wg.Wait()
	_ = db.QueryRow(`select n from counter587 where id = 1`).Scan(&n)
	fmt.Printf("without retry: counter=%d, transactions that failed with a retryable error=%d (counter+failures=%d)\n", n, noRetryErrs.Load(), int64(n)+noRetryErrs.Load())
	_, _ = db.Exec(`drop table counter587`)
}

go run . 2>&1

workers=8 final counter=8 failures=0
attempts > workers (some were retried): true
without retry: counter=3, transactions that failed with a retryable error=5 (counter+failures=8)

WithRetry takes a function that does the work inside a transaction, and it owns the loop. Each attempt begins a Serializable transaction, runs the function, and commits. If the function or the commit returns a retryable error, the transaction is rolled back and the loop runs the function again; any other result returns immediately. The attempt cap, 20 here, turns an endless conflict into an error that says gave up after 20 attempts.

Three points about the design.

Retry the whole function. After a serialization failure, PostgreSQL has aborted the transaction; further statements on it fail with current transaction is aborted. The only useful action is a fresh transaction that reruns everything, including the reads, because the data may have changed. A loop that retries only the failing statement is wrong.

The function must be safe to run twice. It should depend only on the database and its arguments. If it sends an email, increments an in-memory counter or calls an API, those effects repeat. Move them after WithRetry returns, or make them idempotent.

Count the attempts. The program passes an atomic.Int64 that the wrapper increments, so the experiment can report how many attempts happened. In a service, export this as a metric: a rising retry rate is the signal of contention before it becomes a latency problem.

The experiment

Eight goroutines each read a counter row and write back the value plus one, in a Serializable transaction. This is the lost update scenario: without protection, concurrent read-modify-writes overwrite each other.

With retry, the result was workers=8 final counter=8 failures=0. Every increment landed. The line attempts > workers (some were retried): true shows that more than eight attempts were made, because the database aborted some transactions and the wrapper ran them again. Conflicts happened; the application absorbed them.

Without retry, the same eight transactions produced counter=2 and six transactions that failed with a retryable error. The final line adds them up: counter+failures=8. Serializable did its job: every transaction either committed with a consistent result or was aborted, and none was lost silently. The two that succeeded incremented the counter twice; the other six got errors the application did not handle. In a service, those would be six failed requests for a normal condition.

Compare with Read Committed on the same code. All eight would commit, and the counter would end somewhere between 1 and 8, with updates lost and no error. The strict level converted silent data loss into a loud, recoverable error.

Backoff and jitter

Retrying at once can repeat the conflict if the same transactions collide again. A short randomised delay breaks the lockstep: wait rand(0 to base * 2^attempt) milliseconds before the next attempt, with a base of a few milliseconds and a cap. Inject the sleep so tests do not wait, as in testing retry logic. Respect the context: if it is done, stop retrying and return its error.

How many retries

The cap should be small enough to fail fast and large enough that a busy system does not surface errors. Five to ten attempts handle typical contention. If the retry count regularly hits the cap, the transactions are too large or too contended for optimistic concurrency, and the design needs to change: shorter transactions, row locks (for update) that make the second transaction wait instead of aborting, or partitioning the hot row. A hot counter that every request updates is the classic case; shard it or use update ... set n = n + 1, which needs no retry.

Where to apply it

Wrap any transaction that runs at Repeatable Read or Serializable. Wrap transactions that take several locks in varying order, where deadlocks are possible, although the better fix is a consistent lock order. Do not wrap read-only autocommit queries. Do not retry inside the function passed to WithRetry; nested loops multiply attempts.

Libraries help: many ORMs and the pgx ecosystem have retry helpers, and CockroachDB's Go library ships a crdbpgx.ExecuteTx wrapper because its default level is Serializable. The loop is short enough that owning it is reasonable, and you get to put your metrics and logging in it.

Testing it

Write the two-transaction test from the isolation article, but assert that your wrapper produces the right result. Spawn N workers, as the lab does, and assert the final state equals N. Make the test fail first by removing the retry, which shows that the test really exercises the conflict. The PostgreSQL manual lists the codes in Appendix A and describes the behaviour in the serialization failure section; the Go type is documented in the pgconn package.