Database Transactions: ACID, Isolation Levels & Locking

Database Transactions

A transaction groups operations so they either all succeed or all fail — the foundation of data integrity when many users hit the database at once. The classic example is a money transfer: debit one account, credit another, with no possibility of one happening without the other.

Beyond atomicity, transactions let you tune how much concurrent operations can interfere via isolation levels — a direct trade-off between consistency and performance.

TL;DR

Transactions make a group of operations atomic (all-or-nothing).
ACID = Atomicity, Consistency, Isolation, Durability.
Pick an isolation level by weighing consistency against concurrency.
Keep transactions short to reduce lock contention, and retry on deadlock.

Quick Example

A transfer that's atomic — both updates commit together, or the whole thing rolls back:

Core Concepts

ACID

Atomicity — all operations succeed or none do.
Consistency — the database moves from one valid state to another.
Isolation — concurrent transactions don't corrupt each other.
Durability — committed data survives a crash.

Isolation levels & anomalies

Higher isolation prevents more anomalies but reduces concurrency. Read Committed is a common default; use Serializable for the strictest correctness.

Locking

Optimistic — assume no conflict; check a version on update and retry if it changed.
Pessimistic — lock the rows up front (SELECT … FOR UPDATE) so others wait.

Best Practices

Keep transactions short — don't hold them open across slow API calls or user think-time.
Retry deadlocks with backoff (databases detect and abort one victim).
Choose the lowest isolation level that's still correct for the operation.
Use optimistic locking for low-contention updates, pessimistic for hot rows.

Common Mistakes

Long-running transactions

Not handling deadlocks

FAQ

What does ACID actually guarantee?

That a transaction is atomic (all-or-nothing), moves the database between valid states, is isolated from other concurrent transactions, and—once committed—durably survives crashes. Together these let you reason about concurrent writes safely.

Which isolation level should I use?

Start with the database default (often Read Committed) and raise it only where you need stronger guarantees. Use Repeatable Read or Serializable for operations that must not see non-repeatable or phantom reads, accepting more contention and possible retries.

Optimistic or pessimistic locking?

Optimistic locking (version checks) suits low-contention data — it avoids holding locks and retries on the rare conflict. Pessimistic locking (FOR UPDATE) suits hot rows where conflicts are frequent, trading concurrency for certainty.

What causes deadlocks and how do I handle them?

Deadlocks happen when two transactions wait on locks each other holds. The database detects this and aborts one. Handle it by retrying the aborted transaction with backoff, and reduce frequency by acquiring locks in a consistent order and keeping transactions short.