SQL Server 隔离级别问题？

悬赏园豆：10 [已关闭问题] 关闭于 2017-07-14 08:15

　　有两张表，一张表[01_SubjectiveScoreInfo]存储要查询的记录，每条记录只能被查出一次；另一张表[01_SubjectiveScoreInfoFlag]存储被查过的记录的ID防止同一条记录被查询两次及以上；

使用了Serializable隔离级别，但依然有重复的记录被查出,也就是说表[01_SubjectiveScoreInfoFlag]中的列ID有重复值进入。

INSERT时我都带了NOT IN也无济于事。请问有什么好的解决办法么，期待高人？

代码片断如下：

set @sql = '
                BEGIN TRAN
                    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

                    INSERT INTO #TStudentCode(StudentCode,TaskHandledID,HandleStepId) 
                    SELECT TOP 100 SS.StudentCode,null,0  
                    FROM [01_SubjectiveScoreInfo] AS SS 
                      INNER 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 SS.ID NOT IN
                      (
                        SELECT DISTINCT ID  FROM [01_SubjectiveScoreInfoFlag] WITH(NOLOCK)
                      )
                    GROUP BY SS.StudentCode
                    
                    INSERT INTO [01_SubjectiveScoreInfoFlag](ID)
                    SELECT SS.ID
                    FROM [01_SubjectiveScoreInfo] AS SS 
                      INNER JOIN SubjectiveItemInfo AS SI  
                        ON SS.TestCode=SI.TestCode 
                          AND SS.MajorQuestionID=SI.MajorQuestionID 
                          AND SS.MinorQuestionID=SI.MinorQuestionID 
                      INNER JOIN #TStudentCode TS
                        ON SS.StudentCode = TS.StudentCode
                    WHERE SI.QuestionGroupCode=''' + @QuestionGroupCode + '''
                    AND SS.ID NOT IN
                    (
                      SELECT DISTINCT ID  FROM [01_SubjectiveScoreInfoFlag] WITH(NOLOCK)
                    )
                COMMIT TRAN
                ' 
        exec sp_executesql @sql