Why does an index order scan not cause missing/duplicate rows due to page splits?



  • In read committed isolation mode, an index order scan can cause missing rows when scan has a point after which insert has happened at a prior point.

    A duplicate rows occurs when scan has already read a record which was later updated and placed at a point further from where the scan point has currently reached.

    Why does an index order scan not cause missing/duplicate rows due to page splits?



  • Imagine an index with pages like this:

    page keys next page
    1 000-099 2
    2 100-199 3
    3 200-299

    Now imagine you are reading rows in ascending key order starting with page 1.

    Meanwhile, another process causes page #2 to split:

    page keys next page
    1 000-099 2
    2 100-149 4
    3 200-299
    4 150-199 3

    Half the rows have moved from page 2 to (new) page 4. The next page pointers mean your scan will continue to see keys in index order as is required for correctness.

    When you finish with page 1, the next page pointers will take you to page 2, then page 4, then page 3. So the page split caused you no problems.

    Note: SQL Server protects all affected pages with an exclusive latch during the split, so nothing can interact with them until the split is complete and all linkages updated. There is no opportunity to miss rows or read them twice during the split.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2