A while back\u00a0I was involved in an email thread where people were wondering about some \u2018weird\u2019 behavior from SQL Server. The problem was occurring on SQL Server 2016 and used the default isolation level of read committed. The scenario was as follows:<\/p>\n
The \u2018weird\u2019 behavior is that when the \u201cBatch 2\u201d select completes, after having been blocked by the \u201cBatch 1\u201d transaction, it doesn\u2019t return all 1,000 rows (even though \u201cBatch 1\u201d has completed). Furthermore, depending on when<\/em><\/strong> the \u201cBatch 2\u201d select is started, during the 10-seconds that \u201cBatch 1\u201d executes, \u201cBatch 2\u201d returns different numbers of rows. This behavior had also been reported on earlier versions of SQL Server as well. It\u2019s easy to reproduce on SQL Server 2016\/2017 and can be reproduced in all earlier versions with a single configuration change (more details in a moment).<\/p>\n Additionally, if the table has a clustered index created, 1,000 rows are returned every time, on all versions of SQL Server.<\/p>\n So why is this weird? Many people expect that all 1,000 rows\u00a0will be returned every time AND that the structure of the table or the version of SQL Server should not make any difference.<\/p>\n Unfortunately, that assumption is not correct when using read committed. The read committed isolation level guarantees that only committed data will be read; it does NOT guarantee how much of the committed data will be returned and is prone to inconsistent analysis problems. If you want to avoid inconsistent analysis, you\u2019d need to increase your isolation level (if using locking) or change to versioning for read committed (or even snapshot isolation).<\/p>\n However, I do agree that the assumption is reasonable, even though it’s not correct.<\/p>\n The definition of the requirement is straightforward however. SQL Server guarantees that only committed data will be read, not how much of it will be read, and so the amount of committed data returned in this case is variable. Furthermore, depending on the table structure, SQL Server version, and configuration option (which I\u2019ve mentioned twice now, but haven\u2019t explained as I don\u2019t want to spoil my story\u2026<\/em>), the number of rows returned will vary wildly.<\/p>\n So, what\u2019s going on? Why the discrepancy in the number of rows?<\/p>\n It\u2019s because the table is a heap.<\/p>\n The ‘weird’ behavior manifests itself when the heap has an extent<\/a> allocated to it immediately, from which the first data pages are allocated. When the allocation-order scanner for the select starts, it looks at the PFS bytes<\/a> for the 8 pages in the extent to see which ones are allocated. These will be scanned. Depending on when the select starts within the 10 seconds that \u201cbatch 1\u201d executes, there will be more rows or fewer rows read by the scanner (and returned in the \u201cbatch 2\u201d results) because more or fewer of the PFS byte 0x40 bits will have been set indicating the page in the extent is allocated.<\/p>\n For a table to have an extent allocated to it immediately, mixed pages have to be disabled, which is the default for SQL Server 2016 onward, which is why the ‘weird’ behavior manifests with a small number of rows on SQL Server 2016. However, if you\u2019ve enabled trace flag 1118, you\u2019ll see this behavior in all versions. This is why some people report seeing the ‘weird’ behavior on versions earlier than SQL Server 2016.<\/p>\n When mixed extents are NOT disabled, i.e. in earlier versions than SQL Server 2016 when 1118 is not enabled, the first 8 pages allocated are mixed pages, and so the allocation order scanner has to pick them up individually and sees them all (without going into details of the synchronization around the single-page slot array on the first IAM page in the IAM chain\/allocation unit<\/a> for the table), and so as long as no more than 8 data pages are used by the insert, all the rows on them will be returned by the select in this example.<\/p>\n So there you have it, the behavior is \u201cby design\u201d and hasn’t changed at all, but is understandably confusing unless you know exactly how the Engine is performing the select under the covers.<\/p>\n And if you create a clustered index, the \u2018weird\u2019 behavior doesn’t occur for this example in any version. This is because the select gets hung up on the S(hare) row lock for the first row in the clustered index, and by the time the select is granted the S lock, the insert has completed and the index leaf scan picks up all 1,000 rows.<\/p>\n Don\u2019t think that using a clustered index stops \u2018weird\u2019 behavior using read committed; it doesn\u2019t. However, it can reduce some forms of inconsistencies. In this old blog post<\/a>, I demonstrate a scenario where a scan of clustered index returns four rows when only three rows exist, using read committed. Kimberly also describes and shows \u201cinconsistent analysis\u201d in these blog posts:<\/p>\n