SQL Server › Isolation & Locking

What is the difference between an optimistic and a pessimistic approach to handling concurrency, and where does SQL Server's snapshot isolation fit into this?

Intermediate

Answer

A pessimistic approach assumes conflicts are likely and uses locks to prevent them from happening in the first place, while an optimistic approach assumes conflicts are rare and instead detects them after the fact, and snapshot isolation is SQL Server's optimistic option, letting readers see a consistent version of the data as it existed at the start of their transaction without blocking writers.

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM Orders;
COMMIT TRANSACTION;

Real-world example A reporting system uses snapshot isolation to run long running reports against a consistent view of the data, without blocking the many concurrent transactions that continue reading and writing to the same tables during that time.

Common follow-ups: What is the storage overhead of using snapshot isolation?;How does snapshot isolation differ from Read Committed Snapshot Isolation?

Transactions & ACID;In-Memory OLTP (Memory-Optimized Tables)