How to use multiple, nested transactions?

Question

I perform several operations in linq2sql that need to be run in a transaction. However, some of the methods that I use inside the transaction also use linq2sql and are executed inside the own transaction (the internal transaction is executed in a stored procedure). It gives me an 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 get an exception instead

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

Does anyone have experience with linq2sql and transactions this way?

Answer 1

It was a moment of "palm" for me, but, given that I saw this exact behavior, and it did not immediately hit me, I thought that I would continue and publish this as an opportunity:

I saw this behavior when I had a TransactionScope for ReadUncommitted:

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

and my Linq to SQL called a stored procedure to return the results of proc. The palm of your hand is that inside the proc itself you can specify the WITH (NOLOCK) SQL prompt, so there is no need to transfer the Linq query into SQL to the ReadUnCommitted transaction area. (At least in my case)

Answer 2

I think you are trying to read some data and change it on the fly (while reading is still ongoing).
In this case, the easiest way is to first read all the data (for example, using the IEnumerable <>. ToList () extension method), and then perform all data operations.

Answer 3

I know that we encountered this problem in the project that I am currently working on, and I know that this is due to the fact that LinqToSql does not generate the dbml file correctly when working with sproc. We had to do it manually to make it work. LinqToSql appears to have returned ISingeResult from sproc and generated an error.

I was not the one to fix the error, but I know this has something to do with it.

Additional information: http://www.west-wind.com/weblog/posts/246222.aspx

Answer 4

I know this is an old thread, but this is the highest google level for this error, so I thought it was worth adding my answer. In my case, I used the Entity Framework, so I'm not even sure if this is applicable, but even so, some people using EF are more likely to come here. In my case, this was because I called the stored process and did nothing with the returned rows. Since EF did not read the lines back, it was an "open operation". My solution was to simply add First () at the end of each procedure call:

 myentities.CallMyStoredProc(A, B, C).First();