Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
1  
+1 But keep in mind that turning on SNAPSHOT isolation will cause much more activity on TempDB. Be sure to monitor that appropriately. –  Matt M Oct 11 '11 at 12:55
1  
@gbn this is not true. Enabling snapshot has no effect other than to make it available for use. Enabling RCSI changes all read committed activity to RCSI, which is different and separate to snapshot. If the OP cannot change the application generating the select, he can't force usage of snapshot, only RCSI. –  Mark Storey-Smith Oct 11 '11 at 13:01
    
@Mark Storey-Smith: correct, updated –  gbn Oct 11 '11 at 13:07
    
Just to clarify and confirm - if I use ALTER DATABASE that will enable READ_COMMITTED_SNAPSHOT for all select statements, regardless of their source? edit just read this is the case. I'll keep a sharp eye on tempdb. –  growse Oct 11 '11 at 13:19
    
@growse: updated answer –  gbn Oct 11 '11 at 13:28
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.