Redis can hold a key. MySQL holds the seat.

The IPL final is open, and two fans tap Book on seat 12A. Both requests hit my API while the row still says empty.

I used to think a lock was one move: grab 12A, write booked, let go. That is one kind. The same two taps need the others.

Fan A taps Book on 12A for the final. Then B taps the same seat. Two requests hit one empty row. — Figure 1. One seat. Then a second tap.

Lock 12A first, or check after

Pessimistic locking means I assume a fight. A starts a transaction, locks the row for 12A, sees it empty, writes booked, commits. The lock drops. B has been waiting. B now reads booked and gets “seat taken.”

I pay the wait so 12A is not sold twice.

Optimistic locking means I assume they usually do not fight. A and B both read 12A empty. The row has a version, say 3. A writes booked and sets version to 4 where version is still 3. That update changes one row. B runs the same update. Zero rows change. B tells the fan the seat is gone.

No one waited. The second writer lost at the update, not at a lock.

Left: A locks 12A, writes, unlocks. B waits then sees booked. Right: both read version 3. A writes version 4. B update hits zero rows. — Figure 2. Same seat. Lock before, or check after.

There is one 12A. Two taps on it is contention. I pick pessimistic when that fight is the normal path, a cheap fare that just opened. I pick optimistic when collisions are rare, or when I do not want B sitting on a lock while A talks to a payment service.

Someone has to keep the wait list

When B waits, it is not magic. Inside the database there is a lock manager: which row, who holds it, who is waiting. A asks for an exclusive lock on 12A. The manager writes “holder: A.” B asks for the same row. The manager puts B on the wait list. A commits. The manager wakes B.

The lock is not the seat. The seat is the row. The lock is that entry. Optimistic booking often never talks to this table on the read. It only finds out at update time that version 3 is gone.

Shared and exclusive are two modes on that same table. Two check-in screens can take a shared lock to print that 12A is still free. FOR SHARE is that mode. A booking needs exclusive: FOR UPDATE. The manager will not give exclusive while a share is out, and it will not give a new share while exclusive is out. Read together. Write alone.

Transactions A and B talk to a lock manager. Row 12A is exclusive, holder A, waiter B. The seat row is separate. — Figure 3. The lock is not the seat. It is an entry in that table.

They also want 12B

A is booking a pair: 12A and 12B. A locks 12A. B is booking the same pair from the other side. B locks 12B. A wants 12B. B wants 12A. Each holds what the other needs. That cycle is a deadlock. Nobody can finish.

MySQL looks for that cycle soon. If an edge will close a loop, it kills one transaction. A gets a deadlock error. B books both seats. A retries.

Postgres lets them wait. After a timeout it builds a wait-for graph: who is waiting on whom. If it finds a cycle, it kills one. If it does not, they keep waiting.

Same two fans. Same pair. MySQL fails fast. Postgres waits, then looks.

A holds 12A and wants 12B. B holds 12B and wants 12A. MySQL kills one now. Postgres waits then walks the wait-for graph. — Figure 4. A cycle is a deadlock. Someone has to lose.

If every booking locks 12A then 12B, the cycle does not form. The kill is a safety net, not a booking strategy.

Wait, fail, or take 12B

FOR UPDATE is the exclusive row lock. A has 12A. B’s SELECT ... FOR UPDATE on 12A waits until A commits.

NOWAIT means B does not wait. If 12A is locked, the database returns an error now.

SKIP LOCKED means B does not wait and does not error. It skips 12A and takes the next free row, 12B. That is “any window,” not “I asked for 12A.”

FOR UPDATE waits on 12A. NOWAIT errors. SKIP LOCKED takes 12B. — Figure 5. Wait, fail, or skip.

If B must have 12A, do not skip.

A lock in Redis is not 12A

The next version puts A and B on two API boxes. Those boxes do not share memory. They ask Redis for seat:12A.

A calls SET seat:12A <token> NX EX 30. NX means set only if the key is missing. EX 30 means the key dies in thirty seconds if the box crashes. A gets the key. B gets nil and does not book. A writes the MySQL row, then unlocks.

Unlock is not DEL seat:12A. If A’s lock expired and C now holds the key, A’s late delete would steal C’s lock. A Lua script runs on Redis as one step: read the value, delete only if it is still A’s token.

if redis.call("get", KEYS[1]) == ARGV[1] then
  return redis.call("del", KEYS[1])
end
return 0

That lock and that write are two systems. The Redis key can expire while the MySQL write is still running. B can take the key and write again. Or the write fails and the key remains, and 12A looks taken when the row is empty.

A remote lock is a hint plus a timeout. The seat row is still the truth. I still do the version check or the FOR UPDATE on the primary when I write booked.

Worse: I take FOR UPDATE on a read replica. The replica is a copy. The write goes to the primary. I locked a picture of 12A. Two bookings land. The lock has to sit where the write happens.

Left: Redis lock then MySQL write can disagree. Right: FOR UPDATE on a replica does not stop a primary write. — Figure 6. The lock has to sit where the write happens.

Two API boxes talk to Redis with SET NX and a Lua unlock. Redlock uses three Redis boxes and a majority. — Figure 7. Two boxes cannot share a mutex in memory. The seat row is still the truth.

One Redis can die, and then nobody can take seat:12A. Redlock is the same SET on several Redis machines. A needs a majority before a deadline, then writes the row. I treat that as harder to lose the key, not as “12A cannot be double-booked.” The row decides the winner.

A Redis key is a hint. A lock on a replica is a picture. Two API boxes still have to write 12A on the primary.

I still lock in Redis and skip the version on the row. I still FOR UPDATE a replica. I still SKIP LOCKED when the fan asked for 12A.

The same Redis-is-not-the-row mistake showed up when a homepage click found the sneakers missing from cache. I wrote that when a second GET sat on the pool. The shop that grew more API boxes around that pool is the proxy post.

If this is useful, wrong, or incomplete, write to me.