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.
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?