Simplified
Simplified

L3.1What if it fails halfway?

YOU

What if it fails halfway?

Simplified's server sendsPostgres sends backINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yetnot final yetcompletionsuser_idlesson_id1112not final yetusersidnamexp1Ada120………CoursesLesson 1✓Lesson 2✓Lesson 3✓?
Simplified's server sendsPostgres sends backINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yet✕server crashesUPDATE users SET xp = xp + 10 WHERE id = 1;never sentnot final yet↻server restartsnot final yetcompletionsuser_idlesson_id111213not final yetusersidnamexp1Ada120………CoursesLesson 1✓Lesson 2✓Lesson 3✓?
Simplified's server sendsPostgres sends backUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yet✕server crashesINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);never sentnot final yetnot final yetcompletionsuser_idlesson_id1112not final yetusersidnamexp1Ada120………CoursesLesson 1✓Lesson 2✓Lesson 3✓?
Venmo's server sendsPostgres sends backUPDATE balances SET balance = balance - 20 WHERE id = 1;UPDATE 1not final yet✕server crashesUPDATE balances SET balance = balance + 20 WHERE id = 2;never sentnot final yetnot final yetbalancesidnamebalance1You502Mia10You → Mia: $20→30YouMia?
Simplified's server sendsone unitINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);not final yetboth happen,UPDATE users SET xp = xp + 10 WHERE id = 1;not final yetor neither doesVenmo's server sendsone unitUPDATE balances SET balance = balance - 20 WHERE id = 1;not final yetboth happen,UPDATE balances SET balance = balance + 20 WHERE id = 2;not final yetor neither does

Databases · Lesson 3.1

All or Nothing

What if it fails halfway?

Simplified
0:00 / 2:21

Ada has finished lessons 1 and 2 on Simplified.

So completions holds her two rows, (1, 1) and (1, 2), one for each lesson.

And her row in users holds 120 XP.

Now say she finishes lesson 3, "Reading data", worth 10 XP.

So what does Simplified's server send Postgres, to save that she finished it?

Simplified's server sendsPostgres sends backINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yetnot final yetcompletionsuser_idlesson_id111213not final yetusersidnamexp1Ada120 → 130………CoursesLesson 1✓Lesson 2✓Lesson 3✓

It sends two writes, one after the other.

First, an INSERT adds the row (1, 3) to completions.

That row is the tick Ada sees beside lesson 3 on Courses.

Then, an UPDATE adds 10 to her XP, so 120 becomes 130.

Postgres runs each write on its own, and sends back a command tag for each.

But what if something goes wrong between the two writes?

Simplified's server sendsPostgres sends backINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yet✕server crashesUPDATE users SET xp = xp + 10 WHERE id = 1;never sentnot final yet↻server restartsnot final yetcompletionsuser_idlesson_id111213not final yetusersidnamexp1Ada120………CoursesLesson 1✓Lesson 2✓Lesson 3✓

Say Simplified's server crashes just after the INSERT, before it sends the UPDATE.

Postgres already stored the row (1, 3), so Ada sees the tick on lesson 3.

But the UPDATE was never sent, so she still has 120 XP.

She finished the lesson, and never got the 10 XP it earned.

And once the server restarts, it has no memory of what it was halfway through.

So nothing ever sends that UPDATE.

So would sending the UPDATE first fix it?

Simplified's server sendsPostgres sends backUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yet✕server crashesINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);never sentnot final yetnot final yetcompletionsuser_idlesson_id1112——not final yetusersidnamexp1Ada120 → 130………CoursesLesson 1✓Lesson 2✓Lesson 3✓

Say the server sends the UPDATE first, so Ada's XP goes from 120 to 130.

Then it crashes, before it sends the INSERT.

Now Ada has 130 XP, but no tick on lesson 3.

So neither order is safe: a crash between them leaves one write without the other.

Now what happens when you pay a friend on Venmo?

Venmo's server sendsPostgres sends backUPDATE balances SET balance = balance - 20 WHERE id = 1;UPDATE 1not final yet✕server crashesUPDATE balances SET balance = balance + 20 WHERE id = 2;never sentnot final yetnot final yetbalancesidnamebalance1You50 → 302Mia10You → Mia: $20→30YouMia

Say you have $50 on Venmo, and your friend Mia has $10.

Venmo might keep everyone's money in a balances table, one row each.

You send Mia $20, so Venmo's server sends two UPDATEs.

The first takes 20 from your balance, so 50 becomes 30.

The second would add 20 to Mia's balance, so 10 becomes 30.

But say the server crashes between them.

Your $20 has left your balance and never reached Mia's, so it just vanishes.

In both, the two writes belong together, so they must act as one unit: both happen, or neither does.

