Optimistic locking with a version column

8873 views

                  ALTER TABLE documents ADD COLUMN version BIGINT NOT NULL DEFAULT 0;

                  package store

import (
  "context"
  "errors"

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

var ErrConflict = errors.New("conflict")

func UpdateDoc(ctx context.Context, db *pgxpool.Pool, id string, version int64, body string) error {
  ct, err := db.Exec(ctx,
    `UPDATE documents SET body=$3, version=version+1 WHERE id=$1 AND version=$2`,
    id, version, body,
  )
  if err != nil {
    return err
  }
  if ct.RowsAffected() == 0 {
    return ErrConflict
  }
  return nil
}

When multiple clients can update the same record, I prefer optimistic locking over heavy row locks. The idea is simple: every row has a version that increments on each update. The update statement includes WHERE id=$1 AND version=$2, so if someone else updated the row first, your update affects zero rows and you return a 409 Conflict. This is a clean, DB-native way to detect lost updates without forcing a transaction to hold locks while the client thinks. The operational benefit is better concurrency under load, especially for “edit settings” style endpoints. In practice, the API returns the latest record so the client can re-apply changes or show a merge UI. It’s a small schema change with a big correctness payoff.

Leah Thompson

More from Leah Thompson

Optimistic locking with a version column

0 Comments

More from Leah Thompson