Transaction Snapshots Isolation Levels: Does It Really Work the Way It Is Advertised?

Question

Having trouble using it in a high concurrency environment? Does it really work, as advertised by MS? I use SQL Server 2005 and would like to hear the experience of those who use / use it in applications that use it in production.

Isolation of snapshots per se is not new to me, since I develop / administer Firebird / Interbase, and there, where there is no explicit blocking, and everything works in version versions of strings ...

Answer 1

We use snapshot isolation on several of our servers, including the highly competitive replica of our billing system (updates are constantly replicated), and there are dozens of requests / second that choose from it. Before we enable snapshot isolation, lengthy selection requests often block the replication of billing data to the point that the replica will be outdated for an hour or more because the single-threaded replication service was blocked.

After we turned on snapshot isolation, the problems were immediately resolved - Select statements display the latest internal consistent version of the data, and replication can continue in the background. The trade-off is that the data you choose can be updated, so the two simultaneous selection operators can return different data, but in exchange for increased competition tolerance, this was good with us.

Have you had any questions in particular or just a general feeling of how well this works?