Contented added from a mail conversion

We use dotnet framework TransactionScope object for manage/handle database Transaction.

Often we create instance of Transactionscope object like the followings

1) var scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 1, 0))

      Or

2) var scope = new TransactionScope()

      Or

use some other overloaded constructor.

Everything is working fine.

But story begins when a few days ago production error message came (actually it was came before also and as usual ignore).

Actually the error was a Deadlock exception. So I thought that happened for concurrent user access for same resource(database record) and thought it is a very Rare case to reproduce and ignore it. But unfortunately again it came(error message details in the last of this mail) And assigned to me for investigate (I am the responsible dev for fixing production issues).

I start googling for understand the reason why the deadlock again and again. Why it is happing? First I start investigating from TransactionScope object.

Any issues are in this object or not.

Finally I got my answer. The investigating result is

TransactionScope object's default ISOLationLevel is Serializable. That is the culprit for Raising deadlock. If we need to fix that we should use others isolation level.

We can use others as our requirement.SQL Server use default Isolation is ReadCommitted. It is recommended To use that for general purpose.

Reference Url:

http://blogs.msdn.com/b/dbrowne/archive/2010/05/21/using-new-transactionscope-considered-harmful.aspx

http://msdn.microsoft.com/en-us/library/ms173763.aspx

http://www.sql-server-performance.com/2007/isolation-levels-2005

How to fix:

Create a method in by business layer(Consider we should manage our transaction from BusinessLayer)

Method Body is:

        public static TransactionScope CreateTransactionScope()

        {

            var transactionOptions = new TransactionOptions

                                         {

                                             IsolationLevel = IsolationLevel.ReadCommitted,

                                             Timeout = net TimeSpan(0,0,0,0,10,0) //assume 10 min is the timeout time

                                         };

            return new TransactionScope(TransactionScopeOption.Required, transactionOptions);

        }

Use of this method:

        //using (var scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 1, 0)))[Old implemnetation]

        using (var scope =  CreateTransactionScope() )   {

            saveProjectEntity();

            scope.Complete();

        }

 

 

Comments

rokon's picture

This is really a big one. Thanks Habib vi for sharing this. This was very tough to find out the cause as it was a rare case and we could not reproduce the issue in local environment.

Add new comment