Databases · Lesson 6.1
Making the second write wait
Ada finishes lesson 3 on her laptop, and another lesson on her phone, at the same moment.
Server A handles one, and server B handles the other.
Each sends Postgres the UPDATE that sets xp to xp plus 10.
Say server A's UPDATE runs first, and makes Ada's XP 130.
But until server A commits, everyone else still sees her 120.
So how does server B's UPDATE start from server A's 130, to reach 140?
Server A sends BEGIN, then its UPDATE to Ada's row.
Postgres changes her XP from 120 to 130, inside A's transaction.
And it marks Ada's row as server A's, until A's transaction ends.
Then server B sends BEGIN, and the same UPDATE to Ada's row.
Postgres sees the mark, so B's UPDATE doesn't run yet: it waits.
Then server A sends COMMIT, so 130 is final, and the mark goes.
Now B's UPDATE goes ahead, from the new committed value, 130.
It adds 10, writes 140, and server B commits.
So Ada ends at 140, with both awards counted.
That mark is called a row lock.
A row lock is a mark an UPDATE puts on each row it changes.
It stays until the UPDATE's transaction ends, with COMMIT or ROLLBACK.
Any other write to a locked row waits until then.
Now what happens when two friends like your Instagram post at once?
Instagram might keep a posts table, where your post's likes column holds 7.
Each like reaches its own server, which sends BEGIN and an UPDATE adding 1.
Server 1's UPDATE runs first: it locks your post's row, and writes 8.
So server 2's UPDATE waits for that lock.
Server 1 commits, so 8 is final, and the lock goes.
Then server 2's UPDATE starts from 8, and writes 9.
So your post shows 9 likes: both friends' likes count.
An UPDATE locks each row it changes, and other writes to that row wait for its COMMIT or ROLLBACK.
Dee's row in Simplified's users table has id 4 and 300 XP. Server A sends BEGIN; and UPDATE users SET xp = xp + 10 WHERE id = 4;, and hasn't committed yet. Then server B sends BEGIN; and the same UPDATE to Dee's row.
What does server B's UPDATE start from?
Explanation
First, server A's UPDATE locked Dee's row, so server B's UPDATE waits.
Then, server A commits, so its 310 is final, and the lock goes.
Therefore, server B's UPDATE starts from 310, and writes 320.
Ada's row in Simplified's users table holds 120 XP. Server A and server B each send BEGIN;, then UPDATE users SET xp = xp + 10 WHERE id = 1;, then COMMIT;, at about the same moment. Server A's UPDATE arrives first, and Ada ends at 140.
Why doesn't server B's UPDATE start from 120?
Databases · Lesson 6.2
Making the second read wait
So server B's UPDATE waits for server A's row lock, then starts from 130.
But a plain SELECT never waits for a lock.
It reads Ada's last committed value, even while her row is locked.
And a lost update starts with exactly that read.
Say server A and server B each SELECT Ada's XP, with a plain SELECT.
Both get 120, because neither has written anything yet.
Each adds 10 in its own code, and sets Ada's XP to 130.
So Ada ends at 130, and one award is lost.
Letting Postgres add the 10 fixes this, when adding is all the app does.
And a guard fixes it, when the check fits in the UPDATE's WHERE.
But sometimes the app must read the value first, and decide in its own code.
So how can server A's read make server B wait, so B never reads 120?
Server A sends BEGIN, then a SELECT for Ada's XP, with FOR UPDATE on the end.
Postgres sends back 120, and locks Ada's row, just as an UPDATE would.
Then server B sends BEGIN, and the same SELECT, with FOR UPDATE.
Ada's row is locked, so server B's SELECT waits.
Server A adds 10 in its code, and sends an UPDATE setting XP to 130.
Then server A commits, so 130 is final, and the lock goes.
Now server B's SELECT goes ahead, gets the new committed value, 130, and locks the row.
Server B adds 10, sets XP to 140, and commits: no award is lost.
A SELECT like this is called SELECT … FOR UPDATE.
SELECT … FOR UPDATE is a SELECT that locks the rows it returns, until its transaction ends.
So read, decide, then write is safe: a second FOR UPDATE SELECT waits for the first one's COMMIT.
A plain SELECT, without FOR UPDATE, still never waits.
Now what if Venmo's server checks your balance in its own code first?
Say your Venmo balance is $30, and you send two friends $20 each, at once.
Server 1 sends BEGIN, then selects your balance with FOR UPDATE.
It gets 30, and your row is locked.
30 is enough for 20, so server 1 pays, sets your balance to 10, and commits.
Server 2's same SELECT waited for that lock, so now it gets 10.
10 isn't enough for 20, so server 2 sends ROLLBACK, and that payment is declined.
So your balance ends at $10, and never drops below zero.
SELECT … FOR UPDATE locks the row it reads, so a second one waits, then reads the committed value.
Dee's row in Simplified's users table has id 4 and 300 XP. Server A and server B each send BEGIN;, then SELECT xp FROM users WHERE id = 4 FOR UPDATE;, then add 10 in their own code, set Dee's XP to that number, and commit. Server A's SELECT arrives first.
What does server B's SELECT get back?
Explanation
First, server A's SELECT gets 300 and locks Dee's row, so server B's SELECT waits.
Then, server A sets Dee's XP to 310 and commits, so 310 is final, and the lock goes.
Therefore, server B's SELECT gets 310, and server B sets Dee's XP to 320.
Say Venmo keeps a balances table, and your row, id 9, holds 50 dollars. You send three friends 20 dollars each, at the same moment. Each payment's server sends BEGIN; and SELECT balance FROM balances WHERE id = 9 FOR UPDATE;. If the balance is at least 20, it sets the balance to 20 less and commits; if not, it sends ROLLBACK;.
What is your balance afterwards, in dollars?
Fay's row in Simplified's users table has id 6 and 75 XP. Server A and server B each send BEGIN;, then SELECT xp FROM users WHERE id = 6; with no FOR UPDATE. Both SELECTs arrive before either server writes. Each adds 10 in its own code, sets Fay's XP to that number, and commits.
How much XP does Fay have afterwards?
Ada's row in Simplified's users table holds 120 XP. Server A sends BEGIN; and SELECT xp FROM users WHERE id = 1 FOR UPDATE;, and gets 120. Server B sends BEGIN; and the same SELECT, which waits. Then server A decides not to award any XP, and sends ROLLBACK; without writing anything.
What happens to server B's SELECT?
Ada's row in Simplified's users table holds 120 XP. Server A sends BEGIN; and SELECT xp FROM users WHERE id = 1 FOR UPDATE;, and hasn't committed yet. Now server B sends SELECT xp FROM users WHERE id = 1;, with no FOR UPDATE.
What happens to server B's SELECT?
Simplified's server reads Ada's XP into its code, adds 10 there, then sets her XP to the new number. Server A and server B can run this at the same moment.
Which way of running it makes sure no award is lost?
Say Amazon keeps a stock table, and item 5's in_stock column holds 2. Three shoppers order it at the same moment, on three servers. Each server sends BEGIN; and SELECT in_stock FROM stock WHERE id = 5 FOR UPDATE;. If the number it gets is above 0, it sets in_stock to 1 less and commits; if not, it sends ROLLBACK;.
Which shopper's SELECT gets 0?