Module 20 / Databases in depth

Module 20 · lesson 4 of 10 · Read 9 min

Pessimistic and optimistic locking

SELECT FOR UPDATE, version columns, and choosing between them.

What you'll learn

  1. The last plate of biryani
  2. First, try to avoid the lock
  3. Pessimistic locking: SELECT ... FOR UPDATE
  4. Deadlocks
  5. Optimistic locking: version columns
  6. Choosing between them
  7. Locks beyond rows

The last three lessons kept running into the same bug shape: read a value, decide something in the app, write a result. If two requests do that at the same time, one of them decides based on data that's already out of date.

There are two families of fixes. Pessimistic locking assumes a conflict will happen and blocks others up front. Optimistic locking assumes it probably won't, lets everyone proceed, and checks at write time. This lesson shows both in real SQL, plus a third option that's often better than either: not needing a lock at all.

Quick recap if you came here directly: under Postgres's default read committed level, or MySQL's repeatable read, a plain SELECT followed by an UPDATE doesn't protect you from another transaction changing the row in between (see lessons 2 and 3 of this module).

The last plate of biryani

Tiffin lets restaurants cap daily portions. Paradise Kitchen makes 40 plates of its famous biryani a night, stored as portions_left on menu_items. At 8:47pm there's one left, and two customers check out at the same moment. The naive code:

row = db.query("SELECT portions_left FROM menu_items WHERE id = 311")
if row.portions_left < 1:
    raise SoldOut()
db.execute("UPDATE menu_items SET portions_left = %s WHERE id = 311",
           row.portions_left - 1)

Both requests read 1, both pass the check, both write 0. The kitchen gets two orders for one plate. Somebody's dinner gets cancelled 20 minutes later.