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.

A race condition is when two things try to do something at the same time, and the result depends on who gets there first. It's not about speed - it's about unpredictable ordering. Like two people grabbing the last samosa. Someone wins, someone loses, and it's chaos if no one checks first.

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

Atomic means "all or nothing." Either the whole operation happens, or none of it does. No halfway. When a database says an UPDATE is atomic, it means no one can sneak in while you're mid-write. It's one unbreakable unit.

Locking is how you stop that interleaving from corrupting your data.

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.

Contention is just competition for the same resource. High contention means lots of requests fighting over the same rows at the same time. Low contention means they mostly stay out of each other's way.

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.

TTL is an expiration timer. "This lock disappears in 30 seconds no matter what." It's a safety net - if the process holding the lock crashes and never releases it, the TTL eventually frees it anyway.

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)

Split-brain happens when a system breaks into parts that can't talk to each other, and both parts think they're in charge. Two workers both believing they hold the lock. Two databases both accepting writes. Nothing good follows.

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.

Sources and further reading

Where to check the behaviour I described, and where to go deeper.

• PostgreSQL: Explicit locking. The full table of lock modes, which ones conflict, and the advisory lock functions. If you only read one thing here, read this.
• PostgreSQL: Transaction isolation. Read committed, repeatable read and serializable, and exactly which anomalies each one still allows. Half the locking people add is really an isolation level they never chose deliberately.
• PostgreSQL: SELECT, the locking clauses. FOR UPDATE, FOR SHARE, NOWAIT and SKIP LOCKED, with the precise semantics of each.
• How to do distributed locking by Martin Kleppmann. The argument that a lock you cannot fence is not a lock for correctness. Read it alongside the Redlock docs and make up your own mind.
• Redis: Distributed locks. The Redlock algorithm from the other side of that debate, including the safety properties it does and does not claim.
• etcd: Interacting with etcd. Leases and the primitives behind a coordination service, if you decide a single Redis is not enough.
• Jepsen analyses. Real distributed systems tested against their own consistency claims. The most effective cure I know for trusting a database's marketing page.
• Designing Data-Intensive Applications by Martin Kleppmann, chapter 7. The clearest book-length treatment of isolation levels, lost updates and write skew.

Continue learning

If the lock types now make sense, the next thing to understand properly is transaction isolation, because several of the problems above disappear at a higher isolation level and several others do not. After that, deadlock detection and how your database reports it, so you can read a deadlock log instead of fearing it. Then consensus and leases, which is where distributed locking stops being a Redis key and starts being a real coordination problem.

FAQ

Is SELECT ... FOR UPDATE enough for a job queue? With SKIP LOCKED, usually yes, and it is a lot simpler than a message broker. The pattern breaks down when you need multi-day delays or very high throughput, at which point a purpose-built queue earns its keep.

Do I need a distributed lock or a database lock? If the thing you are protecting lives in one database, use that database's lock. A distributed lock adds a second system that can fail independently, and it can only be as correct as its fencing.

Why did my optimistic lock start failing constantly? Contention rose above the level optimistic concurrency suits. Optimistic locking assumes conflicts are rare, because every conflict costs a retry. When conflicts become common, pessimistic locking is cheaper.