Part 5 of 8 · 2 min read
Concurrency
Locks and constraints.
Problem 2: two different users want the last room. Suppose total_inventory is 100 and total_reserved is 99. User 1 and user 2 both check, and both see 1 room left. Both then run the update. Each transaction is isolated from the other (the I in ACID), so transaction 2 still sees 99 when it runs its update. Both commits succeed. Each writes 100 as the new count, so the table shows 100 reserved. But two guests hold reservations, and the last room has been sold twice. A lock or a check is needed.
The book compares three methods.
Option 1: pessimistic locking. Lock the rows as soon as a user starts reading them. In MySQL, SELECT ... FOR UPDATE locks the rows returned. Transaction 2 waits for transaction 1 to finish, then sees the new count and is refused.
- Good: simple, prevents conflicts, suits heavy contention.
- Bad: locks held too long slow down everything, and deadlocks are possible when several resources are locked. It does not scale. The book does not recommend it here.
Option 2: optimistic locking. Let everyone read, and check at write time that nothing changed. Add a version number to the row. Read the version. When writing, increase it by 1, and require that the stored version is still the one you read. If another transaction got there first, the check fails and the user retries.
- Good: no database locks, fast when conflicts are rare.
- Bad: when many users go for the same row, only one wins each round and the rest retry again and again.
A worked example (made up). Five users read version 7 of a row with 5 rooms left. Round 1: one wins and five writes were tried, four fail. Round 2: four tried, one wins. And so on. Total writes tried: 5 + 4 + 3 + 2 + 1 = 15 for 5 bookings. This is correct but wasteful. With low traffic, this is fine.
Option 3: database constraint. Add a rule to the table that the database enforces:
CHECK (total_inventory * 1.1 - total_reserved >= 0)
If a second booking would break it, the database rolls that transaction back. Simple to build, and like optimistic locking, good when conflicts are rare. Drawbacks the book lists: under heavy contention users see "available" and then "sold out", which is frustrating; the constraint is not easy to version-control as code; and not all databases support such constraints well.
The book judges optimistic locking and constraints both suitable, because reservation traffic is low.
Common mistake
Checking availability in one statement and updating in another, with nothing to link them. Between the two, someone else can book the last room. The check and the update must be protected together, by a lock, a version, or a constraint.
Check yourself: 1. Why do two isolated transactions both succeed? 2. What happens to the second writer under optimistic locking? 3. When does optimistic locking work badly?