How to Implement Optimistic Locking in Go

Web Database
2026-04-17T12:05:42+00:00
Implement optimistic locking in Go by adding a version column to your database and checking it in the WHERE clause of your UPDATE statement to prevent concurrent overwrites.

Implement optimistic locking in Go by adding a version field to your data model and checking it before writing updates.

import (
	"database/sql"
	"errors"
)

type Item struct {
	ID       int64
	Version  int64
	Data     string
}

func UpdateItem(db *sql.DB, item *Item) error {
	_, err := db.Exec(
		"UPDATE items SET data = $1, version = version + 1 WHERE id = $2 AND version = $3",
		item.Data, item.ID, item.Version,
	)
	if err != nil {
		return err
	}
	if item.Version == 0 {
		item.Version = 1
	} else {
		item.Version++
	}
	return nil
}

func GetItem(db *sql.DB, id int64) (*Item, error) {
	var item Item
	err := db.QueryRow("SELECT id, version, data FROM items WHERE id = $1", id).Scan(&item.ID, &item.Version, &item.Data)
	if err == sql.ErrNoRows {
		return nil, errors.New("not found")
	}
	return &item, err
}
  1. Fetch the current item including its version.
  2. Modify the item data in memory.
  3. Execute an update query that only succeeds if the stored version matches the fetched version.
  4. Increment the version in your local struct if the update succeeds.