Unit of Work and Transaction Boundaries in Go Services

A transaction is a business decision. "Debit one account and credit another, or do neither" is a rule of the application, and it belongs where the application's operations are defined, not inside a repository method that happens to touch one table. The unit of work is the small piece of code that draws that boundary: it opens a transaction, gives the operation's code a set of repositories that all use it, and then commits or rolls back according to how the code ended. This guide builds one in Go 1.25.6 against PostgreSQL 18.6, shows what goes wrong without it, and goes through the details that make it reliable: errors at commit time, panics, nesting, side effects and long transactions.

It belongs to the database cornerstone, follows from the repository pattern and relies on the mechanics in transactions and deferred rollback.

The problem it solves

database/sql makes each call on *sql.DB an independent statement on whichever connection the pool picked. Repositories written naturally take a *sql.DB, so a service method that calls two of them performs two statements in two separate transactions. If the second fails, the first has already committed, and nothing can undo it.

The cure is not to give repositories transaction logic. It is to give them the same handle for the whole operation. A repository needs only two methods from its database handle (ExecContext and QueryRowContext, plus QueryContext if it lists rows), and both *sql.DB and *sql.Tx have them with identical signatures. An interface of just those methods lets one repository type run against either.

The code

package main

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

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

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

// DBTX is what both *sql.DB and *sql.Tx can do, so a repository works inside or outside a transaction.
type DBTX interface {
	ExecContext(ctx context.Context, q string, args ...any) (sql.Result, error)
	QueryRowContext(ctx context.Context, q string, args ...any) *sql.Row
}

type Accounts struct{ q DBTX }

var ErrNoAccount = errors.New("no such account")

func (a Accounts) Add(ctx context.Context, id int64, delta int) error {
	res, err := a.q.ExecContext(ctx, "update accounts608 set balance = balance + $1 where id = $2", delta, id)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNoAccount
	}
	return nil
}

type Ledger struct{ q DBTX }

func (l Ledger) Record(ctx context.Context, account int64, delta int) error {
	_, err := l.q.ExecContext(ctx, "insert into ledger608 (account_id, delta) values ($1, $2)", account, delta)
	return err
}

// Repos is what a unit of work hands to its function: every repository bound to the same transaction.
type Repos struct {
	Accounts Accounts
	Ledger   Ledger
}

type UnitOfWork struct{ db *sql.DB }

// Do runs fn in one transaction. It commits when fn returns nil and rolls back on an error or a panic.
func (u UnitOfWork) Do(ctx context.Context, fn func(Repos) error) (err error) {
	tx, err := u.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			panic(p)
		}
		if err != nil {
			_ = tx.Rollback()
		}
	}()
	if err = fn(Repos{Accounts{tx}, Ledger{tx}}); err != nil {
		return err
	}
	return tx.Commit()
}

func total(ctx context.Context, db *sql.DB) (sum, entries int) {
	_ = db.QueryRowContext(ctx, "select coalesce(sum(balance), 0) from accounts608").Scan(&sum)
	_ = db.QueryRowContext(ctx, "select count(*) from ledger608").Scan(&entries)
	return
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	reset := func() {
		for _, q := range []string{
			"drop table if exists ledger608", "drop table if exists accounts608",
			"create table accounts608 (id bigint primary key, balance int not null check (balance >= 0))",
			"create table ledger608 (id bigserial primary key, account_id bigint not null references accounts608 deferrable initially deferred, delta int not null)",
			"insert into accounts608 values (1, 100), (2, 100)",
		} {
			if _, err := db.ExecContext(ctx, q); err != nil {
				log.Fatal(err)
			}
		}
	}
	report := func(label string) {
		s, e := total(ctx, db)
		fmt.Printf("%-44s total balance %d, ledger rows %d\n", label, s, e)
	}

	// 1. Two repository calls, two transactions: the second fails after the first committed.
	reset()
	acc, led := Accounts{db}, Ledger{db}
	err = acc.Add(ctx, 1, -30)
	if err == nil {
		err = acc.Add(ctx, 99, +30) // an account that does not exist
	}
	if err == nil {
		_ = led.Record(ctx, 1, -30)
	}
	fmt.Println("no unit of work, error:", err)
	report("1. without a unit of work")

	// 2. The same steps in one unit of work.
	reset()
	uow := UnitOfWork{db}
	err = uow.Do(ctx, func(r Repos) error {
		if err := r.Accounts.Add(ctx, 1, -30); err != nil {
			return err
		}
		if err := r.Accounts.Add(ctx, 99, +30); err != nil {
			return err
		}
		return r.Ledger.Record(ctx, 1, -30)
	})
	fmt.Println("unit of work, error:", err)
	report("2. inside a unit of work")

	// 3. The success path.
	err = uow.Do(ctx, func(r Repos) error {
		if err := r.Accounts.Add(ctx, 1, -30); err != nil {
			return err
		}
		if err := r.Accounts.Add(ctx, 2, +30); err != nil {
			return err
		}
		return r.Ledger.Record(ctx, 1, -30)
	})
	fmt.Println("unit of work, error:", err)
	report("3. a transfer that succeeds")

	// 4. A business rule enforced by the database: the check constraint fails inside the transaction.
	err = uow.Do(ctx, func(r Repos) error { return r.Accounts.Add(ctx, 1, -500) })
	fmt.Println("overdraw, error:", err)
	report("4. after the rejected overdraw")

	// 5. A deferred constraint fails at Commit, not at the statement.
	err = uow.Do(ctx, func(r Repos) error { return r.Ledger.Record(ctx, 77, 5) })
	fmt.Println("ledger row for a missing account, error:", err)
	report("5. after the commit-time failure")

	// 6. A panic inside the function still rolls back.
	func() {
		defer func() { fmt.Println("recovered:", recover()) }()
		_ = uow.Do(ctx, func(r Repos) error {
			_ = r.Accounts.Add(ctx, 1, -10)
			panic("boom")
		})
	}()
	report("6. after a panic")
}

no unit of work, error: no such account
1. without a unit of work                    total balance 170, ledger rows 0
unit of work, error: no such account
2. inside a unit of work                     total balance 200, ledger rows 0
unit of work, error: <nil>
3. a transfer that succeeds                  total balance 200, ledger rows 1
overdraw, error: ERROR: new row for relation "accounts608" violates check constraint "accounts608_balance_check" (SQLSTATE 23514)
4. after the rejected overdraw               total balance 200, ledger rows 1
ledger row for a missing account, error: ERROR: insert or update on table "ledger608" violates foreign key constraint "ledger608_account_id_fkey" (SQLSTATE 23503)
5. after the commit-time failure             total balance 200, ledger rows 1
recovered: boom
6. after a panic                             total balance 200, ledger rows 1

Four parts matter.

DBTX is the interface that makes repositories transaction agnostic. Accounts and Ledger hold a DBTX, so Accounts{db} works against the pool and Accounts{tx} works inside a transaction. The repositories contain no Begin, Commit or Rollback. They do not know whether they are inside one.

Repos is a bundle of repositories constructed over one DBTX. It is what a unit of work gives its function: every repository the operation might need, all bound to the same transaction.

UnitOfWork.Do is the whole mechanism. It begins a transaction, builds Repos over the *sql.Tx, calls the function, and then decides. A nil return commits. An error rolls back and is returned. A panic rolls back and is re-raised, so the caller still sees the panic and nothing is half committed. The named return value err is what lets the deferred function see the final outcome, including a failure from Commit.

The service code is what the application author writes: a function passed to Do that calls repository methods and returns the first error. It never touches a transaction. That is the point of the pattern: the rule "all of this or none of it" is expressed by writing the steps inside one Do.

What the run shows

The program resets two accounts to 100 each and runs six scenarios. The columns report the total balance and the number of ledger rows afterwards.

1. Without a unit of work. The debit of 30 on account 1 committed, and the credit to account 99, which does not exist, failed with no such account. Total balance: 170. Thirty units of money vanished, and no ledger row explains where. This is not a contrived failure: any error between two statements, a constraint violation, a timeout or a crash of the process, leaves the same result.
2. Inside a unit of work. The same two steps and the ledger insert, but the credit failed and the function returned the error. Total balance: 200, ledger rows: 0. The debit was rolled back with everything else.
3. A transfer that succeeds. Account 1 to account 2, with a ledger row. Total: 200, ledger rows: 1. The transaction committed and all three effects are visible together.
4. A rejected overdraw. Taking 500 from an account with 70 violates the balance >= 0 check. The error is violates check constraint "accounts608_balance_check" with SQLSTATE 23514, and the state afterwards is unchanged. The database enforces the invariant even if the application forgot to.
5. A failure at commit time. The ledger table has a foreign key declared deferrable initially deferred, so inserting a row for account 77 passes the statement and fails when the transaction commits. The error reports violates foreign key constraint with SQLSTATE 23503 and comes from tx.Commit(). Any code that ignores the result of Commit would report success for a write that did not happen.
6. A panic. The function debited 10 and then panicked. The unit of work rolled back and re-raised, the caller recovered, and the total stayed at 200.

Scenarios 5 and 6 are the reason to write Do once and carefully instead of repeating the begin and commit lines in every service method. Hand rolled transaction code tends to get the happy path and the plain error path right and forget the commit error and the panic.

Where the boundary goes

The boundary belongs around one business operation: usually one service method, usually one HTTP request or one message handled. A few rules keep it in the right place.

• The service layer opens it, not the repository. A repository method does not know what else the operation will do. If it commits, the operation can no longer be undone.
• One operation, one transaction. Do not open a transaction in a handler and pass it through three layers by hand. Pass the function to Do and let the unit of work carry it.
• Do the reads that decide the writes inside it. A check that reads a balance outside the transaction and a write inside it can be invalidated between the two. The reads that justify the writes go in the same transaction, with the isolation level and locking the operation needs: see optimistic locking and pessimistic locking with SELECT FOR UPDATE.
• Keep it short. A transaction holds a pooled connection and its locks until it ends. Everything slow, such as HTTP calls, sending mail or rendering, belongs outside.

