Are missed rows and duplicate rows the symptoms due to allocation order scan or due to the no lock and page splits?
0
votes
1
answer
971
views
NOLOCK results in an allocation order scan rather than an index order scan.
NOLOCK doesn't block writes because it doesn't take shared locks on the table.
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.
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.
I have been reading articles which seem to indicate that the missing/duplicate row problem is due to allocation order scans.
1. 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?
2. 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?
Asked by variable
(3590 rep)
Feb 21, 2022, 05:38 AM
Last activity: Sep 18, 2024, 01:59 PM
Last activity: Sep 18, 2024, 01:59 PM