SQL Server 幻讀 的真實案例

標籤：單線程   可靠   指定   font   group   from   item   isolation   insert   

資料庫中有表[01_SubjectiveScoreInfo]，要實現表中的資料只被查出一次，此表資料量較大，有三四百萬資料。表結構也確實不是很合理，無法修改表結構，即使是新增一個欄位也會有相當大的修改量。

因之前代碼中存在大量的insert into select *的語句，加一個欄位什麼也不做也會導致整個項目癱瘓，當然我不想去討論前人的代碼品質。

於是乎我加了一個新表[01_SubjectiveScoreInfoFlag]來進行記錄取過的記錄ID。於是就有了如下的代碼：

BEGIN TRAN                                        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;                                        INSERT INTO [01_SubjectiveScoreInfoFlag](ID)                    SELECT TOP 100 SS.ID                    FROM [01_SubjectiveScoreInfo] AS SSINNER JOIN SubjectiveItemInfo AS SI                         
                        ON SS.TestCode=SI.TestCode                           AND SS.MajorQuestionID=SI.MajorQuestionID                           AND SS.MinorQuestionID=SI.MinorQuestionID                    WHERE SS.TestCode=‘‘‘ + @TestCode + ‘‘‘                          AND SI.QuestionGroupCode=‘‘‘ + @QuestionGroupCode + ‘‘‘                         AND (SI.MinorQuestionCount=0                         OR SI.MinorQuestionID>0)                      AND NOT EXISTS                      (                        SELECT TOP 1 1                         FROM [01_SubjectiveScoreInfoFlag]                         WHERE ID = SS.ID                      )                                    COMMIT TRAN

此處用到了事務，並且指定了隔離等級。這個是必須的，我們之前就是沒有指定，單線程無論你怎麼調用這段代碼運行都非常可靠，但多線程類比並發調用時就出現了非常嚴重的重複。

上面的代碼也並沒有解決問題，原因是鎖用得不對，多線程直接就出現了死結現象。因之前對於隔離等級沒什麼經驗，我一度曾經以為這個問題是無解的，直到今天突然被解開了。

按照我自己的理解，是因為鎖的層級不夠，導致了資源爭搶。

就相當於上廁所時，一定要擷取完全的排它鎖，關好門不讓其它人進來；否則如果其它人進來了，雖然他沒有佔到位子，但他拿走了手紙。你佔著位子卻沒有手紙，他拿著手紙卻沒有位子，雙方互不相讓，誰也無法完成上廁所的事務，相持不下，進而導致死結。這時就需要廁所管理出面，要麼強制讓你讓出位子走人，不管理你擦沒擦屁股；要麼搶來手紙趕走他，任他拉到褲子上。

於是乎就有了下面的代碼：

BEGIN TRAN                                        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;                                        INSERT INTO [‘[email protected]+‘_SubjectiveScoreInfoFlag](ID)                    SELECT TOP 100 SS.ID                    FROM [‘[email protected]+‘_SubjectiveScoreInfo] AS SS WITH(UPDLOCK)                      INNER JOIN SubjectiveItemInfo AS SI WITH(UPDLOCK)                        ON SS.TestCode=SI.TestCode                           AND SS.MajorQuestionID=SI.MajorQuestionID                           AND SS.MinorQuestionID=SI.MinorQuestionID                    WHERE SS.TestCode=‘‘‘ + @TestCode + ‘‘‘                          AND SI.QuestionGroupCode=‘‘‘ + @QuestionGroupCode + ‘‘‘                         AND (SI.MinorQuestionCount=0                         OR SI.MinorQuestionID>0)                      AND NOT EXISTS                      (                        SELECT TOP 1 1                         FROM [‘[email protected]+‘_SubjectiveScoreInfoFlag] WITH(UPDLOCK)                        WHERE ID = SS.ID                      )                                    COMMIT TRAN

UPLOCK是排它鎖，這樣就沒有死結了。

 

SQL Server 幻讀 的真實案例