Optimistic Locking in Go With Version Columns and Retries

Two people open the same record, both change it and both press save. Without protection the second save silently overwrites the first, and nobody is told. This is the lost update, and it is common in any system where a read and the write that depends on it are separate steps. Optimistic locking prevents it without holding any lock between the read and the write: you assume nobody else will interfere, and you check at write time that the row is still the one you read. This article implements it in Go 1.25.6 with PostgreSQL 18.6, measures the lost updates it stops and what retries cost, and shows when a plain atomic update is the better tool.

It is a technique from the database cornerstone. Its counterpart, which locks the row while you work, is pessimistic locking with SELECT FOR UPDATE.

How it works

Add an integer version column to the table, starting at zero. Every writer follows the same three steps:

1. Read the row, including its version.
2. Decide the new values in application code, with no lock held.
3. Write with a guard: update ... set ..., version = version + 1 where id = $1 and version = $2, with the version from step 1.

If another writer changed the row in between, its update bumped the version, so your guard matches zero rows. Zero rows affected means "someone got there first". You then read again, redo the decision with the fresh data and try again, or report a conflict to the caller. Nothing was blocked and nothing was overwritten.

The experiment

Eight goroutines each add one to a counter 50 times, so the correct total is 400. Each one reads the value, adds one in Go and writes it back. That read, add and write cycle is the lost update in its simplest form. The program runs it three ways.

package main

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

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

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

const workers, perWorker = 8, 50

func reset(ctx context.Context, db *sql.DB) {
	for _, q := range []string{
		"drop table if exists counters610",
		"create table counters610 (id int primary key, n int not null, version int not null default 0)",
		"insert into counters610 (id, n) values (1, 0)",
	} {
		if _, err := db.ExecContext(ctx, q); err != nil {
			log.Fatal(err)
		}
	}
}

func run(name string, db *sql.DB, step func(ctx context.Context) (retries int)) {
	ctx := context.Background()
	reset(ctx, db)
	var retries atomic.Int64
	var wg sync.WaitGroup
	start := time.Now()
	for w := 0; w < workers; w++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for i := 0; i < perWorker; i++ {
				retries.Add(int64(step(ctx)))
			}
		}()
	}
	wg.Wait()
	var n, version int
	_ = db.QueryRowContext(ctx, "select n, version from counters610 where id = 1").Scan(&n, &version)
	fmt.Printf("%-34s final n=%d (want %d), version=%d, retries=%d, %v\n",
		name, n, workers*perWorker, version, retries.Load(), time.Since(start).Round(time.Millisecond))
}

func main() {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(workers)

	// Read, add in Go, write back: the last writer wins and increments disappear.
	run("read-modify-write, no version", db, func(ctx context.Context) int {
		var n int
		if err := db.QueryRowContext(ctx, "select n from counters610 where id = 1").Scan(&n); err != nil {
			log.Fatal(err)
		}
		if _, err := db.ExecContext(ctx, "update counters610 set n = $1 where id = 1", n+1); err != nil {
			log.Fatal(err)
		}
		return 0
	})

	// The same, guarded by the version the row had when it was read. Zero rows updated means someone else got there first.
	run("optimistic: where version = $read", db, func(ctx context.Context) int {
		retries := 0
		for {
			var n, v int
			if err := db.QueryRowContext(ctx, "select n, version from counters610 where id = 1").Scan(&n, &v); err != nil {
				log.Fatal(err)
			}
			res, err := db.ExecContext(ctx, "update counters610 set n = $1, version = version + 1 where id = 1 and version = $2", n+1, v)
			if err != nil {
				log.Fatal(err)
			}
			if rows, _ := res.RowsAffected(); rows == 1 {
				return retries
			}
			retries++
		}
	})

	// When the change is a delta, the database can do it atomically and no version is needed.
	run("atomic: set n = n + 1", db, func(ctx context.Context) int {
		if _, err := db.ExecContext(ctx, "update counters610 set n = n + 1, version = version + 1 where id = 1"); err != nil {
			log.Fatal(err)
		}
		return 0
	})
}

read-modify-write, no version      final n=71 (want 400), version=0, retries=0, 142ms
optimistic: where version = $read  final n=400 (want 400), version=400, retries=1760, 672ms
atomic: set n = n + 1              final n=400 (want 400), version=400, retries=0, 152ms

