Inside RCSI - Deep dive into concurrency and version store

Oct 02, 2025

I recently presented a session on RCSI(Read Committed Snapshot Isolation) and version store, and I learned a few things myself. I wanted to clarify a few points and here are the internal details of the scenarios I deep dived into.
These details are quite technical, so if you’re interested in deepening your understanding, dive in and enjoy!

When RCSI is enabled, I’d like to discuss three scenarios:

UPDATE is in progress, and SELECT starts to run.
Where does SELECT read from?
SELECT runs, and there are no concurrent operations or uncommitted transactions. Where does SELECT read from?
SELECT is running, and now an UPDATE starts to run concurrently. What happens to SELECT that is in progress? What about the UPDATE that started? Does it wait for SELECT to finish?

Scenario 1: UPDATE in Progress, SELECT Starts

Create a table named dbo.T in DBATest database.

IF OBJECT_ID(’dbo.T’, ‘U’) IS NOT NULL
	DROP TABLE dbo.T;

CREATE TABLE dbo.T (
	id INT IDENTITY(1, 1) PRIMARY KEY
	,val VARCHAR(40) NOT NULL
	);

--Insert 2000 records
WITH n
AS (
	SELECT TOP (2000) ROW_NUMBER() OVER (
			ORDER BY (
					SELECT 0
					)
			) AS rn
	FROM sys.objects a
	CROSS JOIN sys.objects b
	)
INSERT dbo.T (val)
SELECT CONCAT (
		‘row_’
		,rn
		)
FROM n
ORDER BY rn;

Step a. On Window 1, run UPDATE but do not commit yet:

--Window 1 
BEGIN TRAN;
UPDATE dbo.T SET val='UPDATED_AFTER_START' WHERE id=1201; 

--Do not commit yet
--COMMIT;

Use dmv - sys.dm_tran_version_store to find out how many rows are versioned. On dbatest database, there is no other transactions running apart from this update.
At this point, only 1 row is versioned because a single row was updated.

Step b. On Window 2, run SELECT using a cursor (to run slowly):

/*
SELECT statement, run using a cursor to run it slowly, so concurrent operations can be run
Display the row numbers it reaches. */

BEGIN TRAN;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT id, val FROM dbo.T ORDER BY id; -- predictable ascending order 
OPEN cur;
DECLARE @id INT, @val VARCHAR(40), @started DATETIME2 = SYSDATETIME(); PRINT ‘Reader started at ‘+CONVERT(varchar(30),@started,121);

WHILE 1=1 BEGIN FETCH NEXT FROM cur INTO @id,@val; IF @@FETCH_STATUS <> 0 BREAK;

-- Progress prints every 1000 rows
IF @id % 100 = 0
    PRINT CONCAT(’Fetched id=’,@id,’ val=’,@val,’ time=’,CONVERT(varchar(12),SYSDATETIME(),114));

-- Make it slow enough to give you time to run the UPDATE
WAITFOR DELAY ‘00:00:00.01’;  -- 10 ms per row (~120s total)

-- Capture the target row when eventually reached
IF @id = 1200
    PRINT CONCAT(’Target row 1200 seen with val=’,@val,’ (should be OLD)’);


	END

--COMMIT

The UPDATE above modifies the record at id 1201, until the cursor reaches id 1200, SELECT gets data from the table and it doesn’t have to get the versioned rows, so max_version_chain_traversed = 0 in dmv sys.dm_tran_active_snapshot_database_transactions.
What is max_version_chain_traversed ?
The number of versions, the reader had to traverse to get the committed data at the time the read statement started.
Value 0 = no version lookups (all rows current).
Value 1 = version store used (at least one row needed one older version).
The reader had to follow exactly one version pointer (one hop) for at least one row.
That happens only if a row it read was updated (or deleted) after the reader’s snapshot point, so it fetched the older committed image from the version store.

In this screenshot, as soon as the SELECT reaches id = 1201, max_version_chain_traversed value becomes 1, meaning SQL Server had to traverse to the versioned row to fetch the previously committed value (the snapshot value at SELECT start).

Basically, it detects the row was updated after its snapshot began and falls back to the prior committed version.

Key Point:

When an UPDATE is in progress and SELECT starts:

For rows not updated - SELECT reads directly from the table
For rows being updated - SELECT fetches the old committed version from the version store.

