SQL Transactions and ACID: Keep Your Data Correct

Moving $100 from one bank account to another takes two steps: take it out of one, put it into the other. What if the system crashes between the two? The money has vanished.

A transaction prevents that. It groups several statements into one all-or-nothing unit. Either every step happens, or none of them do.

The problem, in code

Run this in the SQL editor to set up two accounts.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Ana', 500), (2, 'Ben', 100);

The CHECK rule means a balance can never go below zero. Remember that, because we'll use it in a moment.

A successful transaction

Wrap related changes in BEGIN and COMMIT.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

SELECT owner, balance FROM accounts ORDER BY id;

owner  balance
-----  -------
Ana    400
Ben    200

Both updates took effect together. Until COMMIT runs, nobody else can see the half-finished state, and nothing is permanent.

Undoing with ROLLBACK

If something goes wrong, ROLLBACK cancels everything since BEGIN.

BEGIN;
UPDATE accounts SET balance = balance - 400 WHERE id = 1;
-- Oops, we realize this was a mistake.
ROLLBACK;

SELECT owner, balance FROM accounts ORDER BY id;

owner  balance
-----  -------
Ana    400
Ben    200

Ana's balance is exactly what it was. It's as if the update never happened. That makes transactions a safety net, as well as a tool for consistency.

When a step fails halfway

Now let's try to move more money than Ana has. The second update would drop her balance below zero, so the CHECK rule rejects it. In an application, you'd catch that error and roll back, so the second update never happens. Here's the pattern:

BEGIN;
UPDATE accounts SET balance = balance + 9999 WHERE id = 2;   -- step 1 worked
UPDATE accounts SET balance = balance - 9999 WHERE id = 1;   -- step 2 breaks the rule

Error: CHECK constraint failed: balance >= 0

At this point Ben has been credited, but Ana hasn't been debited. If we committed now, money would be created from nothing. Instead, the code that sees the error issues a rollback:

ROLLBACK;

SELECT owner, balance FROM accounts ORDER BY id;

owner  balance
-----  -------
Ana    400
Ben    200

Both balances are back to normal. In real application code the idea is always the same: BEGIN, do the work inside a try, COMMIT on success, and ROLLBACK in the catch.

Savepoints: undo just part of the work

Sometimes you want to roll back one step but keep the rest. A savepoint marks a spot you can return to.

BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 1000 WHERE id = 1;
ROLLBACK TO bonus;       -- undo only the 1000 bonus

COMMIT;

SELECT owner, balance FROM accounts WHERE id = 1;

owner  balance
-----  -------
Ana    410

The first +10 stayed. The +1000 inside the savepoint was undone. This is handy in long imports, where one bad row shouldn't throw away everything.

ACID: what transactions promise

You'll hear this acronym often. It describes four guarantees of a reliable database.

• Atomicity: all steps happen, or none do.
• Consistency: a transaction moves the database from one valid state to another, so rules like CHECK and foreign keys are respected.
• Isolation: transactions running at the same time don't see each other's half-finished work.
• Durability: once committed, the change survives a crash or power loss.

Isolation and concurrency

Isolation is the trickiest. Imagine two people booking the last seat at the same time. Both check, both see "1 seat left," and both book it. That's a race condition.

Databases offer isolation levels, from relaxed to strict, to control what concurrent transactions can see. Stricter levels prevent more anomalies but can slow things down or cause some transactions to wait or retry. The exact options differ between PostgreSQL, MySQL, and SQL Server, so check the docs for yours. SQLite, which CompileZ uses, runs one writer at a time, so you won't see these races in the browser editor.

A common fix for the seat problem is to make the check and the update one statement:

CREATE TABLE seats (id INTEGER PRIMARY KEY, available INTEGER);
INSERT INTO seats VALUES (1, 1);

UPDATE seats SET available = available - 1
WHERE id = 1 AND available > 0;

SELECT changes() AS rows_changed;

rows_changed
------------
1

If the update changes 1 row, you got the seat. If it changes 0 rows, someone else did. There's no gap between "check" and "act" for another transaction to slip into. (changes() is SQLite-specific. Other databases report affected rows through their own APIs.)

Autocommit

Outside of an explicit BEGIN, most databases treat each statement as its own tiny transaction and commit it immediately. That's called autocommit. It's why a single UPDATE is safe by itself, but two related updates need BEGIN and COMMIT to be safe together.

Habits for using transactions well

• Keep them short. Long transactions hold locks and block other users. Don't wait for user input in the middle of one.
• Group related changes only. Don't wrap unrelated work in one transaction.
• Always handle the failure path. Every BEGIN needs a matching COMMIT or ROLLBACK, even when an error occurs.
• Let constraints help. CHECK, NOT NULL, UNIQUE, and foreign keys catch bad data automatically.

Key takeaways

• A transaction turns several statements into one all-or-nothing unit.
• BEGIN, COMMIT, and ROLLBACK are the core commands, and SAVEPOINT handles partial undo.
• ACID stands for atomicity, consistency, isolation, and durability.
• Keep transactions short and always plan for the error path.