Without a version. The workers read, compute n+1 and write the result unconditionally. The final value was 71 where 400 were expected: 329 increments vanished, and no call returned an error. Each worker overwrote others' writes with a value computed from stale data. The database did exactly what it was told.

With the version guard. Each worker reads n and version, then runs update ... where id = 1 and version = $2. When RowsAffected is not 1, the worker loops and reads again. The final value was 400, with version=400, one bump per successful write. There were 1,760 retries across the 400 successes, an average of 4.4 per increment. Nothing was lost; the conflicts were detected and redone.

With an atomic update. set n = n + 1 lets the database read and write in one statement. The final value was 400 with no retries and no version needed, in 152ms.

Reading the numbers

The guard is correct, and the cost is visible. The guarded run took 672ms against 142ms for the broken version and 152ms for the atomic one, because eight workers fighting for one row mostly lose and repeat. This workload is the worst case for optimistic locking: all writers hit the same row all the time. The method assumes conflicts are rare. With 100,000 distinct rows and the same eight workers, nearly every first attempt would succeed and the retries would be close to zero.

That gives the rule for choosing:

An atomic update beats a version check whenever it can express the change, because it is one statement and has nothing to retry.

Writing it in Go

The guarded update is a few lines. Keep it as a method that returns a typed error so callers can decide.

var ErrConflict = errors.New("row was changed by someone else")

func (s Store) Save(ctx context.Context, id int64, n, version int) error {
	res, err := s.db.ExecContext(ctx,
		"update counters set n = $1, version = version + 1 where id = $2 and version = $3",
		n, id, version)
	if err != nil {
		return err
	}
	if rows, _ := res.RowsAffected(); rows == 0 {
		return ErrConflict
	}
	return nil
}

Three habits matter.

Always check RowsAffected. A guarded update that matches nothing is not an error to the driver. It returns success with zero rows, which is the only sign of the conflict. Code that ignores the count has the lost update again, with a version column that looks like protection.

Keep the version in the thing you pass around. The version read must travel with the data to the point of writing, whether that is a function argument or a field on a struct. For a web form that means a hidden field, or an ETag and If-Match pair. Server side, a stale If-Match becomes a 412 Precondition Failed or a 409 Conflict, and the client can show the user what changed. The version is also the natural place to implement HTTP's conditional requests.

Bump the version on every write, from every code path. One path that updates the row without incrementing it breaks the guard for everyone. Put the increment in a database trigger if several programs write the table, so it cannot be forgotten.

Retry or report

When the guard fails, there are two correct responses and the choice depends on who decided the new value.

• Retry automatically when the new value is computed from the old one by code, as in the counter or a stock decrement. Read again, recompute, write. Bound the loop (a handful of attempts) and add a small random delay, because the lab's 4.4 retries per success are partly a stampede.
• Report the conflict when a human decided the new value from what they saw. Retrying silently would apply their change on top of data they never looked at. Return a conflict with the current state so they can merge.

A bounded retry helper looks like this:

func retry(attempts int, fn func() error) error {
	var err error
	for i := 0; i < attempts; i++ {
		if err = fn(); !errors.Is(err, ErrConflict) {
			return err
		}
		time.Sleep(time.Duration(rand.IntN(5*(i+1))) * time.Millisecond)
	}
	return err
}

fn must read, decide and write again each time. A retry that reuses the old read repeats the same failure forever.

Alternatives to a version column

• A timestamp (updated_at) can serve as the version. It works until two writes land in the same tick or the clock steps back, so an integer is safer.
• PostgreSQL's xmin system column changes whenever a row is updated and can act as a free version, but it is an implementation detail, it is not stable across some operations such as dump and restore, and the portable column is easier to reason about.
• Comparing every column (where a = $old_a and b = $old_b ...) needs no new column and gets unwieldy and slow, and it misses a change that went away and came back. It is the technique some ORMs use when you do not give them a version.
• Serializable transactions let the database detect the conflict instead, with retries on serialization failures; see retrying serialization failures and isolation levels. They protect every query without a version column and cost throughput under contention.

When not to use it

Do not use optimistic locking where the writers always conflict, as in the hot counter above, where an atomic statement or a queue is right. Do not use it when a lost retry is expensive and the work between the read and the write is long and has side effects, because the retry repeats those side effects. And do not mix it with code that updates the row without the guard: the protection is only as strong as the weakest writer. The PostgreSQL manual's chapter on concurrency control explains the behaviour of concurrent updates underneath, and the RowsAffected contract is in the sql.Result documentation.