2023 Halloween was just past, I came across this SQL Server related Halloween Problem accidently on an article I read.
Usually update operation has two separate cursors, one doing read and one doing the update.
If update operation caused row to updated in a way it cause index key also need updated and hence it's position changed, then there can be a occasions where read cursor might read it again and update again.
What is basically happening is when row is updated it change the position to match the index key and hence read by the read cursor again and updated again.
Though it sound like it could happen very frequently, issue doesn't occur regularly on SQL server because it need to update index key and also phase separation happens while update operation doesn't allow it. Phase separation, make sure all matching rows are read first before updates are occur. However, for performance reasons, SQL server might decide some time to write some of the updated data before it read the next batch of data to be need to updated.
Above is a very high level and very simplified explanation (so that I can remember it). There are great articles on internet regarding this:
https://www.sqlshack.com/the-halloween-problem-in-sql-server-and-suggested-solutions/
https://en.wikipedia.org/wiki/Halloween_Problem
No comments:
Post a Comment