How to use multiple, nested, transactionscopes?

Question

I am doing a few operation in linq2sql that needs to be run in a transaction. However, some of the methods I use inside of the transaction also uses linq2sql and runs inside a transaction of their own (the inner transaction is run within a stored procedure). This gives me the exception

[TransactionInDoubtException: The transaction is in doubt.]
   System.Transactions.TransactionStateInDoubt.EndCommit(InternalTransaction tx) +76
with the inner exception
[SqlException (0x80131904): There is already an open DataReader associated with this Command which must be closed first.]

if I use MultipleActiveResultSets for SQL Server, I instead get the exception

[SqlException (0x80131904): The transaction operation cannot be performed because there are pending requests working on this transaction.]

Does anyone have experience from working with linq2sql and transactionscopes in this way?

Answer 1

I know that we have experienced this problem on the project I´m currently working on and I know it had something to do with LinqToSql generating the dbml file the wrong way when working with a sproc. We had to do it manually to get it working. LinqToSql apparently returned ISingeResult from the sproc and that generated the error.

I wasn´t the one who corrected the error but I know it had something to do with this.

More info: http://www.west-wind.com/weblog/posts/246222.aspx

Answer 2

This was a "face-palm" moment for me, but considering I was seeing this exact behavior, and it didn't immediately hit me, I figured I'd go ahead and post this as a possibility:

I was seeing this behavior when I had a TransactionScope set for ReadUncommitted:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, 
       new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted })

and my Linq to SQL was calling a stored procedure to return the results of the proc. The face-palm is that within the proc itself one can specify the WITH (NOLOCK) SQL hint, so there is no need to wrap the Linq to SQL query in the ReadUncommitted transaction scope. (At least in my case)

Answer 3

I guess, you are trying to read some data and modify it on the fly (while the reading is still in progress).
In this case, the easiest option is to read all the data first (for instance, using IEnumerable<>.ToList() extension method), then perform all the operations on the data.