Simplified
Simplified

L5.1What other requests see

YOU

What other requests see

Simplified's serverBEGIN;INSERT INTO completions (user_id, lesson_id) VALUES (1, 3);UPDATE users SET xp = xp + 10 WHERE id = 1;COMMIT;orROLLBACK;
Ada's laptoplesson 3Ada's phoneanother lessonserver Aserver BPostgresusersid1nameAdaxp120+10+10?140
server Aserver BAda's xpcommittedtime120?BEGIN;120?INSERT INTO completions  (user_id, lesson_id)VALUES (1, 3);120?UPDATE users SET xp = xp + 10WHERE id = 1;inside A: 130120?
server Aserver BAda's xpcommittedtimeUPDATE users SET xp = xp + 10WHERE id = 1;inside A: 130120?SELECT xp FROM usersWHERE id = 1;→130120?SELECT xp FROM usersWHERE id = 1;→120120?COMMIT;130?SELECT xp FROM usersWHERE id = 1;→130130?
server A's transactionAda's xp130PostgresAda's xp, committed120120server B
server Aserver BAda's xpcommittedtime120?BEGIN;120?UPDATE users SET xp = xp + 10WHERE id = 1;inside A: 130120?SELECT xp FROM usersWHERE id = 1;→120120?ROLLBACK;120?SELECT xp FROM usersWHERE id = 1;→120120?
placing your orderyour order historyyour orderscommittedtimeyou: user_id 82?BEGIN;2?INSERT INTO orders (user_id)VALUES (8);2?SELECT * FROM ordersWHERE user_id = 8;→2 rows2?COMMIT;3?SELECT * FROM ordersWHERE user_id = 8;→3 rows3?
server A's transactionAda's xp130PostgresAda's xp, committed120120server B

Databases · Lesson 5.1

When Requests Overlap

What other requests see

Simplified
0:00 / 2:51

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.

Ada's laptoplesson 3Ada's phoneanother lessonserver Aserver BPostgresusersid1nameAdaxp120+10+10?140

So while server A's transaction is still open, what does server B see in Ada's row?

server Aserver BAda's xpcommittedtime120?BEGIN;120?INSERT INTO completions  (user_id, lesson_id)VALUES (1, 3);120?UPDATE users SET xp = xp + 10WHERE id = 1;inside A: 130120?SELECT xp FROM usersWHERE id = 1;→130120?SELECT xp FROM usersWHERE id = 1;→120120?

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.

server Aserver BAda's xpcommittedtime120?BEGIN;120?INSERT INTO completions  (user_id, lesson_id)VALUES (1, 3);120?UPDATE users SET xp = xp + 10WHERE id = 1;inside A: 130120?SELECT xp FROM usersWHERE id = 1;→130120?SELECT xp FROM usersWHERE id = 1;→120120?COMMIT;130?SELECT xp FROM usersWHERE id = 1;→130130?

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.

server A's transactionAda's xp130PostgresAda's xp, committed120120server B

So why hide server A's 130 from server B?

Because until COMMIT, server A could still roll back.

server Aserver BAda's xpcommittedtime120?BEGIN;120?UPDATE users SET xp = xp + 10WHERE id = 1;inside A: 130120?SELECT xp FROM usersWHERE id = 1;→120120?ROLLBACK;120?SELECT xp FROM usersWHERE id = 1;→120120?

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.

placing your orderyour order historyyour orderscommittedtimeyou: user_id 82?BEGIN;2?INSERT INTO orders (user_id)VALUES (8);2?SELECT * FROM ordersWHERE user_id = 8;→2 rows2?COMMIT;3?SELECT * FROM ordersWHERE user_id = 8;→3 rows3?

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.

Example: What other requests see

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.

Question 1

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?

orders

Two likes at the same moment

server A's transactionAda's xp130PostgresAda's xp, committed120120server B
server Aserver BAda's xpcommittedtime120?SELECT xp FROM usersWHERE id = 1;→120120?120 + 10 = 130120?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?
server Aserver BAda's xpcommittedtime120?SELECT xp FROM usersWHERE id = 1;→120120?SELECT xp FROM usersWHERE id = 1;→120120?120 + 10 = 130120 + 10 = 130120?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?
one after the other120server A130server B140both read first120server A130server B130
lost update
two requests read the same value, both write, and the second write overwrites the first
server Aserver BAda's xpcommittedtime120?SELECT xp FROM usersWHERE id = 1;→120120?SELECT xp FROM usersWHERE id = 1;→120120?120 + 10 = 130120 + 10 = 130120?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?
server Aserver BAda's xpcommittedtime120?BEGIN;120?SELECT xp FROM usersWHERE id = 1;→120120?UPDATE users SET xp = 130WHERE id = 1;inside A: 130120?BEGIN;120?SELECT xp FROM usersWHERE id = 1;→120120?
server Aserver BAda's xpcommittedtimeUPDATE users SET xp = 130WHERE id = 1;inside A: 130120?SELECT xp FROM usersWHERE id = 1;→120120?COMMIT;130?UPDATE users SET xp = 130WHERE id = 1;130?COMMIT;130?
first friend's likesecond friend's likelikescommittedtime41?SELECT likes FROM postsWHERE id = 5;→4141?SELECT likes FROM postsWHERE id = 5;→4141?41 + 1 = 4241 + 1 = 4241?UPDATE posts SET likes = 42WHERE id = 5;→UPDATE 142?UPDATE posts SET likes = 42WHERE id = 5;→UPDATE 142?
server Aserver BAda's xpcommittedtime120?SELECT xp FROM usersWHERE id = 1;→120120?SELECT xp FROM usersWHERE id = 1;→120120?120 + 10 = 130120 + 10 = 130120?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?

