pgx Transactions Savepoints and Retry Loops in Practice

Transactions are where most data bugs start and where pgx gives you the most help. It wraps the begin, commit and rollback in a helper that cannot forget the rollback, models savepoints as nested transactions, and returns errors with enough detail to build a correct retry loop. This article exercises each against PostgreSQL 18.6 from Go 1.25.6, including the cases that surprise people: what a transaction does after an error, and why an unretried serializable transaction is a bug.

It belongs to the database cornerstone. The transaction mechanics common to every driver are in transactions and deferred rollback, the boundary design in unit of work, and the isolation levels themselves in isolation levels and retrying serialization failures.

The lab

package main

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

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

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

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

// withRetry runs fn in a transaction at the given isolation level and runs it again when PostgreSQL aborts it as a serialization failure or a deadlock.
func withRetry(ctx context.Context, pool *pgxpool.Pool, level pgx.TxIsoLevel, retries *atomic.Int64, fn func(pgx.Tx) error) error {
	for attempt := 0; attempt < 500; attempt++ {
		err := pgx.BeginTxFunc(ctx, pool, pgx.TxOptions{IsoLevel: level}, fn)
		var pg *pgconn.PgError
		if errors.As(err, &pg) && (pg.Code == "40001" || pg.Code == "40P01") {
			retries.Add(1)
			continue
		}
		return err
	}
	return errors.New("gave up after 500 attempts")
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn+"&pool_max_conns=8")
	must(err)
	defer pool.Close()
	for _, q := range []string{
		"drop table if exists acct670",
		"create table acct670 (id int primary key, balance int not null, note text)",
		"insert into acct670 values (1, 100, null), (2, 100, null)",
	} {
		_, err := pool.Exec(ctx, q)
		must(err)
	}
	balance := func(id int) (b int) {
		_ = pool.QueryRow(ctx, "select balance from acct670 where id = $1", id).Scan(&b)
		return
	}

	// 1. BeginFunc: commit on nil, rollback on error, and a panic also rolls back.
	err = pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error {
		_, _ = tx.Exec(ctx, "update acct670 set balance = balance - 30 where id = 1")
		_, _ = tx.Exec(ctx, "update acct670 set balance = balance + 30 where id = 2")
		return errors.New("a check failed")
	})
	fmt.Printf("1. BeginFunc returned %q; balances are %d and %d\n", err, balance(1), balance(2))
	err = pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error {
		_, _ = tx.Exec(ctx, "update acct670 set balance = balance - 30 where id = 1")
		_, _ = tx.Exec(ctx, "update acct670 set balance = balance + 30 where id = 2")
		return nil
	})
	fmt.Printf("   and with nil: err %v, balances %d and %d\n", err, balance(1), balance(2))

	// 2. The manual form, and what a closed transaction says.
	tx, err := pool.Begin(ctx)
	must(err)
	_, _ = tx.Exec(ctx, "update acct670 set note = 'manual' where id = 1")
	must(tx.Commit(ctx))
	_, err = tx.Exec(ctx, "update acct670 set note = 'after commit' where id = 1")
	fmt.Println("2. a statement on a committed transaction:", err, "| ErrTxClosed:", errors.Is(err, pgx.ErrTxClosed))
	fmt.Println("   Rollback after Commit returns the same error, which a deferred Rollback should ignore:", tx.Rollback(ctx))

	// 3. Savepoints: Begin on a transaction creates one, and a failed part can be undone alone.
	tx, err = pool.Begin(ctx)
	must(err)
	_, _ = tx.Exec(ctx, "update acct670 set note = 'outer' where id = 1")
	inner, err := tx.Begin(ctx)
	must(err)
	_, _ = inner.Exec(ctx, "update acct670 set note = 'inner' where id = 2")
	_, err = inner.Exec(ctx, "insert into acct670 values (1, 0, 'duplicate')")
	_ = inner.Rollback(ctx)
	must(tx.Commit(ctx))
	var n1, n2 string
	_ = pool.QueryRow(ctx, "select note from acct670 where id = 1").Scan(&n1)
	_ = pool.QueryRow(ctx, "select coalesce(note, 'NULL') from acct670 where id = 2").Scan(&n2)
	fmt.Printf("3. the inner part failed on a duplicate and was rolled back to its savepoint: row 1 note %q kept, row 2 note %s\n", n1, n2)

	// 4. After an error the whole transaction is poisoned until rollback.
	tx, _ = pool.Begin(ctx)
	_, err = tx.Exec(ctx, "insert into acct670 values (1, 0, 'dup')")
	_, err2 := tx.Exec(ctx, "select 1")
	fmt.Printf("4. after a failed statement the next one says: %v\n", err2)
	_ = err
	_ = tx.Rollback(ctx)

	// 5. Concurrent read-modify-write at read committed loses updates; serializable with a retry loop does not.
	for _, level := range []pgx.TxIsoLevel{pgx.ReadCommitted, pgx.Serializable} {
		_, _ = pool.Exec(ctx, "update acct670 set balance = 0 where id = 1")
		var retries atomic.Int64
		var wg sync.WaitGroup
		for w := 0; w < 4; w++ {
			wg.Add(1)
			go func() {
				defer wg.Done()
				for i := 0; i < 5; i++ {
					err := withRetry(ctx, pool, level, &retries, func(tx pgx.Tx) error {
						var b int
						if err := tx.QueryRow(ctx, "select balance from acct670 where id = 1").Scan(&b); err != nil {
							return err
						}
						_, err := tx.Exec(ctx, "update acct670 set balance = $1 where id = 1", b+1)
						return err
					})
					must(err)
				}
			}()
		}
		wg.Wait()
		fmt.Printf("5. %-14s 4 workers x 5 increments: balance %d (want 20), %d transactions retried\n", level, balance(1), retries.Load())
	}

	// 6. Read only and deferrable options.
	tx, err = pool.BeginTx(ctx, pgx.TxOptions{AccessMode: pgx.ReadOnly})
	must(err)
	_, err = tx.Exec(ctx, "update acct670 set balance = 1")
	fmt.Println("6. a write in a read only transaction:", err)
	_ = tx.Rollback(ctx)
}

