Serializability, isolation, locking, deadlocks, and optimistic concurrency reveal why correct transactions still need coordination.
"How database concurrency control prevents lost updates, dirty reads, inconsistent results, and deadlocks using isolation, serializability, locking, two-phase locking, and optimistic concurrency."
Suppose a bank account has a balance of: $200
Transaction T applies 10% interest.
Transaction U also performs a valid update involving that account.
Run T by itself.
Correct.
Reset the database.
Run U by itself.
Also correct.
So running them at the same time should make the system ✨faster✨
That feels reasonable because concurrency is usually sold to us as a performance feature.
More work happening simultaneously.
Better utilization.
More clients served.
Less unnecessary waiting.
The database already knows how to perform every individual operation correctly, so perhaps concurrency is just several correct operations happening together.
That last sentence is the trap.
Two correct transactions do not automatically create one correct execution.
Because once transactions overlap, correctness depends not only on what each transaction does.
It depends on the order in which their operations become visible to each other.
Serial Execution Would Solve Everything
There is a brutally simple way to avoid the problem.
Run one transaction.
Finish it.
Then run the next: T --> U --> V --> W
No overlap.
No interference.
No mystery.
If each transaction takes the database from one valid state to another, serial execution preserves that reasoning beautifully.
Transactions are supposed to provide the familiar ACID properties:
- Atomicity - all of the transaction happens or none of it does.
- Consistency - the transaction preserves the database’s invariants.
- Isolation - concurrent transactions should not interfere in ways that expose invalid intermediate behaviour.
- Durability - once committed, the effects persist.
Oracle describes the same essential contract: transactions are atomic units of work, and isolation prevents simultaneously executing transactions from exposing one another’s incomplete changes.
So why not simply serialize everything?
Because throwing away concurrency to preserve isolation is generally an unacceptable bargain.
If Transaction T is working with Account A while Transaction U is working with an unrelated Account Z, forcing U to sit idle achieves correctness by refusing useful parallelism.
We need something better.
We want transactions to overlap when that overlap is harmless.
And that means we first have to understand what harmful overlap looks like.
The Lost Update Looks Perfectly Innocent
Consider two transactions reading the same balance.
Initially: balance = $200
Transaction T reads: T: balance = 200
Before T writes its result, Transaction U also reads: U: balance = 200
T calculates a new value: 200 x 1.1 = 220 and writes: balance = 220
Then U, still working from the value it read earlier, performs its own calculation: 200 x 1.1 = 220 and writes: balance = 220
Both transactions ran valid arithmetic.
Neither crashed.
Neither wrote malformed data.
But one update effectively disappeared.
If the two operations were intended to accumulate, the second transaction should have seen the first transaction’s new value.
Instead, both trusted the same old state.
This is the lost update problem.
The database did not lose the update because storage failed.
It lost it because concurrency allowed this sequence:
- T reads old value
- U reads old value
- T writes
- U writes based on old value
The problem is not the transactions.
It is the interleaving.
Reads Can Be Wrong Without Returning Invalid Data
Writes are not the only concern.
Suppose Transaction V transfers $100 from Account A to Account B:
- A.withdraw(100)
- B.deposit(100)
The bank has not gained or lost money.
The total should remain unchanged.
Now Transaction W calculates the branch total.
The timing goes like this:
- V withdraws $100 from A
- W reads A
- W reads B
- V deposits $100 into B
Depending on the exact interleaving, W may observe the withdrawal but not yet observe the matching deposit.
Every value W reads exists.
Nothing is corrupted.
Yet W can calculate a branch total that was never the correct total before or after the transfer.
This is the inconsistent retrieval problem.
The uncomfortable part is that W may be read-only.
It never modifies anything.
It can still produce a false view of the database because it observed different objects at incompatible moments in another transaction’s lifecycle.
Concurrency has introduced a version of reality that existed only because we looked halfway through someone else’s work.
Atomicity Is Not Enough
At this point, it is tempting to say:
Fine. Transactions are atomic. Just commit everything together.
But atomicity solves a different problem.
Atomicity prevents a transaction from permanently leaving half of its own work behind.
It does not automatically stop another transaction from observing or interfering with that work while both are executing concurrently.
That is what isolation is for.
The desired behaviour is stronger:
Even if transactions execute concurrently, their combined effect should look as though they executed one at a time in some order.
That property is serial equivalence , or serializability.
PostgreSQL describes serializable isolation in almost exactly these terms: committed concurrent transactions should behave as though they had been executed one after another in some serial ordering.
Notice what we are not demanding.
We are not saying transactions must literally run serially.
That would destroy useful concurrency.
We are saying:
Concurrency is allowed as long as nobody can tell, from the final behaviour, that an unsafe interleaving occurred.
That is a much more interesting constraint.
Not Every Overlap Is Dangerous
Suppose Transaction T reads Account A.
Transaction U reads Account A.
No problem.
The order does not matter.
- read T
- read U
and:
- read U
- read T
produce the same effect.
Now suppose T reads A while U writes A.
Order matters.
If T runs first, it sees the old value.
If U runs first, T sees the new value.
That is a conflict.
Likewise, two writes conflict because their order determines which state survives.
So the useful conflict rules are pleasantly small:
- read + read → no conflict
- read + write → conflict
- write + read → conflict
- write + write → conflict
This gives concurrency control something concrete to protect.
We do not need to stop every pair of operations from overlapping.
We need to control conflicting operations so their ordering remains compatible with some serial execution.
That distinction is where concurrency becomes practical.
The database can be permissive where order does not matter and suspicious where it does.
Then One Transaction Reads Something That Never Really Existed
There is another failure mode, and this one gets meaner.
Transaction T changes Account A from $100 to $110
but has not committed.
Transaction U reads the $110.
Then U changes it again: $130
and commits.
Now Transaction T aborts.
Its $110 was tentative.
It was never supposed to become part of permanent database history.
But U already based a committed result on it.
U has performed a dirty read.
PostgreSQL defines a dirty read precisely as one transaction reading data written by a concurrent transaction that has not yet committed.
Now recovery gets awkward.
If T’s state never officially existed, what does that make U’s committed result?
U depended on something that vanished.
So perhaps U must also abort.
And if Transaction V already read U’s changes?
V may have to abort too.
Then another transaction that depended on V.
One aborted transaction can pull a chain of dependants down behind it.
These are cascading aborts.
The database has discovered that temporary state is contagious.
Strict Execution Stops Tentative State From Escaping
The obvious defence is to prevent other transactions from observing values that are not yet trustworthy.
If Transaction T writes an object, conflicting reads and writes by other transactions can be delayed until T either: COMMIT or ABORT
Now tentative changes remain private.
If T commits, the new state becomes legitimate.
If T aborts, nobody else has built committed work on top of it.
This gives us strict execution.
It protects isolation and makes recovery much cleaner.
But there is an unavoidable consequence hidden inside the word “delayed.”
If another transaction wants the object while T is still using it…
it has to wait.
And now we need something capable of saying:
T currently owns access to this object.
Enter the lock.
The Lock Turns a Conflict Into Waiting
Suppose Transaction T wants to update Account B.
It acquires a write lock on B.
Transaction U then tries to update B.
The operations conflict.
So U does not proceed.
It waits.
- T: lock(B)
- T: write B
- U: wants lock(B)
- U: wait…
When T commits or aborts, its lock is released.
Then U can proceed.
Locks make the ownership boundary explicit.
A read lock can be shared with other compatible reads.
A write lock conflicts with other reads or writes that require incompatible access.
Real database systems use the same basic concept: incompatible lock modes cannot be held by separate transactions simultaneously, so one transaction waits until the conflicting holder finishes.
The lock is not protecting the object from being used.
It is protecting the ordering of conflicting operations.
That is why compatible reads can coexist while conflicting writes wait.
The goal is still serial equivalence.
The lock is merely how we enforce it.
Two-Phase Locking Makes the Ordering Stick
There is a subtle problem if transactions can acquire and release locks whenever they feel like it.
Suppose T locks A.
Touches A.
Unlocks A.
Then later locks B.
Meanwhile U slips into A between those operations.
It becomes possible to create conflicting orderings that cannot correspond to one consistent serial order.
So two-phase locking imposes a rule:
Once a transaction starts releasing locks, it may not acquire new ones.
Conceptually there is a *growing * phase:
- acquire
- acquire
- acquire
Followed by a shrinking phase:
- release
- release
- release
For strict two-phase locking , the important conflicting locks are retained until the transaction commits or aborts.
That is why another transaction cannot observe tentative state and why conflicting operations remain ordered consistently.
Oracle’s database documentation likewise describes locks as a mechanism for maintaining concurrency and integrity, with transaction locks released when the transaction commits or rolls back.
Two-phase locking now feels less like an arbitrary rule.
The transaction holds onto ownership because releasing that ownership too early would let another transaction enter the middle of an ordering we are still trying to make appear serial.
So we solved the concurrency problem.
Which is usually when The Origami Software Engineer receives the invoice from distributed and concurrent systems.
Waiting Creates a New State: Nobody Moves
Transaction T acquires a write lock on Account A.
Transaction U acquires a write lock on Account B.
Then T needs B.
- T holds A
- T waits for B
But B belongs to U.
Meanwhile U needs A.
- U holds B
- U waits for A
Now:
- T waits for U
- U waits for T
Neither can continue.
Neither can release what the other needs because each transaction is waiting before it reaches the point where it can finish.
This is a deadlock.
And there is something beautifully annoying about it.
Every participant is behaving correctly.
T is correctly respecting U’s lock.
U is correctly respecting T’s lock.
The locking system is correctly preventing conflicting access.
The database has become stuck because everybody followed the rules.
PostgreSQL gives essentially this exact pattern: one transaction holds A and waits for B while another holds B and waits for A; the database detects the resulting deadlock and aborts one transaction so the other can proceed.
The mechanism that protected correctness created a cycle of perfectly correct waiting.
A Deadlock Is a Circle of Dependency
The situation becomes clearer as a wait-for graph.
If Transaction T waits for U: T --> U
If U waits for V: U --> V
If V waits for T: V --> T
We have:
T → U → V
↑ ↓
└───────┘
A cycle.
Nobody inside the cycle can make progress because each transaction is waiting for an event that another transaction in the same cycle must cause.
The general deadlock model identifies four conditions that allow this state:
- Mutual exclusion - Some resource can be held exclusively.
- Hold and wait - A process may hold resources while requesting more.
- No preemption - Resources are not simply ripped away whenever convenient.
- Circular wait - A cycle of dependencies exists.
Locks naturally create several of these conditions because that is precisely how they protect data.
So deadlock prevention is not free.
To prevent deadlock absolutely, we must attack at least one condition.
And every condition exists for a reason.
We Can Avoid the Circle, but Concurrency Pays
One approach is to require every transaction to acquire all required locks at the beginning.
No gradual acquisition.
No hold-and-wait cycle later.
Except transactions do not always know in advance which objects they will need.
And even when they do, locking everything early can keep other transactions away from resources long before they are actually used.
Correct.
Safe.
Also rather rude to concurrency.
Another approach is to impose a global lock order.
For example: A before B before C
Every transaction must acquire locks in that order.
Now T cannot choose: A --> B while U chooses: B --> A
The circular wait disappears.
PostgreSQL similarly recommends acquiring locks on multiple objects in a consistent order as a primary defence against deadlocks.
But again, the ordering constraint can cause premature locking or complicate transaction logic.
The database keeps discovering the same pattern:
Stronger guarantees narrow the freedom of concurrent execution.
Safety is buying structure.
Or Detect the Deadlock After We Create It
Instead of preventing every possible deadlock, we can allow them to occur and detect them.
Build the wait-for relationships.
Find a cycle.
Choose a victim.
Abort one transaction.
Release its locks.
The remaining transaction can proceed.
This feels destructive, but it is often much cheaper than restricting every transaction in advance to prevent a rare problem.
Another approach uses lock timeouts.
A lock remains protected for some period.
If another transaction is waiting and that period expires, a lock can be broken and its owning transaction aborted.
Simple.
Except timeout is a guess.
A transaction might merely be slow rather than deadlocked.
An overloaded system makes legitimate operations slower.
Long transactions become more vulnerable.
Choose a timeout too short and healthy work gets killed.
Choose it too long and actual deadlocks sit around doing nothing.
The timeout did not determine whether a deadlock exists.
It merely decided how long our patience lasts.
Optimism Tries the Opposite Bargain
Locks assume conflict is important enough to prevent in advance.
There is another possibility.
What if conflicts are rare?
Then perhaps forcing transactions to wait “just in case” is unnecessary.
Optimistic concurrency control starts from that assumption.
Let transactions proceed without locking each other out.
No waiting.
Therefore no lock-based deadlock.
At closeTransaction , validate whether the transaction actually conflicted with concurrent work.
If not: commit
If conflict occurred: abort
The transaction did useful work optimistically and only discovers at the end whether that optimism was justified.
This simply moves the cost.
Pessimistic locking says:
Conflict might happen, so coordinate before proceeding.
Optimistic concurrency says:
Conflict probably will not happen, so proceed and validate later.
If contention is low, optimism can avoid unnecessary waiting.
If contention is high, repeatedly doing work only to abort it later becomes expensive.
Neither strategy has escaped the underlying problem.
They choose when to pay for conflict.
Concurrency Was Never Just “Run Both”
This is the part worth keeping.
We started with two transactions that were individually correct.
Nothing about their business logic was wrong.
Then they overlapped.
A lost update appeared.
A read observed an inconsistent view.
An uncommitted value escaped and created a dirty read.
Preventing that required a stronger model:
Concurrent execution must have the same effect as some valid serial execution.
That gave us serial equivalence.
Serial equivalence made conflicting operations important.
Locks controlled those conflicts.
Holding locks until commit protected tentative state and prevented cascading problems.
But locks converted conflict into waiting.
Waiting created dependency.
Dependency formed cycles.
Cycles created deadlocks.
So the system learned to prevent, detect, break, or sometimes avoid those cycles altogether.
Or it became optimistic and moved the conflict check to the end.
Every mechanism follows from the constraint before it.
That is why database concurrency control is easier to understand when we stop memorizing anomalies and isolation rules as a collection of unrelated warnings.
They are one continuous problem.
We want the performance of: run T and U together
with the correctness of: run T then U
without necessarily paying the full cost of either extreme.
PostgreSQL describes the goal of concurrency control in almost exactly this tension: Allow efficient simultaneous access while maintaining strict data integrity.
That middle ground is where all the machinery lives.
The transactions were correct.
The database became wrong only when their operations were allowed to observe one another in an order that no correct serial history could explain.
So we added isolation.
Then locks.
Then waiting.
Then deadlocks.
Then increasingly careful ways of deciding when coordination was actually worth paying for.
That’s not failure.
That’s evolution.
The “I liked this” Starter Pack:
Don’t let your fingers get lazy now.
- Like : It tells me this was worth writing.
- A Comment: Tell me your thoughts, your favorite snack, or a better title for this blog.
- Boost it: Especially with that one developer who definitely needs this.
Thanks for being here. It genuinely helps more than you know!
— Aaroophan Varatharajan
Find me elsewhere:
- Professional stuff: linkedin.com/in/Aaroophan
- Code stuff: github.com/Aaroophan
- UI stuff: aaroophan.dev/Aaroophan
- Life stuff: instagram.com/Aaroophan
Top comments (0)