Snapshot transaction Isolation levels: it really works as advertised?
Have you any problems using it on high concurrency environment? It's really works as advertised by M开发者_运维技巧S? 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...
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?
精彩评论