SQL Server - what isolation level for non-blocking select statements?

Question

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?

Answer 1

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.

Answer 2

Correct, use SNAPSHOT isolation to get consistent, commited data from before the transaction started.

The READ UNCOMMITTED isolation (aka NOLOCK hint) will read dirtz, inconsistent data

When you enable SNAPSHOT isolation, then it takes effect for all SELECTs going forward. You run ALTER DATABASE with READ_COMMITTED_SNAPSHOT in this case

Edit: added link +quote of ALTER DATABASE (my bold)

Enables Read-Committed Snapshot option at the database level. When it is enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking. When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement.

And from Using Snapshot Isolation (my bold)

The READ_COMMITTED_SNAPSHOT database option determines the behavior of the default READ COMMITTED isolation level when snapshot isolation is enabled in a database. If you do not explicitly specify READ_COMMITTED_SNAPSHOT ON, READ COMMITTED is applied to all implicit transactions. This produces the same behaviour as setting READ_COMMITTED_SNAPSHOT OFF (the default). When READ_COMMITTED_SNAPSHOT OFF is in effect, the Database Engine uses shared locks to enforce the default isolation level. If you set the READ_COMMITTED_SNAPSHOT database option to ON, the database engine uses row versioning and snapshot isolation as the default, instead of using locks to protect the data.

So, yes.

Enabling RCSI will allows reads to get consistent data and not be blocked by writers whi will continue using Read Committed

Answer 3

I'd suggest you read the following question and its answers: Database locking issues?.

Finding the right isolation level to use at the db level is the fastest thing that you can do right now to help you fix this issue, because it's difficult now to change all applications that touch the database and change their code. Since you said "I don't have any control over the process that is calling the select statements", the fastest answer would be to switch the db to Read Committed Snapshot isolation level, so you won't touch the reading queries. Otherwise you'd need to use Snapshot isolation level for the sessions that read data during your big transactions.

More details here about choosing the right one: Choosing Row Versioning-based Isolation Levels.