Transactions are the contract between your application and the database: either all of these operations succeed together, or none of them take effect. Without transactions, a crash between two related writes leaves the database in an inconsistent state. With them, the database guarantees that partial updates never happen — and that concurrent writers see a consistent view of the data. This is how they actually work.
What changed in 2026
- Distributed transactions are now viable. Distributed databases (CockroachDB, YugabyteDB, Spanner) support serializable isolation at global scale with latency that is usable in production — though still 2–5× more expensive than single-region.
- Postgres 17 improved lock contention monitoring —
pg_stat_activity and pg_locks views became easier to query, and the new lock waiter metrics make finding blocked transactions much faster.
- Application-level sagas replaced distributed transactions in microservice architectures — multi-service workflows use compensating transactions rather than two-phase commit.
- SQLite WAL mode + concurrent readers made it a serious choice for embedded and edge databases, with transaction semantics closely matching Postgres.
The ACID properties
Atomicity — A transaction is all-or-nothing. If any operation fails, the entire transaction is rolled back as if it never happened.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If the second UPDATE fails, the first is also rolled back
COMMIT;
Consistency — The database transitions from one valid state to another. Constraints (foreign keys, NOT NULL, CHECK) are enforced at commit time.
Isolation — Concurrent transactions behave as if they ran serially. The degree of isolation is configurable (see below).
Durability — Once committed, data survives crashes. The write-ahead log (WAL) ensures committed data is flushed to disk before the transaction returns success.
Isolation levels and what goes wrong
| Level |
Dirty Read |
Non-Repeatable Read |
Phantom Read |
Common use |
| READ UNCOMMITTED |
Possible |
Possible |
Possible |
Rarely used |
| READ COMMITTED |
Prevented |
Possible |
Possible |
Default in Postgres, MySQL |
| REPEATABLE READ |
Prevented |
Prevented |
Possible |
MySQL InnoDB default |
| SERIALIZABLE |
Prevented |
Prevented |
Prevented |
Financial, inventory systems |
Dirty read — reading uncommitted data from another transaction that may still be rolled back.
Non-repeatable read — reading the same row twice in one transaction and getting different values because another transaction committed in between.
Phantom read — a query returns different rows on repeat execution because another transaction inserted or deleted rows matching the WHERE clause.
Setting isolation level
-- Postgres: per-transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
-- balance is stable for the life of this transaction
COMMIT;
Optimistic vs pessimistic locking
Pessimistic locking acquires the lock before reading, preventing other writers from modifying the row:
-- SELECT FOR UPDATE holds a lock until COMMIT/ROLLBACK
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
Safe, but blocks concurrent readers and writers. Appropriate when conflicts are likely (high-contention rows).
Optimistic locking reads without a lock, then checks at write time whether anyone else modified the row:
-- version column tracks modifications
SELECT id, balance, version FROM accounts WHERE id = 1;
-- application reads version = 7, computes new balance
UPDATE accounts
SET balance = 900, version = 8
WHERE id = 1 AND version = 7; -- fails if another writer incremented version
-- If 0 rows affected, retry with fresh read
Optimistic locking scales better for read-heavy workloads with infrequent conflicts.
Savepoints
Savepoints allow partial rollbacks within a transaction:
BEGIN;
INSERT INTO orders (user_id, total) VALUES (42, 500);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 99);
-- Something fails here
ROLLBACK TO SAVEPOINT after_order; -- undo only the items insert
-- The order row is still in place
COMMIT; -- commits the order without items
Useful for complex workflows where one optional step failing should not abort the whole transaction.
Deadlocks
A deadlock occurs when transaction A holds a lock that B needs, and B holds a lock that A needs. The database detects and resolves this by rolling back one transaction.
T1: UPDATE accounts SET ... WHERE id = 1 (holds lock on row 1)
T2: UPDATE accounts SET ... WHERE id = 2 (holds lock on row 2)
T1: UPDATE accounts SET ... WHERE id = 2 (waits for T2)
T2: UPDATE accounts SET ... WHERE id = 1 (waits for T1) → deadlock
Prevention: always acquire locks in a consistent order (e.g., always lock the lower ID first). Keep transactions short to reduce the window for conflicts.
How to pick isolation level
- Typical web app reads and writes? READ COMMITTED (Postgres default) is correct — you get committed data without the overhead of higher isolation.
- Financial transfers or inventory updates? REPEATABLE READ or SERIALIZABLE — prevents the phantom reads and non-repeatable reads that cause lost updates.
- Reporting queries that must see a point-in-time snapshot? REPEATABLE READ within the transaction gives you a consistent view without locking.
- Low-latency, high-concurrency writes with rare conflicts? Optimistic locking + READ COMMITTED — skip heavy locks, retry on conflict.
Common mistakes
External I/O inside transactions. Sending an email, calling a payment API, or writing to S3 inside a BEGIN/COMMIT block means the transaction stays open while the I/O runs. Rollback cannot undo the email. Do I/O after commit.
Long-running transactions. A transaction open for minutes holds locks and accumulates WAL. It blocks autovacuum in Postgres (bloat accumulates), delays replication, and increases deadlock probability.
Not handling transaction retry. Under SERIALIZABLE or with optimistic locking, serialization failures (error code 40001 in Postgres) are expected and must be retried. Application code that does not retry will fail intermittently.
Implicit transactions with ORMs. Many ORMs start a transaction per request by default. Verify this in your ORM's configuration — implicit long-lived transactions are a common source of lock contention.
What to skip
- Two-phase commit (2PC) across microservices — latency and coordinator failure modes make it painful; use a saga pattern with compensating transactions instead.
- SERIALIZABLE for all queries — the overhead is real; reserve it for operations that genuinely require it.
FOR UPDATE on high-contention rows when optimistic locking would work — row locks become a bottleneck at scale.
FAQ
When should I use SERIALIZABLE isolation?
For operations where a phantom read or non-repeatable read could cause a real business problem — inventory reservations, financial double-spend prevention, or any place where "check then act" logic must be atomic.
What happens to a Postgres transaction that is idle for hours?
It holds its locks and prevents autovacuum from reclaiming dead tuples. Postgres 14+ introduced idle_in_transaction_session_timeout — set it to a few minutes in production.
Are NoSQL databases ACID-compliant?
Some are. MongoDB has supported multi-document ACID transactions since v4.0. DynamoDB supports transactions within a single partition. But most NoSQL databases default to eventual consistency, not ACID.
What is a saga pattern?
A sequence of local transactions, each publishing an event. If a step fails, the saga executes compensating transactions to undo previous steps. It achieves eventual consistency across services without a distributed transaction.
Where to go next