Choosing the Right Lock

If you've ever had two users edit the same record and one overwrote the other, or two background workers picked up the same job and ran it twice - you've hit a concurrency problem. Locking is how we solve it.

But locking isn't one thing. It's a family of techniques, each with tradeoffs. Pick wrong and you'll either corrupt data or grind your system to a halt.

This is what I wish someone had explained to me earlier in my career. Not just the definitions, but when each one actually makes sense.

The core problem

Here's the scenario that burns everyone at least once.

You have a bank account with $100. Two requests come in at the same time - both trying to withdraw $80.

Request A reads the balance: $100. Okay, $80 is fine. Request B reads the balance: $100. Okay, $80 is fine. Request A writes: $100 - $80 = $20. Request B writes: $100 - $80 = $20.

Both succeeded. You dispensed $160 from a $100 account. The balance shows $20 and someone's having a very bad day.

This is a race condition.

The read and write weren't atomic. Something else snuck in between.

Locking, at its core, is about making sure that doesn't happen.

Pessimistic locking

Pessimistic locking assumes conflict is likely. So you lock the resource before you even start working on it. Everyone else waits.

In SQL databases, this usually looks like SELECT ... FOR UPDATE:

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;
-- Row is now locked. No one else can modify it until we're done.
UPDATE accounts SET balance = balance - 80 WHERE id = 42;
COMMIT;

The moment you run that SELECT ... FOR UPDATE, the database locks that row. Any other transaction trying to touch it will block - just sit there waiting - until you commit or rollback.

When it works well:

• High contention. Many writers hitting the same rows constantly.
• Short transactions. Lock, do your thing, release. Seconds at most.
• When you absolutely cannot tolerate a conflict and retry.

When it hurts:

• Long-running operations. You're holding everyone hostage.
• Distributed systems where database-level locks don't span services.
• Read-heavy workloads. You're blocking readers for no reason (unless you use FOR SHARE for read locks, but that's another layer of complexity).

I've seen systems grind to a halt because someone held a row lock while calling an external API. The API timed out after 30 seconds. Every other request piled up behind it. Don't do that.

Optimistic locking

Optimistic locking assumes conflict is rare. So you don't lock anything upfront. You just check, at the moment of writing, whether someone else changed things while you weren't looking.

The most common pattern is a version number:

-- Read the record
SELECT id, balance, version FROM accounts WHERE id = 42;
-- Returns: balance = 100, version = 5

-- Later, when updating:
UPDATE accounts 
SET balance = 20, version = 6 
WHERE id = 42 AND version = 5;

If someone else modified that row between your read and write, the version won't be 5 anymore. Your WHERE clause won't match. Zero rows updated. You know you lost the race.

Your application then decides what to do - usually retry with fresh data.

Some ORMs handle this automatically. Prisma has @updatedAt, some frameworks use a dedicated version or lock_version column.

When it works well:

• Low to medium contention. Conflicts are the exception, not the rule.
• Read-heavy workloads. No one blocks anyone during reads.
• Web applications where users might have a form open for minutes before submitting.

When it hurts:

• Very high contention. If every other write is a conflict, you're just burning CPU on retries.
• When retrying is expensive or has side effects you can't undo.

Optimistic locking without a version column

Here's the pattern I showed earlier in that job queue code:

const job = await db.job.findFirst({
  where: { status: 'PENDING' }
});

const claimed = await db.job.updateMany({
  where: { id: job.id, status: 'PENDING' },
  data: { status: 'RUNNING' }
});

if (claimed.count === 0) {
  // Someone else has it
  continue;
}

No explicit version column. You're using the status itself as the lock indicator.

This works because:

1. The UPDATE with a WHERE clause is atomic at the database level
2. Two workers can't both match status = 'PENDING' - one will run first, change it to RUNNING, and the second's WHERE won't match

Simple, no schema changes, works great for state-machine style workflows where the status transition itself is what you're protecting.

Row-level vs table-level locks

Worth knowing the difference.

Row-level locks (what FOR UPDATE typically does) only lock the specific rows your query touches. Other transactions can still read and write other rows in the same table.

