快照事务隔离级别：它真的像宣传的那样有效吗？
您在高并发环境下使用有遇到什么问题吗？真的像MS宣传的那样有效吗？我正在使用 SQL Server 2005，并且想听听那些正在/曾经在生产中使用它的应用程序中使用它的人的经验。
快照隔离本身对我来说并不新鲜，因为我也开发/管理 Firebird/Interbase - 没有显式锁定并且所有工作都在行版本控制中......
Have you any problems using it on high concurrency environment? It's really works as advertised by MS? I'm using SQL Server 2005 and would like to hear the experiences of those who are/was using it on applications using it on production.
Snapshot isolation per se is not new for me as I develop/administer Firebird/Interbase as well - where there no explicit locking and all works in row versioning...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我们在几台服务器上使用快照隔离，包括计费系统的高争用副本（不断复制更新），并且每秒有数十个查询从中选择。在我们打开快照隔离之前，长时间运行的选择查询会经常阻塞计费数据复制，以至于由于单线程复制服务被阻塞，副本有时会过时一个小时或更长时间。
在我们启用快照隔离后，问题立即自行解决 - Select 语句查看数据的最新内部一致版本，并且复制可以在后台继续进行。权衡是您选择的数据可能正在更新过程中，因此两个同时的 Select 语句可能会返回不同的数据，但作为对争用的容忍度的提高，这对我们来说没问题。
您是否有任何特别的疑问，或者只是对其效果的总体感觉？
We use snapshot isolation on a couple of our servers, including a high-contention replica of our billing system (updates are constantly being replicated), and there are dozens of queries/second that Select from it. Before we turned on snapshot isolation, long-running select queries would frequently block the billing data replication, to the point that the replica would be an hour or more out of date at time because the single-threaded replication service was blocked.
After we enabled snapshot isolation, the problems resolved themselves immediately - Select statements view the most recent internally-consistent version of the data, and replication can continue in the background. The trade-off is that the data you're selecting could be in the process of being updated, so two simultaneous Select statements might return different data, but in exchange for the increased tolerance for contention, that was fine with us.
Did you have any questions in particular, or just an overall feeling of how well it works?