Transactions: All or Nothing

Picture a bank transfer: $500 moves from account A to account B. Two writes have to happen - subtract from A, add to B. If the process crashes between them, money has left one account and not arrived at the other. The data is permanently wrong, and no retry logic will fix it.

A transaction is the mechanism that prevents this. It wraps a group of SQL statements into a single unit: either every statement succeeds, or none of them do. There is no in-between state.

The basic syntax

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

BEGIN opens the transaction. Both UPDATE statements run, but their effects are invisible to other connections until COMMIT. At that point both changes become permanent and visible at the same instant.

If anything goes wrong between BEGIN and COMMIT - a constraint violation, an application error, a server crash - neither update is applied. The database rolls back to where it was before the transaction started.

ROLLBACK

You can manually cancel a transaction at any point with ROLLBACK:

BEGIN;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;

-- Decided not to proceed
ROLLBACK;

The inventory update is gone. The database is exactly where it was before BEGIN. Use ROLLBACK whenever you discover mid-transaction that the operation should not go through - it is cleaner than letting a constraint do it for you.

ACID: the four guarantees

You will hear the acronym ACID everywhere. Here is what it actually means:

Atomicity and durability are non-negotiable - the database always enforces them. Isolation is configurable, and the tradeoffs matter (see the Isolation Levels guide). Consistency is a shared responsibility: the database handles constraints and foreign keys, but the application has to get the logic right.

Savepoints

Savepoints let you create a checkpoint inside a running transaction. If something goes wrong later, you can roll back to that checkpoint without losing everything:

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 100.00);
SAVEPOINT order_inserted;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (999, 5, 2);
-- This might fail if order 999 doesn't exist

-- If the second INSERT fails, roll back only to the savepoint
ROLLBACK TO SAVEPOINT order_inserted;

-- The original order INSERT is still in the transaction
COMMIT;

The first INSERT survives. Only the work after the savepoint is undone. This is handy when you have a transaction that does significant setup work and you want to attempt a risky operation without betting the whole transaction on it.

Autocommit and application transactions

Here is something that surprises people: most SQL clients run every statement in its own implicit transaction that commits automatically. This is autocommit mode. You only need BEGIN when you want to group multiple statements together.

ORMs and libraries (Prisma, SQLAlchemy, JDBC) provide their own transaction abstractions on top of BEGIN / COMMIT. Check your library's docs to understand exactly when it opens and commits - the defaults are not always what you expect.

Keeping transactions short

Transactions are not free. A long-running transaction holds locks on every row it has modified, blocking anyone else who wants to write to those rows. The longer it runs, the more contention builds up - other requests queue behind it, timeouts start firing, and things get ugly fast.

The rule is simple: keep transactions as short as possible. Do all your pre-computation in application code before you open the transaction. Write to the database. Commit. Never hold a transaction open while waiting for user input, an HTTP response, or anything else that could take an unpredictable amount of time. Get in, write, get out.