TransactionScope & TSQL Transactions

.NETPhreak

I was able to discover that if we use TransactionScope with a particular IsolationLevel (ReadCommitted, for example), all the child scopes need to use the same IsolationLevel, or else an ArgumentException will be thrown. Now the issue that keeps boggling me is what will happen if someone else (another developer on my team) writes a TSQL procedure with a different IsolationLevel, and this SPROC will be called from within my TransactionScope Will I still receive an ArgumentException, will the TransactionScope behave in some unexpected manner, or will TransactionScope behave as advertised (i.e. it will promote itself to a distributed transaction even if the IsolationLevel in the TSQL SPROC is set to something else, say SERIALIZE, for example)

Thanks in advance, guys!

Florin Lazar - MSFT

TransactionScope will pass the isolation level to SQL Server only as a suggestion. It depends on each database server implementation on what will happen in these type of situations (ignore the isolation level, try to follow it only when possible or return error when mismatched). Also see http://blogs.msdn.com/florinlazar/archive/2003/12/10/42686.aspx

Cheers!

.NETPhreak

Florin Lazar - MSFT wrote:

TransactionScope will pass the isolation level to SQL Server only as a suggestion. It depends on each database server implementation on what will happen in these type of situations (ignore the isolation level, try to follow it only when possible or return error when mismatched). Also see http://blogs.msdn.com/florinlazar/archive/2003/12/10/42686.aspx Cheers!

Thanks for the reply, Florin. In my opinion, I think this is definitely a show-stopper. I would have hoped to mix transaction levels, especially for scenarios where multiple SPROCs are involved. For example, in .NET I could set my IsolationLevel to ReadCommitted, while in SQL Server I could set it to SERIALIZE for a SPROC that makes changes to the DB. To me that would increase the overall scalability, and at the same time maintain the integrity of the data in a limited manner. Anyway, I digress.

Do you think we can hope for things to change in a future version of .NET Thanks again!

Florin Lazar - MSFT

Mixing isolation levels is not a good practice.

Imagine the ambient transaction having the isolation level set to Serializable and imagine that a new piece of code (for instance in a new library added to the project) is called under this transaction. If that new piece of code is using TransactionScope with isolation level set to ReadUncommitted, then that meant that it expects a certain level of performance and a certain level of locking. If we would allow the Serializable transaction to get in, those expectations might not be met and this behavior can lead to performance degradation and more than that, possible deadlocks. I hope this helps.

.NETPhreak

I see your point, Florin. I did not want to sound as if I was planning to abuse the "transaction" facilities provided to us by the .NET framework. I guess it makes sense to go from a lower transaction (ReadCommitted, for example) level to a higher one (Serialize, for example) and not vice versa

Also, this discussion also brings another issue to my mind: why is XACT_ABORT set to OFF by default in SQL Server I have to explicitly set it to ON within my SQL SPROCs and raise exceptions - I have observed on more than one occasion, that a transaction timed out, but SQL Server did not raise an exception because XACT_ABORT was OFF. Thanks for the quick replies :)

Florin Lazar - MSFT

There are problems with both cases: going to higher or going to a lower isolation level.

As for the XACT_ABORT default, please follow up with the SQL Server team on that. They have a forum at http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=85&SiteID=1.

Cheers!