Simplified
Simplified

L6.1Making the second write wait

YOU

Making the second write wait

Ada's laptopAda's phoneserver Aserver Busersidnamexp1Ada120timeUPDATE users SET xp = xp + 10WHERE id = 1;UPDATE 1xp 120 → 130UPDATE users SET xp = xp + 10WHERE id = 1;?
server Aserver Busersidnamexp1Ada120timeBEGIN;UPDATE users SET xp = xp + 10WHERE id = 1;UPDATE 1xp 120 → 130BEGIN;UPDATE users SET xp = xp + 10WHERE id = 1;waitsCOMMIT;UPDATE 1xp 130 → 140COMMIT;
row lock
a mark an UPDATE puts on each row it changes
usersidnamexp1Ada1202Ben403Cy04Dee3005Eli406Fay75server ACOMMIT;ROLLBACK;UPDATE users SET xp = xp + 10WHERE id = 1;waitsserver B
friend 1's likefriend 2's likeserver 1server 2postsidlikes427timeBEGIN;BEGIN;UPDATE postsSET likes = likes + 1WHERE id = 42;UPDATE 1likes 7 → 8UPDATE postsSET likes = likes + 1WHERE id = 42;waitsCOMMIT;UPDATE 1likes 8 → 9COMMIT;?
usersidnamexp1Ada1202Ben403Cy04Dee3005Eli406Fay75server ACOMMIT;ROLLBACK;UPDATE users SET xp = xp + 10WHERE id = 1;waitsserver B

Databases · Lesson 6.1

Making Requests Wait

Making the second write wait

Simplified
0:00 / 2:10

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 Aserver Busersidnamexp1Ada120timeBEGIN;UPDATE users SET xp = xp + 10WHERE id = 1;UPDATE 1xp 120 → 130BEGIN;UPDATE users SET xp = xp + 10WHERE id = 1;waits

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.

server Aserver Busersidnamexp1Ada140timeBEGIN;UPDATE users SET xp = xp + 10WHERE id = 1;UPDATE 1xp 120 → 130BEGIN;UPDATE users SET xp = xp + 10WHERE id = 1;waitsCOMMIT;UPDATE 1xp 130 → 140COMMIT;

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.

usersidnamexp1Ada1202Ben403Cy04Dee3005Eli406Fay75server ACOMMIT;ROLLBACK;UPDATE users SET xp = xp + 10WHERE id = 1;waitsserver B

Now what happens when two friends like your Instagram post at once?

friend 1's likefriend 2's likeserver 1server 2postsidlikes429timeBEGIN;BEGIN;UPDATE postsSET likes = likes + 1WHERE id = 42;UPDATE 1likes 7 → 8UPDATE postsSET likes = likes + 1WHERE id = 42;waitsCOMMIT;UPDATE 1likes 8 → 9COMMIT;

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.

Example: Making the second write wait

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.

Question 1

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?

Making the second read wait

