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. The definition of the requirement is straightforward however. However, I do agree that the assumption is reasonable, even though it’s not correct. If you want to avoid inconsistent analysis, you’d need to increase your isolation level (if using locking) or change to versioning for read committed (or even snapshot isolation). 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. Unfortunately, that assumption is not correct when using read committed. So why is this weird? Many people expect that all 1,000 rows will be returned every time AND that the structure of the table or the version of SQL Server should not make any difference. It’s 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).Īdditionally, if the table has a clustered index created, 1,000 rows are returned every time, on all versions of SQL Server. This behavior had also been reported on earlier versions of SQL Server as well. Furthermore, depending on when the “Batch 2” select is started, during the 10-seconds that “Batch 1” executes, “Batch 2” returns different numbers of rows. The ‘weird’ behavior is that when the “Batch 2” select completes, after having been blocked by the “Batch 1” transaction, it doesn’t return all 1,000 rows (even though “Batch 1” has completed). Insert 1,000 rows into the table, with a 0.01 second WAITFOR DELAY between each insert.Batch 1: In one SSMS window, do the following (which takes 10 seconds to run):.The problem was occurring on SQL Server 2016 and used the default isolation level of read committed.
A while back I was involved in an email thread where people were wondering about some ‘weird’ behavior from SQL Server.
0 kommentar(er)
