Pessimistic Locking and SELECT FOR UPDATE in Go Programs

Optimistic locking, covered in the previous article, lets writers collide and detects it afterwards. Pessimistic locking prevents the collision: a transaction that is about to read something and then change it locks the rows first, and everyone else who wants them waits. In PostgreSQL the tool is SELECT ... FOR UPDATE. This article shows what it fixes with an overselling experiment on Go 1.25.6 and PostgreSQL 18.6, then covers the two variants that avoid waiting, NOWAIT and SKIP LOCKED, and the deadlocks that follow when two transactions lock in different orders.

It is one of the techniques of the database cornerstone and is used inside the transaction boundaries described in unit of work.

The problem: check, then act

A shop has ten units of a product. The code to reserve one reads the stock, checks it is above zero, does some other work and writes the new stock. Between the read and the write, other transactions do the same, and each of them sees the same old number. With a read committed transaction, which is PostgreSQL's default, nothing stops that.

package main

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

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

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

func must(db *sql.DB, qs ...string) {
	for _, q := range qs {
		if _, err := db.Exec(q); err != nil {
			log.Fatalf("%s: %v", q, err)
		}
	}
}

// reserve takes one unit if any is left. lock decides whether the read takes a row lock.
func reserve(ctx context.Context, db *sql.DB, lock bool) (bool, error) {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return false, err
	}
	defer tx.Rollback()
	q := "select stock from products611 where id = 1"
	if lock {
		q += " for update"
	}
	var stock int
	if err := tx.QueryRowContext(ctx, q).Scan(&stock); err != nil {
		return false, err
	}
	if stock <= 0 {
		return false, nil
	}
	time.Sleep(2 * time.Millisecond) // the application checks something else before committing
	if _, err := tx.ExecContext(ctx, "update products611 set stock = $1 where id = 1", stock-1); err != nil {
		return false, err
	}
	return true, tx.Commit()
}

func sell(db *sql.DB, lock bool) (sold int64, left int) {
	must(db, "update products611 set stock = 10 where id = 1")
	var n atomic.Int64
	var wg sync.WaitGroup
	for w := 0; w < 8; w++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for {
				ok, err := reserve(context.Background(), db, lock)
				if err != nil {
					log.Fatal(err)
				}
				if !ok {
					return
				}
				n.Add(1)
			}
		}()
	}
	wg.Wait()
	_ = db.QueryRow("select stock from products611 where id = 1").Scan(&left)
	return n.Load(), left
}

func main() {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(16)
	must(db, "drop table if exists products611", "create table products611 (id int primary key, stock int not null)", "insert into products611 values (1, 10), (2, 10)")

	for _, lock := range []bool{false, true} {
		sold, left := sell(db, lock)
		fmt.Printf("for update=%-5v 10 in stock, 8 workers sold %d, stock left %d\n", lock, sold, left)
	}

	// NOWAIT and SKIP LOCKED against a row someone else holds.
	ctx := context.Background()
	holder, _ := db.BeginTx(ctx, nil)
	defer holder.Rollback()
	_, _ = holder.ExecContext(ctx, "select * from products611 where id = 1 for update")

	var pgErr *pgconn.PgError
	var id int
	err = db.QueryRowContext(ctx, "select id from products611 where id = 1 for update nowait").Scan(&id)
	if errors.As(err, &pgErr) {
		fmt.Printf("nowait on a locked row: SQLSTATE %s %s\n", pgErr.Code, pgErr.Message)
	}
	rows, _ := db.QueryContext(ctx, "select id from products611 order by id for update skip locked")
	var got []int
	for rows.Next() {
		_ = rows.Scan(&id)
		got = append(got, id)
	}
	rows.Close()
	fmt.Println("skip locked while row 1 is held returned ids:", got)
	holder.Rollback()

	// Two transactions locking the same two rows in opposite orders.
	var wg sync.WaitGroup
	results := make([]error, 2)
	for i, order := range [][2]int{{1, 2}, {2, 1}} {
		wg.Add(1)
		go func() {
			defer wg.Done()
			tx, _ := db.BeginTx(ctx, nil)
			defer tx.Rollback()
			if _, err := tx.ExecContext(ctx, "select * from products611 where id = $1 for update", order[0]); err != nil {
				results[i] = err
				return
			}
			time.Sleep(200 * time.Millisecond)
			if _, err := tx.ExecContext(ctx, "select * from products611 where id = $1 for update", order[1]); err != nil {
				results[i] = err
				return
			}
			results[i] = tx.Commit()
		}()
	}
	wg.Wait()
	for i, err := range results {
		if errors.As(err, &pgErr) {
			fmt.Printf("transaction %d (order %v): SQLSTATE %s %s\n", i+1, [][2]int{{1, 2}, {2, 1}}[i], pgErr.Code, pgErr.Message)
		} else {
			fmt.Printf("transaction %d (order %v): err=%v\n", i+1, [][2]int{{1, 2}, {2, 1}}[i], err)
		}
	}
}

for update=false 10 in stock, 8 workers sold 68, stock left 0
for update=true  10 in stock, 8 workers sold 10, stock left 0
nowait on a locked row: SQLSTATE 55P03 could not obtain lock on row in relation "products611"
skip locked while row 1 is held returned ids: [2]
transaction 1 (order [1 2]): err=<nil>
transaction 2 (order [2 1]): SQLSTATE 40P01 deadlock detected