Simplified's server sendsone unitINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);not final yetboth happen,UPDATE users SET xp = xp + 10 WHERE id = 1;not final yetor neither doesnot final yetcompletionsuser_idlesson_id111213not final yetusersidnamexp1Ada120 → 130………

Writes that belong together must all be kept, or none of them.

Example: What if it fails halfway?

Eli, id 5, has 40 XP on Simplified. He finishes lesson 1, worth 10 XP. Simplified's server sends INSERT INTO completions (user_id, lesson_id) VALUES (5, 1);, and Postgres sends back INSERT 0 1. Then the server crashes, before it sends UPDATE users SET xp = xp + 10 WHERE id = 5;.

How much XP does Eli have afterwards?

Explanation

First, Postgres stored the row (5, 1) on its own, so Eli sees the tick on lesson 1.

Then, the UPDATE was never sent, and the restarted server has no memory of it.

Therefore, Eli still has 40 XP.

Question 1

On Simplified, completions holds one row for Dee, id 4: (4, 1), because she finished lesson 1. She now finishes lesson 2, worth 10 XP. Simplified's server sends INSERT INTO completions (user_id, lesson_id) VALUES (4, 2);, and Postgres sends back INSERT 0 1. Then the server crashes, before it sends the UPDATE that adds 10 to her XP.

How many rows does completions hold for Dee afterwards?

rows

Making two writes one

Simplified's server sendsPostgres sends backINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yet✕server crashesUPDATE users SET xp = xp + 10 WHERE id = 1;never sentnot final yet↻server restartsnot final yetcompletionsuser_idlesson_id1112not final yetusersidnamexp1Ada120………CoursesLesson 1✓Lesson 2✓Lesson 3✓?
Simplified's server sendstransactionINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;not final yet
transaction
a group of statements Postgres treats as one unit
Simplified's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yetCOMMIT;COMMITnot final yetnot final yetcompletionsuser_idlesson_id1112not final yetusersidnamexp1Ada120………CoursesLesson 1✓Lesson 2✓Lesson 3✓?
Venmo's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetUPDATE balances SET balance = balance - 20 WHERE id = 1;UPDATE 1not final yetUPDATE balances SET balance = balance + 20 WHERE id = 2;UPDATE 1not final yetCOMMIT;COMMITnot final yetnot final yetbalancesidnamebalance1You502Mia10?

Databases · Lesson 3.2

All or Nothing

Making two writes one

Simplified
0:00 / 1:40

So when Ada finishes lesson 3, Simplified's server sends two writes.

The INSERT adds her row (1, 3), and the UPDATE takes her from 120 to 130 XP.

If the server crashes between them, she gets the tick without the 10 XP.

So how can the server make the two writes one unit?

It asks Postgres to group them, so they count as one.

A group like this is called a transaction.

A transaction is a group of statements Postgres treats as one unit.

Simplified's server sendstransactionINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;not final yet

So how does the server mark where a transaction starts and ends?

It sends one statement before the writes, and one after.

BEGIN starts a transaction.

COMMIT ends the transaction, and makes every change in it final, together.

Simplified's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yetCOMMIT;COMMITnot final yetnot final yetcompletionsuser_idlesson_id111213not final yetusersidnamexp1Ada120 → 130………CoursesLesson 1✓Lesson 2✓Lesson 3✓

First, the server sends BEGIN, and Postgres sends back BEGIN.

Then it sends the INSERT and the UPDATE, and gets back a command tag for each.

But neither change is final yet.

Last, it sends COMMIT, and Postgres sends back COMMIT.

So the tick and the 10 XP become final at the same moment.

We will explore what a crash before COMMIT does in a later lesson.

For now, just know nothing in a transaction is final until COMMIT.

Now how would Venmo send your $20 to Mia?

Venmo's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetUPDATE balances SET balance = balance - 20 WHERE id = 1;UPDATE 1not final yetUPDATE balances SET balance = balance + 20 WHERE id = 2;UPDATE 1not final yetCOMMIT;COMMITnot final yetnot final yetbalancesidnamebalance1You50 → 302Mia10 → 30

Venmo's server might send BEGIN first.

Then the UPDATE that takes 20 from your balance, so 50 becomes 30.

Then the UPDATE that adds 20 to Mia's balance, so 10 becomes 30.

Then COMMIT, so both balances become final together.

So a transaction's changes all become final together, when Postgres sends back COMMIT.

Example: Making two writes one

Cy, id 3, has 0 XP on Simplified, and finishes lesson 1, worth 10 XP. Simplified's server sends BEGIN;, then INSERT INTO completions (user_id, lesson_id) VALUES (3, 1);, then UPDATE users SET xp = xp + 10 WHERE id = 3;, then COMMIT;. Postgres sends back COMMIT.

How much XP does Cy have once COMMIT comes back?

Explanation

First, BEGIN started a transaction, so the INSERT and the UPDATE weren't final when they ran.

