5

We're currently encountering a problem with out ASP.NET Application that results in deadlocks on the SQL Server database. A bit of background information I can provide is that we rely heavy on AJAX callbacks.

The problem occurs on a page with various "fields" that contain information about a resource. The user can click on each field to turn it into "edit mode" which allows the change of data. Basically what this does is open a transaction, save the item, update the html and close the transaction.

The user can change the data and then save the changes again with a transaction. We also have buttons that can be pressed to trigger scripts (C# dynamic code) which run a new transaction.

Below is a small transcript of two threads running in such a scenario and what they're doing.

* Thread 1 * 

Step 1. SET TRANSACTION ISOLATION LEVEL read committed 
Step 2. begin transaction 
Step 3. update Person set Name = 'Person1' where itemId  = 801 
Step 7. update Person set Name = 'Person3' where itemId  = 801 
Step x. commit transaction

* Thread 2 * 

Step 4. SET TRANSACTION ISOLATION LEVEL read committed 
Step 5. begin transaction 
Step 6. update Person set Name = 'Person2' where itemId  = 801 
Step x. commit transaction

After executing step 7 you will receive a deadlock.

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

We're currently investigating how deadlocks are working in SQL Server and how we can prevent them from our side, but any input/advice on this to do/check is welcome.

If additional information is required, I can add it as long as it's not violating our rules here at work.

Improve this question
4
  • Have you captured the deadlock graph? Not sure the example in your OP would cause a deadlock. T1 would maintain an X lock on itemId = 801 following step 3 so would have thought T2 would be blocked at step 6 and that T1 would be fine at step 7 as it already has the required locks. What resource does T2 acquire that T1 needs?
    – Martin Smith
    Feb 3, 2012 at 10:57
  • One of our devs looked at the graphs, and we currently replaced the indexes on the tables with clustered indexes. Deadlocks seem to be gone for the moment.
    – codingbunny
    Feb 3, 2012 at 11:41
  • I don't think this would be a true deadlock, just blocking.
    – Thomas Stringer
    Mar 5, 2012 at 14:03
  • It's definitly a deadlock, as the log clearly states that thread X has been aborted as victim of a deadlock on the server.
    – codingbunny
    Mar 6, 2012 at 7:42

1 Answer 1

Reset to default
1

How to solve deadlocks depends entirely on your data requirements.

Are you content with only applying the last update to the data ?
In that case, don't use a transaction at all - this will happen automatically.

Does every user have to see the real updates being applied for only that user?
This is what you are currently attempting, including (ugh) keeping a transaction open while waiting on a web page, of all things.

I would start with investigating the actual need to do this very bad thing...

Regardless of the ultimate goal, explicit transactions should be used sparsely, wisely, and with good reason.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.