reserve runs in a transaction. With lock set to false it reads select stock from products611 where id = 1. With it set to true the same statement ends with for update. After the read it sleeps two milliseconds, standing in for the other checks a real handler makes, and writes stock - 1 back. sell resets the stock to ten and starts eight workers that call reserve until it returns false.

The output starts with the two runs:

• Without a lock: 68 units sold, stock left 0. Ten units existed. The workers sold 68. Each read the stock, saw a positive number and wrote back its own number minus one. Many of them read the same number and wrote the same result, so the stock went down slowly while the sales counter went up fast. No statement failed.
• With for update: 10 sold, stock left 0. The second worker's select ... for update waited until the first committed, then read the new value. Every worker saw the stock as it really was when it decided.

Without the lock the final stock is correct, which hides the bug in a spot check of the table. The error is in the sales, not the stock.

What the lock does

FOR UPDATE locks every row the query returns, until the transaction commits or rolls back. Another transaction trying to FOR UPDATE, UPDATE or DELETE the same row waits. Plain SELECT is not affected, so readers are never blocked. Three things to know:

• The lock covers returned rows only. select ... where stock > 0 for update locks the matching rows, and a row inserted later that would match is not locked. Locking protects existing rows, not a range.
• The check must be in the same transaction as the write. The lock is released at commit. A read in one transaction and a write in the next is no protection.
• After waiting, the row is read again. In read committed mode, a transaction that waited for a lock re-evaluates its where clause on the updated row. That is why the second worker saw the new stock. It is also why a locked select with a condition and a limit can return nothing after a wait, even though other rows match; see the queue discussion in building a job queue with SKIP LOCKED.

There are weaker variants for narrower needs. FOR NO KEY UPDATE is what update takes on a row that does not change a key column and it does not conflict with foreign key checks from other tables. FOR SHARE and FOR KEY SHARE block writers while allowing other readers of the lock. Use FOR UPDATE when you will change the row, and FOR NO KEY UPDATE when you will update non key columns of a table that other tables reference and you want fewer collisions.

Not waiting: NOWAIT and SKIP LOCKED

Waiting is sometimes the wrong behaviour. Two clauses change it.

for update nowait raises an error immediately if any row is locked. In the lab a transaction held row 1, and a second statement asking for update nowait for it failed with SQLSTATE 55P03, could not obtain lock on row in relation "products611". Use it when a clear failure is better than a hung request: an admin action on a record someone else is editing can say "in use" at once.

for update skip locked skips rows that are locked and returns the rest. With row 1 held, select id from products611 order by id for update skip locked returned [2]. This is how several workers take different items from one table without blocking each other. It gives an inconsistent view on purpose, so it suits queues and work distribution and is wrong for anything that needs every row. It is the technique behind a job queue on Postgres.

In Go, 55P03 arrives as a *pgconn.PgError and is checked with errors.As, the same way as other server errors; handling database errors shows how to turn codes into your own error types.

Deadlocks

Locks bring one more failure. If transaction A locks row 1 and then wants row 2, while transaction B has locked row 2 and wants row 1, neither can proceed. The last block of the program does exactly that, with a 200 millisecond pause between the two locks so both reach the second one.

PostgreSQL notices the cycle after a moment (the deadlock_timeout, one second by default) and aborts one of the transactions. In the recorded run transaction 2 failed with SQLSTATE 40P01, deadlock detected, and transaction 1 committed. Which one is chosen is not something to rely on. The aborted transaction's work is rolled back and it can be retried.

Two rules prevent almost all of them:

1. Lock in a fixed order. If every transaction that touches rows 1 and 2 takes them in ascending id order, a cycle cannot form. For a transfer between two accounts, lock min(a, b) first. With several rows, select ... where id = any($1) order by id for update locks them in order in one statement.
2. Keep transactions short. The longer locks are held, the more likely a cycle. Do not wait on a network call while holding a row lock.

Still handle 40P01 when it happens, because a stray ordering difference somewhere will eventually cause one. Treat it like a serialization failure: roll back and run the whole transaction again, a limited number of times, as in retrying serialization failures.

Choosing between locking styles

The second row deserves emphasis. The overselling bug disappears with one statement, update products set stock = stock - 1 where id = 1 and stock > 0, and you read RowsAffected to see if it worked. No lock call, no transaction and no wait beyond the row's own lock inside the statement. Use for update when the decision needs more than the single row's value, such as checking a balance across several accounts, or reading data that decides what to write.

Costs and limits

Locks serialise access, so a hot row becomes a queue. If a thousand requests a second contend for one row and each holds the lock for 5ms, throughput on that row is 200 a second and the rest wait. Shorten the critical section, shard the contended row (several stock rows per product, summed on read), or move the decision out of the database into an in-memory structure or a queue. Locks also stay until the transaction ends, so an idle transaction holding one, from a forgotten Rollback or a handler that crashed, blocks everyone. Set idle_in_transaction_session_timeout on the server and a context deadline in the client; query timeouts covers the client side.

The behaviour of each lock mode is specified in the PostgreSQL manual's page on explicit locking, and the clauses in the SELECT reference.