What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Two database transfers can each commit successfully and still leave the system in a state that could not have resulted from running the transactions one at a time. The risk is not that a committed transfer literally duplicates cash; it is that concurrent transactions can make separate, locally valid decisions from overlapping reads and together violate a shared business invariant.
How two committed transactions can violate one rule
Imagine a system enforcing a rule about a shared pool of funds or a set of accounts. Each transaction checks the relevant data, decides its action is allowed, and writes to a different row. If both checks rely on overlapping data, each transaction may see a state in which its action is valid. Their separate writes can then leave the combined result outside the rule.
This pattern is called write skew: concurrent transactions read overlapping data, make disjoint writes, and produce a result that could not occur in any one-at-a-time ordering. PostgreSQL’s explanation of the anomaly is in its SSI documentation. The “create money” framing is an illustration of how a violated aggregate or account rule might look; it is not an example claimed by PostgreSQL itself.
Recommended Free Tools
A simplified schedule
| Transaction | Reads | Decision and write |
|---|---|---|
| Transfer A | Checks the shared total or a set of accounts | Concludes the operation is allowed; changes account A |
| Transfer B | Checks overlapping data before A’s change is visible | Also concludes the operation is allowed; changes account B |
| Combined result | Both transactions’ checks were based on their respective views | The two writes may together violate the shared rule |
The schedule is illustrative, not a claim that every pair of bank transfers behaves this way. A transfer that updates a predetermined account row is different from a workflow that decides whether an operation is allowed by searching several rows, checking a predicate, or calculating an aggregate. PostgreSQL notes that Read Committed can work well for simpler targeted updates, while more complex search-condition logic can be problematic. See PostgreSQL 16’s transaction-isolation documentation.
#1 Best Overall
Why successful commits do not guarantee a serial result
A transaction’s atomic commit means its changes take effect together or not at all. Avoiding dirty reads means a transaction does not read another transaction’s uncommitted changes. Neither property alone guarantees that concurrent transactions, considered together, obey every business rule as if they ran sequentially.
PostgreSQL 16 defines Serializable 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.” That is a guarantee about the combined outcome, not a promise that every transaction will commit: a database can reject a transaction when it detects a serialization conflict.
What PostgreSQL’s three isolation levels mean for this problem
The following comparison describes PostgreSQL’s documented behavior, not a universal implementation rule for every database product. The level determines what a transaction can see and which concurrent outcomes are prevented; the application must also account for blocking or failures.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware match| PostgreSQL level | What a transaction sees | Relevance to a shared rule | Application consideration |
|---|---|---|---|
| Read Committed | Each ordinary command sees data committed before that command began. A later SELECT in the same transaction may see a different committed state. This is PostgreSQL’s default. | Different reads can support decisions based on different states. Simple updates to known rows may be suitable, but complex conditions spanning data can produce inconsistent reasoning. | Design the operation around its actual rows and conditions; do not assume multiple queries share one fixed view. |
| Repeatable Read | The transaction sees a stable snapshot. | A stable snapshot does not itself guarantee a serializable outcome. PostgreSQL describes its implementation as snapshot isolation. | Enforcing business rules at this level can require careful explicit locks. |
| Serializable | Concurrent transactions are guaranteed to have the same effect as some one-at-a-time ordering. | Prevents executions whose combined effect is not serializable. | Conflicting transactions can fail with a serialization error, so the application must handle the relevant error and retry the whole transaction when appropriate. |
How PostgreSQL Serializable detects conflicts
PostgreSQL uses predicate locking to track read dependencies for Serializable transactions. If a concurrent write would have affected a prior read under a different execution order, the database can detect a serialization conflict and abort a transaction rather than allow a non-serializable result. These predicate locks are used for detection; their presence does not mean every such conflict is resolved by waiting for the other transaction.
Rank #3
Serializable therefore changes the application contract: code must be prepared for an operation to fail even when its SQL is otherwise valid. PostgreSQL’s documentation describes retry handling for serialization failures. Check the guidance for the deployed PostgreSQL release and retry the entire transaction, including its reads and decisions, rather than replaying only the last write.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Choose protection based on the invariant and query pattern
Start by stating the business invariant in terms the database operation must preserve—for example, a constraint on a total or on a set of related accounts. Then inspect which rows and conditions each transaction reads and writes. The key question is whether two executions can read overlapping facts, write disjoint rows, and jointly break that invariant.
- If the operation updates known rows: determine whether its row-level updates and any database constraints are enough for the rule. PostgreSQL says Read Committed can work well for simpler updates to predetermined rows.
- If the decision depends on a predicate, total, or several rows: a stable snapshot alone may not be sufficient. Evaluate Serializable or an explicit locking strategy appropriate to the invariant.
- If using Serializable: include whole-transaction retry handling for serialization failures, and make sure the transaction’s decisions are recalculated on retry.
- If considering explicit locks: identify all data whose concurrent change could invalidate the decision and ensure the locking design covers it. PostgreSQL warns that business-rule enforcement under Repeatable Read may require careful explicit locks.
The correct choice depends on the database, its isolation implementation, the SQL statements, indexes, and locking design. PostgreSQL’s documented behavior should not be assumed to apply unchanged to another database.
Quick Recap
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.
