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.
Best Answer
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.