Great question, Stanley! Reducing locking contention is crucial for improving performance and concurrency in a database — especially in high-traffic systems.
🔒 What Is Locking Contention?
Locking contention happens when multiple transactions compete for the same locks, and they block or wait on each other — causing slowdowns or deadlocks.
Example:
- Two users try to update the same row at the same time → one must wait.
✅ How to Reduce Locking Contention (Best Strategies)
1. Keep Transactions Short and Fast
🔧 The longer a transaction runs, the longer it holds locks.
- Do as little work as possible inside a transaction
- Avoid long-running queries or user input during a transaction
BEGIN;
-- ✅ Do the update quickly
UPDATE users SET balance = balance - 10 WHERE id = 1;
COMMIT;
2. Access Data in a Consistent Order
🔄 Always access tables/rows in the same order to avoid deadlocks.
If transaction A locks rows in order (1 → 2) and B locks in (2 → 1), they can deadlock.
✅ Always access data in the same sequence.
3. Use Optimistic Locking When Possible
🧠 Avoid locking until you’re ready to commit — useful when conflicts are rare.
Example (in PostgreSQL or with ORMs):
UPDATE users
SET balance = 500, version = version + 1
WHERE id = 1 AND version = 3;
✅ Fails safely if someone else changed the row — no hard locks needed
4. Use Row-Level Locks Instead of Table Locks
Prefer finer-grained locking.
✅ Good:
UPDATE users SET balance = balance - 100 WHERE id = 42;
❌ Bad:
LOCK TABLE users IN EXCLUSIVE MODE;Table-level locks block everyone else
Row-level locks let others work on other rows freely
5. Use Appropriate Isolation Levels
Higher isolation levels = stronger safety, but more locking
| Level | Locks Used | Contention | Use For |
|---|---|---|---|
| READ COMMITTED | Minimal | ✅ Low | Most OLTP workloads |
| REPEATABLE READ | More | ⚠️ Higher | When needed for accuracy |
| SERIALIZABLE | Most | ❌ High | Only when truly required |
✅ Use the lowest isolation level that still guarantees correctness.
6. Batch Writes Smartly
Instead of 1 big transaction for 1000 rows, break it into 10 smaller ones.
✅ Less lock time per batch
✅ Reduces wait time for other queries
7. Use Indexes Efficiently
When updating or deleting rows, good indexes help you find and lock only what’s needed.
❌ Slow scan: locks many rows
✅ Fast lookup: locks just one or few
8. Use Lock Timeouts or Deadlock Detection
In PostgreSQL:
SET lock_timeout = '1s';
In MySQL:
- Use
innodb_lock_wait_timeout = 5
✅ Prevents long waits from unresolved locks
🧠 Summary Table
| Technique | How It Helps |
|---|---|
| Short transactions | Less time holding locks |
| Consistent access order | Avoids deadlocks |
| Optimistic locking | Avoids locks entirely unless needed |
| Row-level locking | Reduces conflict scope |
| Lower isolation levels | Fewer locks |
| Batch processing | Less contention per transaction |
| Use of indexes | Locks only necessary rows |
| Lock timeouts | Prevents long blocking waits |