I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select statements from this table.
Under default isolation settings (READ COMMITTED I think?), T1 blocks any select statements from running until the transaction commits or is rolled back.
What I'd like to see is for the select statements to function on consistent data even while the transaction is underway. I believe SNAPSHOT isolation can help, but am not sure if I'm going in the right direction. Would this be the best isolation level for this application?
Secondly, I don't have any control over the process that is calling the select statements, but I do have control over the .NET application that calls T1. Would any isolation level changes be required on both the select statements and T1, or would it be sufficient to mark just T1 as having a different isolation level?
Best Answer
In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.
This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.