Understanding of T-SQL repeating read (Double read) problem

My understanding is:

Step1, assuming that the table has 100 rows of ordered records, transaction 1 reads from row 1 to row 50 and prepares to continue reading the 100 rows.

Note that SQL Server automatically frees the lock for the row that has been read.

Step2, this time, another transaction 2 modifies some data in the previous 50 rows that transaction 1 has read and is freed by SQL Server.

This modification caused the data to be sorted, and the 20th line that had already been read could now go behind 50 rows.

Step3, then, transaction 1 continues to read the remaining data. It is possible to find that data has been read repeatedly.

The demo is as follows:

1. Create tables and populate data.

CREATE TABLEDbo.testdoubleread (IDint Identity(Ten,1)PRIMARY KEY, Text_ascnvarchar( -))GoCREATE nonclustered INDEXIx_testdoubleread_text_asc onDbo.testdoubleread (TEXT_ASCASC);INSERT  intoDbo.testdoubleread (TEXT_ASC)VALUES('A'),('B'),('C'),('D')

Note that the collation of the nonclustered index is ASC, when the data in the table is:

SELECT *  from Dbo.testdoubleread /* ID    text_asc10    A11    B12    C13    D* *

2. Open a new session and perform the following incomplete transaction:

-- SESSION 1 SCRIPT BEGIN TRANSACTION UPDATE Dbo.testdoubleread SET = ' Update_c ' WHERE =  A

3. Open another session and execute the following query:

SELECT *  from Dbo.testdoubleread

Obviously, this query will be session1 block.

　　

4. Go back to Session1 and execute the following code:

UPDATE Dbo.testdoubleread SET = ' update_b_2 ' WHERE =  One COMMIT TRANSACTION

5. At this time, the Session2 will complete the query, the results are as follows:

SELECT *  from Dbo.testdoubleread /* ID    text_asc10    A11    B13    D11    update_b_212    update_c* *

As you can see, there are two records with ID = 11!

Again under analysis:

1. According to the definition of a nonclustered index, the first data should be like this

/* ID    text_asc10    A11    B12    C13    D* *

2. Session1 opened the transaction and modified the Id=12 row, but did not commit. At this point, the Session2 attempts to read the data but can only read the id=11, no further reading, unless Session1 releases the id=12 rows.

3. Next, Session1 changed the id=12 line, TEXT_ASC ASCAccording to the collation of the nonclustered index, you can determine that the value ' Update_c ' should be the last row. And id=11 's new value ' Update_b ' is in the penultimate row.

　　The ideal data should look like this:

/* ID    text_asc10    A13    D11    update_b_212    update_c* *

　　

4.session modified id=11 After the submission, so that Session2 can continue the previous query. Note that the session2 was read before being block: (id=11,text_asc= ' B ')

5. Combining the above points, Session2 continues to read (id=13 start) in the order of the nonclustered index, rather than emptying (id=11,text_asc= ' B ') that was previously read.

So the end result is this:

/* ID    text_asc10    A11    B    --After reading this line of    D--session1 COMMIT TRANSACTION before the modified transaction block is Session1    , Session2 from this line to read    update_b_212    update_c* *

　　

Understanding of T-SQL repeating read (Double read) problem