Row Versioning in SQL Server

 Row Versioning is a SQL Server mechanism that stores previous versions of modified rows in the tempdb Version Store. When a row is updated, SQL Server copies the old version into tempdb and updates the current row. Transactions using Snapshot Isolation or Read Committed Snapshot Isolation can then read these historical versions instead of waiting for locks to be released. This provides transactionally consistent reads while significantly reducing blocking between readers and writers. Internally, SQL Server maintains version chains and uses transaction sequence numbers to determine which version a transaction should see. Row Versioning is heavily used by Snapshot Isolation, RCSI, online index operations, and several internal SQL Server features. The main benefit is improved concurrency and reduced blocking, while the primary cost is increased tempdb usage and additional version management overhead. In modern OLTP systems, RCSI is commonly enabled to improve user experience and scalability without requiring application code changes.

Locking model

Reader ---> Shared Lock (S)

Writer ---> Exclusive Lock (X)

S and X conflict. Due to that blocking the transaction

See the example:

Session 1

BEGIN TRAN;

UPDATE Employee

SET Salary = 70000

WHERE EmployeeID = 1;

No commit yet.

Session 2:

SELECT *

FROM Employee

WHERE EmployeeID = 1;

Under normal READ COMMITTED this is BLOCKED because Session 1 holds an X lock.

Instead of waiting Reader à Gets Older Version à No Blocking. This is Row Versioning.

SQL Server stores row versions in tempdb.

Row Versioning vs Locking

Traditional Locking	Row Versioning
Readers Wait	Readers Read Old Version
More Blocking	Less Blocking
Lower TempDB Usage	Higher TempDB Usage
Simpler	Additional Version Management