Databases · Lesson 3.1
What if it fails halfway?
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?
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?
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?
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?
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.
Writes that belong together must all be kept, or none of them.
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.
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?
Databases · Lesson 3.2
Making two writes one
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.
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.
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 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.
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.
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?
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?
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?
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?
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?
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?
Databases · Lesson 3.3
Taking back what you already sent
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.
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.
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?
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?
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.
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.
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.
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?
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?
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?
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?
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?
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?