4

I am working on analaysis and preventive measure for deadlock in application, Where I found following line of code for transaction scope:

var tranaction = new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted };

// TransactionScopeOption.Required changed to Suppress for 2 tier issue with MSDTC
using (var transactionScope = new TransactionScope(TransactionScopeOption.Suppress, tranaction))
{
   //Select entity command.
}

We are only having some select entity statement which will allow dirty read operations. I have read some resources on TransactionScopeOption but here I don't find exact TransactionScopeOption in this case or feel like we can change option to RequiredNew and let's create new transaction every time when select command executed. Need help to move forward. We are using SQL Server as datasource. If we change it to RequiredNew then what will be the performance impact since this change will be done for all select entity command in application?

Ankush Madankar
  • 3,689
  • 4
  • 40
  • 74

2 Answers2

4

If you really want to allow IsolationLevel to be ReadUncommitted, then you shouldn't use TransactionScopeOption.Suppress.

Usage of TransactionScopeOption.Suppress will not take part in any Transaction and the IsolationLevel always defaults to the Database default which is IsolationLevel.ReadCommitted in SQL server.

You need to either join with an Ambient transaction or create a new transaction to IsolationLevel.ReadUncommitted.

So you can definitely change to TransactionScopeOption.RequiredNew to allow dirty reads if that's what you want.

EDIT
Short Answer: In a way

Long Answer:
Escalation to MS DTC is the result of having more than one connections to DB in a single TransactionScope.

Different versions of SQL Server handles this escalation in different ways, SQL Server 2008 will not escalate unless both connections are open at the same time whereas previous version will always escalate on multiple connections.

Make sure you don't have multiple connections in a single TransactionScope and you should be alright. But by seeing your code, you already have multiple connections opened and MS DTC escalations are created.

Using RequiresNew means creating a new TransactionScope always, even if this encompassed in Ambient Scope. This will almost always creates deadlocks and timeout issues if not properly handled. Your best option here is to change the way you're accessing the DB to more proven recommended patterns to avoid issues.

Vishnu Prasad V
  • 1,228
  • 1
  • 8
  • 18
  • Does [`Microsoft Distributed Transaction Coordinator`](https://en.wikipedia.org/wiki/Microsoft_Distributed_Transaction_Coordinator) do play any role in it? – Ankush Madankar Jun 21 '16 at 10:39
  • @AnkushMadankar Added my thoughts to answer – Vishnu Prasad V Jun 21 '16 at 11:31
  • Can you give your thoughts on performance if we do change transaction scope to `RequiredNew`.. Since this change will done for all call where having select entity command in whole application. – Ankush Madankar Jun 22 '16 at 04:38
2

I am not sure I understand your question.

If you mean to ask if - programmatically - you can use the RequiredNew scope option for your transaction scope, then yes, this is possible:

enter image description here

Does it makes sense to use RequiredNew? Most likely not, as it creates a transaction scope always for each transaction, rather than re-using scopes. The drawback is mentioned in this SO post.

Community
  • 1
  • 1
Wicher Visser
  • 1,513
  • 12
  • 21