In this lesson, you will learn how the choice between pessimistic locking and optimistic snapshot isolation fundamentally shifts the performance bottleneck of your database.
When multiple transactions run at the same time, the database must prevent anomalies like dirty reads or write skew. We typically manage this using one of two strategies: Locking or Snapshot Isolation.
The Pessimistic Approach: Locking
Locking is a pessimistic strategy. It assumes that data conflict is highly likely, so it restricts access before a transaction even begins its work. If Transaction A wants to update a row, it places an exclusive LOCK on that row. If Transaction B tries to read or modify that same data, it is forced to wait until Transaction A commits or rolls back.
- Performance Characteristic: High contention. While safe, this creates "blocking," where threads sit idle waiting for resources. In high-traffic systems, this leads to long queues and potential deadlocks.
The Optimistic Approach: Snapshot Isolation
Snapshot Isolation is an optimistic strategy. Instead of locking rows, the database provides each transaction with a "snapshot" of the data as it existed at the start of the transaction. If Transaction A updates a row, Transaction B continues to read the old version from its snapshot without being blocked.
-- Transaction A starts
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- It works on a consistent "version" of the database
-- It doesn't block Transaction B from reading the same rows!
- Performance Characteristic: High concurrency. Because readers never block writers, throughput increases significantly. However, there is a cost: if two transactions try to modify the same data, the database must perform a conflict check at the end. If a conflict is detected, one transaction must be aborted and restarted.
Which one to choose?
- Use Locking when you have high contention on specific rows, as you want to prevent the "work and fail" cycle of retrying aborted transactions.
- Use Snapshot Isolation when you have a read-heavy workload where you want to ensure that readers can always access data without waiting for long-running write operations to finish.
Key Takeaway: Locking prioritizes safety by blocking concurrent access, while Snapshot Isolation prioritizes performance by allowing concurrent access and resolving conflicts only when they occur.