Ever been stuck in a situation where two people try to grab the same seat at the same time? That little chaos is exactly what databases try to avoid when multiple transactions happen at once.
In today’s fast-paced apps — whether it’s an online store, a banking system, or a food delivery app — hundreds or even thousands of actions are hitting the database every second. Managing this chaos without messing up your data is what makes or breaks a good system.
If you’re using TiDB, a distributed database built for high concurrency and scale, you’ve probably heard about Optimistic and Pessimistic Locking. These two approaches are like different game plans to make sure data stays clean and reliable, even when everyone’s rushing in at once.
In this post, we’ll break down how these two locking strategies work in TiDB, where each one shines, and how you can pick the right strategy for your applications, without getting lost in technical jargon.
Managing chaos in the database starts with understanding how transactions stay consistent. If you’re new to the concept, check out our blog post Transaction Isolation Levels in TiDB to get a solid grounding before diving into locking strategies.
Pessimistic Locking in TiDB
Think of pessimistic locking like this: you're booking a seat for a concert. The moment you pick a seat, it's blocked for everyone else until you either confirm your booking or leave the page.
Pessimistic locking works the same way. TiDB assumes that conflicts will probably happen (especially when many people are fighting over the same data), so it locks the data early — right when a transaction starts. No one else can touch it until you’re done.
.png)
Since TiDB 3.0.8, pessimistic locking is the default mode. It’s a safe choice, especially when your workload is crowded and busy.
How Pessimistic Locking Works in TiDB
- As soon as a transaction begins, it grabs locks on the rows it wants to modify.
- Other transactions have to wait their turn.
- The locks stay in place until you either commit or roll back the transaction.
When to Use Pessimistic Locking
- High Contention: If you expect high contention for a piece of data and want to avoid conflicts at all costs.
- Critical Operations: When you need absolute consistency in scenarios where multiple transactions need to access and modify the same data (e.g., financial transactions, order processing).
- Large Transactions: For long-running transactions that involve multiple reads and writes, it can be useful to use pessimistic locking to prevent other transactions from interfering during the entire process.
TiDB supports the following two isolation levels in pessimistic mode:
Advantages:
- Better for High Contention Scenarios – Works well when multiple transactions are updating the same rows.
- Ensures Data Consistency – Prevents write conflicts by locking data until the transaction is complete.
- Less Need for Retries – Since transactions lock rows early, there are fewer conflicts at commit time
Drawbacks:
- Lower Concurrency – Transactions must wait for locks, reducing overall throughput.
- Potential for Deadlocks – Multiple transactions waiting for locks can lead to deadlocks.
- More Overhead – Holding locks for a long time consumes resources and can slow down the system.
Optimistic Locking in TiDB
Now imagine a different scene: you're shopping online during a regular day (not a flash sale). You add items to your cart, browse calmly, and only at checkout does the system quickly check if your items are still available.
That is optimistic locking in action. TiDB trusts that most of the time, conflicts are rare, so it lets transactions run freely without grabbing locks upfront. Only at the end, when it’s time to commit, it checks if everything is still safe.
.png)
In TiDB, optimistic transactions are lighter and faster when the system isn’t under heavy stress.
How Optimistic Locking Works in TiDB
- Transactions start without locking the data. They assume no conflicts will occur.
- Before committing, the system checks whether any other transactions have modified the data that the current transaction is trying to update.
- If no conflicts are found, the transaction commits. If a conflict is detected, the transaction is aborted and rolled back.
This strategy is ideal for scenarios where conflicts are unlikely and concurrency is high, as it reduces the overhead of acquiring locks.
When to Use Optimistic Locking
- Low Contention: If you expect that most of the time, transactions will not conflict (for example, when updates are spread across different rows or tables).
- High Concurrency: Optimistic locking is great in environments with many transactions that are likely to modify different pieces of data, where locking would otherwise cause performance bottlenecks.
- Non-Critical Operations: When you don't need strong guarantees on the consistency of every transaction and can afford some level of retry.
Advantages:
- Better Concurrency – No need to acquire locks during the transaction, leading to higher performance.
- Reduced Deadlocks – Since transactions don’t block each other until commit time, deadlocks are less likely.
- Faster Read Operations – Transactions read data without acquiring locks, which improves read performance.
- Efficient for Read-heavy Workloads – Suitable for workloads where conflicts are infrequent.
Drawbacks:
- Higher Risk of Conflicts – If multiple transactions modify the same row, one will fail at commit time.
- Rollback Overhead – When conflicts occur, transactions have to be retried, which adds overhead.
- Not Ideal for High Contention Workloads – If many transactions update the same rows, frequent conflicts reduce efficiency.
You can set the transaction mode by configuring the tidb_txn_mode system variable.
SET GLOBAL tidb_txn_mode = 'pessimistic';
We can explicitly enable any transaction mode using BEGIN statement
BEGIN PESSIMISTIC ;
The BEGIN PESSIMISTIC; and BEGIN OPTIMISTIC; statements override the tidb_txn_mode system variable. Transactions initiated with these statements ignore the system variable, allowing the use of both pessimistic and optimistic transaction modes
TiDB’s Default Locking Mechanism: Snapshot Isolation
TiDB uses snapshot isolation (SI) as the default isolation level, which combines aspects of both optimistic and pessimistic locking. TiDB uses MVCC (Multi-Version Concurrency Control) to ensure that each transaction operates on a snapshot of the data at the start of the transaction. This allows it to provide consistent reads without needing to acquire locks.
- Read Consistency: With snapshot isolation, TiDB ensures that each transaction reads a consistent view of the data, even if other transactions are concurrently modifying the database.
- Commit Validation: During commit, TiDB validates that the data being modified hasn’t been altered by another transaction. If it has, the transaction will be aborted and can be retried.
In essence, TiDB's MVCC-based optimistic concurrency control offers a hybrid approach that gives the advantages of both optimistic and pessimistic locking, depending on the workload and configuration.
Optimistic vs. Pessimistic Locking: Key Differences
In TiDB, both optimistic and pessimistic locking are available to cater to different types of workloads. Pessimistic locking is beneficial for scenarios with high contention and where data consistency is critical, while optimistic locking allows for higher concurrency in systems where conflicts are rare.
TiDB’s underlying MVCC and snapshot isolation offer flexibility, enabling you to work with a mix of both strategies depending on your workload requirements. Understanding these two locking mechanisms and when to use them can significantly improve the performance and scalability of your TiDB-based applications.
Need Help Choosing the Right Locking Strategy?
Whether you’re scaling fast or fine-tuning your concurrency model, our TiDB experts at Mydbops are here to guide you. Get a Free Personalized TiDB Consultation and ensure your database is ready to handle high-concurrency demands with confidence.
.svg)
