Transactions: All or Nothing
How databases guarantee that related changes succeed or fail together.
Consider a bank transfer: $500 moves from account A to account B. This requires two writes: subtract from A, add to B. If the system crashes between the two, you end up with money that has left one account but not arrived at the other. The data is permanently wrong.
A transaction is the mechanism that prevents this. It is a group of SQL statements that the database treats as a single unit. Either every statement in the group succeeds, or none of them do. There is no state where one succeeds and the other does not.
The basic syntax
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
BEGIN starts the transaction. The two UPDATE statements execute, but their effects are not yet visible to other connections. COMMIT makes both changes permanent and visible atomically.
If anything fails between BEGIN and COMMIT, whether a constraint violation, an application error, or a server crash, neither update is applied. The database rolls back to the state it was in 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 discarded. The database is unchanged. This is useful when you discover mid-transaction that the operation should not proceed.
ACID: the four guarantees
Transactions provide four properties, summarized as ACID:
| Property | Meaning |
|---|---|
| Atomicity | All statements succeed, or none do. No partial results. |
| Consistency | The database moves from one valid state to another. Constraints are checked at commit. |
| Isolation | Concurrent transactions do not see each other's in-progress changes (to varying degrees). |
| Durability | Once committed, changes survive crashes. The database writes to disk before confirming. |
Atomicity and durability are always fully enforced. Isolation is configurable (see the Isolation Levels guide). Consistency is a collaboration between the database (constraints, foreign keys) and the application (correct logic).
Savepoints
A savepoint lets you roll back to a specific point within a transaction without discarding the entire transaction:
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;
Savepoints let you build retry logic within a transaction, recovering from partial failures without losing all the work done so far.
Autocommit and application transactions
In most SQL clients and application frameworks, every individual SQL statement runs in its own implicit transaction that commits automatically. This is called autocommit mode. To group multiple statements, you have to explicitly write BEGIN.
Application ORMs and libraries (Prisma, SQLAlchemy, JDBC, etc.) provide their own transaction abstractions that translate to BEGIN and COMMIT behind the scenes. Check your library's documentation to understand when it implicitly starts and commits transactions.
Keeping transactions short
Transactions are not free. A long-running transaction holds locks on the rows and pages it has modified, which blocks other transactions that want to write to those same rows. The longer a transaction runs, the more contention it creates.
The practical rule: keep transactions as short as possible. Do all the pre-computation in application code before opening the transaction. Perform the database writes. Commit immediately. Never hold a transaction open while waiting for user input, making an HTTP request, or doing anything that might take an unpredictable amount of time.