Pessimistic locking
Claiming a row before you change it, so that two transactions cannot both act on the same old value, and what goes wrong when locks cross.
5 minute lesson
Read then write is a race
Two customers buy the last concert ticket at the same moment. Each transaction reads the stock, sees 1, and writes 0. Both succeed, and one ticket is sold twice. MVCC does not prevent this: both read a valid value. What is missing is a claim on the row between the read and the write.
- Both readstock 1
- Both writestock 0
- Resultsold twice
Claim the row first
SELECT stock FROM tickets WHERE id = 7 FOR UPDATE;- A locks
FOR UPDATEreads the row and takes a lock on it. Buyer A now holds the row until its transaction ends.- Buyer Aholds the lock
- Rowstock 1
- B waits
Buyer B runs the same statement and stops there. It is not refused. It waits in line.
- Buyer Awrites stock 0
- Buyer Bwaiting
- A commits
A commits and the lock is released. Now B's read returns the current value: stock 0. B sees that the ticket is gone and sells nothing.
- Buyer Breads stock 0
- Resultsold once
Two transactions run SELECT ... FOR UPDATE on the same ticket row. The first reduces the stock from 1 to 0 and commits.
What does the second transaction read when it continues?
- Stock 0, the value after the first commit
- Stock 1, the value from when it began
- An error, since the row was locked
- Nothing, since the row was deleted
Show the answer
- Stock 0, the value after the first commit
The second transaction was held at its read until the first one finished. When it is let through, it reads the row as it is now, with stock 0. Its decision is based on the truth and not on a value that was about to change. That is what the lock is for: the read and the write of one transaction happen with nobody in between.
A waiting locker reads the row after the holder commits, so it decides on the current value.
When two locks cross
Locks make transactions wait, and waiting can form a circle. Transfer 1 locks account A and then wants B. Transfer 2 has locked B and wants A. Neither can ever continue. That is a deadlock.
- Transfer 1holds A and waits for B
- Transfer 2holds B and waits for A
- The database notices the circle and cancels one of the two. The application has to run it again.
- Avoid the circle: always lock rows in the same order, such as the lower account id first.
- Keep transactions short, so that locks are held for milliseconds.
A transfer locks the sender's account first and the receiver's second. At the same moment one transfer goes from A to B and another from B to A.
Which change prevents the deadlock?
- Always lock the account with the lower id first
- Always lock the receiver before the sender
- Lock the two accounts in a random order
- Hold the two locks for a longer time
Show the answer
- Always lock the account with the lower id first
The circle arises because the two transfers take the same two locks in opposite orders. If every transfer locks the lower id first, both want A first. One gets it, the other waits at the very first lock and holds nothing, and no circle can form. Receiver first only swaps the roles and deadlocks in the same way.
Deadlocks need locks taken in different orders. One global order makes them impossible.
What is a deadlock, what does the database do about it, and how do you avoid it?
Show the answer
Two transactions each hold a lock that the other needs, so both wait for ever. The database detects the circle and cancels one of them, which the application must retry. To avoid deadlocks, every transaction takes its locks in the same order, and transactions are kept short so that locks are released quickly.
Recap
- Reading a value and then writing by it is a race. A row lock closes it.
- SELECT FOR UPDATE claims the row. Others who want it wait until the transaction ends.
- Locks taken in different orders can deadlock. Lock in one order, and keep transactions short.