Read-Modify-Write: The Optimistic Concurrency Pattern Most ORMs Get Wrong

• Book: Database Playbook: Choosing the Right Store for Every System You Build
• Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

Read-modify-write is the most common write pattern in your codebase. Fetch a row. Change a field in memory. Save it back. Every CRUD form, every counter, every status transition.

It's also the most common place to silently lose data. Two requests, same row, same second. Both reads see the same starting value. Both writes succeed. One of them gets stomped, and you'll never find it in the logs because no exception fired.

Most ORMs ship without optimistic concurrency turned on by default. You have to opt in, and the opt-in is different in every framework. So most apps don't.

The lost-update problem

Consider an inventory counter. Two warehouse staff scan the same item out at the same time:

// Request A and Request B both run this code, ~50ms apart.
$product = Product::find(42);          // both read: stock = 10
$product->stock = $product->stock - 1; // both compute: 10 - 1 = 9
$product->save();                      // both write: stock = 9

You sold two items. Stock dropped by one. The second save() overwrote the first with a value calculated from the same starting point.

The naive fix is stock = stock - 1 in raw SQL. That works for counters. It does nothing for the general case: a user edits a customer record while another user is editing the same record on a different screen. Whoever clicks "Save" last wins, and the other one's changes vanish.

This is a classic anomaly. The SQL standard calls it a "lost update" and Postgres' default isolation level (READ COMMITTED) does not prevent it. Neither does MySQL's default (REPEATABLE READ, despite the name: it stops phantoms within a transaction, not cross-transaction updates).

Pessimistic locking works, but pays for it

The first textbook answer is SELECT ... FOR UPDATE. Take a row lock at read time, hold it until the transaction commits. Anyone else who tries to read the same row with FOR UPDATE blocks until you're done.

BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- application logic runs here, holding the lock
UPDATE products SET stock = 9 WHERE id = 42;
COMMIT;

This is correct. It's also a throughput tax you pay on every request. The lock spans the entire round trip from your app server, including any HTTP calls or slow code paths between the SELECT and the UPDATE. A 200ms request becomes a 200ms lock window on a hot row. If 50 requests hit that row in a second, 49 of them queue.

Pessimistic locking is the right call when conflict is the common case: bank accounts during settlement, ticket inventory during a flash sale, anywhere the contention rate is high enough that retries would thrash. For everything else, it's a pessimist's price.

Optimistic concurrency with a version column

The optimistic alternative assumes conflicts are rare and lets the database arbitrate. Add a version column to the table. Every UPDATE includes the version in the WHERE clause and bumps it:

UPDATE products
SET stock = 9, version = version + 1
WHERE id = 42 AND version = 7;

The database returns the affected row count. If you read version 7 and someone else has already bumped it to 8, your UPDATE matches zero rows. You know your write didn't land. You can refetch, replay your business logic against the new state, and try again.

That's it. The whole pattern is one extra column, one extra WHERE clause, and a row-count check after every UPDATE. No locks. No blocking. Throughput stays where it was. The only cost is the retry path, which only fires when there's a real conflict.

The trade-offs are direct:

• Pessimistic: every request pays the lock cost. Concurrency wins are guaranteed.
• Optimistic: only conflicted requests pay the retry cost. You need a retry strategy.

Optimistic should be your default. Pessimistic should be the override for hot rows where you've measured the retry rate getting silly.

