views:

62

answers:

1

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...

+2  A: 

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?

rwmnau
Basicly the overall feeling, since I want to propose to make our production db to be on snapshot isolation. It have a lot of contention and suffer from frequent blocking and some deadlocks.Since I already know how confortable it is after developing some applications with Firebird (where everything is under record versioning) - I just wanna know if MS implementation is good.
Fabricio Araujo
@Fabricio Araujo: I've had no problems with their implementation, that's I've admittedly not had expose to anybody else's row-versioning, so I have no basis for comparison. I do know that we had serious contention issues using read-committed, and enabling snapshot isolation relieved the issues, with no complaints of any kind of unexpected side-effects.
rwmnau