Databases · Lesson 4.1
When one statement is refused
So when a learner finishes a lesson, Simplified's server sends one transaction.
BEGIN, the INSERT for the completion, the UPDATE adding 10 XP, then COMMIT.
Ada has 120 XP, and has finished lessons 1 and 2, so completions holds (1, 2).
completions' primary key is user ID and lesson ID together.
So Postgres refuses a second row for the same learner and lesson.
Now say Ada opens lesson 2 again, and finishes it.
Simplified's server sends the same transaction, for lesson 2.
So what happens to the transaction when one of its statements is refused?
The server sends BEGIN, and Postgres replies BEGIN.
Then it sends the INSERT for (1, 2).
completions already has (1, 2), so the primary key refuses it.
So Postgres sends back an error: duplicate key value.
Next, the server sends the UPDATE adding 10 XP to Ada.
But Postgres doesn't run it.
It sends back a different error: the current transaction is aborted.
Because after any error, Postgres stops the whole transaction.
This is called an aborted transaction.
An aborted transaction is one where Postgres refuses every statement after the error, until it ends.
So Ada's XP stays at 120: no 10 XP for a lesson she already had.
So how does the aborted transaction end?
The server sends COMMIT, as it does at the end of every transaction.
But Postgres replies ROLLBACK, not COMMIT.
Because an aborted transaction can never be made final.
So Postgres ends it, and keeps none of its changes.
The server could send ROLLBACK itself instead, and get the same reply.
And what if a write had already worked, before the error?
Say the server sends the UPDATE first, then the INSERT.
The UPDATE runs, so Ada's XP goes from 120 to 130, and Postgres replies UPDATE 1.
Then the INSERT for (1, 2) is refused, so Postgres aborts the transaction.
The server sends COMMIT, and Postgres replies ROLLBACK.
So even the 130 isn't kept: Ada still has 120 XP.
That's atomicity again: all or nothing, even when something breaks.
A refused write stores nothing, and now neither does the transaction it's in.
After one error, a transaction keeps none of its changes.
Simplified's users table has Dee's row: id 4, 300 XP. completions already holds (4, 1), and its primary key is (user_id, lesson_id). Simplified's server sends BEGIN;, then UPDATE users SET xp = xp + 10 WHERE id = 4;, which gets back UPDATE 1. Then it sends INSERT INTO completions (user_id, lesson_id) VALUES (4, 1);, and then COMMIT;.
What does Postgres reply to the COMMIT?
Explanation
First, completions already holds (4, 1), so the primary key refuses the INSERT, and Postgres aborts the transaction.
Then, an aborted transaction can never be made final, so COMMIT ends it and keeps nothing, not even the UPDATE.
Therefore, Postgres replies ROLLBACK, and Dee still has 300 XP.
Say Instagram keeps posts in a photos table, which holds 500 rows, and its caption column is NOT NULL. A server sends BEGIN;, then three INSERTs into photos, one at a time. The first two each get back INSERT 0 1. The third has a NULL caption, so Postgres refuses it. Then the server sends COMMIT;.
How many rows does photos hold afterwards?
Databases · Lesson 4.2
When can an app say "done"?
So when Ada finished lesson 2 again, its INSERT was refused, and Postgres aborted the transaction.
Simplified's server sent COMMIT, Postgres replied ROLLBACK, and nothing was kept.
So the server was still there to end the transaction, with COMMIT.
But what if Simplified's server crashes halfway, before it can end the transaction?
Say Ada finishes lesson 3, worth 10 XP.
The server sends BEGIN, then the INSERT for (1, 3).
Postgres replies INSERT 0 1.
Then, before the server can send the UPDATE, it crashes.
So no one is left to send COMMIT, or ROLLBACK.
So Postgres handles it by itself.
When the server crashes, its link to Postgres drops.
And when that link drops in the middle of a transaction, Postgres rolls the transaction back.
So the (1, 3) row is gone, and Ada still has 120 XP.
No tick and no XP, so the two still match.
And what if Postgres itself crashes, in the middle of a transaction?
Say the server has sent BEGIN, the INSERT for (1, 3), and the UPDATE.
Postgres has replied INSERT 0 1, then UPDATE 1.
Then, before COMMIT arrives, the database crashes.
When it restarts, there's no (1, 3) row, and Ada has 120 XP.
Because a transaction that has not committed never counts.
So what if the crash comes just after COMMIT?
This time Postgres replies COMMIT, and a moment later the database crashes.
When it restarts, the (1, 3) row is there, and Ada has 130 XP.
Because once Postgres has replied COMMIT, every change in the transaction is kept.
So COMMIT is the line.
COMMIT is the line means a crash before Postgres replies COMMIT keeps none of the changes.
And a crash after that reply keeps all of them.
So when can an app tell you something is done?
Say you send a friend $20 on Venmo.
Venmo's server sends BEGIN, an UPDATE taking 20 from your balance, and one adding 20 to your friend's.
Then it sends COMMIT, and Postgres replies COMMIT.
Only then might your app show "Payment sent".
If a crash came before that reply, neither balance changed, so it never says sent.
And after that reply, both changes are kept, so the payment stays sent.
Simplified's server might wait the same way, before showing Ada her tick and 130 XP.
A change counts only once Postgres has replied COMMIT.
Simplified's users table has Ada's row: id 1, 120 XP. Simplified's server sends BEGIN;, then INSERT INTO completions (user_id, lesson_id) VALUES (1, 3);, which gets back INSERT 0 1. Then it sends UPDATE users SET xp = xp + 10 WHERE id = 1;, which gets back UPDATE 1. Then the server crashes, before it sends COMMIT.
How much XP does Ada have after the crash?
Explanation
First, the server crashed before COMMIT, so its link to Postgres dropped in the middle of the transaction.
Then, Postgres rolls back a transaction whose link drops, so neither write is kept.
Therefore, Ada has 120 XP, and no (1, 3) row.
Simplified's users table has Ben's row: id 2, 40 XP. Ben finishes a lesson worth 10 XP. Simplified's server sends BEGIN;, the INSERT into completions, UPDATE users SET xp = xp + 10 WHERE id = 2;, and COMMIT;. Postgres replies COMMIT. A second later, the database crashes, and then it restarts.
How much XP does Ben have once it has restarted?
Simplified's server sends BEGIN;, the INSERT for completions row (1, 3), which gets back INSERT 0 1, and the UPDATE adding 10 XP to Ada, which gets back UPDATE 1. Then, before the server sends COMMIT;, the database crashes.
Once it restarts, which of the two changes are there?
Say Venmo's server moves 20 from your balance to your friend's, in one transaction: BEGIN;, an UPDATE on your balance, an UPDATE on your friend's, then COMMIT;.
When is the earliest moment your app can safely show "Payment sent"?
Simplified's completions table holds 3 rows. Simplified's server sends BEGIN;, then two INSERTs into completions, (2, 1) and (2, 2), and each gets back INSERT 0 1. Then it sends COMMIT;, and Postgres replies COMMIT. A moment later, before it can show Ben his two ticks, Simplified's server crashes, and its link to Postgres drops.
How many rows does completions hold afterwards?
Simplified's server runs one transaction that adds completions row (1, 3), and Postgres replies COMMIT. Then it starts a second transaction: BEGIN;, then the INSERT for completions row (4, 2), which gets back INSERT 0 1. Before the second COMMIT, the database crashes.
Once it restarts, which of the two rows are in completions?
Simplified's users table has Dee's row: id 4, 300 XP. Simplified's server sends BEGIN;, then UPDATE users SET xp = xp + 10 WHERE id = 4;, which gets back UPDATE 1. Then the server crashes, so it never sends COMMIT or ROLLBACK, and its link to Postgres drops.
What happens to the UPDATE?
Databases · Lesson 4.3
A crash before it reaches disk
So when Postgres replied COMMIT and then crashed, Ada still had her tick and 130 XP once it restarted.
Postgres keeps its tables on its disk, so they survive a restart.
But finding a row's place on disk, and changing it there, is slow.
So Postgres makes each change in memory first, because memory is fast.
It adds the (1, 3) row to completions in memory.
Then it changes Ada's XP to 130 in memory too.
It writes those changes to the tables on disk later, many at once.
So what if Postgres crashes after COMMIT, before the changes reach the tables on disk?
The crash wipes memory, so the (1, 3) row and Ada's 130 are gone.
And on disk, the users table still says 120.
So Ada would lose her tick and her XP, though Postgres replied COMMIT.
A change that is only in memory doesn't survive a crash.
Simplified's users table on disk has Ben's row: id 2, 40 XP. Ben finishes a lesson worth 10 XP, and Postgres changes his XP to 50 in memory. The users table on disk hasn't got the change yet.
What does the users table on disk say for Ben right now?
Explanation
First, Postgres made the change in memory, because memory is fast.
Then, the tables on disk get the changes later, so the users table on disk hasn't got it yet.
Therefore, the users table on disk still says 40 XP.
When Postgres changes a row, it makes the change in memory first, and the tables on disk get it later.
Why doesn't it change the table on disk straight away?
Simplified's users table on disk has Dee at 300 XP. Inside a transaction, Postgres changes her XP to 320 in memory. Before COMMIT arrives, and before the users table on disk gets the change, Postgres crashes, and then restarts.
What is Dee's XP once it has restarted?
Postgres has just changed Eli's XP from 40 to 50 in memory.
When does the users table on disk get the change?
Postgres holds two copies of Fay's XP: 85 in memory, and 75 in the users table on its disk, which hasn't got the change yet. Postgres crashes.
Which copy is lost?
Say Postgres changed every row straight in its table on disk, before doing anything else, instead of making the change in memory first.
What would that cost?
Simplified's users table on disk has Ada at 120 XP. Postgres changes her XP to 130 in memory, and replies COMMIT. Say Postgres kept that change nowhere but memory, and crashed before the users table on disk got it.
What would Ada's XP be once it restarted?
Databases · Lesson 4.4
How a change survives a crash
So when Ada finished lesson 3, Postgres changed her XP to 130 in memory first, because memory is fast.
And the users table on disk would get it later, so a crash could wipe her 130.
So how does Postgres keep a change it has replied COMMIT to, through a crash?
So as it makes each change, Postgres also adds it to a log on its disk.
That's quick, because Postgres never has to find a place: it only adds to the end.
First the new (1, 3) row, then Ada's 130 XP.
When COMMIT arrives, it adds the COMMIT too.
Only then does Postgres reply COMMIT.
The tables on disk get the changes later, so this log is called the write-ahead log.
The write-ahead log is where Postgres writes every change, before the tables on disk get it.
So what happens now, if Postgres crashes before the changes reach the tables?
The crash wipes memory, and the users table on disk still says 120.
But the write-ahead log is on disk too, so it survives.
When Postgres restarts, it reads the write-ahead log.
It finds the new (1, 3) row, Ada's 130 XP, and their COMMIT.
So it puts both changes into the tables on disk.
And Ada has her tick and her 130 XP.
And what if Postgres crashes before COMMIT?
Say the new row and Ada's 130 XP are in the log, but COMMIT hasn't arrived.
Then Postgres crashes, and restarts.
It reads the log, and finds no COMMIT after those two changes.
So those changes don't count, and Ada still has 120 XP.
That's how a crash before COMMIT keeps none of the changes.
Postgres replies COMMIT only once the COMMIT is in the log on its disk.
Simplified's users table has Ben's row: id 2, 40 XP. Ben finishes a lesson worth 10 XP. Postgres changes his XP to 50 in memory and adds the change to its write-ahead log. Then COMMIT arrives: Postgres adds it to the log, and replies COMMIT. Then Postgres crashes, before the change reaches the users table on disk, which still says 40.
How much XP does Ben have once Postgres restarts?
Explanation
First, the crash wiped memory, and the users table on disk still says 40.
Then, Postgres reads its write-ahead log on restart, and finds Ben's 50 with a COMMIT after it, so it puts the change into the table.
Therefore, Ben has 50 XP.
Inside a transaction, Postgres has changed Ada's XP from 120 to 130 in its memory, and added that change to its write-ahead log on disk. Now Simplified's server sends COMMIT;.
What must Postgres do before it replies COMMIT?
As it makes each change, Postgres adds it to its write-ahead log on disk. The tables on disk get the changes later.
Why the log first, and the tables later?
Simplified's users table on disk has Dee at 300 XP and Eli at 40 XP. Dee finishes a lesson worth 20 XP: her new XP and a COMMIT reach Postgres's write-ahead log. Eli finishes a lesson worth 10 XP: his new XP reaches the log, but no COMMIT comes after it. Then Postgres crashes, before either change reaches the users table on disk, and restarts.
How much XP do Dee and Eli have together, once it has restarted?
Postgres adds every change to its write-ahead log on disk, and the tables on disk get the changes later.
Why is the log called write-ahead?
Say Venmo's server moves $20 from your balance to your friend's, in one transaction, and its database is Postgres. Postgres makes both UPDATEs and adds them to its write-ahead log; then COMMIT; arrives.
In what order does Postgres do these three things: add the COMMIT to its log, reply COMMIT, and write the two new balances into the balances table on disk?
Ada finished a lesson, and her XP went from 120 to 130. Postgres replied COMMIT, then crashed before the users table on its disk got the change, so that table still says 120.
When Postgres restarts, where does it get Ada's 130 from?