現(xiàn)象:
在將數(shù)據(jù)庫兼容的級別從80改到90以后, 下文中的游標(biāo)循環(huán)不出數(shù)據(jù), 單獨(dú)SELECT卻會有結(jié)果:
DECLARE MyCursor CURSOR LOCAL READ_ONLY
FOR
SELECT
Col1
FROM tbname WITH(NOLOCK)
WHERE Name LIKE 'SNET%'
AND B_Key IN(
SELECT (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
原因：
游標(biāo)類型的問題。
參照以上的定義, 游標(biāo)類型是: DYNAMIC
在定義此類游標(biāo)的情況下, S鎖是必須下的, NOLOCK提示并不會起作用,此現(xiàn)象通過查詢游標(biāo)OPEN時(shí)的sp_lock信息就可以觀察得到。它產(chǎn)生了IS和S鎖。
而NOLOCK 提示是否起作用, 會影響的執(zhí)行的結(jié)果(執(zhí)行計(jì)劃一樣, 但在取數(shù)據(jù)的時(shí)候, 卻會有所差異)
對于下面這句, 有NOLOCK 和無NOLOCK 時(shí), 它取的數(shù)據(jù)是不一樣的, 因?yàn)樗蝗×?100, 而且沒有ORDER BY 來保證取數(shù)的順序, 所以取數(shù)據(jù)順序的細(xì)致差異, 就導(dǎo)致了最終結(jié)果的不同. 而最終結(jié)果的不同, 導(dǎo)致了整個(gè)游標(biāo)取出來的數(shù)據(jù)不同。
SELECT (100)
KeyID
FROM tbmaster WITH(NOLOCK)
WHERE Date >= '01/01/2007'
AND Date < '02/01/2007')
在游標(biāo)定義SELECT 語句中, 當(dāng)NOLOCK 有效時(shí), 是可以取到數(shù)據(jù)的, 但NOLOCK 無效(DYNAMIC 游標(biāo)導(dǎo)致)時(shí), 查詢結(jié)果是無數(shù)據(jù)的
所以最終看到的結(jié)果是: 游標(biāo)循環(huán)不出來數(shù)據(jù), 但只做查詢卻有數(shù)據(jù)。
如果把游標(biāo)定義中的查詢語句的NOLOCK 去掉做查詢, 也會沒有數(shù)據(jù)(與DYNAMIC 游標(biāo)結(jié)果一致)
故這個(gè)問題嚴(yán)格來說不應(yīng)該是兼容級別的問題, 在80 級別下, 還是有可能發(fā)生, 只是機(jī)率更小, 或者是內(nèi)部執(zhí)行原理不太一樣, 導(dǎo)致沒有這種情況出來而已
由于沒有ORDER BY來保證順序, 而有無NOLOCK的數(shù)據(jù)可能不會一樣, 所以80與90下都可能出現(xiàn)問題, 只是90會顯得比較突出, 或者僅僅時(shí)正好被發(fā)現(xiàn)了。
解決方法如下:
首先，需要把游標(biāo)定義改成下面的,這樣不會導(dǎo)致NOLOCK 失效, 而且速度比原來的定義方式快得多. 如果游標(biāo)一定要與原始表的數(shù)據(jù)變化關(guān)聯(lián)起來, 建議用KEYSET, 或者是去掉NOLOCK 提示(因?yàn)闆]有意義), 假如對取的數(shù)據(jù)有要求, 我們還應(yīng)當(dāng)考慮加ORDER BY來保證取數(shù)順序：
DECLARE MyCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC
FOR
在將數(shù)據(jù)庫兼容的級別從80改到90以后, 下文中的游標(biāo)循環(huán)不出數(shù)據(jù), 單獨(dú)SELECT卻會有結(jié)果:
DECLARE MyCursor CURSOR LOCAL READ_ONLY
FOR
SELECT
Col1
FROM tbname WITH(NOLOCK)
WHERE Name LIKE 'SNET%'
AND B_Key IN(
SELECT (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
原因：
游標(biāo)類型的問題。
參照以上的定義, 游標(biāo)類型是: DYNAMIC
在定義此類游標(biāo)的情況下, S鎖是必須下的, NOLOCK提示并不會起作用,此現(xiàn)象通過查詢游標(biāo)OPEN時(shí)的sp_lock信息就可以觀察得到。它產(chǎn)生了IS和S鎖。
而NOLOCK 提示是否起作用, 會影響的執(zhí)行的結(jié)果(執(zhí)行計(jì)劃一樣, 但在取數(shù)據(jù)的時(shí)候, 卻會有所差異)
對于下面這句, 有NOLOCK 和無NOLOCK 時(shí), 它取的數(shù)據(jù)是不一樣的, 因?yàn)樗蝗×?100, 而且沒有ORDER BY 來保證取數(shù)的順序, 所以取數(shù)據(jù)順序的細(xì)致差異, 就導(dǎo)致了最終結(jié)果的不同. 而最終結(jié)果的不同, 導(dǎo)致了整個(gè)游標(biāo)取出來的數(shù)據(jù)不同。
SELECT (100)
KeyID
FROM tbmaster WITH(NOLOCK)
WHERE Date >= '01/01/2007'
AND Date < '02/01/2007')
在游標(biāo)定義SELECT 語句中, 當(dāng)NOLOCK 有效時(shí), 是可以取到數(shù)據(jù)的, 但NOLOCK 無效(DYNAMIC 游標(biāo)導(dǎo)致)時(shí), 查詢結(jié)果是無數(shù)據(jù)的
所以最終看到的結(jié)果是: 游標(biāo)循環(huán)不出來數(shù)據(jù), 但只做查詢卻有數(shù)據(jù)。
如果把游標(biāo)定義中的查詢語句的NOLOCK 去掉做查詢, 也會沒有數(shù)據(jù)(與DYNAMIC 游標(biāo)結(jié)果一致)
故這個(gè)問題嚴(yán)格來說不應(yīng)該是兼容級別的問題, 在80 級別下, 還是有可能發(fā)生, 只是機(jī)率更小, 或者是內(nèi)部執(zhí)行原理不太一樣, 導(dǎo)致沒有這種情況出來而已
由于沒有ORDER BY來保證順序, 而有無NOLOCK的數(shù)據(jù)可能不會一樣, 所以80與90下都可能出現(xiàn)問題, 只是90會顯得比較突出, 或者僅僅時(shí)正好被發(fā)現(xiàn)了。
解決方法如下:
首先，需要把游標(biāo)定義改成下面的,這樣不會導(dǎo)致NOLOCK 失效, 而且速度比原來的定義方式快得多. 如果游標(biāo)一定要與原始表的數(shù)據(jù)變化關(guān)聯(lián)起來, 建議用KEYSET, 或者是去掉NOLOCK 提示(因?yàn)闆]有意義), 假如對取的數(shù)據(jù)有要求, 我們還應(yīng)當(dāng)考慮加ORDER BY來保證取數(shù)順序：
DECLARE MyCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC
FOR