游标类型产生的数据检索问题

表现 :

将数据库兼容级别从 80 改到 90 后 , 下面的游标循环不出数据 , 但单独 SELECT 有结果

DECLARE MyCursor CURSOR LOCAL READ_ONLY

FOR  

SELECT

    Col1

FROM tbname WITH(NOLOCK)

WHERE Name LIKE 'SNET%'

    AND B_Key IN(

            SELECT TOP(100)

                KeyID

            FROM tbmaster WITH(NOLOCK)

            WHERE Date >= '01/01/2007'

                AND Date < '02/01/2007')

OPEN MyCursor

FETCH NEXT FROM MYCURSOR

WHILE (@@FETCH_STATUS=0)

BEGIN

    FETCH NEXT FROM MYCURSOR

END

CLOSE MyCursor

DEALLOCATE MyCursor

 

分析 :

导致出现这个情况的原因是游标类型的问题 .

按照上述定义 , 游标类型是 : DYNAMIC

定义这种游标的情况下 , S 锁是必须下的 , NOLOCK 提示不会起作用 , 这个通过查询游标 OPEN 时的 sp_lock   信息可以观察得到 . 它产生了 IS 和 S 锁

而 NOLOCK 提示是否起作用 , 会影响的执行的结果 ( 执行计划一样 , 但在取数据的时候 , 会有所差异 )

对于下面这句 , 有 NOLOCK 和无 NOLOCK 时 , 它取的数据是不一样的 , 因为它只取了 TOP 100, 而且没有 ORDER BY 来保证取数的顺序 , 所以取数据顺序的细致差异 , 就导致了最终结果的不同 . 而最终结果的不同 , 导致了整个游标取出来的数据不同 .

SELECT TOP(100)

    KeyID

FROM tbmaster WITH(NOLOCK)

WHERE Date >= '01/01/2007'

    AND Date < '02/01/2007')

 

在游标定义 SELECT 语句中 , 当 NOLOCK 有效时 , 是可以取到数据的 , 但 NOLOCK 无效 ( DYNAMIC 游标导致 ) 时 , 查询结果是无数据的

所以最终看到的结果是 : 游标循环不出来数据 , 但只做查询却有数据 .

如果把游标定义中的查询语句的 NOLOCK 去掉做查询 , 也会没有数据 ( 与 DYNAMIC 游标结果一致 )

 

故这个问题严格来说不应该是兼容级别的问题 , 在 80 级别下 , 还是有可能发生 , 只是机率更小 , 或者是内部执行原理不太一样 , 导致没有这种情况出来而已

因为没有 ORDER BY 保证顺序 , 而有无 NOLOCK 的数据可能不会一样 , 所以理论上 80 与 90 下都可能出现问题 , 只是 90 比较突出 , 或者正好被发现了而已

 

 

解决的办法 :

把游标定义改成下面的 , 这样不会导致 NOLOCK 失效 , 而且速度比原来的定义方式快得多 . 如果游标一定要与原始表的数据变化关联起来 , 建议用 KEYSET , 或者是去掉 NOLOCK 提示 ( 因为没有意义 ), 如果对取的数据是有要求的 , 则还应该考虑加 ORDER BY 保证取数顺序

DECLARE MyCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC

FOR