DailyRNG

Master

All of it or none of it

A multi-step update ran halfway and the process died, leaving the table in a state that was neither the old total nor the new one. A transaction is the promise that a group of writes lands whole or not at all.

What you will see

amount 70 30 sqlite>

About 2 min of reading

Try It

Move a value between two rows as one transaction, and undo a group with ROLLBACK.

1

Draw a tiny bank table with one amount column.

CREATE TABLE bank (amount INTEGER);

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

2

Give it a starting amount of 100.

INSERT INTO bank (amount) VALUES (100);

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

3

Open a transaction, take 30 out, put 30 into a second row, and commit. The total stays 100 across the two rows.

BEGIN; UPDATE bank SET amount = amount - 30; INSERT INTO bank (amount) VALUES (30); COMMIT;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

4

Read both rows. You have 70 and 30, the money moved and nothing was lost.

SELECT amount FROM bank;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

amount 70 30 sqlite>

5

Open a transaction, change a row, then roll it back. The change is discarded and the table is as it was.

BEGIN; UPDATE bank SET amount = 0; ROLLBACK;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

6

Read again. The rollback undid the zero, so the rows are still 70 and 30.

SELECT amount FROM bank;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

amount 70 30 sqlite>

Put it in order

Before you write anything, put these lines back in the right order. Click a line to move it up one step. This support step is easier than writing code from scratch, and it is here to make the last example stick.

Data Flow

BEGIN opens a transaction, the writes inside are held, and COMMIT applies them as one atomic step while ROLLBACK discards them.

1 BEGIN opens a transaction

↓

2 Writes are held inside

↓

3 COMMIT applies them as one step

↓

4 ROLLBACK discards them

Why this works

Why grouping matters

A single statement is already atomic, the database finishes it or does not start it. A group of statements is not. Between two statements the table is in a half state, and a crash or another reader can catch it. BEGIN and COMMIT turn a group into one atomic step so the half state never exists.

The word transaction

This is the same promise every serious database makes, and sqlite gives it to you through BEGIN, COMMIT, and ROLLBACK. The rule is simple: any set of writes that must be true together goes inside one transaction. A transfer, a booking, a move of money between accounts, all of them are one transaction, not a sequence of loose updates.

Examples

The danger of steps

UPDATE bank SET amount = amount - 10;
UPDATE bank SET amount = amount + 10;

Two separate statements. If the program stops between them, money is lost. Each write lands on its own, and the space between them is a window where the data is wrong.

Wrap them together

BEGIN;
UPDATE bank SET amount = amount - 10;
UPDATE bank SET amount = amount + 10;
COMMIT;

Everything between BEGIN and COMMIT is one transaction. It either all applies or none of it does. There is no in-between state another reader can see.

Back out on demand

BEGIN;
UPDATE bank SET amount = amount - 10;
ROLLBACK;

ROLLBACK throws away everything since BEGIN. You use it when a later check fails and you want the whole group undone, not just the last line.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← A saved question

Previous Lesson

A saved question