Databases · Lesson 5.2

When Requests Overlap

Two likes at the same moment

Simplified
0:00 / 2:35

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.

server Aserver BAda's xpcommittedtime120?SELECT xp FROM usersWHERE id = 1;→120120?120 + 10 = 130120?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?

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 Aserver BAda's xpcommittedtime120?SELECT xp FROM usersWHERE id = 1;→120120?SELECT xp FROM usersWHERE id = 1;→120120?120 + 10 = 130120 + 10 = 130120?

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 Aserver BAda's xpcommittedtime120?SELECT xp FROM usersWHERE id = 1;→120120?SELECT xp FROM usersWHERE id = 1;→120120?120 + 10 = 130120 + 10 = 130120?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?

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.

one after the other120server A130server B140both read first120server A130server B130

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 Aserver BAda's xpcommittedtime120?BEGIN;120?SELECT xp FROM usersWHERE id = 1;→120120?UPDATE users SET xp = 130WHERE id = 1;inside A: 130120?BEGIN;120?SELECT xp FROM usersWHERE id = 1;→120120?COMMIT;130?UPDATE users SET xp = 130WHERE id = 1;130?COMMIT;130?

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?

first friend's likesecond friend's likelikescommittedtime41?SELECT likes FROM postsWHERE id = 5;→4141?SELECT likes FROM postsWHERE id = 5;→4141?41 + 1 = 4241 + 1 = 4241?UPDATE posts SET likes = 42WHERE id = 5;→UPDATE 142?UPDATE posts SET likes = 42WHERE id = 5;→UPDATE 142?

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.

Example: Two likes at the same moment

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 40+10=5040 + 10 = 5040+10=50, and each UPDATE puts in 50.

Therefore, Ben ends with 50 XP, not 60: one award is lost.

Question 1

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?

XP

Question 2

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?

XP

Question 3

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?

Question 4

Which of these is a lost update?

Question 5

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?

likes

Question 6

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?

Let the database do the math

server Aserver BAda's xpcommittedtime120?SELECT xp FROM usersWHERE id = 1;→120120?SELECT xp FROM usersWHERE id = 1;→120120?120 + 10 = 130120 + 10 = 130120?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?UPDATE users SET xp = 130WHERE id = 1;→UPDATE 1130?
UPDATE users SET xp = xp + 10 WHERE id = 1;Postgresusersid1nameAdaxp120130120 + 10 = 130
server Aserver BAda's xpcommittedtime120?UPDATE users SET xp = xp + 10WHERE id = 1;→UPDATE 1120?UPDATE users SET xp = xp + 10WHERE id = 1;→UPDATE 1140?
letting the database do the math
the database changes the value in place, not the app
server Aserver BAda's xpcommittedtime120?BEGIN;120?UPDATE users SET xp = xp + 10WHERE id = 1;inside A: 130120?
server Aserver BAda's xpcommittedtimeUPDATE users SET xp = xp + 10WHERE id = 1;inside A: 130120?BEGIN;120?UPDATE users SET xp = xp + 10WHERE id = 1;120?COMMIT;130?inside B: 140130?COMMIT;140?
first friend's likesecond friend's likelikescommittedtime41?UPDATE postsSET likes = likes + 1WHERE id = 5;→UPDATE 142?UPDATE postsSET likes = likes + 1WHERE id = 5;→UPDATE 143?
UPDATE users SETxp = xp + 10WHERE id = 1;Postgresusersid1nameAdaxp120130120 + 10 = 130

Databases · Lesson 5.3

When Requests Overlap

Let the database do the math

Simplified
0:00 / 2:19

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 Aserver BAda's xpcommittedtime120?UPDATE users SET xp = xp + 10WHERE id = 1;→UPDATE 1130?UPDATE users SET xp = xp + 10WHERE id = 1;→UPDATE 1140?

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 Aserver BAda's xpcommittedtime120?BEGIN;120?UPDATE users SET xp = xp + 10WHERE id = 1;inside A: 130120?BEGIN;120?UPDATE users SET xp = xp + 10WHERE id = 1;120?COMMIT;130?inside B: 140130?COMMIT;140?

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?

