Transactions

What problem does a transaction solve?

Moving 5 brake pads from Riverside to Hilltop takes two UPDATEs: one subtracts from Riverside, one adds to Hilltop. If the first runs and the second doesn't (the app crashes, the laptop sleeps, a rule rejects the value), 5 brake pads vanish from your records. Nobody stole them. The database recorded half a job.

A transaction groups statements into one unit. Either every change inside it is saved, or none is. Banks use the same idea for money between accounts, and a bike shop needs it for parts between branches. Any time two or more changes only make sense together, they belong in one transaction.

A quick way to spot a missing transaction: some total that should never change does change. The query below adds up brake pads across both shops. Run it before and after a move. It should give 14 both times.

SELECT part, SUM(qty) AS total
FROM stock
GROUP BY part;

How do BEGIN and COMMIT work?

BEGIN opens a transaction. The statements that follow change the data as usual, and you can read the new values with SELECT, but the changes are provisional. COMMIT makes all of them permanent in one step. Until then, other connections to the same database still see the old values.

SQLite also accepts BEGIN TRANSACTION and END (a synonym for COMMIT). PostgreSQL accepts BEGIN too. MySQL prefers START TRANSACTION, though BEGIN works there outside stored procedures.

Run this and open Changes: two yellow cells in stock and one green row in stock_moves, all from one committed transaction.

BEGIN;
UPDATE stock
SET qty = qty - 2
WHERE id = 3;
UPDATE stock
SET qty = qty + 2
WHERE id = 4;
INSERT INTO stock_moves (part, from_shop, to_shop, qty, moved_on)
VALUES ('inner tube', 1, 2, 2, '2026-03-12');
COMMIT;

What does ROLLBACK undo?

ROLLBACK throws away every change since BEGIN and closes the transaction. The rows go back to exactly how they were. Use it when you notice a problem part-way through: a count went negative, the wrong row changed, a check query came back with something unexpected.

Below, every log row is deleted and then rolled back. The final count is still 2, and Changes shows no differences because, as far as the database is concerned, the DELETE never happened.

BEGIN;
DELETE FROM stock_moves;
SELECT COUNT(*) AS during FROM stock_moves;
ROLLBACK;
SELECT COUNT(*) AS after_rollback FROM stock_moves;

What happens when a statement fails inside a transaction?

In SQLite, a failing statement undoes its own changes and stops there. The transaction stays open, and changes made by earlier statements in it are still pending. Nothing is committed or rolled back for you. Your code (or you) must decide: fix the problem and carry on, or ROLLBACK.

The example below tries to move 5 tyres out of Hilltop, which has none. The first UPDATE adds 5 to Riverside. The second fails the CHECK (qty >= 0) rule. Riverside's extra 5 tyres are still sitting in the open transaction. If a script blindly runs COMMIT next, the half-move is saved.

PostgreSQL behaves differently: after any error, the whole transaction is marked as failed and every further statement is refused until you ROLLBACK. That is stricter and safer by default. In SQLite the safe habit is the same either way: when anything in a transaction fails, roll it back and start again.

-- not runnable: the second UPDATE fails on purpose
BEGIN;
UPDATE stock SET qty = qty + 5 WHERE id = 7;
UPDATE stock SET qty = qty - 5 WHERE id = 8;  -- CHECK constraint failed
ROLLBACK;  -- the right response

What is autocommit, and why did my earlier UPDATEs save without COMMIT?

When no transaction is open, SQLite wraps each statement in its own tiny transaction and commits it as soon as it finishes. That is autocommit mode. It is why every INSERT, UPDATE and DELETE you wrote in earlier lessons was saved without a COMMIT.

Autocommit is fine for a single change. It is the wrong choice for a group of changes that only make sense together, because each one is saved the moment it succeeds. BEGIN switches autocommit off until the next COMMIT or ROLLBACK.

Transactions also make bulk writes much faster in SQLite. Each commit waits for the disk to confirm the write. A thousand INSERTs in autocommit mean a thousand waits. The same thousand inside one BEGIN … COMMIT mean one.

What are savepoints for?

A savepoint is a named marker inside a transaction. ROLLBACK TO name undoes everything after the marker and keeps the transaction open, so the work before it survives. RELEASE name removes the marker and keeps the changes.

They are handy when one optional step might fail. Below, the inner tube move is kept, the chain move is undone with ROLLBACK TO, and COMMIT saves only the first move. Open Changes and you will see row 3 and row 4 change while rows 5 and 6 do not.

