遊標類型產生的資料檢索問題

表現:

將資料庫相容層級從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 保證順序,