NOLOCK results in an allocation order scan rather than an index order scan.
This is not correct. Using read uncommitted means the storage engine has a choice between allocation-order and index-order scans. It may choose either strategy at runtime without the execution plan recompiling.
NOLOCK doesn't block writes because it doesn't take shared locks on the table.
Reading data at read uncommitted means shared locks are not taken at the row, page, or table granularity. Since these locks are not taken, they will not block an exclusive lock needed by a concurrent transaction to change data.
During the NOLOCK's scan, since there are no shared locks, and a write (insert/update) happens prior to the point the scan has currently reached, then this situation will cause missing records.
This is not specific to read uncommitted. Reading data using locking read committed or repeatable read can also miss committed data.
Locking read committed normally releases a shared lock on a row just before reading the next row:
Repeatable read maintains shared locks to the end of the transaction, but only data actually encountered so far is locked. A row ahead of the current scan position can move behind the scan point if an index key value is changed:
prior images from Craig Freedman's posts linked inline
The issues described above are specific to index-order scans.
Similarly, during a write (insert/update), when page split happens after a row is read, and that row now is part of the next page (due to the page split), then this situation will cause duplicate records.
Index-order scans do not care about page splits. Pages are linked in logical key order both before and after the split. An index-order scan will encounter rows on the page that split, and the new page arising from the split. There's no way to avoid this if you are following pages in key order.
- As shown in my examples above, the missing/duplicate records problem is cause due to no lock and page splits. Correct? Can it also be caused due to allocation order scans?
No, those were examples of rows being missed or encountered multiple times due to rows moving in index order while an index-order scan is in progress.
Rows being missed or encountered multiple times due to page splits is an issue that can only happen when an allocation-order scan is used. When scanning in allocation order, a page split moves some rows to a new page. That new page may or may not be encountered by the allocation-order scan. If the page that split has already been encountered, we see some rows again. If the split page had not been encountered yet, we might miss some rows if the new page falls behind the allocation-order scan position.
Allocation-order scans are only possible without using read uncommitted if the engine has an acceptable guarantee that the data cannot change during the scan. Missing committed rows or encountering them multiple times due to an allocation-ordered scan over changing data is therefore specific to using read uncommitted isolation.
- If allocation ordered scan can indeed cause missing rows/duplicates, then I want to ask- suppose the engine had used an index order scan (I know that the engine won't do this but just assume for the sake of this question) rather than the allocation order scan, then how will it have solved the missing/duplicate rows problem?
Using an index-order scan avoids missing/duplicate rows due to page splits, as described. Missing or duplicate rows can still occur due to index key changes, also as described above.
Note again: Using read uncommitted does not guarantee you get an allocation-order scan.
Further reading:
