Simplified
Simplified

L4.1When one statement is refused

YOU

When one statement is refused

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 2);INSERT INTO completions (user_id, lesson_id)VALUES (…, …);INSERT 0 1UPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1COMMIT;COMMITcompletionsuser_idlesson_id111212PRIMARY KEY (user_id, lesson_id)usersidnamexp1Ada120?
Simplified's serverPostgresBEGIN;INSERT INTO completions (user_id, lesson_id)VALUES (1, 2);ERROR: duplicate key value violatesunique constraint "completions_pkey"UPDATE users SET xp = xp + 10 WHERE id = 1;ERROR: current transaction isaborted, commands ignored until endof transaction blockcompletionsuser_idlesson_id111212usersidnamexp1Ada120
aborted
a transaction where Postgres refuses every statement after the error, until it ends
Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 2);ERROR: duplicate key value violatesunique constraint "completions_pkey"UPDATE users SET xp = xp + 10 WHERE id = 1;ERROR: current transaction isaborted, commands ignored until endof transaction blockcompletionsuser_idlesson_id111212usersidnamexp1Ada120
Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 2);ERROR: duplicate key value violatesunique constraint "completions_pkey"UPDATE users SET xp = xp + 10 WHERE id = 1;ERROR:current transaction isaborted,commands ignored until endof transaction blockCOMMIT;ROLLBACK;ROLLBACK?
Simplified's serverPostgresBEGIN;BEGINUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1INSERT INTO completions (user_id, lesson_id)VALUES (1, 2);ERROR: duplicate key value violatesunique constraint "completions_pkey"COMMIT;ROLLBACKcompletionsuser_idlesson_id1212usersidnamexp1Ada120?

Databases · Lesson 4.1

When a Transaction Breaks

When one statement is refused

Simplified
0:00 / 2:28

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?

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 2);ERROR: duplicate key value violatesunique constraint "completions_pkey"completionsuser_idlesson_id111212

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.

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 2);ERROR: duplicate key value violatesunique constraint "completions_pkey"UPDATE users SET xp = xp + 10 WHERE id = 1;ERROR: current transaction isaborted, commands ignored until endof transaction blockcompletionsuser_idlesson_id111212usersidnamexp1Ada120

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?

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 2);ERROR: duplicate key value violatesunique constraint "completions_pkey"UPDATE users SET xp = xp + 10 WHERE id = 1;ERROR: current transaction isaborted, commands ignored until endof transaction blockCOMMIT;ROLLBACKcompletionsuser_idlesson_id111212usersidnamexp1Ada120

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?

Simplified's serverPostgresBEGIN;BEGINUPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1INSERT INTO completions (user_id, lesson_id)VALUES (1, 2);ERROR: duplicate key value violatesunique constraint "completions_pkey"COMMIT;ROLLBACKcompletionsuser_idlesson_id111212usersidnamexp1Ada120

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.

Example: When one statement is refused

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.

Question 1

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?

rows

When can an app say "done"?

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES(1, 2);UPDATE users SET xp = xp + 10 WHERE id = 1;ERROR: current transaction isaborted, commands ignored until endof transaction blockCOMMIT;ROLLBACK;ROLLBACK?
Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1server crashesCOMMIT;ROLLBACK;rolled backcompletionsuser_idlesson_id111213usersidnamexp1Ada120
Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1UPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1database crashescompletionsuser_idlesson_id111213usersidnamexp1Ada120?
Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1UPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1COMMIT;database crashescompletionsuser_idlesson_id111213usersidnamexp1Ada130?
Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1UPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1crashCOMMIT;COMMITcrashcrash: none keptcrash: all kept
Venmo's serverPostgresBEGIN;BEGINUPDATE balances SET balance = balance - 20WHERE id = 1;UPDATE 1UPDATE balances SET balance = balance + 20WHERE id = 2;UPDATE 1crashCOMMIT;COMMITcrashVenmo$20Payment sent?
Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1UPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1COMMIT;COMMITcompletionsuser_idlesson_id111213usersidnamexp1Ada130

