GORM Locking With Optimistic and Pessimistic Examples

Two requests read the same row, both add one, both write it back, and one increment disappears. Nothing fails, no error is logged, and the data is wrong. This is the lost update, and it is the concurrency bug that ORM code produces most often, because Find followed by Save reads like a single operation. GORM gives you three defences: a database lock, an atomic statement, and a version check. This article runs 40 concurrent workers against each on PostgreSQL 18.6 and reports the counts.

It builds on transactions, because every lock lives inside one. The same ideas without an ORM are in SELECT FOR UPDATE in Go and optimistic locking in Go, and the queue pattern is covered in depth in a PostgreSQL queue with SKIP LOCKED.

The lab

The program runs 40 goroutines at a time against a Counter row and a Job table, six times, and prints how many increments survived:

package main

import (
	"fmt"
	"gorm.io/gorm/clause"
	"log"
	"strings"
	"sync"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

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

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

type Counter struct {
	ID      uint
	Hits    int
	Version int
}

type Job struct {
	ID    uint
	State string
}

const workers = 40

func main() {
	db := connect("l0719", &gorm.Config{Logger: logger.Discard})
	must(db.AutoMigrate(&Counter{}, &Job{}))
	db.Create(&Counter{ID: 1})

	run := func(fn func()) {
		var wg sync.WaitGroup
		for i := 0; i < workers; i++ {
			wg.Add(1)
			go func() { defer wg.Done(); fn() }()
		}
		wg.Wait()
	}
	hits := func() int {
		var c Counter
		db.First(&c, 1)
		return c.Hits
	}

	// 1. Read, add one in Go, write back: updates are lost.
	run(func() {
		var c Counter
		db.First(&c, 1)
		db.Model(&c).Update("hits", c.Hits+1)
	})
	fmt.Println("read-modify-write, 40 workers:", hits(), "of 40")

	// 2. Same thing under SELECT ... FOR UPDATE.
	db.Model(&Counter{}).Where("id = 1").Update("hits", 0)
	run(func() {
		db.Transaction(func(tx *gorm.DB) error {
			var c Counter
			if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).First(&c, 1).Error; err != nil {
				return err
			}
			return tx.Model(&c).Update("hits", c.Hits+1).Error
		})
	})
	fmt.Println("FOR UPDATE, 40 workers:", hits(), "of 40")

	// 3. Or do it in the database in one statement.
	db.Model(&Counter{}).Where("id = 1").Update("hits", 0)
	run(func() { db.Model(&Counter{}).Where("id = 1").Update("hits", gorm.Expr("hits + 1")) })
	fmt.Println("hits = hits + 1, 40 workers:", hits(), "of 40")

	// 4. Optimistic locking: the version in the WHERE clause detects a concurrent write.
	db.Model(&Counter{}).Where("id = 1").Updates(map[string]any{"hits": 0, "version": 0})
	var conflicts, wins int
	var mu sync.Mutex
	run(func() {
		var c Counter
		db.First(&c, 1)
		res := db.Model(&Counter{}).Where("id = ? and version = ?", c.ID, c.Version).
			Updates(map[string]any{"hits": c.Hits + 1, "version": c.Version + 1})
		mu.Lock()
		defer mu.Unlock()
		if res.RowsAffected == 0 {
			conflicts++
		} else {
			wins++
		}
	})
	fmt.Println("optimistic: wins", wins, "conflicts", conflicts, "final hits", hits(), "wins+conflicts =", wins+conflicts)

	// 5. NOWAIT fails fast instead of waiting.
	tx1 := db.Begin()
	tx1.Clauses(clause.Locking{Strength: "UPDATE"}).First(&Counter{}, 1)
	err := db.Clauses(clause.Locking{Strength: "UPDATE", Options: "NOWAIT"}).First(&Counter{}, 1).Error
	fmt.Println("NOWAIT while locked:", strings.Contains(err.Error(), "55P03"), "|", strings.Split(err.Error(), " (")[0])
	tx1.Rollback()

	// 6. SKIP LOCKED hands each worker a different job.
	for i := 0; i < 8; i++ {
		db.Create(&Job{State: "new"})
	}
	claimed := make(chan int, 16)
	run8 := func() {
		db.Transaction(func(tx *gorm.DB) error {
			var j Job
			err := tx.Clauses(clause.Locking{Strength: "UPDATE", Options: "SKIP LOCKED"}).
				Where("state = ?", "new").Order("id").Limit(1).Take(&j).Error
			if err != nil {
				return nil // nothing left
			}
			claimed <- int(j.ID)
			return tx.Model(&j).Update("state", "taken").Error
		})
	}
	var wg sync.WaitGroup
	for i := 0; i < 8; i++ {
		wg.Add(1)
		go func() { defer wg.Done(); run8() }()
	}
	wg.Wait()
	close(claimed)
	seen := map[int]int{}
	for id := range claimed {
		seen[id]++
	}
	dups := 0
	for _, n := range seen {
		if n > 1 {
			dups++
		}
	}
	fmt.Println("SKIP LOCKED: jobs claimed", len(seen), "of 8, claimed twice:", dups)
}

read-modify-write, 40 workers: 2 of 40
FOR UPDATE, 40 workers: 40 of 40
hits = hits + 1, 40 workers: 40 of 40
optimistic: wins 3 conflicts 37 final hits 3 wins+conflicts = 40
NOWAIT while locked: true | ERROR: could not obtain lock on row in relation "counters"
SKIP LOCKED: jobs claimed 8 of 8, claimed twice: 0

The lost update

