101
0/252
Loading content...
We've explored the theoretical foundations, advantages, disadvantages, and quantitative behavior of timestamp-based protocols compared to lock-based alternatives. Now comes the most important question: When should you actually use each approach?
This page synthesizes our analysis into actionable guidance. We examine specific use cases, workload archetypes, and industry patterns to help you make informed protocol selection decisions. Rather than abstract principles, we focus on recognizable scenarios you're likely to encounter in practice.
The goal is to equip you with a decision framework—a mental checklist that, given a workload description and requirements, quickly identifies the appropriate concurrency control approach. Master this framework, and you'll make protocol decisions with confidence.
By the end of this page, you will understand specific use cases for timestamp-based protocols, lock-based protocols, and hybrid approaches. You'll have a decision framework for protocol selection, be aware of industry patterns and real-world deployments, and understand how modern database systems combine these techniques.
Timestamp-based protocols shine when their advantages—deadlock freedom, non-blocking operations, distributed suitability—are critical and their disadvantages—restart overhead, high-contention collapse—are minimal.
Why Timestamps Excel:
Example Scenarios:
Real-World Examples:
Why Timestamps Excel:
Example Scenarios:
When transactional workloads have naturally low contention, timestamps provide efficiency:
Characteristics:
Why It Works:
Example Scenarios:
If your data naturally partitions such that transactions rarely cross partition boundaries (e.g., per-user, per-tenant, per-region), timestamp protocols are likely excellent choices. The natural isolation reduces contention to near zero.
Lock-based protocols are preferable when their advantages—work preservation, graceful degradation under contention—outweigh their disadvantages—blocking, deadlock risk.
Why Locks Excel:
Characteristics:
Example Scenarios:
Why Locks Excel:
Example Scenarios:
When specific data items are known to be frequently accessed, locks provide orderly access:
Characteristics:
Why Locks Excel:
Example Scenarios:
When transaction work is expensive (external API calls, complex computations, I/O operations), the ability to preserve work during conflict resolution becomes critical. Lock-based waiting preserves work; timestamp-based restart discards it.
In practice, most production systems don't use pure timestamp ordering or pure locking. They employ hybrid approaches that combine the best of both paradigms.
Description:
Deployed By:
Benefits:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263
// HYBRID PROTOCOL: Optimistic with Pessimistic Fallback// ===================================================== function executeTransaction(Transaction txn): retryCount = 0; maxOptimisticRetries = 3; while true: if retryCount < maxOptimisticRetries: // Try optimistic (timestamp-based) first result = tryOptimistic(txn); if result.success: return SUCCESS; else: retryCount++; log("Optimistic attempt failed, retry ", retryCount); else: // Too many optimistic failures, switch to pessimistic log("Switching to pessimistic (lock-based) mode"); result = executePessimistic(txn); return result; function tryOptimistic(Transaction txn): // Timestamp-based execution txn.readSet = []; txn.writeSet = []; for each operation in txn.operations: if operation.isRead: value = readWithTimestamp(operation.item, txn.timestamp); txn.readSet.add(operation.item, value.timestamp); else: txn.writeSet.add(operation.item, operation.value); // Validation phase for each item in txn.readSet: if item.currentTimestamp > txn.startTimestamp: return ABORT; // Item was modified since we read it // Commit phase for each item in txn.writeSet: writeWithTimestamp(item, txn.commitTimestamp); return SUCCESS; function executePessimistic(Transaction txn): // Lock-based execution - guaranteed completion acquiredLocks = []; try: // Acquire all locks upfront (conservative 2PL) for each item in txn.requiredItems: acquire_lock(item); // May block acquiredLocks.add(item); // Execute with locks held for each operation in txn.operations: execute(operation); commit(); return SUCCESS; finally: release_all(acquiredLocks);Description:
Implementation:
Benefits:
Description:
Benefits:
Pure protocols are for textbooks; hybrids are for production. Don't think "timestamp OR lock"—think "how do I combine their strengths?" The best database engineers design custom hybrids tuned for their specific workloads.
Let's examine how major database systems and industry sectors approach protocol selection.
| Database | Primary Approach | Details |
|---|---|---|
| PostgreSQL | MVCC + SSI | Timestamp-based snapshots; serializable via conflicts tracking |
| MySQL InnoDB | MVCC + Locking | Undo logs for snapshots; next-key locking for ranges |
| Oracle | MVCC + Locking | Read consistency via undo; row-level locking for writes |
| SQL Server | Lock + Optional MVCC | Traditional locking; SNAPSHOT isolation adds MVCC |
| SQLite | Locking | Database-level locking (single writer); WAL adds concurrency |
| Database | Primary Approach | Details |
|---|---|---|
| Google Spanner | TrueTime + 2PL | Timestamp ordering; wound-wait for writer conflicts |
| CockroachDB | HLC + OCC | Hybrid logical clocks; optimistic concurrency; read refresh |
| TiDB | Percolator | Snapshot isolation via 2PC with timestamp ordering |
| YugabyteDB | Hybrid Clock + MVCC | DocDB storage; YSQL uses PostgreSQL's MVCC semantics |
| VoltDB | Deterministic Ordering | Single-threaded partition execution; timestamp ordering across partitions |
Financial Services:
E-Commerce:
Social Media:
Gaming:
IoT/Telemetry:
Notice that nearly all modern major databases use some form of MVCC—timestamp-based snapshots for reads. The differentiation is in how writes are handled (locks vs optimistic) and how serialization conflicts are resolved. MVCC is the industry-standard foundation.
Let's synthesize everything into a practical decision framework. This framework helps you navigate from workload characteristics to protocol choice.
| If Your Workload Has... | Choose... | Because... |
|---|---|---|
| Low contention, short transactions | Timestamp/MVCC | Minimal restart risk, avoids lock overhead |
| High contention on specific items | Lock (for hot items) | Orderly access prevents collapse |
| Long-running transactions | Lock | Work preservation critical |
| Distributed across regions | Timestamp | Avoids distributed lock coordination |
| Mixed read/write, unpredictable | MVCC + Write Locks | Best of both worlds |
| Read-heavy analytics | MVCC/Timestamp | Snapshot isolation, no read blocking |
| Real-time requirements | Consider Timestamp | Non-blocking preferred |
| Strong durability/audit needs | Either + careful design | Focus on recovery, not just concurrency |
When characteristics are mixed or unclear, start with MVCC + locks (the most common hybrid). Measure actual contention and rollback rates. Adjust based on observed behavior rather than predictions. Most modern databases offer this hybrid as their default mode.
Understanding what not to do is as important as knowing best practices. Here are common anti-patterns in protocol selection and application.
The Problem:
The Solution:
The Problem:
The Solution:
The Problem:
The Solution:
Many performance issues blamed on protocol choice are actually data model problems, inefficient queries, or missing indexes. Ensure you're solving the right problem before protocol-level changes.
Concurrency control continues to evolve. Understanding emerging trends helps anticipate future capabilities and make forward-compatible decisions.
Description:
Examples:
Implications:
Description:
Status:
Description:
Implications:
Challenges:
Approaches:
Examples:
Despite advances, the fundamental trade-offs remain. No technology eliminates the CAP theorem implications or the conflict between concurrency and consistency. New systems provide new points on the trade-off curve, but the curve itself persists.
We've completed a comprehensive examination of timestamp-based versus lock-based concurrency control. Let's consolidate the key learnings from this entire module.
After this module, you should think about concurrency control as a spectrum of trade-offs, not a choice between "good" and "bad" approaches:
Timestamp protocols: Non-blocking, deadlock-free, distributed-friendly, but sensitive to contention and expensive restarts.
Lock protocols: Work-preserving, graceful under contention, but blocking, deadlock-prone, and coordination-heavy.
Hybrid approaches: Real-world systems combine elements of both, tuned for specific workload characteristics.
The key skill: Matching protocol characteristics to workload requirements through analysis, measurement, and iterative optimization.
| Choose Timestamp-Based When... | Choose Lock-Based When... |
|---|---|
| Distributed system architecture | Single-node or tightly coupled |
| Low contention workload | High contention workload |
| Short transactions | Long transactions |
| Read-heavy (OLAP) | Write-heavy (OLTP with hot spots) |
| Deadlock avoidance critical | Work preservation critical |
| Restart cost is low | Restart cost is high |
Congratulations! You now possess comprehensive, expert-level understanding of timestamp-based versus lock-based concurrency control. You can analyze workloads, predict protocol behavior, design hybrid systems, and make informed architectural decisions. This knowledge is foundational for understanding MVCC and advanced isolation levels in the modules that follow.