Databases · Lesson 4.2

When a Transaction Breaks

When can an app say "done"?

Simplified
0:00 / 2:36

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?

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1server crashescompletionsuser_idlesson_id111213usersidnamexp1Ada120

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.

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1server crashesrolled backcompletionsuser_idlesson_id111213usersidnamexp1Ada120

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?

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1UPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1database crashescompletionsuser_idlesson_id111213usersidnamexp1Ada120

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?

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1UPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1COMMIT;COMMITdatabase crashescompletionsuser_idlesson_id111213usersidnamexp1Ada130

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.

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1UPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1COMMIT;COMMITcrash: none keptcrash: all kept

So when can an app tell you something is done?

Venmo's serverPostgresBEGIN;BEGINUPDATE balances SET balance = balance - 20WHERE id = 1;UPDATE 1UPDATE balances SET balance = balance + 20WHERE id = 2;UPDATE 1COMMIT;COMMITVenmo$20Payment sent

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.

Example: When can an app say "done"?

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.

Question 1

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?

XP

Question 2

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?

Question 3

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"?

Question 4

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?

rows

Question 5

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?

Question 6

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?

A crash before it reaches disk

Simplified's serverPostgresBEGIN;BEGININSERT INTO completions (user_id, lesson_id)VALUES (1, 3);INSERT 0 1UPDATE users SET xp = xp + 10 WHERE id = 1;UPDATE 1COMMIT;COMMITdatabase crashescompletionsuser_idlesson_id111213usersidnamexp1Ada130
Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada120completionsuser_idlesson_id111213laterlogcompletions: add (1, 3)users: set Ada's xp to 130COMMITno COMMIT?

Databases · Lesson 4.3

When a Transaction Breaks

A crash before it reaches disk

Simplified
0:00 / 0:58

So when Postgres replied COMMIT and then crashed, Ada still had her tick and 130 XP once it restarted.

Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada120completionsuser_idlesson_id111213laterlogcompletions: add (1, 3)users: set Ada's xp to 130COMMITno COMMIT

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?

Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada120completionsuser_idlesson_id111213laterlogcompletions: add (1, 3)users: set Ada's xp to 130COMMITno COMMIT

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.

Example: A crash before it reaches disk

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.

Question 1

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?

Question 2

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?

XP

Question 3

Postgres has just changed Eli's XP from 40 to 50 in memory.

When does the users table on disk get the change?

Question 4

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?

Question 5

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?

Question 6

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?

XP

How a change survives a crash

Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada120completionsuser_idlesson_id111213laterlogcompletions: add (1, 3)users: set Ada's xp to 130COMMITno COMMIT?
Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada120completionsuser_idlesson_id111213laterlogcompletions: add (1, 3)users: set Ada's xp to 130COMMITno COMMIT?
Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada120completionsuser_idlesson_id111213laterwrite-ahead logcompletions: add (1, 3)users: set Ada's xp to 130COMMITno COMMIT?
Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada120completionsuser_idlesson_id111213laterwrite-ahead logcompletions: add (1, 3)users: set Ada's xp to 130COMMITno COMMIT

Databases · Lesson 4.4

When a Transaction Breaks

How a change survives a crash

Simplified
0:00 / 1:50

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?

Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada120completionsuser_idlesson_id111213laterwrite-ahead logcompletions: add (1, 3)users: set Ada's xp to 130COMMITno COMMIT

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?

Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada130completionsuser_idlesson_id111213laterwrite-ahead logcompletions: add (1, 3)users: set Ada's xp to 130COMMITno COMMIT

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?

Simplified's serverCOMMITPostgresdatabase crashesrestartmemoryusersidnamexp1Ada130completionsuser_idlesson_id111213diskusersidnamexp1Ada120completionsuser_idlesson_id111213laterwrite-ahead logcompletions: add (1, 3)users: set Ada's xp to 130COMMITno 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.

Example: How a change survives a crash

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.

Question 1

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?

Question 2

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?

Question 3

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?

XP

Question 4

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?

Question 5

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?

Question 6

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?

Assistant

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