What is Database Transaction?
Definition
A database transaction groups several reads and writes into a single, all-or-nothing unit of work. When the transaction is committed, all of its changes become permanent; if it is rolled back or fails, none of them are applied. Relational databases describe these guarantees with the ACID properties, while the isolation level determines how much concurrent transactions can see of each other's work.
Also known as: transaction, ACID transaction, ACID, commit and rollback, DB transaction
Life cycle of a transaction
A transaction starts with BEGIN, runs any number of statements and ends with either COMMIT or a complete ROLLBACK. Without an explicit transaction, most databases run in autocommit mode, where each statement is a tiny transaction of its own. The real value comes from grouping steps that depend on each other. Placing an order means reducing stock, writing the order and writing its lines, and those must happen together or not at all:
BEGIN;
-- Reduce stock only if enough is left; if 0 rows change, the app rolls back
UPDATE products SET stock = stock - 2
WHERE id = 42 AND stock >= 2;
INSERT INTO orders (customer_id, total)
VALUES (77, 37.00) RETURNING id; -- returns e.g. 9131
INSERT INTO order_items (order_id, product_id, qty)
VALUES (9131, 42, 2);
COMMIT;The stock >= 2 condition is a small but important detail. Because the check and the update happen in one statement, two simultaneous orders cannot both sell the last item.
ACID: what each letter does and does not promise
The database entry gives short definitions of the four ACID properties. In practice, what matters more is how each guarantee is delivered and where it stops:
| Property | How it is achieved | Where it ends |
|---|---|---|
| Atomicity | Changes go to a log first (WAL, undo log), so an interrupted transaction can be unwound | Covers only the database; an email that was already sent stays sent |
| Consistency | Declared constraints (foreign keys, CHECK, UNIQUE) are verified when the transaction completes | The database only knows the rules you gave it; business rules living in code are not protected |
| Isolation | Locks and multi-version concurrency control (MVCC) | Default levels do not provide full isolation |
| Durability | A commit is acknowledged only after the log reaches disk | Settings relaxed for speed and single-disk setups weaken it |
Isolation levels in brief
The SQL standard defines four isolation levels. Higher levels allow fewer interference effects between concurrent transactions, at the cost of more waiting and more retries:
- Read Uncommitted: other transactions' uncommitted changes may be visible. PostgreSQL treats a request for this level as Read Committed.
- Read Committed: each statement sees data committed before it began, so running the same query twice within a transaction can return different results. This is PostgreSQL's default.
- Repeatable Read: rows read once look the same for the rest of the transaction. This is the default in MySQL's InnoDB.
- Serializable: the outcome is as if transactions had run one at a time. On conflict, the database aborts one of them with a serialization failure (SQLSTATE 40001), and the application must retry it from the start.
The classic problem at default levels is the lost update: two requests both read a stock level of 5, both subtract 1 in application code, both write 4, and one sale vanishes. The fixes are to change the value in a single atomic UPDATE, to lock the row with SELECT ... FOR UPDATE, or to use optimistic locking with a version column. The PostgreSQL documentation compares the levels in detail.
Designing transactions in application code
- Keep them short. An open transaction holds locks and ties up a connection from the connection pool. External calls, such as an HTTP request to a payment provider, belong outside it.
- Expect retries. Serialization failures and deadlocks are normal operating conditions, so the retried work must be safe to run twice, in other words idempotent.
- Lock in a consistent order. Two transactions touching the same rows in opposite order can wait on each other forever; the database detects this and kills one of them.
- The boundary ends at the database. Saving an order and publishing a message cannot share one transaction. The usual answer is the outbox pattern: write the message to a table in the same transaction as the order, and let a separate process forward it to the message queue.