Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →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 transactions can each be valid on their own and still produce an incorrect result when they overlap. Concurrency control governs that overlap: it aims to preserve behavior equivalent to a serial order while allowing as much useful work to proceed as possible. Depending on the database and isolation level, that can mean blocking, detecting a conflict and aborting a transaction, or both.
How individually valid transactions create incorrect results
Consider two transactions that both read an account balance of $100. One adds $20 and writes $120; the other subtracts $10 and writes $90. If both calculate from the original value, the later write can erase the earlier one. The result reflects neither a proper sequence of both changes nor the intended combined balance of $110. This is a lost update.
The problem is not limited to writes. Imagine a read-only transaction calculating a total from two related records while another transaction transfers value between them. If the reader sees one record before the transfer and the other after it, its calculation can combine values from incompatible points in time.
A dirty read is different: a transaction reads a value another transaction has written but not committed. If the writer later aborts, the reader has acted on a value that never became committed data. Whether a system permits this depends on its isolation behavior.
#1 Best Overall
What isolation and serializability mean
Isolation controls how concurrent transactions can observe and affect one another. Stronger isolation generally restricts more interleavings, but databases implement isolation levels and their behavior differently. The label alone does not guarantee that every system uses the same mechanism.
Serializability is a correctness goal: the committed effects of concurrent transactions should be equivalent to some execution in which those transactions ran one at a time. They need not literally run one by one. A database may let them overlap, then block or abort work if the resulting execution cannot be reconciled with any serial order.
PostgreSQL 18 documentation calls Serializable “the strictest transaction isolation” level. In PostgreSQL, a serializable transaction can fail with a serialization error rather than commit an inconsistent result. Applications using that level need to be prepared to retry the entire transaction. PostgreSQL 18: Transaction Isolation
Recommended Free Tools
Rank #2
One important system-specific detail: PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, so READ UNCOMMITTED does not provide a separate, weaker behavior there. Do not assume isolation-level names have identical effects across database products. PostgreSQL 18: SET TRANSACTION
How locking makes conflicting work wait
A database can use locks to coordinate access to data. When one transaction holds a lock that conflicts with another transaction’s operation, the second may have to wait until the first releases it. This prevents some conflicting operations from proceeding simultaneously, but waiting can reduce throughput when many transactions contend for the same resources.
In two-phase locking, a transaction acquires locks during a growing phase and releases them during a shrinking phase; it does not acquire new locks after it begins releasing them. This is a locking discipline used to constrain schedules, not a promise that every database uses one identical locking scheme for every operation.
Why deadlocks happen and how to reduce them
Waiting becomes a deadlock when transactions form a cycle. For example, transaction A holds a lock on record 1 and waits for record 2, while transaction B holds record 2 and waits for record 1. Neither can make progress unless one gives up its locks.
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 →PostgreSQL detects deadlocks and aborts one transaction so the other can continue. Its documentation recommends acquiring multiple objects in a consistent order as the principal way to prevent deadlocks. Applications should still be able to handle a transaction being aborted and run it again when appropriate. PostgreSQL 18: Explicit Locking
Pessimistic locking or optimistic validation?
These are useful conceptual approaches to conflict handling, not universal performance prescriptions. A database may combine mechanisms, and the right tradeoff depends on the workload and the application’s ability to retry.
Rank #4
- HP ProLiant DL360 G7 8B Server
- 2x X5650 2.66GHz 12-Cores Total
- 32GB RAM / 8x 146GB 10K 2.5in SAS Hard Drives
- P410 w/ 512MB
| Question | Pessimistic locking | Optimistic validation |
|---|---|---|
| When is conflict handled? | Before or during work, by coordinating access to contested data. | After work has proceeded, when the system validates whether it can commit. |
| What can contention cost? | Waiting while another transaction holds a conflicting lock. | Discarded work if validation fails and the transaction must be retried. |
| When might the tradeoff fit? | When conflicts are frequent enough that avoiding repeated failed work is valuable. | When conflicts are uncommon and avoiding coordination during work is worthwhile. |
| What must the application support? | Handling delays and possible deadlock or other aborts. | Retrying transactions safely when validation rejects them. |
Serializable isolation can involve both overlap and aborts; it is not a guarantee of free concurrency or zero retries. Conversely, optimistic validation is not inherently faster: if conflicts are common or transactions are expensive to repeat, discarded work can be costly.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What applications should do when a transaction is aborted
An abort is a correctness mechanism, not necessarily a sign that the database is broken. The application should distinguish retryable transaction failures from permanent errors, and retry the complete transaction rather than only the final statement. A partial retry can reuse decisions based on stale reads and fail to preserve the logic of the original transaction.
Quick Recap
- Keep transaction boundaries around the full set of reads and writes that must be consistent.
- For serializable workloads, handle serialization failures with a bounded retry strategy that reruns the whole transaction.
- Acquire multiple locks in a consistent order where the application controls that order.
- Make retry behavior safe for external side effects; do not repeat actions outside the database as if they had automatically rolled back.
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.
