Transactions: all or nothing

Why transactions?

Sending ₹1500 over UPI from Asha to Ravi is two changes: subtract 1500 from Asha's account and add 1500 to Ravi's. If the server crashes after the first step, Asha has lost money that Ravi never got. A transaction is a group of statements that the database treats as one unit: either all of them take effect, or none of them do.

BEGIN, COMMIT and ROLLBACK

BEGIN;
UPDATE accounts SET balance = balance - 1500 WHERE id = 1;   -- Asha
UPDATE accounts SET balance = balance + 1500 WHERE id = 2;   -- Ravi
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 1500);
COMMIT;
SELECT id, holder, balance FROM accounts;
-- 1 | Asha Verma | 3500
-- 2 | Ravi Kumar | 4500
-- 3 | Priya Nair | 1200

• BEGIN (or BEGIN TRANSACTION; START TRANSACTION in MySQL and PostgreSQL; BEGIN TRAN in SQL Server) starts a transaction.
• COMMIT makes every change since BEGIN permanent and visible to others.
• ROLLBACK throws away every change since BEGIN, as if it never happened.

BEGIN;
UPDATE accounts SET balance = balance - 4000 WHERE id = 1;
UPDATE accounts SET balance = balance + 4000 WHERE id = 3;
ROLLBACK;     -- the payment gateway reported a failure
SELECT id, balance FROM accounts;
-- 1 | 5000 ,  2 | 3000 ,  3 | 1200   (unchanged)

Autocommit

If you never write BEGIN, SQLite, PostgreSQL, MySQL and SQL Server run each statement in its own tiny transaction and commit it straight away. This is called autocommit. It is fine for a single statement, but two related statements in autocommit mode are two separate transactions, and a crash between them leaves half the work done. Some tools, such as Oracle SQL*Plus and many database GUIs set to manual commit, work the other way: nothing is saved until you COMMIT. Know which mode your tool is in.

SAVEPOINT: undo part of the work

A savepoint is a named marker inside a transaction. ROLLBACK TO name undoes the work done after the marker but keeps what came before, and the transaction carries on. RELEASE name removes the marker. SQLite, PostgreSQL and MySQL use these words; SQL Server writes SAVE TRANSACTION name and ROLLBACK TRANSACTION name.

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
SAVEPOINT cashback;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- offer not valid after all
ROLLBACK TO cashback;
COMMIT;
-- Asha 4000, Ravi 4000: the transfer stays, the cashback is undone

Errors inside a transaction

What happens when one statement fails depends on the engine. The accounts table has CHECK (balance >= 0), so taking 2000 from Priya's 1200 fails with "CHECK constraint failed: balance >= 0".

• SQLite and MySQL: by default only the failing statement is undone. The transaction stays open, and the earlier statements are still pending. Your code must decide to ROLLBACK.
• PostgreSQL: the whole transaction becomes unusable. Every next statement fails with "current transaction is aborted, commands ignored until end of transaction block" until you ROLLBACK (or ROLLBACK TO a savepoint).
• SQL Server: depends on the setting XACT_ABORT; with SET XACT_ABORT ON, most errors roll back the whole transaction.

The safe rule for app code in every language: start the transaction, run the statements, COMMIT; if anything throws an error, ROLLBACK. Most database libraries have a helper for exactly this, such as a "with" block in Python or a transaction function in Node.js ORMs.

Common mistakes

• Running related statements without BEGIN, so a failure in the middle leaves half the work saved.
• Forgetting COMMIT. The changes stay invisible to others and may be thrown away when the connection closes, and the open transaction can block other writers.
• Keeping a transaction open while waiting for a user or a slow API call. Keep transactions short.
• Ignoring errors and committing anyway. On any error, ROLLBACK.
• Expecting DDL (CREATE, ALTER, DROP) to roll back in MySQL. MySQL commits automatically before such statements; PostgreSQL and SQLite can roll them back.