Table-level locks lock the entire table. Everyone waits. This is the nuclear option.

LOCK TABLE accounts IN EXCLUSIVE MODE;

You almost never want this in application code. It's for maintenance operations, bulk migrations, that sort of thing. If you're reaching for table locks in regular code, something's gone wrong architecturally.

Most modern databases default to row-level locking. But certain operations can escalate - if you're updating a huge percentage of a table, some databases decide a table lock is more efficient than tracking thousands of row locks.

Distributed locks

All of the above assumes a single database. But what if you have multiple services? Multiple databases? Operations that span systems?

You need a lock that lives outside any single database. Enter distributed locks.

The most common approach: use Redis.

const lockKey = `lock:order:${orderId}`;
const lockValue = uuid(); // Unique to this worker
const ttl = 30000; // 30 seconds max

// Try to acquire
const acquired = await redis.set(lockKey, lockValue, 'NX', 'PX', ttl);

if (!acquired) {
  // Someone else has it
  return;
}

try {
  await processOrder(orderId);
} finally {
  // Release - but only if we still own it
  const current = await redis.get(lockKey);
  if (current === lockValue) {
    await redis.del(lockKey);
  }
}

The NX flag means "only set if not exists." If two workers try simultaneously, only one succeeds.

The PX sets an expiration. This is crucial - if your process crashes while holding the lock, the lock eventually releases itself. Without TTL, you'd have a deadlock forever.

The release check (if current === lockValue) prevents a nasty bug: your lock expired, someone else acquired it, and now you're deleting their lock. By checking the value, you only delete if you still own it.

When it works well:

• Cross-service coordination
• Preventing duplicate processing of events or webhooks
• Rate limiting per-resource operations

When it hurts:

• Redis goes down, your locking breaks
• TTL is tricky - too short and you release while still working, too long and recovery from crashes takes forever
• Network partitions can cause split-brain (two workers both think they have the lock)

Advisory locks

This one's underrated.

Advisory locks are database-provided locks that don't lock any actual row or table. You're just locking an arbitrary number. The database keeps track of who holds what.

Postgres example:

-- Acquire a lock on the number 12345
SELECT pg_advisory_lock(12345);

-- Do your work...

-- Release
SELECT pg_advisory_unlock(12345);

The number can mean whatever you want. Maybe it's a user ID. Maybe it's a hash of some resource identifier. The database doesn't care - it's purely advisory. It only works if everyone agrees to check the lock.

There's also pg_try_advisory_lock which returns immediately with true/false instead of blocking.

When it works well:

• You want a distributed lock but don't want to add Redis
• Locking logical resources that don't map to single rows
• Preventing concurrent runs of the same background job

When it hurts:

• Only works within that one database. Cross-service? No help.
• If you forget to release (or crash without releasing), session-level locks stick around until disconnect. Use pg_advisory_xact_lock for transaction-scoped locks that auto-release on commit/rollback.

I've used advisory locks to ensure only one worker processes a particular user's queue at a time, without locking any actual rows. Clean, lightweight, no schema changes.

How to choose

Here's my mental model:

Single database, short operation, high contention? → Pessimistic locking with FOR UPDATE. Maybe SKIP LOCKED if it's a queue.

Single database, longer operation, low contention? → Optimistic locking with version column or status-based conditions.

Multiple services need to coordinate? → Distributed lock via Redis. Keep TTLs sane.

Need to lock a logical concept, not a row? → Advisory locks if Postgres, distributed lock otherwise.

Don't know your contention level yet? → Start optimistic. It's simpler. Add pessimistic locking if you see too many retry storms.

The real lesson

Locking is risk management. You're trading off between data correctness, system throughput, and complexity.

No lock at all? Fast but dangerous. Lock everything? Safe but slow.

The art is finding the minimum locking that keeps your data correct. Most systems need way less locking than developers initially think - and the locking they do need is often in places they didn't expect.

Start by understanding where your actual race conditions are. Not theoretical ones. Actual ones. Then lock precisely those.

Everything else is premature pessimism.