Then, COMMIT made both changes final, together: the row (3, 1), and 0 plus 10 XP.

Therefore, Cy has 10 XP.

Question 1

Simplified's server sends Postgres four statements, in this order: BEGIN;, INSERT INTO completions (user_id, lesson_id) VALUES (2, 1);, UPDATE users SET xp = xp + 10 WHERE id = 2; and COMMIT;. Each one works.

What does Postgres send back, in order?

Question 2

Ben, id 2, has 40 XP on Simplified. He finishes lesson 1, and then lesson 2, each worth 10 XP. For each finish, Simplified's server sends BEGIN;, an INSERT into completions, UPDATE users SET xp = xp + 10 WHERE id = 2; and COMMIT;. Postgres sends back COMMIT both times.

How much XP does Ben have afterwards?

XP

Question 3

Ada has 120 XP, and finishes lesson 3, worth 10 XP. Simplified's server has sent BEGIN;, then INSERT INTO completions (user_id, lesson_id) VALUES (1, 3);, then UPDATE users SET xp = xp + 10 WHERE id = 1;, and Postgres has sent back UPDATE 1. The server hasn't sent COMMIT yet.

Are the row (1, 3) and Ada's 130 XP final?

Question 4

Simplified's server sends BEGIN;, then INSERT INTO completions (user_id, lesson_id) VALUES (3, 2);, then UPDATE users SET xp = xp + 10 WHERE id = 3;, then UPDATE users SET xp = xp + 10 WHERE id = 5;, then COMMIT;. Each one works.

How many writes become final when Postgres sends back COMMIT?

writes

Question 5

Say Venmo keeps everyone's money in a balances table. You send your friend $20, which takes two UPDATEs: one takes 20 from your balance, and the other adds 20 to your friend's.

Which list of statements makes the two UPDATEs one unit?

Question 6

Say Venmo keeps everyone's money in a balances table: you have 50, and Mia has 10. You send Mia $20. Venmo's server sends BEGIN;, an UPDATE taking 20 from your balance, an UPDATE adding 20 to Mia's, then COMMIT;. Postgres sends back BEGIN, UPDATE 1, UPDATE 1, then COMMIT.

When does your new balance of 30 become final?

Taking back what you already sent

Simplified's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yetCOMMIT;COMMITnot final yetnot final yetcompletionsuser_idlesson_id1112not final yetusersidnamexp1Ada120………CoursesLesson 1✓Lesson 2✓Lesson 3✓
Simplified's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yet✕the server's code throws an errorROLLBACK;ROLLBACKnot final yetnot final yetcompletionsuser_idlesson_id1112not final yetusersidnamexp1Ada120………CoursesLesson 1✓Lesson 2✓Lesson 3✓??
Venmo's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetUPDATE balances SET balance = balance - 20 WHERE id = 1;UPDATE 1not final yet✕Venmo's code throws an errorROLLBACK;ROLLBACKnot final yetnot final yetbalancesidnamebalance1You502Mia10?
atomicity: all or nothingall keptCOMMITnone keptROLLBACKonly some keptneverAda finishes lesson 3tickand10 XPneithertick, no XPVenmo payment$20 to Mianeither$20 vanishes
atomicity
every change in a transaction is kept, or none is
atomicity: all or nothingall keptCOMMITnone keptROLLBACKonly some keptneverAda finishes lesson 3tickand10 XPneithertick, no XPVenmo payment$20 to Mianeither$20 vanishes

Databases · Lesson 3.3

All or Nothing

Taking back what you already sent

Simplified
0:00 / 2:19

So when Ada finishes lesson 3, Simplified's server sends BEGIN, the INSERT, the UPDATE, then COMMIT.

And COMMIT makes her row (1, 3) and her 130 XP final, together.

But say something goes wrong in the server's own code, before it sends COMMIT.

Simplified's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yet✕the server's code throws an errornot final yetcompletionsuser_idlesson_id111213not final yetusersidnamexp1Ada120 → 130………

The server has sent BEGIN, the INSERT for (1, 3), and the UPDATE.

So inside the transaction, the row (1, 3) is there, and Ada's XP is 130.

Then a line of the server's JavaScript throws an error.

So the server decides to stop, and keep none of Ada's finish.

So how does the server take back the two writes it already sent?

It sends ROLLBACK instead of COMMIT.

Simplified's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yet✕the server's code throws an errorROLLBACK;ROLLBACKnot final yetnot final yetcompletionsuser_idlesson_id111213not final yetusersidnamexp1Ada130 → 120………CoursesLesson 1✓Lesson 2✓Lesson 3✓

ROLLBACK ends the transaction, and undoes every change since BEGIN.

So Postgres undoes the INSERT: there's no row (1, 3).

And it undoes the UPDATE: Ada still has 120 XP.

Then Postgres sends back ROLLBACK.

