Two database transactions can each be valid on their own and still produce an inconsistent result when they overlap. Concurrency control manages that overlap: it aims to preserve behavior equivalent to a serial order while allowing useful work to proceed concurrently. Depending on the system and isolation level, it may make transactions wait or abort one so the application can retry.
How valid transactions can produce an invalid combined result
Consider two transactions that both read an account balance of $100. One adds $20 and the other subtracts $10. If each computes a new value from the same original balance and writes it back, the later write can overwrite the earlier one. The final balance may be $90 or $120 instead of the $110 that either serial order would produce. This is a lost update: each transaction’s individual arithmetic is valid, but their combined execution has discarded work.
As an Amazon Associate I earn from qualifying purchases.
A read-only transaction can also get an inconsistent result. Imagine it reads the balance from one account, then a transfer moves money between that account and another before the transaction reads the second balance. The two values may reflect different moments, so their sum does not represent the consistent state the reader intended to inspect.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →A dirty read is different: a transaction reads a value another transaction has written but not committed. If the writer later aborts, the reader may have used a value that never became part of the database’s committed state.
#1 Best Overall
What isolation and serializability mean
Isolation describes how concurrent transactions are allowed to interact. Lower isolation may permit some anomalies in exchange for more concurrency; stricter isolation aims to rule out more conflicting outcomes. The exact behavior depends on the database and its implementation, so an isolation-level name should not be treated as a guarantee identical across every system.
Serializability is the strongest conceptual target: the committed effects of concurrent transactions must be equivalent to some order in which those transactions ran one at a time. They do not have to execute literally one after another. A database can allow overlap as long as the resulting committed behavior is explainable by a serial ordering.
PostgreSQL 18 documentation calls Serializable “the strictest transaction isolation” and explains that a transaction may be aborted if its concurrent execution cannot be reconciled with a serial order. In PostgreSQL, that means applications using serializable transactions must be prepared to retry the whole transaction after a serialization failure. PostgreSQL 18: Transaction Isolation
Free tools Windows power users keep installed
One-click scans. No signup required.
How locks turn conflicts into waits
One common approach is to lock data that a transaction needs to read or change. If another transaction requests an incompatible lock, it waits until the lock can be granted or released. This can prevent conflicting operations from proceeding in an order that loses updates or exposes disallowed intermediate state.
Two-phase locking is a general concurrency-control approach in which a transaction acquires locks during a growing phase and releases them during a shrinking phase; it does not acquire new locks after releasing one. Holding locks through the transaction helps preserve serializable behavior, but can also make other transactions wait. Implementations and lock details vary, so this is a model for understanding the tradeoff rather than a claim that every database uses identical mechanics.
Why deadlocks happen and how databases respond
Waiting can become a deadlock when transactions form a cycle. For example, transaction A holds a lock on row 1 and waits for row 2, while transaction B holds row 2 and waits for row 1. Neither can proceed without the other releasing its lock.
PostgreSQL detects deadlocks and aborts one transaction so the other can continue. An application should handle that failure rather than assume every transaction will complete. A principal prevention strategy is to acquire locks on multiple objects in a consistent order, reducing the chance that concurrent transactions form a cycle. PostgreSQL 18: Explicit Locking
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsOptimistic validation: proceed first, check for conflicts later
Optimistic concurrency control lets transactions do work without first blocking every possible conflict. The system checks for conflicting activity during validation or commit. If it finds an execution that cannot be accepted, it discards a transaction’s work and the application can retry.
This shifts the cost rather than removing it. Locking handles conflicts earlier, often by making transactions wait; optimistic validation permits more work to proceed, but a conflict can mean that work must be thrown away. Which is preferable depends on how often conflicts occur, how costly waiting is, and whether the application can safely retry the entire operation. Neither approach is a universal performance recommendation.
| Question | Pessimistic locking | Optimistic validation |
|---|---|---|
| When is a conflict handled? | Before or during the work, by acquiring locks. | At validation or commit, after work has proceeded. |
| Typical conflict cost | Waiting for an incompatible lock. | Discarded work and a retry after a conflict is detected. |
| When it may fit | When preventing conflicting work up front is important. | When conflicts are expected to be infrequent and retry is practical. |
What application code should do when a transaction fails
A serialization failure or deadlock victim is not necessarily a database malfunction; it can be the system’s way of preserving consistency under concurrency. Code that uses such transactions should be able to rerun the complete transaction, including its reads and decisions, rather than merely replaying the final write. Retrying only part of the operation can reuse assumptions based on a state that is no longer current.
Retry handling should be bounded and should distinguish retryable concurrency failures from validation errors or other permanent failures. PostgreSQL’s transaction isolation documentation describes serialization failures and the need for applications to retry affected transactions. PostgreSQL 18: Transaction Isolation
PostgreSQL isolation detail: READ UNCOMMITTED
Isolation labels are not portable guarantees. PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, so selecting READ UNCOMMITTED in PostgreSQL does not provide an independent, weaker behavior that allows dirty reads. Check the documentation for the specific database and version in use before relying on a named isolation level. PostgreSQL 18: SET TRANSACTION
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.