Clean up - commit or rollback on both windows, and version store cleanup will kick off in a minute after the active transactions are committed/rolled back.

Scenario 2: SELECT with No Concurrent Operations

When there are no active transactions:

SELECT reads data directly from the table, as there are no rows that are versioned.
In the screenshot below, you can notice, that max_version_chain_traversed is 0, which means it didn’t have to traverse to versioned row, as there are no write operations modifying any rows.

Key Point:

When there are no concurrent or uncommitted operations, SELECT reads data directly from the table.

Scenario 3: SELECT in Progress, UPDATE Starts

When SELECT is running with no concurrent operations, it reads data directly from the table and notes the timestamp at the moment the statement began.
Now, when an UPDATE comes in and starts modifying rows, the SELECT identifies those rows as newer and traverses to the version store to fetch the previously committed versions of those rows.

So, where does the in-progress SELECT get its data from?

For rows not modified → directly from the table.
For rows being modified → from the version store.

And what about the UPDATE? Does it wait for SELECT to finish?

No, UPDATE does not wait, because U lock initially taken by UPDATE is compatible with Sch-S(Schema Stability) lock taken by SELECT. Both operations can run independently.
Once UPDATE takes the U lock, it creates versioned rows, which SELECT then reads.

DEMO:

Step 1. Run SELECT first

Step 2. Check versioned rows - 0, as there are no row modifications.

Step 3. Run UPDATE now, before SELECT reaches id 1201, the row being updated.

Step 4. After SELECT passes ID 1200, check number of versioned rows, value is 1, since 1 row is versioned and max_version_chain_traversed is also 1, as the SELECT had to traverse to the versioned row once.

Key Point:

SELECT reads from the table when rows are not being modified.
For rows that are being updated, SELECT follows the pointer to the version store and retrieves the committed version that existed at the time the SELECT statement began.

How about Locks?

If you’re wondering, like me, what locks a SELECT takes in RCSI, and why it doesn’t block other queries, here we go:
I ran an Extended Event with the same SELECT as above, and found that SELECT takes only a Sch-S (Schema Stability) lock, so metadata does not change, while it retrieves data. There are no S(shared) locks taken.

I removed the cursor and tried with just SELECT, it is still the same: Sch-S locks.
Ignore S lock on database, that lock shows up the moment you open up a query window on a database.

Key Point:

In RCSI, SELECT takes only a Sch-S (Schema Stability) lock.
In RC(Read Committed), SELECT takes both Sch-S and Shared (S) locks at the row/page level.

--XE script 
CREATE EVENT SESSION [Track_Locks_AllDB] ON SERVER ADD EVENT sqlserver.lock_acquired (
	ACTION(sqlserver.client_app_name, sqlserver.database_id, sqlserver.session_id, sqlserver.sql_text) 
WHERE (
		[sqlserver].[database_id] = (5)
		OR [sqlserver].[database_id] = (2)
		)
	) ADD TARGET package0.event_file (
	SET filename = N’D:\XE\Locks_AllDB.xel’
	,max_file_size = (2)
	,max_rollover_files = (10)
	)
	WITH (
			MAX_MEMORY = 4096 KB
			,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
			,MAX_DISPATCH_LATENCY = 30 SECONDS
			,MAX_EVENT_SIZE = 0 KB
			,MEMORY_PARTITION_MODE = NONE
			,TRACK_CAUSALITY = OFF
			,STARTUP_STATE = OFF
			)
GO

Screenshot below: This is the XE result set dumped into a temp table for analysis. You can see that all 477 locks taken for SELECT were Sch-S, and no other locks were captured during the time the SELECT ran.

Additionally, to enable RCSI, just enabling ‘Is_Read_Committed_Snapshot’ is enough. Option ‘Allow_Snapshot_Isolation’ is not needed to be enabled for RCSI.

Summary

SELECT reads from the table when rows are not being modified.
UPDATE creates pointers to versions of rows before modifying the rows.
For rows that are being updated, SELECT follows the pointer to the version store and retrieves the committed version that existed at the time the SELECT statement began.
Whenever SELECT reads versioned rows, value of max_version_chain_traversed will change from 0 to 1 or more.
SELECT takes only Sch-S locks in RCSI.
Enable RCSI using ‘Is_Read_Committed_Snapshot’