Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '12 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. –  NekoNova Feb 3 '12 at 11:41
    
I don't think this would be a true deadlock, just blocking. –  Thomas Stringer Mar 5 '12 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. –  NekoNova Mar 6 '12 at 7:42
add comment

migrated from serverfault.com Feb 3 '12 at 5:24

This question came from our site for professional system and network administrators.

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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