Optimistic Locking with Versioning
What you'll learn: How to prevent conflicts in concurrent systems by tracking version numbers instead of holding locks.
The Core Idea
Optimistic locking assumes conflicts are rare. Instead of locking a row when you read it (pessimistic locking), you let multiple transactions read and work with the data freely. The key: you add a version number or timestamp to each record. When you try to update, you check if the version is still what you originally read. If it changed, someone else modified it first—conflict detected, update rejected.
How It Works
- Read a record along with its version (e.g.,
version = 5) - Modify the data in your application
- Update with a condition: "SET name = 'Alice', version = 6 WHERE id = 123 AND version = 5"
- If zero rows updated → version changed → another transaction won the race → handle conflict
Why It Matters
This approach avoids holding locks during the entire transaction, which improves throughput when conflicts are uncommon. Think of it like editing a shared document: you don't lock the file for an hour while thinking. You save your changes, and if someone else saved first, you get a "conflict" notification.
Common Patterns
- Version column: Integer incremented on each update
- Timestamp: Last-modified datetime (less reliable due to clock skew)
- Conflict resolution: Retry logic, merge strategies, or user notification
Trade-offs
Works best when writes are infrequent relative to reads. In high-contention scenarios with many concurrent updates, you'll face many failed updates and retries—at which point pessimistic locking might be more efficient.
Key Takeaway: Optimistic locking with versioning detects conflicts after-the-fact by comparing version numbers, enabling high concurrency without holding locks, but requires handling rejected updates gracefully.