server Aserver Busersidnamexp1Ada120timeBEGIN;UPDATE users SET xp = xp + 10WHERE id = 1;UPDATE 1xp 120 → 130BEGIN;UPDATE users SET xp = xp + 10WHERE id = 1;waitsCOMMIT;UPDATE 1xp 130 → 140COMMIT;
SELECT xp FROM users WHERE id = 1 FOR UPDATE;usersidnamexp1Ada1202Ben403Cy0server Aserver BSELECT xp FROM usersWHERE id = 1 FOR UPDATE;waitsSELECT xp FROM usersWHERE id = 1;120
server Aserver Busersidnamexp1Ada120timeSELECT xp FROM usersWHERE id = 1;120SELECT xp FROM usersWHERE id = 1;120120 + 10 = 130120 + 10 = 130UPDATE users SET xp = 130WHERE id = 1;UPDATE users SET xp = 130WHERE id = 1;
UPDATE usersSET xp =xp + 10WHERE id = 1;UPDATE eventsSET tickets_left =tickets_left - 1WHERE id = 7AND tickets_left > 0;
server Aserver Busersidnamexp1Ada130timeSELECT xp FROM usersWHERE id = 1;120SELECT xp FROM usersWHERE id = 1;120120 + 10 = 130120 + 10 = 130UPDATE users SET xp = 130WHERE id = 1;UPDATE users SET xp = 130WHERE id = 1;?
server Aserver Busersidnamexp1Ada120timeBEGIN;SELECT xp FROM usersWHERE id = 1 FOR UPDATE;120BEGIN;120 + 10 = 130SELECT xp FROM usersWHERE id = 1 FOR UPDATE;waitsUPDATE users SET xp = 130WHERE id = 1;COMMIT;130130 + 10 = 140UPDATE users SET xp = 140WHERE id = 1;COMMIT;
SELECT … FOR UPDATE
a SELECT that locks the rows it returns, until its transaction ends
SELECT xp FROM users WHERE id = 1 FOR UPDATE;usersidnamexp1Ada1202Ben403Cy0server Aserver BSELECT xp FROM usersWHERE id = 1 FOR UPDATE;waitsSELECT xp FROM usersWHERE id = 1;120
$20 to friend 1$20 to friend 2server 1server 2balancesidbalance930timeBEGIN;BEGIN;SELECT balance FROM balancesWHERE id = 9 FOR UPDATE;30SELECT balance FROM balancesWHERE id = 9 FOR UPDATE;waits30 is enough for 20: payUPDATE balances SET balance = 10WHERE id = 9;COMMIT;1010 isn't enough for 20ROLLBACK;?
server Aserver Busersidnamexp1Ada140timeSELECT xp FROM usersWHERE id = 1 FOR UPDATE;120SELECT xp FROM usersWHERE id = 1 FOR UPDATE;waits120 + 10 = 130UPDATE users SET xp = 130WHERE id = 1;COMMIT;130130 + 10 = 140UPDATE users SET xp = 140WHERE id = 1;COMMIT;

Databases · Lesson 6.2

Making Requests Wait

Making the second read wait

Simplified
0:00 / 2:40

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.

server Aserver Busersidnamexp1Ada130timeSELECT xp FROM usersWHERE id = 1;120SELECT xp FROM usersWHERE id = 1;120120 + 10 = 130120 + 10 = 130UPDATE users SET xp = 130WHERE id = 1;UPDATE users SET xp = 130WHERE id = 1;

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 Aserver Busersidnamexp1Ada120timeBEGIN;SELECT xp FROM usersWHERE id = 1 FOR UPDATE;120BEGIN;SELECT xp FROM usersWHERE id = 1 FOR UPDATE;waits

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 Aserver Busersidnamexp1Ada140timeBEGIN;SELECT xp FROM usersWHERE id = 1 FOR UPDATE;120BEGIN;120 + 10 = 130SELECT xp FROM usersWHERE id = 1 FOR UPDATE;waitsUPDATE users SET xp = 130WHERE id = 1;COMMIT;130130 + 10 = 140UPDATE users SET xp = 140WHERE id = 1;COMMIT;

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.

SELECT xp FROM users WHERE id = 1 FOR UPDATE;usersidnamexp1Ada1202Ben403Cy0server Aserver BSELECT xp FROM usersWHERE id = 1 FOR UPDATE;waitsSELECT xp FROM usersWHERE id = 1;120

Now what if Venmo's server checks your balance in its own code first?

$20 to friend 1$20 to friend 2server 1server 2balancesidbalance910timeBEGIN;BEGIN;SELECT balance FROM balancesWHERE id = 9 FOR UPDATE;30SELECT balance FROM balancesWHERE id = 9 FOR UPDATE;waits30 is enough for 20: payUPDATE balances SET balance = 10WHERE id = 9;COMMIT;1010 isn't enough for 20ROLLBACK;

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.

Example: Making the second read wait

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.

Question 1

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?

dollars

Question 2

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?

XP

Question 3

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?

Question 4

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?

Question 5

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?

Question 6

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?

Assistant

Highlight lesson text and click Ask AI, or type a question below.