What Transactions Really Guarantee

Transactions are less about SQL syntax and more about making related changes safe when systems fail or requests overlap. The useful question is not whether transactions are always necessary, but which guarantees an operation needs and what they cost.

Transactions turn partial failures into one outcome

A transaction groups related reads and writes into one logical unit. It either commits as a whole or aborts, leaving no partial result behind. That gives application code a safe retry boundary instead of forcing it to guess which earlier writes succeeded.

flowchart TB
A[Create unread email] --> B[Insert email record]
A --> C[Increase unread count]
B --> D{All required work succeeds?}
C --> D
D -->|Yes| E[Commit both changes]
D -->|No| F[Abort and roll back]

Not every workload needs the same strength of transaction. Weaker guarantees can improve availability or performance, but they shift more failure handling and concurrency reasoning into application code.

ACID needs precise reading

Atomicity   failed multi-step work is undone
Consistency application invariants remain valid
Isolation   concurrent work does not interfere incorrectly
Durability  committed data survives expected failures

Atomicity means abortability

ACID atomicity is about failure, not about running only one operation at a time. If a crash, timeout, full disk, or constraint violation interrupts a multi-write operation, the database aborts it and undoes its writes. Without that guarantee, a retry can duplicate work because the application cannot tell what already took effect.

Consistency belongs mostly to the application

ACID consistency means business invariants, such as credits and debits remaining balanced. Databases can enforce some rules, including uniqueness and foreign keys, but they cannot generally know what makes a product rule valid. Atomicity and isolation help the application preserve those invariants; they do not define them.

💡 “Consistency” is overloaded. Replica consistency, consistent hashing, CAP consistency, and ACID consistency are different ideas. In ACID, it means the application’s data rules still hold.

Isolation protects concurrent work

Two clients can both read a counter at 42, each calculate 43, and each write 43. One increment is lost. Serializable isolation aims to make concurrent transactions produce the same result as if they ran one at a time, but it has a performance cost. Many databases choose weaker levels instead.

flowchart LR
A[Client 1 reads 42] --> C[Writes 43]
B[Client 2 reads 42] --> D[Writes 43]
C --> E[Final value 43]
D --> E

Durability is layered risk reduction

A commit normally means the database waited for durable storage, enough replica acknowledgments, or both. Neither disks nor replicas are perfect: a machine can fail, replicas can share one outage or software bug, asynchronous replication can lose recent writes, and storage can corrupt silently. Durable systems combine persistent storage, remote replication, and historical backups.

💡 A committed write is safer, not magically indestructible. Historical backups matter because replicas and recent backups may already contain the same silent corruption.

Read committed blocks dirty data, not every race

Read committed is a common baseline. It prevents dirty reads, so another transaction cannot see an uncommitted value, and dirty writes, so a writer cannot overwrite another writer’s uncommitted work.

flowchart LR
A[Writer sets x to 3] --> B[Uncommitted value]
B --> C[Other reader still gets x = 2]
B --> D[Commit]
D --> E[New readers get x = 3]

That avoids inconsistent halfway views, such as seeing a newly inserted email while its unread counter is still old. It also avoids mixing conflicting multi-record work, such as awarding a car to one buyer while sending the invoice to another. It does not prevent lost updates: a second writer can still overwrite a value that the first writer has already committed.

Databases commonly use row-level locks for competing writers. For reads, many keep both the old committed value and a writer’s new uncommitted value, returning the old value until commit instead of making readers wait.

Snapshot isolation gives a consistent point in time

Read committed can still produce read skew. During a transfer between two accounts, a reader may see one balance before the transfer and the other after it, making money appear to vanish. This is especially dangerous for backups, analytics, and integrity checks that scan many records.

flowchart LR
A[Transaction starts] --> B[Snapshot of committed data]
W[Another transaction commits a change] --> N[New version]
B --> R[Original transaction keeps its old consistent view]
N --> L[Later transaction sees the new version]

Snapshot isolation lets every read in a transaction use the database state that existed when that transaction began. Long-running reads get a coherent view while writes continue. Its key performance property is that readers do not block writers, and writers do not block readers.

💡 Snapshots usually do not lock rows. MVCC keeps older versions for active readers while writers create newer versions. Competing writes to the same row still conflict: one may wait, abort, or retry.

MVCC keeps versions until old readers no longer need them

Multi-version concurrency control tags versions with transaction IDs. An update creates a new version and marks the old one for deletion. A transaction sees versions committed before its snapshot, while ignoring aborted work, work still in progress when it began, and work started later.

flowchart TB
O[Old account version: balance 500] --> R[Older snapshot reads 500]
N[New account version: balance 400] --> L[Later snapshot reads 400]
O --> G[Garbage collect after no snapshot needs it]

💡 If three transactions read the same balance and all try to write a new value, the database does not infer their intended arithmetic. For one-row debits, use an atomic conditional update. For transfers or multi-record rules, use a transaction as well.

Indexes also need to track versions long enough for old snapshots. Another implementation uses copy-on-write B-trees: a write creates new changed pages and a new root, while older readers follow the old root. Both approaches eventually need garbage collection or compaction.

💡 Snapshot isolation is often named differently across databases. “Repeatable read” or even “serializable” may describe it, so check the anomalies a specific database prevents instead of trusting the label.

The practical takeaway

Use transactions around invariants that span records, indexes, or denormalized copies. Treat retries as a design problem: commit acknowledgment can be lost, overload can be amplified by retries, permanent errors will not heal by retrying, and external side effects such as emails need coordination beyond a database rollback.

These are my personal learning notes from Designing Data-Intensive Applications by Martin Kleppmann.