Real case of SQL Server Phantom read

The database has a table [01_subjectivescoreinfo], to implement the data in the table is only detected once, the table data is large, there are 3 million or 4 million data. The table structure is also really not very reasonable, unable to modify the table structure, even if a new field will have a considerable amount of modification.

Because there are a large number of INSERT INTO SELECT * Statements in the previous code, adding a field to do nothing will also cause the entire project to be paralyzed, but I don't want to discuss the code quality of the predecessors.

So I added a new table [01_subjectivescoreinfoflag] to record the record ID that was taken. Then there is the following code:

BEGIN TRAN                                        SET TRANSACTION Isolation  Level SERIALIZABLE; INSERT  into [01_subjectivescoreinfoflag](ID)SELECT TOP  -ss.id from [01_subjectivescoreinfo]  asSsINNER JOINSubjectiveiteminfo asSI
                         onSs. Testcode=SI. Testcode andSs. Majorquestionid=SI. Majorquestionid andSs. Minorquestionid=SI. MinorquestionidWHERESs. Testcode=" "+ @TestCode +" "                           andSI. Questiongroupcode=" "+ @QuestionGroupCode +" "                          and(SI. Minorquestioncount=0                         ORSI. Minorquestionid>0)                       and  not EXISTS                      (                        SELECT TOP 1 1                          from [01_subjectivescoreinfoflag]                         WHEREId=ss.id)COMMIT TRAN

The transaction is used here, and the isolation level is specified. This is a must, we have not specified before, single-threaded, no matter how you call this code to run is very reliable, but multithreaded simulation of concurrent calls there is a very serious duplication.

The above code also does not solve the problem, the reason is that the lock is not used, multi-threaded directly on the emergence of deadlock phenomenon. Having had little experience with the isolation level before, I once thought that the problem was non-solvable until it was suddenly solved today.

According to my own understanding, because the level of locks is not enough, causing the resource scramble.

The equivalent of the toilet, be sure to obtain a complete lock, close the door to let others come in, otherwise if the other people came in, although he did not occupy the seat, but he took the toilet paper. You occupy the seat but no toilet paper, he took the toilet paper but no seat, the two sides do not give, who can not complete the toilet affairs, stalemate, and lead to deadlock. At this point you need to come to the toilet management, or force you to let go of the seat, do not manage you wipe your butt, or grab the toilet paper to get rid of him, let him pull on the trousers.

The following code is then available:

BEGIN TRAN                                        SET TRANSACTION Isolation  Level SERIALIZABLE; INSERT  into [' [email protected]+ ' _subjectivescoreinfoflag](ID)SELECT TOP  -ss.id from [' [email protected]+ ' _subjectivescoreinfo]  asSs with(UPDLOCK)INNER JOINSubjectiveiteminfo asSI with(UPDLOCK) onSs. Testcode=SI. Testcode andSs. Majorquestionid=SI. Majorquestionid andSs. Minorquestionid=SI. MinorquestionidWHERESs. Testcode=" "+ @TestCode +" "                           andSI. Questiongroupcode=" "+ @QuestionGroupCode +" "                          and(SI. Minorquestioncount=0                         ORSI. Minorquestionid>0)                       and  not EXISTS                      (                        SELECT TOP 1 1                          from [' [email protected]+ ' _subjectivescoreinfoflag]  with(UPDLOCK)WHEREId=ss.id)COMMIT TRAN

Uplock is the lock, so there is no deadlock.

Real case of SQL Server Phantom read