Skip to content

Databases · 6 of 9

Step 1 · The pattern

No locks, just a check at the end

Each like is a transaction: read the count, add one, commit. Nobody takes a lock and nobody waits. At commit the database only asks: has what you read changed since you read it?

  • transaction
  • committed

In short

Optimistic transactions: how it works and how it breaks

A database can keep concurrent transactions correct in two ways. It can lock what they touch, so others wait. Or it can be optimistic: every transaction reads a snapshot without waiting, and at commit the database checks whether anything it read was changed by a transaction that committed first. If so, the commit is refused and the client runs the transaction again. Nothing blocks, which is excellent when transactions touch different keys. It is poor when many clients read and then rewrite the same key, such as a counter. Each successful commit invalidates every other transaction that read the old value, so most work is thrown away and retried, and the retries collide too. There are two fixes. Use an atomic operation: the database applies the change itself, the transaction never reads the value, and so it cannot conflict. Or spread a frequently updated value across many keys.

How this comes up in interviews

"A like counter is updated by read, add one, commit, on a database with optimistic transactions. What happens when a post goes viral?"

Then they ask

  1. Exactly which pairs of overlapping transactions conflict, and which do not?
  2. How do you fix the counter without adding locks?
  3. When would you prefer locking over optimistic transactions?

A strong answer covers

  • Transactions read a snapshot without waiting, and the commit is refused if something they read has since changed.
  • This works well when transactions touch different keys, because nothing blocks.
  • On one hot key, each commit invalidates every other transaction that read the old value, so most work is retried.
  • An atomic add never reads the value, so it cannot conflict.
  • A frequently updated value can also be spread across many keys.

Points to cover, not a script. Say them in your own words and tie them to a system you have worked on.

On one page: the track cheat sheet.

Last updated: Research paper

Diagrams are our own simulations: the timings, rates and counts in them are illustrative, not measurements. Captions are original wording.