Introduction
We often use the .NET Framework TransactionScope
object
to manage/handle database transactions. Often we create
an instance of TransactionScope
object like the following:
var scope = new
TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 1, 0))
Or:
var scope = new TransactionScope()
or use some other overloaded constructor. Everything works fine. But
the story begins when a timeout exception message comes.
Details
Actually it is a deadlock exception. So anyone can think it happened for concurrent user access for same resource (database row) and it might be a very rare case and ignore it. But unfortunately it comes again and again and you cannot overlook it. I start rnd for understanding the reason why the deadlock
happens again and again. Why is it happening? First I start investigating the
TransactionScope
object. I tried to find if there are any issues in
that component. Finally I got my answer. The investigating result is the TransactionScope
object's default Isolation Level is Serializable. That is the culprit for raising such
a deadlock exception for some scenarios. If we need to fix that we should use other isolation levels like
ReadCommitted
, etc. As we know SQL Server uses ReadCommitted
as the default Isolation Level. It is recommended to use that for general purposes.
How to Fix
Create a factory method in
the business layer (considering transaction will be managed by the Business Layer).
The Factory method body is as follows:
public static TransactionScope CreateTransactionScope()
{
var transactionOptions = new TransactionOptions
{
IsolationLevel = IsolationLevel.ReadCommitted,
Timeout = net TimeSpan(0,0,0,0,10,0) };
return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
}
Use of Factory method
Instead of creating
a TransactionScope
object with default constructor like the following
using (var scope = new
TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 1, 0)))
we can create that object with the help of a factory method and it will fulfill our purpose.
using (var scope = CreateTransactionScope() ) {
saveProjectEntity();
scope.Complete();
}