How ORMs ship it (or don't)

This is where the abstraction gets weird, because every framework picks a different verb and most of them make it opt-in.

Eloquent (Laravel) does not ship optimistic locking. The community goes back and forth on this every couple of years. You're expected to either add it yourself or use a package. The DIY version usually looks like:

$affected = DB::table('products')
    ->where('id', $product->id)
    ->where('version', $product->version)
    ->update([
        'stock'   => $newStock,
        'version' => $product->version + 1,
    ]);

if ($affected === 0) {
    throw new StaleObjectException('product', $product->id);
}

You're back to writing SQL through the query builder because the model save path won't enforce the version check for you. Easy to get right once, easy for the next person on the team to forget when they add a new write path.

Doctrine ORM (Symfony / PHP) does it properly. Mark the field with the Version attribute and Doctrine generates the right UPDATE and raises OptimisticLockException on conflict:

#[ORM\Entity]
class Product
{
    #[ORM\Id, ORM\Column]
    private int $id;

    #[ORM\Column]
    private int $stock;

    #[ORM\Version, ORM\Column(type: 'integer')]
    private int $version;
}

// On conflict, EntityManager::flush() throws OptimisticLockException.

This is the cleanest implementation in mainstream PHP. The annotation is the toggle, the WHERE clause is generated, the exception is typed.

Prisma (Node / TypeScript) has no first-class optimistic lock as of 6.x. You add a version Int field, then write the WHERE-and-bump by hand inside updateMany. update won't work — it targets a primary key and ignores extra where predicates. Subtle and easy to miss:

// updateMany returns { count }; update would throw P2025 instead.
const result = await prisma.product.updateMany({
  where: { id: 42, version: currentVersion },
  data:  { stock: newStock, version: currentVersion + 1 },
});

if (result.count === 0) {
  throw new StaleProductError(42);
}

The Prisma team has tracked this in their roadmap for a while. Until it lands as a first-party feature, the per-model boilerplate is what you've got.

GORM (Go) ships it as an opt-in plugin: gorm.io/plugin/optimisticlock. Register the field type and db.Save does the right thing automatically. Worth knowing the plugin exists; not enabled by default.

type Product struct {
    ID      uint
    Stock   int
    Version optimisticlock.Version
}

// Save() now adds WHERE version = ? and bumps it. RowsAffected == 0 means conflict.
result := db.Save(&product)
if result.RowsAffected == 0 {
    return ErrStaleProduct
}

SQLAlchemy (Python) is interesting. It supports version_id_col on the mapper, and on conflict raises StaleDataError. It's the only one where the configuration sits next to the table definition rather than at the column level. If you've ever used SQLAlchemy's Mapper(version_id_col=...), you've seen this.

The takeaway: don't assume your ORM handles this. Look up the exact name in your framework. Test it with two concurrent requests against a real database before you trust the docs.

Handling the conflict

A conflict isn't an error in the application sense. It's information: the row changed under you. You have three honest responses.

Refetch and merge. For counters and accumulators (stock, like counts, view counts), reread the current state, reapply your delta on top, and retry. Bounded retry — three attempts, exponential backoff with jitter, give up and surface the failure if it keeps losing. This is the right answer when the operation is commutative.

Surface to the user. For form edits, the right answer is usually to refuse the save and tell the user. "This record was updated by someone else. Reload to see the latest version." Anything else risks silently dropping their teammate's changes. CRMs and ticketing systems get this right; most internal admin panels get it wrong.

Three-way merge. For documents and rich records, you can attempt a field-level merge: their fields, your fields, base version. Git's merge model, applied to a row. Worth it for collaborative editing tools. Overkill for almost everything else.

The wrong answer is to retry blindly with the same in-memory values. You'll just clobber the same change on the next attempt. The retry only makes sense if you refetch first.

A decorator that adds optimistic locking to any repo

If you don't want to scatter the WHERE-version-bump dance across every UPDATE, wrap your repository in a small decorator. This is roughly the pattern the Database Playbook walks through in its concurrency chapter:

interface Versioned { id: string; version: number; }

interface Repository<T extends Versioned> {
  findById(id: string): Promise<T | null>;
  save(entity: T): Promise<T>;
}

class OptimisticLockingRepository<T extends Versioned>
  implements Repository<T>
{
  constructor(
    private readonly inner: Repository<T>,
    private readonly maxRetries: number = 3,
  ) {}

  async findById(id: string) { return this.inner.findById(id); }

  // The caller passes a function that mutates the entity. We refetch on
  // conflict and reapply until it sticks or we give up.
  async update(
    id: string,
    mutate: (entity: T) => void,
  ): Promise<T> {
    let attempt = 0;
    while (attempt < this.maxRetries) {
      const entity = await this.inner.findById(id);
      if (!entity) throw new NotFoundError(id);

      mutate(entity);

      try {
        return await this.inner.save(entity);
      } catch (err) {
        if (err instanceof StaleObjectError) {
          attempt += 1;
          const backoffMs = 2 ** attempt * 10 + Math.random() * 25;
          await new Promise(r => setTimeout(r, backoffMs));
          continue;
        }
        throw err;
      }
    }
    throw new ContentionError(id, this.maxRetries);
  }

  async save(entity: T) { return this.inner.save(entity); }
}

The pattern keeps the retry loop in one place. Business logic stays in the mutate callback. The infrastructure code knows nothing about products or counters.

Two things to keep honest:

• The mutate callback runs against a fresh entity on each retry. If your mutation has side effects (sending email, charging a card), the decorator will run them twice. Either make the mutate callback pure and do side effects outside, or use an idempotency key on the side-effect call.
• Backoff with jitter, not without. Without jitter, two threads in conflict will retry in lockstep forever. Add a random component so they desync.

The gotcha: bulk updates bypass version checks

Here's the trap that's bitten teams I've talked to more than once: optimistic locking only applies to writes that go through the ORM's save path. The moment you write SQL that updates multiple rows at once, the version column is doing nothing.

-- This kills every concurrent in-flight edit on these products.
-- Versions are not checked. Optimistic locking is silently bypassed.
UPDATE products SET category = 'archived'
WHERE category = 'discontinued';

That UPDATE will overwrite values that another transaction was about to commit, because there's no version predicate in the WHERE clause. You won't see any error. You might not even notice until a customer complains.

Two ways to handle it:

• Bump the version in your bulk UPDATE too: SET ..., version = version + 1. Concurrent single-row writes against any of those rows will now fail their version check and retry. Cheapest fix.
• Schedule bulk operations during low-traffic windows, or behind a feature flag that quiesces writes to the affected rows first. Heavier, but the right call for truly destructive bulk changes (data migrations, GDPR scrubs).

While you're auditing, look for these other version-bypass paths:

• INSERT ... ON CONFLICT DO UPDATE (Postgres upsert): no version check unless you add one to the predicate.
• ORM-level updateAll / updateMany calls: same problem as raw SQL.
• Database triggers that modify the row without bumping the version column.
• Migrations that backfill a new column with a single UPDATE statement.

The check to run against your codebase is direct: grep for every UPDATE you have, then look at whether the version column appears in the WHERE clause. Anywhere it doesn't, you have a potential lost-update window.

Optimistic locking is one of those primitives that costs a few lines and saves a category of bugs that don't show up in your dashboards. Add the column, add the WHERE clause, audit your bulk updates. Pick pessimistic locking deliberately, where you've measured contention. The default should not be "whoever writes last wins."

What's your ORM doing right now? If you grep your codebase for every UPDATE statement and check which ones include a version predicate, how many writes are silently racing? Drop a comment with the ratio.

If this was useful

The Database Playbook: Choosing the Right Store for Every System You Build has a chapter on concurrency control that walks through pessimistic locking, optimistic versioning, and the decorator pattern above, plus when each one stops being a fit at scale. If you've ever debugged a lost update, the chapter on conflict resolution strategies is worth the read.