Got trouble with calling SaveChanges from inside TransactionScope

Question

Hi All!

I have a sample app that queries records from a table and processes them concurrently on different threads using the following code (probably there is a better strategy):

                using (var scope = new TransactionScope(TransactionScopeOption.Required,
                    new TransactionOptions() { Timeout = new TimeSpan(0, 5, 0), IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
                {
                    using (MyContext Db = new MyContext())
                    {
                        var existing_enitities = from MyEntity p in Db.MyEntities
                                               where p.Id = <some id>
                                               select p;

                        Proxy existing_entity = existing_enitities.First();

 			//...modify existing_entity fields
                    
                        Db.SaveChanges();
                    }

                    scope.Complete();
                }

In some rare cases SaveChanges throws exception:

$exception    {"The underlying provider failed on Open."}    System.Exception {System.Data.EntityException}
-        _innerException    {"The operation is not valid for the state of the transaction."}    System.Exception {System.Transactions.TransactionException}
-        _innerException    {"Transaction Timeout"}    System.Exception {System.TimeoutException}


(If I replace IsolationLevel.ReadCommitted with IsolationLevel.RepeatableRead I will get deadlock.)

The question is what can cause Transaction Timeout and what kind of transaction (what isolation level) SaveChanges creates?

Thanks.

Answer 1

Hello,

When using TransactionScope, SaveChanges will not create its own transaction but will use the ambient transaction and the automatic enlistment mechanisms provided by the underlying ADO.NET Provider. Also, by default, ObjectContext (from which MyContext derives) will open and close the database connection as needed. In the case of SQL Server databases, depending of the version you are connecting to, this will end up using lightweight transactions or two-phase commit transactions managed by the Microsoft Distributed Transaction Coordinator.

I don’t see anything wrong with the code included in your post, so we will need more information to investigate:

1. What database server are you using?

2. Would it be possible for you to provide a small standalone repro?

Thanks,
Diego

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Hello, Diego,

thank you for your answer, you provided useful information, but after doing some experimentation I realized that it is not a concurrency problem and SaveChanges() fails each time when the transaction exceeds 10 minutes independently of TransactionOptions.Timeout I specified.

I executed the following code on a single thread and got the same exception (added Thread.Sleep(...) ):

                using (var scope = new TransactionScope(TransactionScopeOption.Required,
                    new TransactionOptions() { Timeout = new TimeSpan(0, 30, 0), IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
                {
                    using (MyContext Db = new MyContext())
                    {
                        var existing_enitities = from MyEntity p in Db.MyEntities
                                               where p.Id = <some id>
                                               select p;

                        Proxy existing_entity = existing_enitities.First();

 			//...modify existing_entity fields (it cat take 1 or 2 minute)
                    
                        Thread.Sleep(600 * 1000 + 100 * 1000);

                        Db.SaveChanges();
                    }

                    scope.Complete();
                }

so looks like there should be some further option where this 10 minutes are specified... or something else could be wrong.

I use SQL Server Express 2008.

Thanks.