Demystifying SQL Server Concurrency: Locks, Latches, and the Shift to RCSI

In a perfect database world, every application query would execute in absolute isolation, untouched by concurrent operations. In the enterprise production world, however, hundreds of client threads are constantly querying, inserting, updating, and deleting records simultaneously.

To prevent data corruption—such as a user reading an incomplete transaction midway through a financial calculation—the database engine must enforce strict concurrency guardrails.

For years, the standard approach was pessimistic locking, a strategy that often results in extensive blocking chains, application timeouts, and deadlocks. Let's look under the hood at how SQL Server manages concurrency and explore why modern applications shift toward row versioning models.

1. The Pessimistic Blueprint: Shared vs. Exclusive Locks

By default, SQL Server uses a pessimistic concurrency architecture. It relies on the Lock Manager to grant locks to threads based on their requested isolation levels.

+------------------------------------------------------------+
|                       PESSIMISTIC LOCK INTERACTION                    |
+------------------------------------------------------------+
| [ Shared Lock (S)]  --> Granted for Read actions. Compatible with S.||         |                                                             |
|         v (BLOCKING INTERSECT POINT)                                  |
| [ Exclusive Lock (X)] --> Granted for Write actions. Incompatible.   |
+------------------------------------------------------------+

• Shared Locks (S): Acquired by reading operations. Multiple threads can hold Shared locks on the exact same row or data page concurrently because reading data doesn't alter its structure.

• Exclusive Locks (X): Acquired by writing operations (Insert, Update, Delete). An Exclusive lock is highly restrictive; it cannot coexist with any other lock type.

This interaction creates the classic database bottleneck: Readers block Writers, and Writers block Readers. If an operational reporting query takes 10 seconds to scan a massive accounting table, every transaction attempt to update a row on that table is forced to wait in the lock queue, stalling system throughput.

2. Breaking the Deadlock Circle with RCSI

To eliminate this read/write friction, senior database architects frequently transition instances over to Read Committed Snapshot Isolation (RCSI).

RCSI shifts the database from a pessimistic model to an optimistic concurrency framework. Instead of forcing reading operations to acquire Shared locks that collide with active writers, RCSI leverages row versioning mechanics:

-- Enabling RCSI at the database container level
ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;

When an active transaction modifies a row under RCSI, the engine doesn't overwrite the existing data block in place for everyone else. Instead, it copies the original, uncommitted version of that row and stores it inside a centralized infrastructure structure known as the Version Store.

3. The Structural Impact on TempDB

The Version Store doesn't live inside your standard database file layout—it is allocated entirely inside TempDB.

When RCSI is active, reading queries bypass the Lock Manager entirely for row verification. They do not request Shared locks, which means they can never be blocked by an active writing exclusive lock.

Instead, the reading thread points directly to TempDB's Version Store, pulls the last committed snapshot of the data row as it existed before the transaction began, and processes it instantly.

+-----------------------------------------------------------------------+
|                         RCSI TRANSACTION ROUTING                      |
+-----------------------------------------------------------------------+
| [ Write Thread ] --> Modifies Row on User DB (Holds X-Lock)           |
|                                                                       |
| [ Read Thread ]  --> Bypasses Lock Manager --> Fetches Version from   |
|                      TempDB Version Store (Zero Blocking)             |
+-----------------------------------------------------------------------+

The operational payoff is immediate: Readers no longer block Writers, and Writers no longer block Readers.

4. The Architectural Trade-offs

While RCSI sounds like a magical performance button, an engineer must account for the physical trade-offs before deploying it to production:

• TempDB Overload: Because every row update streams an older snapshot out to disk, your TempDB must be perfectly configured with multiple data files and fast NVMe storage to handle the additional I/O overhead.

• Row Overhead: SQL Server appends a 14-byte versioning pointer to the suffix of modified rows in your user database to track back to the Version Store. This can lead to minor page splits and increased storage density requirements.

By understanding the underlying locking mechanisms, you can consciously select the right concurrency model for your workload profiles, ensuring your platforms remain scalable, fast, and entirely free of artificial locking constraints.

Have you transitioned your primary enterprise workloads over to RCSI? What impacts did you observe on your TempDB allocation profiles? Let's discuss in the comments below!