CODEWITHSUNDEEP
c#sqltransactionscope
Hasan Shouman
Hasan Shouman

Reputation: 2372

Transaction Scope Locks table

I am using C# 3.5 and I have a code within transaction scope that is locking the tables , how can I prevent the transaction scope from locking these tables ?

Thanks,

Upvotes: 1

Views: 3818

Answers (4)

user6349792
user6349792

Reputation:

You should use keywords to prevent table locking, such as nolock, but pay attention how you read them, for example read committed or read uncommited, as this may leave you with the so called, dirty reads.

Upvotes: 1

Hasan Shouman
Hasan Shouman

Reputation: 2372

I solved this by adding

with(nolock)

to the SQL stored procedures that are involved in the transaction.

Upvotes: 0

Frank
Frank

Reputation: 4481

Add a TransactionScope with the option RequireNew and set the IsolationLevel to ReadUncommitted:

using (var t = new TransactionScope(TransactionScopeOption.RequireNew,
new TransactionOptions { 
    IsolationLevel = IsolationLevel.ReadUncommitted 
}))
{
    // your code
}

Upvotes: 2

Edi G.
Edi G.

Reputation: 2420

The TransactionScope makes the code block transactional. The inwolved tables are locked while "code" has not been COMMITED/ROLLED BACK.

Upvotes: 2

Related Questions