In this blog post, we will observe Double Read Phenomenon in SQL Server. You will see how the same record is read twice in a single SELECT query/operation.
First watch this video.
Now, explanation. So why does this happen?
Let’s go step by step.
Step 1
First, user 1 fires an UPDATE in an explicit transaction. User 1 updates the column LastName. There is a non-clustered index on the LastName. Which means the data in the index is sorted by LastName. The update operation has to update the base table as well as the non-clustered index. Both the Bansal values come in a specific order. If you observer carefully, the update operation updates the second occurrence of the Bansal value, so the first one is available to be read by another user/transaction. The transaction is open, which means exclusive (X) lock is held till the transaction completes (commit or rollback).
Step 2
User 2 comes in and fires the SELECT statement. Note that the SELECT statement can get all its data from the index because the index also contains FirstName column. Yes, it is a multi-column index. (Later you can just fire the SELECT statement and observe the execution plan). User 2 reads the first occurrence of Bansal and waits to read the second occurrence. It waits because it needs a shared (S) lock to read and S & X locks are incompatible. So, it keeps waiting.
Step 3
Now, user 1 fires another update within the same transaction. This time, it updates the first occurrence of Bansal value which was already read by user 2, and updates it in such a fashion that the value is greater than the second occurrence of Bansal value, in effect, placing the updated record after the second occurrence of the Bansal value. Why change the placement/order? Because, LastName is the first column in the multi-column index and the data has to be ordered by LastName and since the value has changed, it has to be put in its correct position as per the sort order. After the update, user 1 commits the transaction releasing all the locks. User 2 now gets access to the second occurrence of the Bansal value and as the read operation continues for other records, it encounters the first Bansal value again, which changed its position after the update – thereby reading it a second time.
Phew!
Thanks for reading. If you liked the content, do share it. I am on Twitter if you wish to connect: https://www.twitter.com/A_Bansal
You can also subscribe to my exclusive newsletter ConnectWithAB – https://sqlmaestros.com/stay-connected-with-amitbansal/
