How Optimistic Locking Saved My Side Project from a Concurrency Nightmare | Tech Blog

A Friday afternoon cold sweat

I had just deployed a new feature and was watching the real-time logs, feeling pretty good about myself. It was a simple first-come-first-served item drop, limited to 100 units. Then I noticed the counter: 107 claimed.

I figured the aggregation logic was off and checked the database directly. It was worse than I thought. Stock was at negative seven. Several users had claimed the item twice. On a Friday afternoon.

The feature was straightforward. Complete a specific action, get a limited-stock item. The code couldn't have been simpler.

async function claimItem(userId: string, itemId: string) {
  const item = await db.items.find({ where: { id: itemId } });
 
  if (item.stock > 0) {
    const newStock = item.stock - 1;
    await db.items.update({ id: itemId }, { stock: newStock });
    await db.userItems.create({ userId, itemId });
    console.log(`Success: ${userId} claimed the item.`);
    return true;
  }
 
  console.log('Failed: Out of stock.');
  return false;
}

Worked flawlessly in solo testing. The answer was concurrency.

The race condition

Picture users A and B clicking at the same time on an item with 1 unit left.

A's request arrives. Reads stock: 1. stock > 0 is true.
Before A writes, B's request arrives on another process. B also reads stock: 1. Also true.
A updates stock to 0. A gets the item.
B updates stock to -1. B also gets the item.

Two people walk away with an item that only had one left. Classic race condition.

Pessimistic locking: effective but heavy

My first instinct was pessimistic locking. SELECT ... FOR UPDATE locks the row from the moment you read it until the transaction ends. Nobody else can touch it.

It works. But on a feature where users are slamming requests concurrently, pessimistic locks create a queue. Requests wait for the lock to release, and throughput tanks. For my side project where collisions were occasional, locking every single read felt like calling in the SWAT team for a parking ticket.

Optimistic locking: lightweight and elegant

The idea is simple: assume collisions rarely happen. But at the very last moment of writing, check if the data changed since you read it.

Implementation is straightforward. Add a version column to the table.

Read the data along with its version.
Run your business logic.
In the UPDATE statement, add a WHERE condition: "is the current version still the same as what I read?"
On success, increment version by 1.

If another transaction modified the row first, version will have changed, and your UPDATE affects 0 rows. You know you lost the race.

async function claimItemOptimistically(userId: string, itemId: string, maxRetries = 3) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    const item = await db.items.find({ where: { id: itemId } });
 
    if (item.stock <= 0) {
      console.log('Failed: Out of stock.');
      return false;
    }
 
    const updateResult = await db.items.update(
      {
        id: itemId,
        version: item.version, // only if version matches what I read
      },
      {
        stock: item.stock - 1,
        version: item.version + 1, // bump version on success
      }
    );
 
    if (updateResult.affectedRows > 0) {
      await db.userItems.create({ userId, itemId });
      console.log(`Success: ${userId} claimed the item.`);
      return true;
    }
 
    console.log(`Collision detected, retrying... (attempt ${attempt + 1})`);
  }
 
  console.log('Failed: Too many collisions, could not complete the operation.');
  return false;
}

The takeaway

Pessimistic locking prevents the problem. Optimistic locking detects and handles it. Since optimistic locking doesn't hold database-level locks, there's virtually no performance penalty. Collision control happens at the application layer.

If your system has frequent write collisions, pessimistic locking might be the better call. But for most web applications where reads far outnumber writes and write collisions are rare, optimistic locking is the efficient choice.

That Friday afternoon cold sweat turned into a deeper understanding of how systems actually work under load. Sometimes the answer isn't a complex technology. It's a slight shift in how you look at the problem.