CODEWITHSUNDEEP
c#transactionscope
KeDaR
KeDaR

Reputation: 81

TransactionScope deadlocked issue in concurrency

I have two different databases on two different servers, and I have used a TransactionScope. I'm using TransactionScope for the first time.

But in concurrency while updating Table1 I'm getting an error

Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Checked TransactionScope related articles on net

option.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
option.Timeout = TimeSpan.FromMinutes(1);

using (_objTransactionScope = new TransactionScope(TransactionScopeOption.Required, option))
{
    //..Open Connection
    //..Process the Document
    //..Update Additional information in Table2 about document(Delete/Insert)   
    //..Update Location Information in Table3 about document(Delete/Insert)
    //..Delete the Document in Table4 in Differant Database on Differant Server(WCF Service)
    //..Update the Checksum of the Document and Metadata in Table1(Deadlock Error in concurrency)
    //..Delete Lock Entry From Table5   
    //..Close Connection
    //..Commit Transaction Scope
}

Upvotes: 1

Views: 1627

Answers (1)

Morteza Jangjoo
Morteza Jangjoo

Reputation: 1790

Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

Solution: Deadlock priority can be set by user. In other words, user can choose which process should stop to allow other process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process which is running the for shorter period then other process.

read this article

Upvotes: 1

Related Questions