How can i use System.Transactions.TransactionScope with IsolathionLevel.Snapshot

Question

i try following:

                using (var sc = new TransactionScope(
                    TransactionScopeOption.RequiresNew,
                    new TransactionOptions
                        {
                            IsolationLevel = IsolationLevel.Snapshot,
                        }))
                {
                    try
                    {
                        Request request =
                            repository.Requests
                            .Where(r => r.RequestId == 1)
                            .FirstOrDefault();
                        request.Status = 1;
                        repository.SaveChanges();
                        sc.Complete();
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }

but when stack come to complete i receive an InvalidOperationException - "Transactions with IsolationLevel Snapshot cannot be promoted". 

if change to following all will be fine:

            using (var repository = new Repository())
            {
                repository.Connection.Open();
                using (EntityTransaction et = repository.Connection.BeginTransaction(System.Data.IsolationLevel.Snapshot))
                {
                    try
                    {
                        Request request =
                            repository.Requests
                            .Where(r => r.RequestId == 1)
                            .FirstOrDefault();
                        request.Status = 1;
                        repository.SaveChanges();
                        et.Commit();
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }

Is so important difference between TransactionScope and EntityTransaction. Or how i can take TransactionScope with Snapshot iso?

Thanks..

Answer 1

Sound like the Transaction specified in EF's SaveChanges causes the transactions to be promoted to distributed transactions, requiring the DTC to use tx. Have you made sure that MSDTC service is on and that tx support is enabled?

Answer 2

Yes, this is being promoted to a distributed transaction, but rather than enabling distributed transactions I think you would be better off changing your code slightly to avoid the promotion.  I won't try to explain here why SqlClient behaves this way, but for the moment just take it on faith that if you have a transaction from a TransactionScope and you open a connection execute a command, close it and then open again and execute another command, that SqlClient will generally end up promoting to a distributed transaction.  With SQL Server 2008 this is less likely to happen, but in any case you can avoid it if you just make sure that you keep the connection open the whole time rather than closing and reopening it in between the two operations.  In most situations you don't have a transaction scope going so it's actually better for the EF to keep the connection closed most of the time and just open it when you have an operation going, but in a case like this you really want to keep it open.  If you explicitly open the connection, then the EF won't close it unless you explicitly do it or you dispose of the context.  So the simplest fix to this situation is to change your first example and add a command to open the connection before your first operation.  Something like this:

                using (var sc = new TransactionScope(
                    TransactionScopeOption.RequiresNew,
                    new TransactionOptions
                        {
                            IsolationLevel = IsolationLevel.Snapshot,
                        }))
                {
                    try
                    {
                        repository.Connection.Open();
                        Request request =
                            repository.Requests
                            .Where(r => r.RequestId == 1)
                            .FirstOrDefault();
                        request.Status = 1;
                        repository.SaveChanges();
                        sc.Complete();
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }

You might notice that your second example also explicitly opened the connection which is what made it work as well.

- Danny

---

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

Answer 3

Thanks very very match.. i yet experimently resolved what connection must be opened before first operation begin (i create TransactionScope after open connection at once) and it works!!!  and as a result is:

        

    public sealed class TransactionBound : IDisposable
    {
        private ObjectContext repository;
        private TransactionScope scope;
        private bool save = false;
        private bool close = false;

        public TransactionBound(ObjectContext repository)
            : this(repository, IsolationLevel.Snapshot)
        {
        }

        public TransactionBound(ObjectContext repository, IsolationLevel isolationLevel)
        {
            this.repository = repository;
            if (this.repository.Connection.State != System.Data.ConnectionState.Open)
            {
                this.close = true;
                this.repository.Connection.Open();
            }

            this.scope = new TransactionScope(
                TransactionScopeOption.RequiresNew,
                new TransactionOptions
                {
                    IsolationLevel = isolationLevel,
                });
        }

        public void Commit()
        {
            this.save = true;
        }

        public void Dispose()
        {
            if (this.save)
            {
                this.repository.SaveChanges();
                this.scope.Complete();
                this.scope.Dispose();

                if (this.close)
                {
                    this.repository.Connection.Close();
                }
            }
        }
    }

and using:

            using (var repository = new Repository())
            {
                using (var t = new TransactionBound(repository))<br/>                {
                    Request request =
                        repository.Requests
                        .Where(r => r.RequestId == 1)
                        .FirstOrDefault();
                    request.Status = 1;
                    t.Commit();
                }
            }

Once more thank..