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.
T1
would maintain anX
lock onitemId = 801
following step 3 so would have thoughtT2
would be blocked at step 6 and thatT1
would be fine at step 7 as it already has the required locks. What resource does T2 acquire that T1 needs?