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?