What happens during RCSI while tempdb is being populated?

Question

Our database/application utilizes Read Committed Snapshot Isolation so that readers are not blocked by writers, and writers are not blocked by readers. Of course that means when a row/page/table lock occurs, the current value of those rows has to be copied to tempdb.

My question is this - if a lock escalates to a table level lock on a large table, and SQL has to copy all of that data into tempdb, what happens when a SELECT statement happens on that table.

Scenario:

Thread 1 - TABLE_A - UPDATE WHERE X=Y (escalates to a table level lock)
Thread 2 - SELECT X FROM TABLE_A

Obviously if there was only 1 or 2 rows in this table, or if it was just a page lock, the select statement would execute pretty quickly. But what if TABLE_A had a few million records in it, and the table got locked. Would the select statement block while waiting for the UPDATE to populate tempdb for the current version?

Answer 1

Of course that means when a row/page/table lock occurs, the current value of those rows has to be copied to tempdb.

It has nothing to do with locks. The previous version of a modified row is copied to the version store in tempdb just before the actual modification is made. Physical integrity is protected by a latch on the page.

The reading query does not have to wait (in the way you suggest), because the version it needs is guaranteed to be in the version store before the data change is made. Since modifications are made a row at a time, the question of waiting for the whole table to be copied to tempdb does not arise.

It may have to wait to acquire a latch on the page containing the row, while the data modification is being made, but this is not specific to using a versioning isolation level.

In a pathological case, where the update and select process the same pages in the same order, at an equal rate, the select would have to wait (on a latch) for each page while the update changed those rows and copied to the version store, but this is exceedingly unlikely to happen in practice. As soon as the update yields even briefly, the select can easily get ahead, and so no longer have to wait.