Transaction isolation level - Read committed and select on large tables

Question

I am trying to troubleshoot an issue in a report and wondering if the below scenario is possible. I have a tables which is 548GB and there are reports which sometimes select 5 years of data which could be alomost the entire table. The table has a non unique clustered index.

I read about read committed isolation levels and was testing it to see how it was locking the table during selects and from my understanding it looks like it puts a series of shared page lock. Acquire shared page lock, read page, release shared page lock, move to next page and so on.

If the above is true, and lets assume the query is doing an clustered index scan, how does it handle an insert at the beginning of the table which it has already read past. What I mean by beginning of table is that since the table is having a clustered index and since it has to be ordered if an lower value index id record gets inserted then it needs to be inserted into the starting half of the leaf level of the clustered index.

Answer 1

In general, the storage engine has two options to perform a clustered index scan: an allocation order scan or an index order scan. For an allocation order scan, the storage engine reads the data in file order. In your scenario, this could result in the data from the insert showing up in the first query. For an index order scan, the storage engine reads data in linked list order. In your scenario, the new data from the insert will not show up in the first query. Even if the page that contains the new row splits the new page will not be read.

The storage engine decides between an allocation order scan or an index order scan depending on the requirements of the query and the isolation level. For example, for a query that requires data to be in order an index order scan is the right choice. For a query with a table lock that does not require the data to be in order, an allocation order scan is the right choice. Specifically, you will end up with an allocation order of the unsafe category (a scan can return multiple occurrences of rows or skip rows when page splits occur) when all of the following are true:

• The query is running under the read uncommitted isolation level
• Changes are allowed to the data (a table lock was not taken by the query)
• The plan shows Index Scan, Ordered: false (the query does not require the data to be sorted)
• The index size is greater than 64 pages.

In your example you have an isolation level of read committed. You also did not take a table lock with your clustered index scan query because the second session was able to insert a row. Therefore, the storage engine will perform a scan of the index order scan variety. The new data from the insert will not show up in the first query that does the scan.

My source for this is pages 65-69 from T-SQL Querying by Itzik Ben-Gan, Dejan Sarka, Adam Machanic, and Kevin Farlee.