Isolation Level Escalation Bug and Workarounds

Isolation Level Escalation Bug and Workarounds

Old forum URL: forums.lhotka.net/forums/t/6564.aspx


tlong posted on Thursday, March 05, 2009

We found this problem when profiling our production SQL 2005 box and trying to figure out why even simple select statements from our CSLA app were acquiring extra locks versus running the select in a query window.  (Disclaimer: We are still running 2.1.4 but it would appear that this problem will exist in newer CSLA versions since the TransactionlDataPortal pattern is the same.)

When using the Trasactional attribute / TransactionScope with a BO method, the isolation level on the connection is raised to Serializable by default.  But when the connection is returned to the ADO.Net pool, the isolation level is not reset back to the default of Read Committed.  And when ADO.Net calls sp_reset_connection before re-using the connection, SQL 2005 doesn't change the isolation level back to the db default, apparently this behavior is by design.   FYI, in most cases the SQL isolation level default is left at the out-of-the-box setting of Read Committed.

The net result is that subsequent re-users of the same connection will have an escalated isolation level, which for even simple non-transactional selects will make the server acquire more locks.  Maybe you want Serializable all the time, maybe not.  But in the current CSLA design, you get it always for transactional and sometimes for non-transactional, which seems too arbitrary.

Workaround #1 - We worked around this by altering the TransactionalDataPortal to always use Read Committed (since we didn't need the stricter Serializable default) like this:

Dim tranopts As TransactionOptions

tranopts.IsolationLevel = IsolationLevel.ReadCommitted

Using tr As New TransactionScope(TransactionScopeOption.Required, tranopts)

Workaround #2 - Another option would have been to always begin and commit an empty transaction before disposing of the connection, but this has to be done religiously in each Transactional BO method.  It looks like this:

cn.BeginTransaction().Commit()

or if you want to reset to a specific isolation level:

cn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted).Commit()

This isn't really a CSLA bug as much as an ADO.Net/SQL bug, but it would nice if the framework could somehow handle this.  Even better would be to give us the ability to set default isolation levels for transactional and non-transactional methods. 

JoeFallon1 replied on Friday, March 06, 2009

Nice catch!

I and the developers on my team spent a lot of time last year figuring this out and then fixing the application to stop using Serializable as the default Isolation Level and use Read Committed. We are using normal ADO.Net but when we built our templates we used the value from the CSLA 1.x book which was Serializable. It was only later when we profiled the app that we noticed the extra locks and perf penalty associated with it. The upside is obviously more reliable transactions. But the downside outweighs that benefit in our simple cases.

I agree that the TransactionalDataportal should at a minimum use Read Committed. But even better would be a way to set the isolation level.

Joe

 

ajj3085 replied on Sunday, March 08, 2009

A way to set the isolation level would be ok, but not changing the default at this point.  If you need something different now, it should be only three lines of code to setup the TransactionScope yourself, right?

tlong replied on Wednesday, March 11, 2009

Roll-your-own doesn't solve the problem.

Even if you do the TransactionScope manually on your Save/Update methods, if you choose an isolation level other than the default of ReadCommitted, you will still have the issue with the isolation on non-transactional methods being potentially incorrect. 

Add in the fact that the isolation escalation will only happen for some of your recycled connections, and you'll have a heck of a time finding and debugging this problem.

I would venture to guess that most CSLA/ADO.Net/SQL apps suffer this problem, but the developers and users are completely unaware of the issue.

RockfordLhotka replied on Wednesday, March 11, 2009

tlong, I don't understand what you just said.

Are you saying that if you write code in a root object that creates its own TransactionScope with a specific isolation level, that this won't work right?

tlong replied on Wednesday, March 11, 2009

Correct, it doesn't fix the problem.  A better description would be "isolation level persistance" rather than escalation.

If the TransactionScope in your root object uses an isolation level other than the default (let's say Serializable) that will persist on the connection even when it is returned to the ADO.Net connection pool.  Any subsequent method (transactional or not) using that recycled connection from the pool will still have the Serializable isolation level, rather than the default of ReadCommitted for a new, unused connection.

 

 

 

tlong replied on Wednesday, March 11, 2009

I found this after my original post.  Perhaps a better explanation of the problem:

http://www.softlion.com/blogs/post/2008/11/21/SQL-Server-transaction-isolation-level-and-NET-sql-library-pitfall-bug-!.aspx

RockfordLhotka replied on Wednesday, March 11, 2009

So this is a bug with SqlConnection, not TransactionScope. Which means that if I do enhance CSLA so you can specify the isolation level on the Transactional attribute, anyone using SQL Server would need to do this bug-fix/workaround, or I'd need to make the data portal (or ConnectionManager) check to see if it is a SqlConnection object and do this workaround.

Interesting, thanks for pointing this out.

tlong replied on Friday, March 13, 2009

You're right, being able to set isolation level for the Transactional attribute doesn't really address the problem, unless you happen to choose to use the default isolation level of ReadCommitted.  We have been looking into adding a new "TransactionalReadCommitted" attribute to the DataPortal to get the behavior we want. 

I'm not sure how many developers want/need/care to set isolation levels and they can always do it manually with their own TransactionScope.

Also, after thinking about it, I don't think the framework should deal with the SQLConnection isolation level pesistance bug.  It's not needed for anyone who's not running SQLServer and it's very possible Microsoft will address the bug in the future.

Turntwo replied on Thursday, October 14, 2010

Sure - they can do it manually in each data access method - but it would be better to have it configurable in one place (so those of us using SQL Server can set it to Read Commited instead of Serializable across the application - and still use the nifty TransactionScope attribute). 

I agree that the SQL Persistance bug should be handled separately however.

Copyright (c) Marimer LLC