first friend's likesecond friend's likelikescommittedtime41?UPDATE postsSET likes = likes + 1WHERE id = 5;→UPDATE 142?UPDATE postsSET likes = likes + 1WHERE id = 5;→UPDATE 143?

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.

Example: Let the database do the math

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 40+10=5040 + 10 = 5040+10=50.

Then, server B's UPDATE starts from server A's 50, and writes 50+10=6050 + 10 = 6050+10=60.

Therefore, Ben ends with 60 XP: neither award is lost.

Question 1

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?

XP

Question 2

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?

XP

Question 3

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?

Question 4

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?

Question 5

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?

likes

Question 6

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?

XP

Selling the last ticket once

server Aserver BAda's xpcommittedtime120?UPDATE users SET xp = xp + 10WHERE id = 1;→UPDATE 1130?UPDATE users SET xp = xp + 10WHERE id = 1;→UPDATE 1140?
UPDATE events SET tickets_left = tickets_left - 1 WHERE id = 7;eventsid7tickets_left1
buyer 1's requestbuyer 2's requesttickets_leftcommittedtime1?UPDATE eventsSET tickets_left =  tickets_left - 1WHERE id = 7;→UPDATE 10?UPDATE eventsSET tickets_left =  tickets_left - 1WHERE id = 7;→UPDATE 1-1?ticketticket-1?
buyer 1's requestbuyer 2's requesttickets_leftcommittedtime1?SELECT tickets_leftFROM events WHERE id = 7;→11?SELECT tickets_leftFROM events WHERE id = 7;→11?1 > 0, so buy1 > 0, so buy1?
buyer 1's requestbuyer 2's requesttickets_leftcommittedtime1?UPDATE eventsSET tickets_left =  tickets_left - 1WHERE id = 7  AND tickets_left > 0;→UPDATE 10?
buyer 1's requestbuyer 2's requesttickets_leftcommittedtimeUPDATE events …→UPDATE 10?UPDATE eventsSET tickets_left =  tickets_left - 1WHERE id = 7  AND tickets_left > 0;→UPDATE 00?ticketsold out0?
UPDATE events SET tickets_left = tickets_left - 1WHERE id = 7AND tickets_left > 0;UPDATE 1ticketUPDATE 0sold out
guard
a condition in the WHERE that must be true for the write to happen
UPDATE events SET tickets_left = tickets_left - 1WHERE id = 7 AND tickets_left > 0;UPDATE 1ticketUPDATE 0sold out
payment 1payment 2your balancecommittedtimeyou: user_id 530?UPDATE balancesSET balance = balance - 20WHERE user_id = 5  AND balance >= 20;→UPDATE 110?sent10?
payment 1payment 2your balancecommittedtimeUPDATE balancesSET balance = balance - 20WHERE user_id = 5  AND balance >= 20;→UPDATE 110?UPDATE balancesSET balance = balance - 20WHERE user_id = 5  AND balance >= 20;→UPDATE 010?sent10?
UPDATE eventsSET tickets_left = tickets_left - 1WHERE id = 7 AND tickets_left > 0;UPDATE 1ticketUPDATE 0sold out

Databases · Lesson 5.4

When Requests Overlap

Selling the last ticket once

Simplified
0:00 / 2:34

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 requestbuyer 2's requesttickets_leftcommittedtime1?UPDATE eventsSET tickets_left =  tickets_left - 1WHERE id = 7;→UPDATE 10?UPDATE eventsSET tickets_left =  tickets_left - 1WHERE id = 7;→UPDATE 1-1?ticketticket-1?

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 requestbuyer 2's requesttickets_leftcommittedtime1?UPDATE eventsSET tickets_left =  tickets_left - 1WHERE id = 7  AND tickets_left > 0;→UPDATE 10?UPDATE eventsSET tickets_left =  tickets_left - 1WHERE id = 7  AND tickets_left > 0;→UPDATE 00?ticketsold out0?

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.

UPDATE events SET tickets_left = tickets_left - 1WHERE id = 7 AND tickets_left > 0;UPDATE 1ticketUPDATE 0sold out

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.

payment 1payment 2your balancecommittedtimeyou: user_id 530?UPDATE balancesSET balance = balance - 20WHERE user_id = 5  AND balance >= 20;→UPDATE 110?UPDATE balancesSET balance = balance - 20WHERE user_id = 5  AND balance >= 20;→UPDATE 010?sentstopped10?

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.

Example: Selling the last ticket once

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.

Question 1

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?

tickets

Question 2

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?

tickets

Question 3

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?

Question 4

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?

Question 5

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?

payments

Question 6

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?

Assistant

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