How does optimistic concurrency control (version columns) work in application code, and when does it beat pessimistic locking?

#Two philosophies for the same problem

Every concurrency-control scheme answers the same question: what happens when two transactions try to modify the same row at overlapping times? Pessimistic locking answers by preventing the conflict from ever occurring: SELECT ... FOR UPDATE takes a row lock at read time, and any other transaction wanting to write that row simply waits until the lock is released. Optimistic concurrency control (OCC) answers by allowing the conflict to occur and detecting it at write time instead: read the row — including a version marker — without locking anything, do your work, and when you write, make the write conditional on the version still matching what you originally read. If it doesn't match, someone else got there first; you lost the race, and you retry or fail.

#How the version column actually works

Add a version column (an incrementing integer is far more common and race-free than a timestamp) to the table. Every update does two things atomically, in a single round trip: it increments the version, and it constrains the WHERE clause to the version you last read.

UPDATE accounts
   SET balance = :newBalance, version = version + 1
 WHERE id = :accountId AND version = :versionYouRead

Because the increment-and-check happens inside one UPDATE ... WHERE version = ? statement, the database itself supplies the atomicity — you don't need an explicit transaction wrapping the read and the write purely for correctness against lost updates, because the compare-and-swap is embedded in the same statement as the write itself. It is effectively a database-native compare-and-swap:

• If the UPDATE affects exactly 1 row, your write won; the version moved forward under you, atomically.
• If it affects 0 rows, someone else updated (and re-versioned) the row between your read and your write — a conflict. Standard handling: reread the current row, reapply your intended change against the new state, retry the write, usually with a capped retry count and exponential backoff.

#Code: an update-with-retry loop

Wrap long lines
package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"
)

var ErrOptimisticLock = errors.New("optimistic lock conflict")

func UpdateBalance(ctx context.Context, db *sql.DB, accountID int64, delta float64, maxRetries int) error {
	for attempt := 0; attempt < maxRetries; attempt++ {
		var balance float64
		var version int
		err := db.QueryRowContext(ctx,
			`SELECT balance, version FROM accounts WHERE id = $1`, accountID,
		).Scan(&balance, &version)
		if err != nil {
			return err
		}

		newBalance := balance + delta

		result, err := db.ExecContext(ctx,
			`UPDATE accounts
			    SET balance = $1, version = version + 1
			  WHERE id = $2 AND version = $3`,
			newBalance, accountID, version,
		)
		if err != nil {
			return err
		}

		rows, err := result.RowsAffected()
		if err != nil {
			return err
		}
		if rows == 1 {
			return nil
		}

		backoff := time.Duration(50*(1<<attempt)) * time.Millisecond
		time.Sleep(backoff)
	}
	return fmt.Errorf("account %d: %w after %d attempts", accountID, ErrOptimisticLock, maxRetries)
}

Notice there is no explicit BEGIN/COMMIT around the read and write here — the safety comes entirely from the WHERE version = $3 clause, not from transaction isolation. A transaction still matters if you need the balance read and written as part of a larger multi-statement unit of work, but the lost-update problem specifically is already closed by the conditional UPDATE.

#When OCC beats pessimistic locking, and when it doesn't

🧠 Check your understanding
0/3 · 0/3 answered
1. In an optimistic-locking UPDATE like `UPDATE accounts SET balance=?, version=version+1 WHERE id=? AND version=?`, what does it mean if the statement reports 0 rows affected?
The database connection dropped mid-statementAnother writer already updated the row, and its version, since you read it — your write lost the raceThe account does not existThe version column has an invalid data type
2. Which scenario is the strongest case for optimistic concurrency control over pessimistic locking?
Rows that must always reflect the very latest committed value across every concurrent transactionA schema with no primary keys at allA single hot row updated hundreds of times per second by tightly-coupled workersA form-editing flow where the user reads a record, thinks for two minutes, then saves
3. Why doesn't the read-then-conditional-write pattern need an explicit transaction wrapping the SELECT and UPDATE to be correct against lost updates?
Because the version check is embedded inside the single atomic UPDATE statement itself, so no other writer can interleave between the compare and the swapBecause optimistic locking disables MVCCBecause SQL databases never allow two connections to run at the same timeBecause the version column is always replicated synchronously to every reader