- SQL Server 2000 Behavior
- SQL Server 2005 Behavior
- Where Would This Feature Be Useful?
- More Useful Qualities
- Enabling Snapshot Isolation in SQL Server 2005
- Summary
- Further Reading
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Where Would This Feature Be Useful?
Because Snapshot Isolation is not turned on by default in SQL Server 2005, you need to evaluate and decide whether it is appropriate for you. If your application has any of these characteristics, you might consider enabling Snapshot Isolation.
Applications that Continuously Read the Recently Updated Data
Imagine that you are an e-business CEO for a company selling millions of dollars of goods over the Internet. The sales records are continuously inserted and updated into the database. As a CEO, you want a statistics page that displays various sales statistics, such as sales in each category, sales amount in dollars, and so on. Furthermore, these statistics should update every two seconds.
Without Snapshot Isolation, the statistic queries would create contention on the inserted/updated rows by trying to read them even as they are being committed into the database.
Long-Running Transactions
Consider a database where the transactions are long-running. This could be for various reasons, including the following:
An OLTP application that has to update the ERP system before a transaction is committed
A mission-critical application that needs to replicate copies of data to geologically distributed databases before the transaction is committed
Without Snapshot Isolation, your read queries would be blocked until the long transactions complete.