[SQLServer2005 SP2]生成多线程的执行计划时，无法取得快照(snapshot)数据。

Question

我的数据库使用了snapshot的功能，为了对应一个不停需要锁定的transaction表。但是上线后发现了奇怪的现象。

<当一个执行时间很长的procedure启动后，在其事务未提交以前，其他session的对proceduce处理所涉及的表的检索结果不一致>

我的测试方案

Session A（模拟procedure）  |  Session B(模拟查询)

打开事务                          |

删除表T1数据                    |

                                     |  查询表T1中被SessionA删除的数据（查询结果不一致）

插入数据至表T1                 |                         

提交事务                          |

比较奇怪的是，SessonB中的查询

时而可以查到数据(检索结果是被SessonA删除的数据，保留在tempdb的快照中)，

时而查不到（此时检索T1数据为0件，tempdb快照中仍然有数据存在）

按理说我数据库启动了快照snapshot,而且事务的隔离级别是read committed snapshot,

要不就是阻塞，要不就是取得行版本，不可能发生其它的现象。(理论上隔离级别是read committed snapshot的时候，不会发生阻塞)

分析实行计划发现，当生成多线程并行的执行计划时，快照中的数据是检索不到的(此时查询tempdb，确实生成了快照数据)。

而生成单线程的执行计划时，快照中的数据会被正确的读取出来。

如果强制指定查询后面加上 option(Maxdop 1) 强制单线程来处理查询的话，就不会发生丢失数据的现象了。

我的表T1是分区表，不知道和障害是否存在关联，希望得到大家的帮助。

大家也可以通过我的邮箱联系我。

chenshuyang.cn

@gmail.com

Answer 1

关注一下 

---

给我写信： QQ我：

Answer 2

How did you set snapshot isolation?

Answer 3

set database read committed snapshot

-----------------------------

select is_read_committed_snapshot_on,snapshot_isolation_state_desc
from sys.databases

is_read_committed_snapshot_on   snapshot_isolation_state_desc

1                                             OFF

------------------------------

i just use the RCSI(read committed snapshot isolation).

Answer 4

Tried set it as query hint only? 

Answer 5

Tried set it as query hint only? 

which hint ?

When i use option(maxdop 1), make it in a single thread execute plan, all is OK !

But I can`t add option(maxdop 1) after all the querys.

As you know, single thread is too slow sometimes.

Answer 6

I want to know whether there is any bug in SQL Server 2005 SP2 of snapshot and Mult-Thread execute plan.

And whether there is some bad setting in my databases option.

Thanks your response!!!!!!!

Answer 7

Should set ALLOW_SNAPSHOT_ISOLATION on in your case, read 'Snapshot Isolation (Transaction-Level Read Consistency)' portion in this link http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx. 

Answer 8

Should set ALLOW_SNAPSHOT_ISOLATION on in your case, read 'Snapshot Isolation (Transaction-Level Read Consistency)' portion in this link http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx. 

Thanks for your response

There is two ways to make the snapshot valiable.

Set allow_snapshot_isolation on, as rmiao said. It needs set isolation snapshot every time.

And another way is that make the RCSI on.

I use the last one.

Answer 9

But you want 'Transaction-Level Read Consistency' which requires allow_snapshot_isolation.

Answer 10

But you want 'Transaction-Level Read Consistency' which requires allow_snapshot_isolation.

yes, i know RCSI will cause the ghost read.

but this is not the ghost read.

I can not get the snapshot rows in multi-thread query plan.

But single thread query plan is OK.

I am also consider that If I drop RCSI and open DB-Snapshot, all is OK.

But I want to know whether the error i met is a SQL Server Bug. 

Answer 11

Install sp4 then try again.

Answer 12

Install sp4 then try again.

 I can not find out the improve point about snapshot in SP3, SP4.

 Thought I use the SP2 now.

Answer 13

最好打上最新的补丁包，缩小排错的范围

---

给我写信： QQ我：

Answer 14

最好打上最新的补丁包，缩小排错的范围

---

给我写信： QQ我：

公司的限制比较多，除非我找到补丁有针对这个问题的改善，否则这个提案是不会通过的。

Answer 15

If you call MS support, they'll let install latest sp first.