Optimistic Locking with Version Columns

Scenario

Example app: a CMS for an online store. Two merchandisers open the same product page in the admin. One fixes the description; the other changes the price a few minutes later and clicks Save.

What you see in production

The description fix vanished — "I saved it, I saw it on the site, now it's gone". Nothing errored. Audit logs show the second save wrote the old description back along with the new price.

The naive code

The form posts every field; the server overwrites the whole row:

def update_product(request, pk):
    product = Product.objects.get(pk=pk)
    form = ProductForm(request.POST, instance=product)   # contains the stale description
    form.save()                                           # last write wins

@PutMapping("/products/{id}")
public Product update(@PathVariable long id, @RequestBody ProductDto dto) {
    Product p = products.findById(id).orElseThrow();
    p.apply(dto);                     // overwrites fields loaded minutes ago in the editor
    return products.save(p);
}

await prisma.product.update({ where: { id }, data: { name, description, price } });   // blind overwrite

Reproduce it

row = {"version": 1, "description": "Old text", "price": 20}

def save(editor, changes, expected_version=None):
    if expected_version is not None and row["version"] != expected_version:
        return f"{editor}: 409 Conflict — product changed since you opened it (v{expected_version} → v{row['version']})"
    row.update(changes)
    row["version"] += 1
    return f"{editor}: saved -> {row}"

# both editors load v1 into their forms
form_a = dict(row); form_b = dict(row)
form_a["description"] = "Fixed text"
form_b["price"] = 25

print("-- last write wins --")
print(save("A", {"description": form_a["description"], "price": form_a["price"]}))
print(save("B", {"description": form_b["description"], "price": form_b["price"]}))

row.update(version=1, description="Old text", price=20)
print("-- optimistic locking --")
print(save("A", {"description": form_a["description"], "price": form_a["price"]}, form_a["version"]))
print(save("B", {"description": form_b["description"], "price": form_b["price"]}, form_b["version"]))

-- last write wins --
A: saved -> {'version': 2, 'description': 'Fixed text', 'price': 20}
B: saved -> {'version': 3, 'description': 'Old text', 'price': 25}
-- optimistic locking --
A: saved -> {'version': 2, 'description': 'Fixed text', 'price': 20}
B: 409 Conflict — product changed since you opened it (v1 → v2)

Root cause

The second editor's form was built from version 1, but the row is now version 2. Writing the whole form back silently reverts the other editor's change — a lost update spread over minutes instead of milliseconds. You can't hold a database lock while a human edits, so you need a check at save time.

The fix

Add a version column. Every update says "only if the version is still what I read", and increments it. Zero rows updated means someone else saved first:

UPDATE products
SET description = $1, price = $2, version = version + 1
WHERE id = $3 AND version = $4;      -- 0 rows → conflict

@Entity
public class Product {
    @Id Long id;
    @Version Long version;            // Hibernate adds "AND version = ?" and bumps it
    String description;
    BigDecimal price;
}
// Conflict → OptimisticLockException (Spring: ObjectOptimisticLockingFailureException) → map to 409

class Product(Base):
    __tablename__ = "products"
    id = mapped_column(Integer, primary_key=True)
    version = mapped_column(Integer, nullable=False)
    description = mapped_column(Text)
    __mapper_args__ = {"version_id_col": version}   # StaleDataError on conflict

$updated = Product::whereKey($id)->where('version', $request->integer('version'))
    ->update([...$request->validated(), 'version' => DB::raw('version + 1')]);
abort_if($updated === 0, 409, 'This product was changed by someone else. Reload and try again.');

@Entity() class Product {
  @PrimaryGeneratedColumn() id!: number;
  @VersionColumn() version!: number;          // incremented on save
  @Column() description!: string;
}
// Or with Prisma: updateMany({ where: { id, version }, data: { ..., version: { increment: 1 } } }) and check count

Expose it over HTTP with ETags: GET returns ETag: "v7", the client sends If-Match: "v7" on PUT, and the server answers 412 Precondition Failed on a mismatch.

Trade-offs

• No blocking: great when conflicts are rare (admin edits, profiles, settings).
• Under high contention (many writers on one row) most attempts fail and retry — use an atomic update or a lock instead.
• Automatic retry is only safe when the change can be re-applied to fresh data ("add 1 to quantity"); for human edits, show the conflict and let the user merge.
• Partial updates (PATCH only changed fields) reduce conflicts but don't remove the need for a version when fields depend on each other.

Detect it in production

• Count 409/412 responses and OptimisticLockExceptions per entity — a rising rate means contention, not bugs.
• Audit logs with before/after values make silent overwrites visible.

Key points

• Optimistic locking: WHERE version = :read_version, then increment; zero rows means conflict.
• JPA @Version, SQLAlchemy version_id_col and TypeORM @VersionColumn do it for you.
• Use ETag / If-Match to carry the version through HTTP.
• Best when conflicts are rare; use locks or atomic updates for hot rows.