The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Two database transactions can each commit successfully and still leave the data in a state that no valid one-at-a-time execution could produce. The problem is not necessarily a failed transfer or a dirty read: it can arise when concurrent transactions make separate decisions from overlapping reads, then write different rows. That pattern is called write skew.
How can two committed transfers appear to create money?
Consider a business rule that says a shared pool must retain at least $100. Two transactions run at the same time. Each reads the same pool balance of $150 and decides it can move $40. One transfers $40 from account A; the other transfers $40 from account B. If they write different account rows and neither rechecks or locks the shared condition, both decisions look acceptable from the state each read. Together, however, they move $80 and leave only $70 in the pool, violating the rule.
As an Amazon Associate I earn from qualifying purchases.
This is an illustrative example of write skew, not a bank scenario described in PostgreSQL’s documentation. Its key ingredients are a shared condition and disjoint writes: each transaction reads data relevant to the rule, but changes different rows. A successful COMMIT confirms that the individual transaction committed; it does not, by itself, prove that the combined result obeys every business invariant.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →What the transactions read and write
| Transaction | Reads | Writes | Decision from its view |
|---|---|---|---|
| T1 | Shared pool total: $150; account A is eligible | Moves $40 from account A | Leaves $110, so the rule appears satisfied |
| T2 | Shared pool total: $150; account B is eligible | Moves $40 from account B | Leaves $110, so the rule appears satisfied |
Those individual calculations are not the combined result. If both changes take effect, the pool loses $80 and falls to $70. This framing does not mean that concurrent transfers generally corrupt balances: a debit and credit applied to predetermined account rows is different from deciding whether a transfer is allowed by inspecting a shared total, predicate, or set of rows.
#1 Best Overall
- hardcover, brand new
Why transaction isolation matters
Atomicity and isolation answer different questions. Atomicity makes each transaction all-or-nothing. Isolation governs how concurrent transactions observe and affect one another. Avoiding dirty reads is useful, but it is not the same as guaranteeing that concurrent work is equivalent to running transactions one at a time.
PostgreSQL defines Serializable as the strictest level, guaranteeing that a concurrent execution of Serializable transactions has the same effect as running them one at a time in some order. Its documentation puts it this way: “The most strict is Serializable, which is defined by the standard in a paragraph which says that any concurrent execution of a set of Serializable transactions is guaranteed to produce the same effect as running them one at a time in some order.” PostgreSQL 16: Transaction Isolation
Rank #2
- Brand: McGraw-Hill Education
- Database System Concepts, 7th Edition
Read Committed, Repeatable Read, and Serializable compared
These levels differ in what each transaction can see and what the database guarantees about concurrent outcomes. The details below describe PostgreSQL 16; other database products and implementations may behave differently.
| PostgreSQL level | What a transaction sees | What it means for a cross-row rule | Application consideration |
|---|---|---|---|
| Read Committed | Each ordinary query gets a snapshot of data committed before that query began. Separate queries in one transaction can see different committed states. This is PostgreSQL’s default. | A complex decision based on a condition spanning rows can reason from a view that changes between queries. The level can work well for simpler updates to targeted rows. | Review the SQL and invariant; do not assume a sequence of reads and writes protects a multi-row condition. |
| Repeatable Read | The transaction sees a stable snapshot. PostgreSQL implements this level using snapshot isolation. | A stable view does not ensure that every concurrent outcome corresponds to a serial order. Enforcing business rules may require carefully chosen explicit locks. | Check whether concurrent transactions can read overlapping data and make disjoint writes that jointly violate the rule. |
| Serializable | The outcome is guaranteed to be equivalent to some serial execution. | PostgreSQL uses predicate locking to detect conflicts where a concurrent write would have affected an earlier read if execution order had differed. | A transaction can fail with a serialization error; the application must handle the reported failure. |
What happens when PostgreSQL detects a serialization conflict?
Serializable isolation prevents an invalid serial outcome by rejecting a transaction when PostgreSQL detects a serialization anomaly. Therefore, an application must not treat every attempted transaction as guaranteed to commit. When PostgreSQL reports a serialization failure, retry the entire transaction—not merely the statement that failed—so its reads and decisions are made again against a valid view. Follow the retry guidance for the PostgreSQL release actually deployed.
How to protect a business invariant
Start from the rule the application must preserve, then examine whether concurrent operations can each appear valid while their combined writes break it.
- State the invariant precisely. For example, define the minimum permitted pool balance and whether it applies before or after pending transfers.
- List each transaction’s reads and writes. Include predicates, aggregates, and all rows used to decide whether a transfer is allowed—not only the rows ultimately updated.
- Choose a protection that matches the access pattern. For a simple update to a known row, a targeted update may be sufficient. For a rule spanning data, consider Serializable isolation or explicit locking designed for that invariant. PostgreSQL cautions that business rules at Repeatable Read may need careful explicit locks.
- Handle expected failure paths. If the chosen approach can block or report a serialization failure, ensure the application handles that outcome; for a reported serialization failure, retry the whole transaction.
- Validate the actual database behavior. Isolation semantics depend on the database product, its implementation, the SQL statements, indexes, and locking choices. Check the documentation for the deployed system rather than assuming PostgreSQL behavior applies everywhere.
Why a stable snapshot may still be insufficient
Repeatable Read sounds strong because repeated reads in one transaction remain stable. But stability answers whether the transaction’s view changes; it does not necessarily answer whether all concurrent transactions together could have run in some serial order. PostgreSQL explicitly distinguishes its Repeatable Read snapshot from the Serializable guarantee. When correctness depends on a condition spanning rows, the isolation level and any locks must be chosen around that condition, not just around whether a read repeats the same value.
Quick Recap
Best Value
Sources and scope
- PostgreSQL 16: Transaction Isolation documents the levels, their behavior, Serializable conflict detection, and retry guidance.
- SSI – PostgreSQL wiki explains write skew and PostgreSQL’s Serializable Snapshot Isolation protection against serialization anomalies.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Recommended Free Tools
