📖 Business
Offline Concurrency Patterns
Offline concurrency addresses a problem that database transactions alone cannot solve: what happens when a business transaction spans multiple system transactions — typically when a user reads data, thinks about it, and then submits changes minutes or hours later? During that gap, another user may have modified the same data, creating a conflict. Fowler presents two primary patterns for handling this: Optimistic Offline Lock (detect conflicts at commit time and force the user to resolve them) and Pessimistic Offline Lock (prevent conflicts by locking data when it's first read). Each pattern makes a different trade-off between concurrency and conflict handling, and choosing between them depends on how frequently conflicts occur and how costly they are to resolve.
2
Minutes
2
Concepts
+45
XP
1
How It Works
- Optimistic Offline Lock — Allows multiple users to read and work with the same data concurrently. Conflicts are detected only at write time by checking whether the data has changed since it was read. Implementation typically uses a version number or timestamp column:
sql
-- Read: grab the version
SELECT , version FROM orders WHERE id = 42; -- version = 7
-- Write: update only if version matches
UPDATE orders SET status = 'shipped', version = 8
WHERE id = 42 AND version = 7;
-- If 0 rows affected → someone else modified it → conflict!
When a conflict is detected, the application must present the user with a merge/retry UI. Works well when conflicts are rare.
- Pessimistic Offline Lock — Prevents conflicts by acquiring a lock when data is first read, preventing others from editing until the lock is released. This guarantees no conflicts but reduces concurrency — other users must wait or are blocked from editing.
- Write lock — only one user can edit at a time; others can read
- Read lock — prevents any modification while data is being reviewed
- Exclusive lock — prevents both read and write by others
Requires a lock manager (often a database table tracking who holds locks on which records) and a timeout mechanism to handle abandoned locks.
- Coarse-Grained Lock — Instead of locking individual rows, lock an entire aggregate (an order with all its line items) as a unit. This simplifies the locking model but reduces concurrency further. The trade-off is simpler conflict resolution at the cost of more blocking.
- Choosing between optimistic and pessimistic — Decision factors:
| Factor | Optimistic | Pessimistic | |
|---|---|---|---|
| --- | --- | --- | |
| Conflict frequency | Low (conflicts rare) | High (conflicts common) | |
| Conflict cost | Low (easy to retry) | High (expensive to lose work) | |
| User experience | Better — no waiting | Worse — may be blocked | |
| Implementation complexity | Lower | Higher (lock management) | |
| Risk | Lost work on conflict | Reduced concurrency, lock starvation |
- Implicit vs. explicit locking — Implicit locking (built into the framework) reduces developer error but can lock too broadly. Explicit locking gives control but risks forgetting to acquire or release locks. Fowler recommends explicit locking at the application layer with clear ownership semantics.