The data is exactly as it was before BEGIN.

And does ROLLBACK undo Ada's finishes of lessons 1 and 2 too?

Simplified's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetINSERT INTO completions (user_id, lesson_id) VALUES (1, 3);INSERT 0 1not final yetUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1not final yet✕the server's code throws an errorROLLBACK;ROLLBACKnot final yetnot final yetcompletionsuser_idlesson_id111213not final yetusersidnamexp1Ada120………CoursesLesson 1✓Lesson 2✓Lesson 3✓

No: her rows (1, 1) and (1, 2) were committed when she finished those lessons.

That was long before this BEGIN.

ROLLBACK only undoes changes since BEGIN, so both rows stay.

So Ada keeps her ticks on lessons 1 and 2.

Now what if Venmo's server stops halfway through your $20 to Mia?

Venmo's server sendsPostgres sends backtransactionBEGIN;BEGINnot final yetUPDATE balances SET balance = balance - 20 WHERE id = 1;UPDATE 1not final yet✕Venmo's code throws an errorROLLBACK;ROLLBACKnot final yetnot final yetbalancesidnamebalance1You30 → 502Mia10

Say it sends BEGIN, then the UPDATE that takes 20 from your balance.

So inside the transaction, your 50 becomes 30.

Then a line of Venmo's own code throws an error, before it adds 20 to Mia's.

So the server might send ROLLBACK, and Postgres undoes the UPDATE.

You still have $50, and Mia still has $10: no money vanishes.

So a transaction keeps every change, with COMMIT, or none, with ROLLBACK.

This is called atomicity: all or nothing.

Atomicity means every change in a transaction is kept, or none is.

atomicity: all or nothingall keptCOMMITnone keptROLLBACKonly some keptneverAda finishes lesson 3tickand10 XPneithertick, no XPVenmo payment$20 to Mianeither$20 vanishes

Ada's finish keeps both the tick and the 10 XP, or neither.

And a Venmo payment takes your $20 and gives it to Mia, or does neither.

A transaction never keeps only some of its changes.

Example: Taking back what you already sent

Dee, id 4, has 300 XP on Simplified. She finishes lesson 2, worth 10 XP. The server sends BEGIN;, then INSERT INTO completions (user_id, lesson_id) VALUES (4, 2);, then UPDATE users SET xp = xp + 10 WHERE id = 4;. Then a line of its own code throws an error, so it sends ROLLBACK;.

How much XP does Dee have afterwards?

Explanation

First, the UPDATE took Dee from 300 to 310, inside the transaction.

Then, ROLLBACK undid every change since BEGIN: the row (4, 2), and the 310.

Therefore, Dee still has 300 XP.

Question 1

Fay, id 6, has 75 XP on Simplified. The server sends BEGIN;, then UPDATE users SET xp = xp + 10 WHERE id = 6;, then ROLLBACK;. Then it sends BEGIN;, the same UPDATE again, and COMMIT;, and Postgres sends back COMMIT.

How much XP does Fay have afterwards?

XP

Question 2

Ben, id 2, has 40 XP on Simplified. The server sends BEGIN;, UPDATE users SET xp = xp + 10 WHERE id = 2; and COMMIT;, and Postgres sends back COMMIT. Then the server sends BEGIN;, then the same UPDATE again, then ROLLBACK;.

How much XP does Ben have afterwards?

XP

Question 3

Simplified's completions table holds 3 rows. The server sends BEGIN;, INSERT INTO completions (user_id, lesson_id) VALUES (2, 1); and COMMIT;, and Postgres sends back COMMIT. Then the server sends BEGIN;, INSERT INTO completions (user_id, lesson_id) VALUES (2, 2);, INSERT INTO completions (user_id, lesson_id) VALUES (5, 1); and ROLLBACK;.

How many rows does completions hold afterwards?

rows

Question 4

Simplified's server sends BEGIN;, then UPDATE users SET xp = xp + 10 WHERE id = 3;, and Postgres sends back UPDATE 1. Then the server sends ROLLBACK;.

What does Postgres send back for the ROLLBACK?

Question 5

Say Venmo keeps everyone's money in a balances table. You have $50, and your friend has $10. Venmo's server sends BEGIN;, an UPDATE that takes 20 from your balance, and an UPDATE that adds 20 to your friend's. Then it sends ROLLBACK;.

What are the two balances afterwards?

Question 6

Cy, id 3, has 0 XP on Simplified, and finishes lesson 1, worth 10 XP. The server sends BEGIN;, then INSERT INTO completions (user_id, lesson_id) VALUES (3, 1); and UPDATE users SET xp = xp + 10 WHERE id = 3;, and Postgres sends back INSERT 0 1 and UPDATE 1. Then a line of the server's own code throws an error, and the server decides to keep none of Cy's finish.

What should it send next?

Assistant

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