That last point has a sharp corner. Do not call an external service inside Do. If the HTTP call succeeds and the commit then fails, the world has been told about something that did not happen. If you call it after the commit and the process dies in between, the world is never told. Writing the event into the same transaction and delivering it afterwards solves both, and is the subject of the outbox pattern.

Passing the transaction without a bundle

The Repos bundle is explicit and easy to follow, and the cost is that adding a repository means adding a field. The alternative is to carry the transaction in the context. A helper conn(ctx, db) returns the *sql.Tx stored in the context if there is one and the pool otherwise, and each repository calls it at the top of every method. Repositories then keep a *sql.DB and work in or out of a transaction on their own, without changing signatures, and a unit of work becomes a function that puts a transaction in the context and calls the function with it.

It is less typing and it hides a dependency: a repository's behaviour now depends on a value somewhere up the call stack, and a method called with a context that lost the transaction (a goroutine started with a fresh context.Background(), say) silently runs outside it. Both designs work. Choose the explicit bundle for small services where the compiler should tell you what a function touches, and the context carried version when a codebase is large enough that threading Repos is the larger cost.

Nested units of work

Calling Do inside Do is the classic trap. database/sql has no nested transactions, so the inner Do opens a second, independent transaction on another connection. It commits on its own, and the outer one can then roll back without it. Two ways out:

1. Do not nest. Make the inner function accept Repos, not a unit of work, so it joins the caller's transaction. This is nearly always the right answer.
2. Use a savepoint when a part of the operation may fail and the rest should continue. SAVEPOINT name and ROLLBACK TO SAVEPOINT name are plain statements that run on the same *sql.Tx. Wrap them in a helper that takes Repos and a function, and rolls back to the savepoint on error. Use it sparingly: an operation that needs partial failure is often two operations.

An error returned from inside a PostgreSQL transaction also puts the transaction in an aborted state: every later statement fails with current transaction is aborted until a rollback. Ignoring a statement's error and carrying on, as scenario 1 did to its own detriment, therefore breaks even the successful path inside a transaction. Return the first error.

Isolation, retries and read only work

The unit of work's BeginTx takes *sql.TxOptions with an isolation level and a read only flag. The lab used the default, which on PostgreSQL is Read Committed. If an operation needs Serializable, the unit of work is also the right place for the retry loop: a serialization failure rolls the transaction back, and the whole function must run again from the start. Because the function is a value, retrying is a loop around Do. The loop and the error code to look for are in retrying serialization failures, and the anomalies each level allows are measured in transaction isolation levels.

For reports and reads that need a consistent snapshot of several tables, a ReadOnly transaction at Repeatable Read gives one snapshot without blocking writers. It is worth a second method on the unit of work, View, so read paths do not accidentally write.

Testing it

A unit of work is straightforward to test with a real database, and that is where its tests belong: run the failing and succeeding scenarios above against PostgreSQL and assert on the state afterwards, as the lab does. For the service code, two options:

• Run service tests against a real unit of work and a test database, and roll the work back at the end if you want isolation. The techniques are in rollback tests.
• Give the service a Do function through a small interface, and in unit tests supply one that calls the function with in-memory repositories. This tests the logic and the order of calls, not the atomicity, which only a real database can check.

Do both. The fake based tests are quick and cover branching; the real ones are few and cover the boundary.

Naming and sizing the operation

One last design question is how much goes inside a single Do. The answer that holds up is: what the business would describe as one thing. "Place an order" is one operation even though it writes an order, its lines, a stock reservation and an outbox event. "Place an order and email the customer and refresh the recommendations" is three operations joined by a sentence, and only the first needs atomicity. When a function passed to Do grows past a screen, look for a step that could happen afterwards from a queue.

Name service methods for the operation, and keep the transaction an implementation detail of them. A caller of PlaceOrder should not know or care whether it is one transaction or three, which leaves room to change the design later without touching handlers. Log the operation name, the duration and the outcome at the unit of work boundary: it is the one place where every transaction in the service passes, so it is where a slow or failing operation is easiest to see.

When you do not need it

A handler that issues one statement is already atomic, and wrapping it in a unit of work adds a round trip for BEGIN and COMMIT for nothing. Reads that need no consistency across statements do not need a transaction either. Services whose operations are all single table writes can use the pool directly. Reach for the pattern when an operation must change two or more rows that have to agree, or when a decision depends on data it must read and write consistently.

The pieces in this article are the standard library's: database/sql transactions are documented by the Go team, and PostgreSQL's chapter on transaction isolation explains the levels the options can select. For libraries that generate code from SQL, the same shape appears as a WithTx method on the generated queries, covered in sqlc transactions.