Databases · Lesson 5.1
What other requests see
When Ada finishes lesson 3, Simplified's server sends one transaction.
It sends BEGIN, the INSERT of her completion, the UPDATE that adds 10 XP, then COMMIT.
Until COMMIT, those changes aren't final: a ROLLBACK would still undo them.
And Simplified runs two servers, server A and server B, both writing to one database.
Say Ada finishes lesson 3 on her laptop, and server A handles it.
A moment later, she finishes another lesson on her phone, and server B handles that.
Each finish awards 10 XP, so from 120, she should end at 140.
So while server A's transaction is still open, what does server B see in Ada's row?
Server A sends BEGIN, then the INSERT of her completion for lesson 3.
Then it sends the UPDATE that adds 10 to Ada's XP.
Inside server A's transaction, Ada's XP is now 130.
So if server A reads Ada's XP, Postgres sends back 130.
Then server B reads Ada's XP, and Postgres sends back 120.
Because 120 is the last value committed, and server A hasn't committed yet.
Then server A sends COMMIT, and its 130 is final.
Now server B reads Ada's XP again, and gets 130.
So server B only sees server A's change once server A commits.
A transaction's changes are invisible to every other request until COMMIT.
Invisible means another request reads the last committed value instead.
So why hide server A's 130 from server B?
Because until COMMIT, server A could still roll back.
Say server A sends BEGIN and the UPDATE, so Ada's XP is 130 inside it.
Server B reads Ada's XP, and gets 120.
Then server A decides to stop halfway, and sends ROLLBACK.
Postgres undoes the UPDATE, so Ada's XP is 120 again.
Server B reads it again, and still gets 120.
So server B never saw a 130 that was never kept.
Now what does your Amazon order history show while an order is still being placed?
Say Amazon keeps your orders in an orders table, and you've placed 2 before.
Amazon's server might place your new order in one transaction.
It sends BEGIN, then an INSERT that adds your new order.
Meanwhile, you open your order history on your phone, which reads your orders.
Postgres sends back your 2 old orders, and not the new one.
Because the new order isn't committed yet.
Once Amazon's server sends COMMIT, your order history shows all 3.
Until a transaction commits, every other request reads the values from before it.
Simplified's users table holds Ada's row, id 1, with 120 XP. Server A sends BEGIN; and UPDATE users SET xp = xp + 10 WHERE id = 1;, and hasn't committed yet. Then server B sends SELECT xp FROM users WHERE id = 1;.
What does server B get back?
Explanation
First, server A's UPDATE made Ada's XP 130, but only inside server A's own transaction.
Then, server A hasn't committed, so every other request reads the last committed value.
Therefore, server B gets back 120.
Say Amazon keeps orders in an orders table, and Fay, user 6, has placed 4 orders. Amazon's server sends BEGIN; and an INSERT that adds a fifth order for Fay, and hasn't committed yet. Meanwhile, Fay opens her order history, and another request sends SELECT COUNT(*) FROM orders WHERE user_id = 6;.
What number does that request get back?
Databases · Lesson 5.2
Two likes at the same moment
So while server A's transaction is open, server B reads Ada's last committed XP, 120.
Now say Simplified's server added XP by reading first, then writing.
First, it selects Ada's XP, and Postgres sends back 120.
Then it adds 10 in JavaScript, which makes 130.
Then it sends an UPDATE that sets Ada's XP to 130.
But what if both of Ada's finishes read before either one writes?
Server A selects Ada's XP, and gets 120.
Then server B selects it too, before server A writes, and also gets 120.
Each server adds 10 to its 120, which makes 130.
Server A sets Ada's XP to 130.
Then server B sets it to 130, in place of server A's 130.
So after two awards of 10, Ada has 130, not 140.
Because server B's UPDATE doesn't add 10: it puts in the number it worked out.
If server B read after server A wrote, it would get 130, and write 140.
When the order of requests changes the result like this, it's called a race condition.
Here, one award of 10 XP is lost, for a lesson Ada really finished.
This is called a lost update.
A lost update means two requests read the same value, both write, and the second write overwrites the first.
So does wrapping each request in BEGIN and COMMIT fix it?
Server A sends BEGIN, selects 120, and sets Ada's XP to 130, without committing yet.
Then server B sends BEGIN, and selects Ada's XP.
Server A hasn't committed, so server B gets 120, the last committed value.
Server A commits its 130, then server B sets 130 too, and commits.
So Ada still ends at 130: BEGIN and COMMIT don't stop a lost update.
Now what happens when two friends like your post at the same moment?
Say your Instagram post has 41 likes, and Instagram's server reads, then writes.
Your first friend's like reads 41, and so does your second friend's.
Each works out 41 plus 1, which is 42.
The first like sets the count to 42, then the second sets 42 in its place.
So two friends liked your post, but the count went up by only 1.
When two requests read the same value, then both write, the first write can be lost.
Simplified's users table holds Ben's row, id 2, with 40 XP. Server A and server B each award Ben 10 XP by reading, then writing. Both send SELECT xp FROM users WHERE id = 2; before either one writes. Then each sends an UPDATE that sets Ben's XP to the value it read plus 10.
What XP does Ben end with?
Explanation
First, both reads come before either write, so both servers get 40.
Then, each works out , and each UPDATE puts in 50.
Therefore, Ben ends with 50 XP, not 60: one award is lost.
Simplified's users table holds Dee's row, id 4, with 300 XP. Server A and server B each award Dee 10 XP by reading her XP, adding 10 in JavaScript, then sending an UPDATE that sets her XP to the result. Server A reads and writes first; only after its UPDATE does server B read.
How much XP does Dee end with?
Simplified's users table holds Eli's row, id 5, with 40 XP. Three requests each award Eli 10 XP by reading his XP, adding 10 in JavaScript, then sending an UPDATE that sets his XP to the result. All three read before any of them writes.
How much XP does Eli end with?
Simplified's users table holds Fay's row, id 6, with 75 XP. Server A sends BEGIN;, reads Fay's XP, and sets it to 85, but hasn't committed yet. Then server B sends BEGIN; and reads Fay's XP. Server A commits. Then server B sets Fay's XP to what it read plus 10, and commits.
What XP does Fay end with, and why?
Which of these is a lost update?
Say an Instagram post has 99 likes, and Instagram's server adds a like by reading the count, adding 1, then setting the count to the result. Two friends like the post at the same moment, and both of their requests read the count before either writes. Later, after both have written, a third friend likes it.
What does the like count end at?
Simplified's users table holds Ada's row, id 1, with 120 XP. Server A and server B both read 120. Then server A sends UPDATE users SET xp = 130 WHERE id = 1;, and after it, server B sends the same UPDATE.
Why doesn't Ada end at 140?
Databases · Lesson 5.3
Let the database do the math
So when server A and server B each read Ada's 120, then write 130, one award is lost.
Because each server works out the new XP itself, from a value that's already old.
So how can both awards count, when both requests come at once?
Simplified's server can send the UPDATE you already know: set xp to xp plus 10.
With xp plus 10, Postgres reads Ada's XP itself, from her row.
Then it adds 10, and writes the result, all in one statement.
It reads the row as it is when the UPDATE runs, not as a request saw it earlier.
Server A sends the UPDATE that sets xp to xp plus 10.
Postgres reads Ada's 120, adds 10, and writes 130.
Then server B sends the same UPDATE.
Postgres reads Ada's XP again, now server A's 130.
It adds 10, and writes 140, so both awards count.
This is called letting the database do the math.
Letting the database do the math means the database changes the value in place, not the app.
But what about Ada's finishing transaction, where server A's 130 isn't committed yet?
Server A sends BEGIN and the UPDATE that adds 10, and hasn't committed.
Then server B sends BEGIN and the same UPDATE.
Server A commits its 130.
Server B's UPDATE still starts from server A's 130, and writes 140.
So when server B commits, Ada has 140.
We will explore how Postgres makes server B's UPDATE start from 130 in the next lesson.
For now, just know it does.
So how can both friends' likes on your Instagram post count?
Your post has 41 likes, and now Instagram's server lets the database do the math.
Your first friend's like sends an UPDATE that sets likes to likes plus 1.
Postgres reads 41, adds 1, and writes 42.
Your second friend's like sends the same UPDATE.
Postgres reads 42, because the first like already wrote it, adds 1, and writes 43.
So both likes count.
When the database can change a value in place, don't read it into the app first.
Simplified's users table holds Ben's row, id 2, with 40 XP. Server A and server B each send UPDATE users SET xp = xp + 10 WHERE id = 2; at the same moment, and server A's runs first.
What XP does Ben end with?
Explanation
First, server A's UPDATE reads Ben's 40, and writes .
Then, server B's UPDATE starts from server A's 50, and writes .
Therefore, Ben ends with 60 XP: neither award is lost.
Simplified's users table holds Dee's row, id 4, with 300 XP. Three servers each send UPDATE users SET xp = xp + 10 WHERE id = 4; at the same moment.
How much XP does Dee end with?
Simplified's users table holds Eli's row, id 5, with 40 XP. Server B reads Eli's XP, gets 40, and works out 50 in JavaScript. Then server A sends UPDATE users SET xp = xp + 10 WHERE id = 5;. After that, server B sends UPDATE users SET xp = 50 WHERE id = 5;.
How much XP does Eli end with?
Say Instagram keeps each post's likes in a posts table, and post 9 has 42 likes. Many friends like post 9 at the same moment.
Which way of adding each like lets the database do the math?
Simplified's users table holds Cy's row, id 3, with 0 XP. Server A and server B each send UPDATE users SET xp = xp + 10 WHERE id = 3; at the same moment, server A's first, and Cy ends with 20 XP.
Why is neither award lost?
Say Instagram keeps each post's likes in a posts table, and post 5 has 41 likes. Five friends like it at the same moment, and each like sends UPDATE posts SET likes = likes + 1 WHERE id = 5;.
What does the like count end at?
Simplified's users table holds Ada's row, id 1, with 120 XP. Server B selects Ada's XP and gets 120. Then server A sends UPDATE users SET xp = xp + 10 WHERE id = 1;, which writes 130. Then server B sends UPDATE users SET xp = xp + 10 WHERE id = 1;.
How much XP does Ada end with?
Databases · Lesson 5.4
Selling the last ticket once
So when Postgres does the math, both of Ada's awards count: 120, then 130, then 140.
And Ticketmaster could sell tickets the same way.
Say it keeps its shows in an events table, with an id and tickets left.
When someone buys a ticket, its server subtracts 1 from tickets left, in the database.
Show 7 has 1 ticket left, and two buyers click buy at the same moment.
Buyer 1's request sends an UPDATE that sets tickets left to tickets left minus 1.
Postgres reads 1, subtracts 1, and writes 0.
Then buyer 2's same UPDATE starts from buyer 1's 0, and writes minus 1.
So two tickets are sold, for one seat.
So how can Ticketmaster sell the last ticket only once?
It could select tickets left first, and only buy if there's more than 0.
But both buyers could read 1 before either one writes, like a lost update.
So Ticketmaster might put the check in the UPDATE itself, in its WHERE.
Buyer 1's UPDATE only changes the row where the id is 7, and tickets left is more than 0.
Tickets left is 1, which is more than 0, so the row matches.
Postgres subtracts 1, writes 0, and sends back UPDATE 1.
Then buyer 2's UPDATE, the same statement, starts from buyer 1's 0.
0 isn't more than 0, so it matches no row, and changes nothing.
Postgres sends back UPDATE 0, which isn't an error: no row matched.
So Ticketmaster's server tells buyer 2 the show is sold out.
The check and the write happen in one statement, so no other write can come between them.
This check is called a guard.
A guard is a condition in the WHERE that must be true for the write to happen.
And the command tag tells the app whether its write won: UPDATE 1, or UPDATE 0.
Now what stops two Venmo payments from spending the same dollars?
Say your Venmo balance is $30, and you send two friends $20 each, at once.
Venmo's server might take $20 only where your balance is at least $20.
The first payment's UPDATE finds $30, so it matches, and leaves $10.
Postgres sends back UPDATE 1, so that payment goes through.
The second payment's UPDATE finds $10, less than $20, so it matches no row.
Postgres sends back UPDATE 0, so Venmo stops that payment.
A guard puts the check and the write in one statement, and the command tag says who won.
Ticketmaster's events table holds show 7 with 2 tickets left. Three buyers click buy at once, and their servers each send UPDATE events SET tickets_left = tickets_left - 1 WHERE id = 7 AND tickets_left > 0;. The three UPDATEs run one after another.
Which command tag does the third buyer get?
Explanation
First, the first UPDATE finds 2, more than 0, so it writes 1 and gets UPDATE 1.
Then, the second finds 1, more than 0, so it writes 0 and gets UPDATE 1.
Therefore, the third finds 0, which isn't more than 0, so it gets UPDATE 0.
Ticketmaster's events table holds show 7 with 3 tickets left. Five buyers click buy at once, and each of their servers sends UPDATE events SET tickets_left = tickets_left - 1 WHERE id = 7 AND tickets_left > 0;. The five UPDATEs run one after another.
How many tickets left does show 7 have afterwards?
Ticketmaster's events table holds show 4 with 1 ticket left. Three buyers click buy at once, and each of their servers sends UPDATE events SET tickets_left = tickets_left - 1 WHERE id = 4;, with no other condition. The three UPDATEs run one after another.
How many tickets left does show 4 have afterwards?
Ticketmaster's server sends UPDATE events SET tickets_left = tickets_left - 1 WHERE id = 7 AND tickets_left > 0; for a buyer, and Postgres sends back UPDATE 0.
What should the server tell the buyer?
Say Venmo keeps balances in a balances table, and user 5 has $30. Two payments of $20 from user 5 are sent at the same moment.
Which UPDATE guards each payment, so the balance can't go below $0?
Say Venmo keeps balances in a balances table, and user 5 has $50. Three payments of $20 from user 5 are sent at once, and each sends UPDATE balances SET balance = balance - 20 WHERE user_id = 5 AND balance >= 20;. They run one after another.
How many of them get back UPDATE 1?
Ticketmaster's events table holds show 7 with 1 ticket left. For each buyer, the server first sends SELECT tickets_left FROM events WHERE id = 7;, and if it gets back more than 0, sends UPDATE events SET tickets_left = tickets_left - 1 WHERE id = 7;. Two buyers click buy at the same moment.
What can happen?