A transaction turns a sequence of statements into one unit of work: BEGIN opens it, COMMIT makes it permanent, ROLLBACK throws it away. The guarantee is atomicity — either every statement in the transaction takes effect or none of them do. That is what lets you move money, sell a seat and write a receipt without ever leaving half a sale behind.
What do transactions guarantee?
Start with the simplest case. Three tickets at the Winter Sessions are repriced to ninety-nine pounds, and then the whole thing is rolled back:
BEGIN;
UPDATE tickets SET price_paid = 99.00 WHERE event_id = 1;
SELECT COUNT(*) AS rows_at_99 FROM tickets WHERE price_paid = 99;
rows_at_99
3
ROLLBACK;
SELECT COUNT(*) AS rows_at_99_after_rollback FROM tickets WHERE price_paid = 99;
rows_at_99_after_rollback
-------------------------
0
Three rows inside the transaction, zero after the rollback. The update really did happen — the SELECT inside the transaction can see it — and then it was undone. Nothing was left half-done, because nothing was left at all.
What does COMMIT change?
Exactly one thing: whether the work survives. The same update, committed instead of rolled back:
BEGIN;
UPDATE tickets SET price_paid = 99.00 WHERE event_id = 1;
COMMIT;
SELECT COUNT(*) AS rows_at_99_after_commit FROM tickets WHERE price_paid = 99;
rows_at_99_after_commit
3
Three rows, permanently. Between BEGIN and COMMIT the changes exist only inside the transaction: they are visible to the session that made them, invisible to everybody else, and undoable at any moment.
What happens when a statement fails mid-transaction?
This is the case that justifies the whole mechanism. In SQLite the failed statement is rolled back on its own and the session can carry on; in PostgreSQL, MySQL and SQL Server the whole transaction is aborted and you must issue ROLLBACK before doing anything else. Either way the transaction cannot be committed into a half-applied state. Here the session rolls back explicitly, and the repricing disappears with it:
BEGIN;
UPDATE tickets SET price_paid = 5.00 WHERE event_id = 1;
ROLLBACK;
SELECT COUNT(*) AS rows_at_5 FROM tickets WHERE price_paid = 5;
rows_at_5
---------
0
Not one ticket carries the five-pound price. And with a commit instead of a rollback, the successful statement stands:
BEGIN;
UPDATE tickets SET price_paid = 5.00 WHERE event_id = 1;
COMMIT;
SELECT COUNT(*) AS rows_at_5_after_commit FROM tickets WHERE price_paid = 5;
rows_at_5_after_commit
----------------------
3
Three rows. Without a transaction, the two statements in the first example would have been two independent units of work, and a failure between them would have left the tickets repriced and the holder unrecorded — exactly the half-applied state that a transaction exists to prevent. And here is the failure itself. Three things are attempted: reprice the tickets, insert a duplicate ticket holder that violates the primary key, and finish. The insert is refused:
BEGIN;
UPDATE tickets SET price_paid = 5.00 WHERE event_id = 1;
INSERT INTO ticket_holders (holder_id, email, full_name, city, signed_up_on)
VALUES (1, '[email protected]', 'Imogen Fair', 'Bristol', '2024-03-12');
ROLLBACK;
UNIQUE constraint failed: ticket_holders.holder_id
The engine refuses the duplicate holder identifier, and the ROLLBACK that follows discards the repricing with it — nothing of the half-finished transaction survives.
What are the four properties a transaction gives you?
- Atomicity: all of the statements take effect, or none do. This is what the rollback demonstrated.
- Consistency: the database moves from one valid state to another valid state. The constraints are checked as part of the commit, so a transaction that would break a foreign key never becomes visible.
- Isolation: concurrent transactions do not see each other's uncommitted work. Session A's intermediate rows are invisible to session B until A commits.
- Durability: once COMMIT returns, the change is permanent and survives a crash — because the engine has written it to its log first.
Three practical notes. Autocommit is the default in every modern engine, so a statement outside an explicit transaction commits by itself; you only need BEGIN when you want several statements to be one unit. A transaction that is never committed is rolled back automatically when the connection closes, which is a safety net, not a strategy. And the correct scope for a transaction is the smallest one that makes the business sense atomic — a single sale, not a whole batch of imports, because long transactions hold locks and block everyone else.
How does a transaction look in application code?
The SQL you have been writing by hand is the same thing a driver runs for you, and the shape is worth recognising because it explains the rule about scope. A typical sequence in any language looks like this:
connection.begin();
try {
connection.run("INSERT INTO tickets (...) VALUES (...)");
connection.run("UPDATE events SET status = 'sold_out' WHERE event_id = ?");
connection.commit();
} catch (error) {
connection.rollback();
throw error;
}
The try block and the catch block are the point: the commit happens only if every statement succeeded, and any exception — a constraint violation, a timeout, a dropped connection — sends the work to the rollback instead. Application code has one dangerous variation, and it is worth naming: if the error is caught and swallowed somewhere between the failed statement and the rollback, the connection can be left with a transaction still open, holding locks. Always roll back in the catch block, always, even if you intend to retry.
The isolation level is the last knob. The default in most engines — read committed in PostgreSQL and MySQL, read committed with different lock semantics in SQL Server — lets one session see another's committed rows but never its uncommitted ones. The stricter levels, repeatable read and serializable, reduce the interleavings that can occur between concurrent transactions at the cost of blocking. SQLite handles concurrency with a whole-database write lock rather than row locks, so a long transaction in SQLite blocks other writers entirely — another reason to keep transactions short.
Quick reference
- BEGIN opens a transaction, COMMIT makes it permanent, ROLLBACK discards it.
- Changes are visible inside the transaction and invisible to other sessions until commit.
- A failed statement can never leave a transaction committed in a half-applied state.
- Autocommit is the default; use an explicit transaction when several statements must succeed together.
- Keep transactions short — the smallest scope that is atomic — because they hold locks.
Transaction drill: write a transaction that sells a seat: it inserts the ticket row, decrements nothing but checks the seat is free, and rolls back if the seat is taken — then say in one sentence what would go wrong if the seat check and the insert were two separate autocommit statements.