Transactions are the contract a database makes with your application: either all of these writes happen, or none of them do. Without transactions, a crash at the wrong moment leaves your data in a half-written, inconsistent state. This 2026 guide explains ACID from first principles through to the isolation levels that real production apps actually use.
What changed in 2026
- Distributed transactions got more practical with Postgres logical replication and CockroachDB's serializable guarantees across nodes — but they still come with a latency cost.
- ORMs improved transaction ergonomics. Prisma, Drizzle, and SQLAlchemy all expose clean transaction APIs that avoid the footgun of auto-commit.
- Neon's branching lets you test transactions against a copy of production data without snapshots or backups.
- Serializable Snapshot Isolation (SSI) in Postgres remains the gold standard for correctness without lock contention, and more teams discovered it this year.
ACID: what each letter actually means
| Property |
What the DB guarantees |
What breaks without it |
| Atomicity |
All writes in a transaction commit or none do |
Partial writes on crash |
| Consistency |
Constraints (FK, CHECK, UNIQUE) are enforced |
Referential integrity violations |
| Isolation |
Concurrent transactions do not see each other's uncommitted data |
Dirty reads, phantom rows |
| Durability |
Committed data survives crash |
Lost writes |
Isolation levels
Postgres offers four isolation levels. Only three matter in practice:
-- Default: good for most reads and non-critical writes
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Snapshot: each statement sees a consistent snapshot from start of transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Serializable: transactions behave as if run one at a time
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
| Level |
Dirty read |
Non-repeatable read |
Phantom read |
Throughput |
| Read Uncommitted |
Possible |
Possible |
Possible |
Highest |
| Read Committed (default) |
No |
Possible |
Possible |
High |
| Repeatable Read |
No |
No |
Possible* |
Medium |
| Serializable |
No |
No |
No |
~10–30% lower |
*Postgres Repeatable Read actually prevents phantoms too, but the SQL standard does not guarantee it.
A practical transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Only commit if both succeeded
COMMIT;
-- On error, the application calls:
-- ROLLBACK;
If the process dies after the first UPDATE and before COMMIT, the database rolls back automatically. This is atomicity in action.
Optimistic vs pessimistic locking
Pessimistic locking (SELECT ... FOR UPDATE) locks the row immediately and holds it until COMMIT. Best when contention is high and conflicts are expected.
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- row is locked; other transactions block here
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
Optimistic locking does not lock; it re-checks a version column before writing. Best when contention is low.
-- Read with version
SELECT balance, version FROM accounts WHERE id = 1;
-- Write only if version unchanged
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = :expected_version;
-- If 0 rows affected, someone else updated — retry in application
Savepoints for partial rollback
BEGIN;
INSERT INTO orders (user_id, total) VALUES (42, 99.99);
SAVEPOINT order_created;
INSERT INTO order_items (order_id, sku) VALUES (1, 'WIDGET');
-- If this fails:
ROLLBACK TO SAVEPOINT order_created;
-- order row is preserved; item insert is undone
COMMIT;
Savepoints let you roll back part of a transaction without aborting the whole thing — useful in complex batch operations.
How to pick an isolation level
- CRUD web app, most reads → Read Committed (default). Fine for 95% of workloads.
- Report that reads multiple tables and must be consistent → Repeatable Read. Prevents seeing different snapshots mid-report.
- Money movement, inventory reservation, ticket booking → Serializable. The cost is worth the correctness guarantee.
- Anything with a SELECT + conditional UPDATE pattern → Use
SELECT FOR UPDATE or Serializable to avoid lost updates.
Common mistakes
Catching exceptions and continuing inside a transaction. Once Postgres reports an error, the transaction is aborted. Every subsequent statement returns ERROR: current transaction is aborted. You must ROLLBACK and start fresh.
Long-running transactions. A transaction open for minutes holds locks and prevents VACUUM from cleaning dead tuples. Keep transactions short — milliseconds, not seconds.
Deadlocks from inconsistent lock order. Two transactions each lock row A then try to lock row B (and vice versa). Always acquire locks in a consistent global order to avoid deadlock cycles.
Autocommit in ORMs. Many ORMs default to autocommit, meaning each query is its own transaction. Explicitly open a transaction for any multi-step write.
Read-only queries inside a transaction. Unnecessary, adds overhead. Simple SELECTs outside a transaction are fine unless you need a consistent snapshot.
What to skip
- Read Uncommitted — it does not exist as a real isolation level in Postgres (it is silently promoted to Read Committed). Avoid it entirely.
- Distributed transactions (2PC) for non-critical writes — the latency and failure complexity rarely justify the consistency guarantee for most apps.
- Holding transactions open across HTTP requests — user think time plus network latency will starve your connection pool.
FAQ
What is the difference between a rollback and an abort?
They are the same outcome — all writes in the transaction are undone. "Abort" is the database term; "rollback" is the application command that triggers it.
Can I nest transactions in Postgres?
Not truly. Postgres has SAVEPOINTS for partial rollback, but nested BEGIN calls are ignored. Use savepoints for sub-transaction semantics.
How do I detect a deadlock?
Postgres raises ERROR: deadlock detected and rolls back one of the transactions. Log for this error, then retry the transaction in your application.
Does every ORM handle transactions correctly?
No. Always read your ORM's transaction docs. Drizzle and Prisma are explicit; some legacy ORMs silently autocommit. Test with a forced error to verify rollback behavior.
Where to go next