The first run reads the counter, adds one in Go and writes it back with Update("hits", c.Hits+1). Forty workers ran it at once and the final value was 2 of 40. Most workers read the same starting value (0 or 1), computed the same result and wrote it, so each overwrote the others. The exact number is not stable between runs; what is stable is that it is far below 40, and that no call returned an error.

Every First then Save or Update in a handler has this window. With one user it never shows. With a hundred requests a second touching the same row (a stock count, an account balance, a counter, a seat) it shows up as numbers that are quietly wrong. Keeping the pattern out of code that changes shared rows is the real fix, and the three tools below are the ways to do it.

Pessimistic locking: FOR UPDATE

tx.Clauses(clause.Locking{Strength: "UPDATE"}).First(&c, 1) adds FOR UPDATE to the select. It must run inside a transaction, because the lock lasts until commit or rollback. A second transaction that tries to lock the same row waits for the first to finish, so the read, the add and the write are serialised. The output: FOR UPDATE, 40 workers: 40 of 40. No increment was lost.

Properties to plan for:

• It serialises. Forty workers queue on one row. Throughput on that row is one transaction at a time, so keep the work inside the lock short: no network calls, no slow queries.
• Order of locking matters. Two transactions that lock rows A and B in opposite orders deadlock, and PostgreSQL aborts one with SQLSTATE 40P01. Lock rows in a consistent order (by id) and be ready to retry.
• Strength. clause.Locking{Strength: "SHARE"} takes FOR SHARE, which blocks writers and allows other readers, and "NO KEY UPDATE" is the weaker write lock that does not block foreign key checks on child rows. FOR UPDATE is the right default for read then modify.

NOWAIT

When waiting is worse than failing, Options: "NOWAIT" returns immediately if the row is locked. The lab held a lock in one transaction and tried a second: NOWAIT while locked: true | ERROR: could not obtain lock on row in relation "counters", with SQLSTATE 55P03. Use it for interactive paths where "someone else is editing this" is a better answer than a hung request. Pair it with a context timeout otherwise, since a FOR UPDATE without NOWAIT waits as long as the other transaction runs (see context timeouts).

SKIP LOCKED

Options: "SKIP LOCKED" skips rows another transaction holds. The lab inserted 8 jobs and started 8 workers, each claiming one with Where("state = ?", "new").Order("id").Limit(1) plus the lock. The result: jobs claimed 8 of 8, claimed twice: 0. Each worker received a different row, with no coordination beyond the database. This is the core of a queue in PostgreSQL: claim, mark, commit, and a crashed worker's transaction rolls back so its job becomes available again. It is also the one case where reading rows that "should" be locked is the wanted behaviour, since the skipped rows are being worked on.

The atomic statement

The simplest fix needs no lock. Update("hits", gorm.Expr("hits + 1")) sends UPDATE counters SET hits = hits + 1 WHERE id = 1. PostgreSQL takes a row lock for the update, applies it to the current value, and the next writer sees the result. The output: hits = hits + 1, 40 workers: 40 of 40.

Prefer this whenever the new value can be computed in SQL from the old one: counters, balances (balance - ?), stock (stock - ? with a Where("stock >= ?", n) guard, then check RowsAffected). It does not need a transaction, does not hold a lock across a round trip, and is the fastest option here. It cannot do read then decide logic that needs the old value in Go.

Optimistic locking

An optimistic lock assumes conflicts are rare and detects them instead of preventing them. The table has a version column. A writer reads the row and its version, computes the change, and updates with the version in the condition and incremented in the same statement:

res := db.Model(&Counter{}).
	Where("id = ? and version = ?", c.ID, c.Version).
	Updates(map[string]any{"hits": c.Hits + 1, "version": c.Version + 1})
if res.RowsAffected == 0 {
	// someone else changed the row since we read it
}

If another writer got there first, the version no longer matches, zero rows are updated and RowsAffected says so. In the lab the 40 workers produced wins 3 conflicts 37 final hits 3. That looks bad and it is correct: every success is counted exactly once (3 hits for 3 wins), and 37 workers were told to try again. Without the version check the same race gave 2 of 40 and nobody knew.

The caller owns the retry. A typical loop re-reads and re-applies up to a few times, with a short randomised pause, and then returns a conflict error (HTTP 409). Optimistic locking pays off when conflicts are rare (editing a document, a form saved from a browser tab) and holds no database lock while the user thinks. It performs badly on hot rows, as the 37 conflicts show, where a pessimistic lock or an atomic statement fits better.

The version column can be a gorm.io/plugin/optimisticlock field, which adds the condition for you. A hand written Where as above is three lines and nothing hidden. Use the same pattern for HTTP: return the version to the client as an ETag and require it back in If-Match.

Choosing

Pitfalls

• A lock outside a transaction does nothing useful. Without Transaction or Begin, the lock is taken and released within the single statement. GORM does not warn you.
• Locking through a join. FOR UPDATE on a join locks rows in every joined table unless you name one with OF. Use Options: "OF counters" or avoid the join.
• Lock timeouts. Set lock_timeout for the session or per transaction (SET LOCAL lock_timeout = '2s') so a stuck transaction elsewhere cannot hold your request forever.
• Isolation level. At REPEATABLE READ or SERIALIZABLE, PostgreSQL detects some conflicts itself and aborts with 40001. Those need a retry loop, which is the same loop an optimistic lock needs.

Checklist

• Never write First followed by Update(x+1) for shared state.
• Prefer one UPDATE with gorm.Expr when SQL can compute the change.
• Use FOR UPDATE inside a transaction, in a consistent order, with short work.
• Use a version column for long edits and treat RowsAffected == 0 as a conflict.
• Use SKIP LOCKED for queues and NOWAIT for interactive refusal.

The clauses are described in the GORM advanced query guide, and the lock modes in the PostgreSQL explicit locking chapter.