Transactions and ACID

By the end you can make several statements succeed or fail together, undo part of a transaction without losing the rest, and say what each letter of ACID guarantees. In SQL Fundamentals a constraint violation stopped one statement; here the question is what happens to the ones around it.

Autocommit: one statement, one transaction

A transaction is a group of statements treated as one unit. Outside an explicit one, SQLite runs each statement in its own transaction and commits it (makes it permanent) at once: autocommit. The sqlite3 shell prints a failing statement as Error: near line N: ... on stderr and carries on with the next, so a two-statement transfer can be half done:

CREATE TABLE wallets (owner TEXT PRIMARY KEY, coins INTEGER NOT NULL CHECK (coins >= 0));
INSERT INTO wallets VALUES ('ana', 50), ('bo', 10);
UPDATE wallets SET coins = coins + 70 WHERE owner = 'bo';
UPDATE wallets SET coins = coins - 70 WHERE owner = 'ana';
SELECT * FROM wallets ORDER BY owner;

Error: near line 4: CHECK constraint failed: wallets
ana|50
bo|80

Seventy coins appeared from nowhere: the credit was already committed when the debit failed.

BEGIN, COMMIT, ROLLBACK

BEGIN opens a transaction. Its changes stay provisional until COMMIT makes them permanent or ROLLBACK throws them all away. The same transfer, from ana 50, bo 10:

BEGIN;
UPDATE wallets SET coins = coins + 70 WHERE owner = 'bo';
UPDATE wallets SET coins = coins - 70 WHERE owner = 'ana';
SELECT * FROM wallets ORDER BY owner;
ROLLBACK;
SELECT * FROM wallets ORDER BY owner;

Error: near line 3: CHECK constraint failed: wallets
ana|50
bo|80
ana|50
bo|10

Lines 2-3 are the table before ROLLBACK, lines 4-5 after it. The failed statement was undone alone; the transaction stayed open with the credit in it. SQLite does not cancel it for you, so the script must notice and choose ROLLBACK; a COMMIT there would have stored bo's 80.

Table changes are transactional too. CREATE TABLE name AS SELECT ... makes a table filled with a query's rows; sqlite_master is SQLite's built-in catalog, one row per table, index and view:

BEGIN;
CREATE TABLE scratch AS SELECT owner, coins FROM wallets;
ROLLBACK;
SELECT COUNT(*) FROM sqlite_master WHERE name = 'scratch';
SELECT COUNT(*) FROM scratch;

0
Error: near line 5: no such table: scratch

Savepoints: undo only part

SAVEPOINT name marks a point inside a transaction. ROLLBACK TO name undoes everything since that mark but leaves the mark and the transaction open. RELEASE name forgets the mark and keeps the work. Marks nest, and rolling back to an outer mark discards every mark made after it. Only the outermost COMMIT makes anything permanent.

CREATE TABLE log (id INTEGER PRIMARY KEY, msg TEXT);
BEGIN;
INSERT INTO log (msg) VALUES ('start');
SAVEPOINT outer_sp;
INSERT INTO log (msg) VALUES ('a');
SAVEPOINT inner_sp;
INSERT INTO log (msg) VALUES ('b');
ROLLBACK TO inner_sp;
INSERT INTO log (msg) VALUES ('c');
SELECT group_concat(msg) FROM log;
ROLLBACK TO outer_sp;
SELECT group_concat(msg) FROM log;
COMMIT;

start,a,c
start

Rolling back to the inner mark dropped only b; rolling back to the outer one then dropped a and c together. The mark you name decides how far back you go.

ACID: what each letter guarantees

Isolation anomalies

PostgreSQL's default (READ COMMITTED) prevents only the dirty read. SQLite acts as SERIALIZABLE, meaning as if transactions ran one after another, so it prevents all four; you choose only when the write lock is taken: plain BEGIN at the first write, BEGIN IMMEDIATE at once.

Common mistakes

• ROLLBACK where ROLLBACK TO was meant - the whole transaction is gone, earlier work included, and a later RELEASE fails with no such savepoint.
• Rolling back to the wrong mark - ROLLBACK TO an inner mark keeps everything before it, so half an order survives.
• Logging the rejection before ROLLBACK TO - the log row sits inside the undone span and vanishes with it.

Your exercise

The starter reserves stock for three orders and runs a price dry run; you supply the transaction control at nine TODO markers. For each oversell, decide how far back it must go: the reserved lines show whether you went too far or not far enough.