"Concurrency bugs aren't caused by fast systems - they're caused by systems that assume they're the only ones running."
Key Takeaways
- Learn why concurrent database updates cause data corruption.
- Understand race conditions with simple real-world examples.
- Learn the difference between Optimistic and Pessimistic Locking.
- Know when each locking strategy should be used.
- Implement both techniques in Laravel.
- Compare MySQL and PostgreSQL behavior.
- Avoid deadlocks and lost updates.
- Learn production best practices.
Index
- Why Database Conflicts Happen
- Understanding Race Conditions
- What is Database Locking?
- Types of Database Locking
- Optimistic Locking
- How Optimistic Locking Works
- Optimistic Locking Example
- Laravel Implementation
- Pessimistic Locking
- How Pessimistic Locking Works
- Laravel Implementation
- Shared Lock vs Exclusive Lock
- Database Transaction Isolation Levels
- Deadlocks
- Performance Comparison
- Real World Examples
- Which One Should You Choose?
- Common Mistakes
- Interesting Facts
- FAQs
- Conclusion
Why Database Conflicts Happen
The Problem
Imagine your bank account contains
Balance = $1000
Two users try to withdraw money simultaneously.
User A Withdraws $200
User B Withdraws $300
Both requests arrive at exactly the same time.
Both read
Balance = $1000
User A writes
800
User B writes
700
Final balance
700
Expected
500
Money magically appeared.
This is called a Race Condition.
Another Example
Suppose an e-commerce website has
1 iPhone left
Two customers click
Buy Now
at exactly the same moment.
Without locking
Customer A purchases.
Customer B also purchases.
Now inventory becomes
-1
Why Does This Happen?
A database operation usually follows
Read Data
↓
Process Data
↓
Update Data
If two users perform this sequence simultaneously,
both read the same old value.
Understanding Race Conditions
Time →
User A
Read Balance =1000
------------------------
User B
Read Balance =1000
------------------------
User A
Write 800
------------------------
User B
Write 700
User A's update is lost.
This is called Lost Update Problem
What is Database Locking?
Database locking is a mechanism that prevents multiple transactions from modifying the same data in conflicting ways.
Think of it like a meeting room.
If someone is inside,
others must wait,
or check whether the room changed before entering.
Types of Database Locking
"A transaction protects a unit of work; a locking strategy protects the integrity of shared data."
Two major strategies exist.
Optimistic Locking
Pessimistic Locking
Optimistic Locking
Optimistic locking assumes Conflicts are rare.
Instead of locking rows,
everyone can read and modify.
Before saving,
the application checks
"Has someone already modified this record?"
If yes
Update fails.
Real Life Example
Imagine editing a Google Doc.
Two people open the same document.
You save first.
When the second user saves,
Google says
This document has changed.
Please reload.
That's optimistic locking.
How Optimistic Locking Works
Usually via
version or updated_at
Example table
User A reads
Version =5
plaintext
User B reads
Version =5
plaintext
User A updates
WHERE version =5
plaintext
Database changes
version=6
plaintext
User B tries
WHERE version=5
No rows affected.
Conflict detected.
Optimistic Locking Example
UPDATE products
SET stock = 5,
version = version +1
WHERE
id=1
AND version=5;
plaintext
If
Affected Rows =0
plaintext
Someone modified it.
Laravel Implementation
$product = Product::find(1);
$currentVersion = $product->version;
$updated = Product::where('id', 1)
->where('version', $currentVersion)
->update([
'stock' => 5,
'version' => $currentVersion + 1,
]);
if (! $updated) {
throw new Exception('Record has been modified by another user.');
}
plaintext
Advantages
- Very fast
- No waiting
- Excellent scalability
- High throughput
Disadvantages
- Update may fail
- User may retry
- More application logic
Pessimistic Locking
Pessimistic locking assumes Conflicts are likely.
Before updating,
the database locks the row.
Nobody else may modify it.
Example
ATM withdraw
Balance =1000
plaintext
User A begins transaction
Database locks row.
User B tries
Wait...
User A commits.
Only then
User B proceeds.
Timeline
`
User A
Lock Row
↓
Update
↓
Commit
↓
Unlock
User B
Wait...
↓
Lock
↓
Update
↓
Commit
`plaintext
How Pessimistic Locking Works
BEGIN;
SELECT *
FROM accounts
WHERE id=1
FOR UPDATE;
UPDATE accounts
SET balance = balance -200;
COMMIT;
plaintext
Laravel Implementation
DB::transaction(function () {
$account = Account::where('id', 1)
->lockForUpdate()
->first();
$account->balance -= 200;
$account->save();
});
plaintext
Laravel automatically generates
SELECT ...
FOR UPDATE
plaintext
Shared Lock vs Exclusive Lock
Shared Lock
Allows
Read
Read
Read
plaintext
But
No updates.
Laravel
DB::table('users')
->sharedLock()
->get();
sql
SQL
`
SELECT *
FROM users
LOCK IN SHARE MODE;
`sql
(MySQL) or FOR SHARE (PostgreSQL).
Exclusive Lock
`
Read ❌
Write ❌
Delete ❌
`plaintext
Only the locking transaction proceeds.
Generated by
`
FOR UPDATE
`plaintext
Database Transaction Isolation Levels
An important concept that interacts with locking is transaction isolation. Different isolation levels control what concurrent transactions can see and how they interact.
Behavior depends on the database engine. For example, MySQL's InnoDB uses next-key locking to reduce phantom reads under **REPEATABLE READ.*
Include a brief explanation of:
- Dirty Read: Reading uncommitted data.
- Non-repeatable Read: Same row returns different values within one transaction.
- Phantom Read: A repeated query returns additional or missing rows due to inserts/deletes by another transaction.
Deadlocks
A deadlock occurs when two transactions wait on each other indefinitely.
Example:
`
Transaction A
Lock Order
↓
Wait Product
Transaction B
Lock Product
↓
Wait Order
`php
Neither can continue.
Most modern databases detect deadlocks automatically and roll back one transaction.
How to reduce deadlocks:
- Always lock resources in the same order.
- Keep transactions short.
- Avoid unnecessary locks.
- Commit or roll back promptly.
Laravel Retry Example
Laravel's transaction helper can retry automatically when a deadlock occurs:
DB::transaction(function () {
// Critical database operations
}, 5); // Retry up to 5 times
Performance Comparison
"Optimistic locking trusts that conflicts are rare. Pessimistic locking prepares for them before they happen. Great engineers know when to choose each."
Real World Examples
Which One Should You Choose?
Choose Optimistic Locking when:
- Reads greatly outnumber writes.
- Conflicts are uncommon.
- Scalability is a priority.
- Users can retry failed updates.
Choose Pessimistic Locking when:
- Every update must succeed in sequence.
- Data integrity is critical.
- Concurrent updates are common.
- Temporary blocking is acceptable.
Some systems combine both approaches - for example, optimistic locking for general edits and pessimistic locking for payment processing.
Common Mistakes
- Assuming transactions automatically prevent lost updates.
- Holding transactions open while calling external APIs.
- Forgetting to handle optimistic lock failures.
- Locking more rows than necessary.
- Ignoring deadlock exceptions.
- Using pessimistic locks in high-traffic read-heavy workloads without measuring the impact.
Interesting Facts
- Amazon, Uber and Stripe use optimistic locking in many high-read systems.
- Banking systems often rely on pessimistic locking. source
- Most developers encounter concurrency bugs only after production deployment.
- Locking is one of the hardest backend topics because bugs are often random and difficult to reproduce.
- Transactions alone do not always prevent lost updates. source
FAQs
Can transactions replace locking?
No. Transactions define the boundaries of a unit of work, but depending on the isolation level and the operations performed, they may not prevent concurrent update conflicts.
Does Laravel support optimistic locking out of the box?
Laravel provides lockForUpdate() and sharedLock() for pessimistic locking. Optimistic locking is not built into Eloquent, but it is straightforward to implement using a version column or timestamp check.
Does updated_at work for optimistic locking?
Yes, but a dedicated integer version column is generally more reliable because timestamps can have precision and synchronization limitations.
Is pessimistic locking slower?
It can reduce throughput because other transactions may have to wait for locks to be released.
Can deadlocks still happen?
Yes. Even with proper locking, deadlocks are possible, so applications should handle deadlock exceptions and retry when appropriate.
Conclusion
Concurrency issues are often invisible during development because they require multiple requests to collide at just the right moment. However, in production systems with many users, these situations become inevitable.
Optimistic locking focuses on performance by detecting conflicts only when updates occur, making it ideal for read-heavy applications. Pessimistic locking prioritizes consistency by preventing conflicting updates before they happen, making it suitable for critical financial or inventory operations.
Understanding both strategies - and knowing when to use each - is a fundamental backend engineering skill. Combined with well-designed transactions, appropriate isolation levels, and careful error handling, they help build applications that remain reliable under real-world load.
About the Author:Vatsal is a web developer at AddWebSolution. Building web magic with Laravel, PHP, MySQL, Vue.js & more.
Top comments (0)