BEGIN;
UPDATE stock SET qty = qty - 1 WHERE id = 3;
UPDATE stock SET qty = qty + 1 WHERE id = 4;
SAVEPOINT chains;
UPDATE stock SET qty = qty - 2 WHERE id = 5;
UPDATE stock SET qty = qty + 2 WHERE id = 6;
ROLLBACK TO chains;
RELEASE chains;
COMMIT;

How do I check a transaction worked?

Write the check before you commit. Inside the transaction, run a SELECT that should give a known answer: the total stays the same, no quantity is negative, exactly one log row was added. If the answer is wrong, ROLLBACK. If it is right, COMMIT.

In PlainFinch, the Changes view shows every table your code touched, with changed cells in yellow and new rows in green. After a ROLLBACK, the rolled-back statements leave no trace there. One quirk: each run starts from a fresh copy of the database, and if your code leaves a transaction open, the views still show the pending changes because they read from inside that same open transaction. A real app that closed the connection at that point would lose them.

BEGIN;
UPDATE stock SET qty = qty - 4 WHERE id = 5;
UPDATE stock SET qty = qty + 4 WHERE id = 6;
SELECT part, SUM(qty) AS total, MIN(qty) AS lowest
FROM stock
WHERE part = 'chain'
GROUP BY part;
COMMIT;

What should I look for in AI-written code that changes data?

AI tools often produce a sequence of UPDATE and INSERT calls with no transaction around them. Each call works in a test, so the problem only shows up when something fails in the middle. Whenever two writes depend on each other, ask where the transaction starts and ends.

Also look at what happens on an error. A good pattern begins a transaction, runs the statements, commits if all went well, and rolls back in the error handler. A bad pattern catches the error, logs it, and carries on, leaving the transaction open or committing a half-finished job.

Libraries add their own twist. Python's sqlite3 module, in its long-standing default mode, opens a transaction for you before INSERT, UPDATE or DELETE, and nothing is saved until you call commit(). Code that forgets the commit runs without errors and saves nothing. Many web frameworks offer a transaction or atomic helper that wraps a block of code in BEGIN and COMMIT, and rolls back if the block raises an error.

• Two or more related writes: is there a BEGIN (or a framework transaction helper)?
• Every path ends in COMMIT or ROLLBACK, including the error path.
• Checks run before COMMIT, while undoing is still possible.
• No user input waits inside an open transaction. Holding one open while a person types blocks other writers in SQLite.

Field notes

• A transaction stops a half-finished job, like stock taken from one shop and never added to the other.
• BEGIN starts the transaction and COMMIT saves all its changes at once.
• ROLLBACK throws away every change since BEGIN and puts the rows back as they were.
• In SQLite a failed statement leaves the transaction open, so roll back yourself when something fails.

Easy to get wrong

Without BEGIN, each UPDATE commits on its own. If the second fails, the first is already saved and the stock total is wrong.

In SQLite an error does not end the transaction. Committing after an error saves whatever earlier statements did, which is the half-job you were trying to avoid.

A transaction left open is never saved. It also holds a write lock, so other connections that want to write have to wait or fail with a 'database is locked' error.

SQLite does not nest BEGIN. The second one fails with 'cannot start a transaction within a transaction'. Use a savepoint for a step inside a transaction.

Questions people ask

Can I undo a transaction after COMMIT?

No. Once committed, the changes are permanent. To reverse them you write new statements that put the old values back, ideally in another transaction. Backups exist for the cases where you can't.

Do SELECT statements need a transaction?

A single SELECT runs in its own read transaction automatically. Wrap several SELECTs in BEGIN and COMMIT when they must all see the same snapshot of the data, for example a report that reads totals from three tables while other people keep writing.

Does CREATE TABLE work inside a transaction?

In SQLite, yes. CREATE, ALTER and DROP can be rolled back like any other change. PostgreSQL also supports this. MySQL commits automatically before most schema changes, so a ROLLBACK will not undo a CREATE TABLE there.

What are BEGIN IMMEDIATE and BEGIN EXCLUSIVE?

Plain BEGIN in SQLite is deferred: it takes a write lock only when the first write happens. BEGIN IMMEDIATE takes the write lock at once, which avoids a 'database is locked' error part-way through when another connection is also writing. For a single user, plain BEGIN is fine.

How long should a transaction stay open?

As short as possible. Do the reading and thinking first, then BEGIN, write, check and COMMIT in quick succession. Long transactions block other writers and make conflicts more likely.