1. BeginFunc returned "a check failed"; balances are 100 and 100
   and with nil: err <nil>, balances 70 and 130
2. a statement on a committed transaction: tx is closed | ErrTxClosed: true
   Rollback after Commit returns the same error, which a deferred Rollback should ignore: tx is closed
3. the inner part failed on a duplicate and was rolled back to its savepoint: row 1 note "outer" kept, row 2 note NULL
4. after a failed statement the next one says: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02)
5. read committed 4 workers x 5 increments: balance 10 (want 20), 0 transactions retried
5. serializable   4 workers x 5 increments: balance 20 (want 20), 27 transactions retried
6. a write in a read only transaction: ERROR: cannot execute UPDATE in a read-only transaction (SQLSTATE 25006)

BeginFunc

pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error { ... }) does the whole lifecycle. It begins a transaction, calls your function, commits if it returns nil, rolls back if it returns an error, and rolls back and re-panics if the function panics. The first block runs a transfer of 30 between two accounts twice. The first time the function returned an error after both updates: the helper rolled back and returned the error, and both balances were still 100. The second time it returned nil: the commit happened and the balances were 70 and 130.

Use it as the default. The hand written equivalent, tx, _ := pool.Begin(ctx); defer tx.Rollback(ctx); ...; tx.Commit(ctx), is correct and has three places to go wrong: forgetting the rollback on an early return, ignoring the commit error, and mishandling a panic. BeginTxFunc is the same with pgx.TxOptions for the isolation level, access mode and deferrable setting.

The manual form

When you need explicit control, pool.Begin(ctx) returns a pgx.Tx with Commit, Rollback and the same query methods as the pool. The second block commits a transaction and then uses it: the next Exec returned tx is closed and errors.Is(err, pgx.ErrTxClosed) was true. Rollback after Commit returned the same error too, which means the standard pattern, defer tx.Rollback(ctx) right after Begin, is correct and its error should be ignored: after a successful commit it is a no-op that reports the closed transaction. Check the error from Commit, always: a commit can fail, for example on a deferred constraint or a serialization failure, and a program that ignores it believes data was saved when it was not.

