When we issue a SELECT statement in SQL Server, it locks the table. Many people wonder why does SQL Server needs to lock the table?
When the SELECT is being processed, when SQL Server is reading the rows in the table, imagine if someone drops the table. What will happen? The drop statement will have to wait until SQL Server finish reading all rows for this select. (1)
What if someone truncates the table when the SELECT is reading the rows? Or delete the rows that the SELECT was about to read? Same thing, the TRUNCATE or DELETE will have to wait until the SELECT finishes. (2)
What if someone update the rows that SELECT was about to read? Same thing, the UPDATE has to wait. (3)
What if someone inserts some rows that fall within the SELECT criteria? Same thing, the INSERT has to wait. (4)
(1), (2), (3) and (4) are the purposes of locking the table when reading it, i.e. to get consistent result from the beginning of the read until the end of the read.
This is the default behaviour of SQL Server. I call it “Clean Read”. Formally it is called READ COMMITTED Isolation Level.
The opposite of Clean Read is Dirty Read, which happens when the data is changed before the end of the reading, so that at one point we see the row, but at a split moment later when we update the row we can find the row. Dirty Read is formally know in the SQL Server world as “Non-Repeatable Read”.
SELECT With NOLOCK
What if we do the SELECT with NOLOCK? Does SQL Server prevent someone from dropping the table? Yes, nobody can drop the table when it is being read, even if the reading is using NOLOCK. The DROP will be executed, after the SELECT is finished.
What about Truncate? Can someone truncate the table, while a SELECT with NOLOCK is reading the table? No, the TRUNCATE will wait until the SELECT finishes.
What about Delete? Can someone delete the rows from the table while those rows are being read by a SELECT with NOLOCK? Yes, that is possible.
If you have a table with 100,000 rows and User1 issued SELECT * FROM TABLE1 WITH NOLOCK, and at the same time User2 issued DELETE FROM TABLE1, then User1 will only get something like 15,695 rows*, because the other 85k rows were deleted.
*This is the result of my test. Obviously you can get some other numbers depending on various factors.
These 15,695 rows are not sequential, but jumpy, i.e. User1 will get
row 90351-90354, then jump to:
row 90820-90824, then jump to:
and didn’t get row 94104 to 100,000.
Because the missing rows have been deleted by User2.
But sometimes, instead of getting the above rows, User1 will get this error message:
Error 601: Could not continue scan with NOLOCK due to data movement
This is when “the page at the current position of the scan” is deleted. See link.