首页 新闻 搜索 专区 学院

SQL Server 隔离级别问题?

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

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

使用了Serializable隔离级别,但依然有重复的记录被查出。

代码片断如下:

复制代码
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
复制代码
问题补充:

用单线程调用这段代码,完全正确,没有一条记录被重复取出。但用线程池就会出现重复读出同一条记录的情况。

空明流光的主页 空明流光 | 初学一级 | 园豆:62
提问于:2017-01-05 16:11
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册