How do I specify the database isolation level to be used, at a high level?

Question

I am using TransactionScopes at a high level to wrap some high level code that makes a number of connections to the database.

One of the functions I call is a generic read that is used for read only functions where a read uncommitted isolation level is needed. But in some cases it is used as part of large update operation and a read committed would be more suitable.

At the highest level where I am calling my functions inside a transaction scope, should I be able to set the Isolation Level for the transaction?

Answer 1

You can set the isolation level on the TransactionScope itself. Is that what you mean?

using (var txn = new TransactionScope(
    TransactionScopeOption.Required, 
    new TransactionOptions
    {
        IsolationLevel = IsolationLevel.ReadUncommitted
    }
))
{
    // Your LINQ to SQL query goes here
}

(Code stolen from my blog post here.)

Answer 2

Hi,

You can define the Transaction Isolation level at both the connection level and the statement/transaction level.

If I understand your scenario correctly, I would consider adjusting the Transaction Isolation level prior to your specific update query only, if the majority of the other activity within the given connection is going to be read only activity.

Keep in mind that the default isolation level for SQL Server is Read Committed, so if you set a different isolation level at the connection level then you will need to swith to Read Commited at the transaction/statement level prior to your update.

One thing to consider of course is to leave the default Isolation Level as is. Is there a specific reason why you feel that Read Committed is not appropriate for your generic read activity?

Make sense? Let me know if you need further clarification.

Cheers, John

Updated Based on comments.

Fair enough, if you are developing a high end OLTP database, you may want to look at a SQL Server technology/principle called row versioning. This will allow you to perform reads of a version of a record so to speak. There is overhead on the tempdb when using such techniques but if your are using sufficient hardware then it may be appropriate. The following white paper is an excellent guide to SQL Server Row Versioning/Snapshot Isolation

http://msdn.microsoft.com/en-us/library/ms345124.aspx