The lost update problem
Two developers pull the same configuration record from a database. Alice changes the timeout to thirty seconds. Bob changes the retry count to five. Alice saves first. Bob saves second. Bob's save overwrites Alice's timeout change because his query never knew she touched the row. The database happily accepts the update, and Alice's work vanishes without a trace. This is the lost update problem, and it happens constantly in concurrent systems.
Pessimistic locking solves it by freezing the row. You run SELECT ... FOR UPDATE, which tells the database to block every other transaction until you commit or roll back. It works, but it creates bottlenecks. Readers wait. Writers queue up. Throughput drops the moment contention spikes.
Optimistic locking takes a different approach. You attach a version counter to the row. You assume the data will not change while you work on it. Right before you write your changes back, you verify that the version number still matches what you read. If it matches, you update the row and bump the version. If it does not match, someone else already changed it, and your update fails. You retry or merge. No one waits. No one blocks. The database only serializes the final write, which takes microseconds.
Optimistic locking trades a potential retry for guaranteed throughput.
How the pattern works in Go
The entire mechanism lives in a single UPDATE statement. You fetch the row, modify it in memory, and execute an update that includes the version in the WHERE clause. The database engine evaluates the condition row by row. If the stored version matches your local copy, the engine applies the new data and increments the version. It returns one affected row. If the stored version differs, the WHERE clause fails, zero rows match, and the engine returns zero affected rows.
Go's database/sql package does not treat zero affected rows as an error. The Exec call succeeds silently. You must inspect the result yourself. This design choice keeps the driver fast and predictable. It also means you cannot rely on the database to panic on a conflict. You write the check.
The database does the comparison. Your code just checks the receipt.
The minimal pattern
Here is the core mechanism in its simplest form. The struct holds the version counter alongside the business data. The update function passes the local version to the query and verifies the outcome.
package main
import (
"database/sql"
"fmt"
)
// Item represents a database row with an optimistic version counter.
type Item struct {
ID int64
Version int64
Name string
}
// UpdateItem applies changes only if the version matches the database.
func UpdateItem(db *sql.DB, item *Item) error {
// The WHERE clause compares the stored version against the local copy.
res, err := db.Exec(
"UPDATE items SET name = $1, version = version + 1 WHERE id = $2 AND version = $3",
item.Name, item.ID, item.Version,
)
if err != nil {
return fmt.Errorf("update failed: %w", err)
}
// Check if the database actually modified a row.
affected, err := res.RowsAffected()
if err != nil {
return fmt.Errorf("check rows affected: %w", err)
}
if affected == 0 {
return fmt.Errorf("optimistic lock conflict: row was modified by another writer")
}
// Bump the local version to match the database state.
item.Version++
return nil
}
The version = version + 1 syntax in the SET clause is intentional. It tells the database to read the current value and increment it atomically. You do not need to calculate the next version in Go. The database handles it, and you only update your local struct after the write succeeds.
What happens under the hood
When the query reaches the database, the storage engine locks the target row for the duration of the statement. It reads the version column. It compares it to the third parameter. If they match, it writes the new name, increments version, releases the row lock, and returns 1 for affected rows. If they differ, it skips the row, releases the lock, and returns 0.
The Go driver receives the result code and wraps it in a *sql.Result. Your call to RowsAffected() reads that code. If you forget to check it, the function returns nil, your local struct keeps the stale version, and the next update will silently overwrite whatever happened in between. The compiler will not stop you. res.RowsAffected() returns two values, and Go allows you to ignore return values with _. If you write affected, _ := res.RowsAffected() and never check affected == 0, you have reintroduced the lost update problem.
Zero rows affected is not a bug. It is a signal.
Production-ready implementation
Real services need context propagation, explicit error types, and predictable retry behavior. Context always travels as the first parameter, conventionally named ctx. Functions that accept a context must respect cancellation and deadlines. You also want a distinct error type so callers can decide whether to retry automatically or surface a conflict message to the user.
Here is how the pattern looks in a service layer.
package service
import (
"context"
"database/sql"
"errors"
"fmt"
)
// ErrConflict signals that an optimistic lock check failed.
var ErrConflict = errors.New("optimistic lock conflict")
// ItemService handles database operations with context support.
type ItemService struct {
db *sql.DB
}
// SaveItem updates a record and returns a conflict error on version mismatch.
func (s *ItemService) SaveItem(ctx context.Context, item *Item) error {
// Context propagates cancellation and deadlines to the driver.
res, err := s.db.ExecContext(
ctx,
"UPDATE items SET name = $1, version = version + 1 WHERE id = $2 AND version = $3",
item.Name, item.ID, item.Version,
)
if err != nil {
return fmt.Errorf("db exec: %w", err)
}
// Read the outcome from the driver result.
affected, err := res.RowsAffected()
if err != nil {
return fmt.Errorf("rows affected: %w", err)
}
// Zero affected rows means the version check failed.
if affected == 0 {
return ErrConflict
}
// Keep the local struct in sync with the database.
item.Version++
return nil
}
The if err != nil { return err } pattern is verbose by design. The community accepts the boilerplate because it makes the unhappy path visible. Wrapping errors with %w preserves the original error chain, which lets callers use errors.Is() to detect ErrConflict later.
When a conflict occurs, the caller typically re-fetches the row, applies the new changes on top of the latest data, and retries. You must re-fetch. Retrying with the same stale version will loop forever. A simple retry loop looks like this:
func (s *ItemService) UpdateWithRetry(ctx context.Context, id int64, mutate func(*Item) error) error {
// Fetch the latest row to start with a clean version.
item, err := s.GetItem(ctx, id)
if err != nil {
return err
}
// Apply business logic to the fresh copy.
if err := mutate(item); err != nil {
return err
}
// Attempt the write.
if err := s.SaveItem(ctx, item); err == nil {
return nil
}
// On conflict, re-fetch and retry once.
item, err = s.GetItem(ctx, id)
if err != nil {
return err
}
if err := mutate(item); err != nil {
return err
}
return s.SaveItem(ctx, item)
}
Context is plumbing. Run it through every long-lived call site.
Common pitfalls and runtime behavior
The version field must be a monotonic counter. int64 is the standard choice. Do not use timestamps for optimistic locking. Clocks drift. Two writes in the same millisecond get the same timestamp. The WHERE clause passes, and you get a lost update. Counters increment by one. They never collide.
If you try to call RowsAffected() on a *sql.Rows instead of a *sql.Result, the compiler rejects the program with rows.RowsAffected undefined (type *sql.Rows has no field or method RowsAffected). Query returns rows. Exec returns a result. Use Exec for updates.
Another frequent mistake is forgetting to increment the local version after a successful write. The database bumps it, but your in-memory struct stays behind. The next update will carry the old version, fail the WHERE check, and return a conflict even though no one else touched the row. Always sync the local state immediately after RowsAffected() == 1.
Some developers try to handle conflicts by returning a generic database error. That breaks retry logic. Define a sentinel error like ErrConflict and check it with errors.Is(). The Go standard library expects this pattern. It keeps error handling explicit and testable.
If you delete rows, you still need the version check. The UPDATE pattern works for soft deletes too. Set a deleted_at timestamp and bump the version in the same statement. Hard deletes work the same way: DELETE FROM items WHERE id = $1 AND version = $2. Check RowsAffected() to confirm the row actually existed and matched the version.
The worst goroutine bug is the one that never logs. The same applies to silent overwrites. Log the conflict rate. If it spikes above one percent, your contention is too high for optimistic locking.
When to reach for optimistic locking
Concurrency control is a spectrum. Pick the constraint that matches your collision rate and latency requirements.
Use optimistic locking when read-heavy workloads dominate and conflicts are rare. Use pessimistic locking with SELECT ... FOR UPDATE when multiple writers constantly collide and you need strict serialization. Use application-level mutexes when all writers run in the same process and share in-memory state. Use plain sequential updates when data isolation is guaranteed by design, such as single-tenant queues or idempotent event processors.
Locking is a spectrum. Pick the constraint that matches your collision rate.