Savepoints

PostgreSQL has no nested transactions, and has savepoints, which behave like them. pgx models a savepoint as a nested transaction: calling tx.Begin(ctx) on a pgx.Tx issues SAVEPOINT and returns a new pgx.Tx; its Commit releases the savepoint and its Rollback rolls back to it.

The third block runs an outer transaction that sets one note, then an inner one that sets another and then fails on a duplicate insert, and rolls the inner one back before committing the outer. The outer change, row 1's note "outer", was kept; the inner change to row 2 was undone, leaving its note NULL. Use savepoints when part of a larger operation may fail and the rest should proceed: trying an insert that may conflict, or a best effort step. Without one, the failed statement would abort the whole transaction, which is the next point.

An error poisons the transaction

The fourth block is the behaviour to know by heart. After a statement fails inside a transaction, PostgreSQL puts the transaction in an aborted state: every further statement fails with ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02), until you roll back. The lab's select 1 after the failed insert returned exactly that. The consequence for code is that you cannot catch a database error in the middle of a transaction, log it and carry on. Either the error ends the transaction (return it, roll back) or the risky statement is wrapped in a savepoint, as above, so that the failure is contained.

This is also why ignoring an error from Exec inside a transaction is so damaging: the next statement fails with a confusing message, and the original error is gone.

Concurrency: losing updates and retrying

The fifth block is the important one. Four goroutines each add 1 to the same balance five times, as a read then a write: select balance, then update ... set balance = $1. The correct total is 20.

At read committed, PostgreSQL's default, the result was 10, with no errors and no retries: half of the increments were lost. Two transactions read the same balance, both wrote balance plus one, and the second overwrote the first. This is the lost update, and it is silent.

At serializable, the same code with a retry loop ended at 20, after 27 transactions had been retried. At that level PostgreSQL detects the conflicting pattern and aborts one of the transactions at commit with SQLSTATE 40001, could not serialize access due to read/write dependencies among transactions. The withRetry helper catches 40001 and 40P01 (deadlock) from pgx.BeginTxFunc, and runs the function again from the start, with a new snapshot. Serializable does not make your code correct by itself: without the retry, those 27 aborts would have been errors, and the program would have returned them to callers. Serializable means "correct, or aborted", and the retry is your side of the contract.

Three rules for the loop. Retry the whole transaction function, never a single statement, since the transaction is gone. Make the function free of side effects other than the database, because it may run several times: no emails, no HTTP calls. And bound the retries and add a small randomised delay, since several transactions that retry together conflict again. The lab allows 500 attempts with no delay because it is a stress test of one hot row; in a service use a handful with backoff, and treat running out as a failure to report. Also remember that a hot row serialises everything: if a row is updated by many transactions, consider an atomic update (set balance = balance + 1), which needs no isolation level at all, as in optimistic locking.

Options

The sixth block shows pgx.TxOptions{AccessMode: pgx.ReadOnly}: a write in a read only transaction failed with cannot execute UPDATE in a read-only transaction (25006). Read only transactions are a cheap guard for code that should not write, and at Repeatable Read or Serializable they give a consistent snapshot across several queries. The option DeferrableMode applies to serializable read only transactions that must not be aborted: a deferrable one waits for a safe snapshot and then runs without risk of a serialization failure, which suits long reports.

Habits

1. Use BeginFunc or BeginTxFunc; if you write it by hand, defer tx.Rollback(ctx) immediately after Begin.
2. Check the error from Commit.
3. Treat any error inside a transaction as the end of it, unless a savepoint contains it.
4. Use savepoints sparingly, for parts that may fail on purpose.
5. Pick the isolation level for the invariant, and pair serializable and repeatable read with a retry loop that handles 40001.
6. Keep transactions short and free of slow calls, since they hold a connection and locks.
7. Pass a context with a deadline, so a stuck transaction releases its connection; see pgx context cancellation.

The API is in the pgx reference, and the semantics of savepoints and isolation in the PostgreSQL manual's pages on SAVEPOINT and transaction isolation.