Database transactions and ACID, explained

A transaction is a group of database operations treated as one unit: all succeed, or none take effect.

The classic example: a transfer

Move 500 from account A to account B:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
COMMIT;

If the server crashes after the first UPDATE, money would vanish. With a transaction, the database undoes the first step. If your code detects a problem, it can run ROLLBACK instead of COMMIT.

ACID

Atomicity. All or nothing. A transaction is never half applied.

Consistency. A transaction moves the database from one valid state to another, respecting rules such as constraints, foreign keys and unique keys. (The application must also keep its own business rules, like "balance cannot go negative", ideally with a CHECK constraint.)

Isolation. Concurrent transactions do not interfere in confusing ways, as if they ran one at a time. The strength is configurable (see the isolation levels part).

Durability. After COMMIT, the data survives a crash or power loss. Databases do this with a write-ahead log (WAL): changes are written to a log on disk first.

Autocommit

Many tools run each statement as its own transaction by default. To group statements you must start a transaction explicitly (BEGIN/START TRANSACTION) or use your library's transaction helper.

Using transactions well

• Keep them short. A long transaction holds locks and blocks others, and slows cleanup. Do not wait for a user or call an external API in the middle.
• Do only database work inside. If you send an email inside and then roll back, the email is already sent. Do side effects after commit.
• Handle errors: roll back on failure and, for conflicts, retry the whole transaction.
• Lock when needed: SELECT ... FOR UPDATE reads a row and locks it until the transaction ends, to avoid lost updates.
• Beware of deadlocks: two transactions waiting on each other. Lock rows in a consistent order and be ready to retry.

Transactions across services

A transaction covers one database. Across several services you need other patterns, such as a saga or the outbox pattern.

Not every database

Most relational databases give full ACID. Many NoSQL systems offer weaker guarantees or limit transactions to a single document. Check before you rely on it.

Takeaway: wrap related changes in a transaction, keep it short, put side effects after the commit, and be ready to retry.

---

Backend & Database series, Part 1 (SQL and transactions): 3 of 4. Previous: Database indexes explained: how a B-tree makes queries fast.