Problems with Deadlocking

We are getting an error with our asp.net webforms application

Error type: An error has occured.

System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException: Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Is there anywhere I can find some resources, Lightspeed notes on deadlocking or so we can work out how to fix this problem?

Thanks, Edward

In some places where the deadlock occurs we are using Aggregates.

Could we be being greedy with the Aggregates and locking tables that we shouldn't be?

Hi Edward,

Your best bet will be to determine the SQL statements which are deadlocking as this will give you an understanding on how it gets deadlocked. This could occur when you have two long running transactions updating resources in a conflicting ordering so they both hold lock sets which each other wishes to acquire. If you are using SQL Server then running a profiler trace will allow you to capture the SQL statements occurring in the database leading up to the deadlock triggering a process kill.

Could hand-written SQL cause problems with Lightspeed?(I expect so, but i wonder if you have any opinions on the matter)

Does Lightspeed tend to deadlock very often when dealing with large aggregates? (As it seems someone has written aggregates for almost every query in this class, and I think some of them could be overlapping).

Certainly is possible. I would generally expect to see no deadlocks, either using an ORM like LightSpeed or when using hand written SQL. A deadlock only comes about when two processes are competing for each others locks, so this can only come from an issue with the way in which you are accessing resources. e.g. we update table A and then table B in a transaction, meanwhile a separate process updates table B and then table A. So I dont believe querying aggregates would not be something you would expect to cause any problems, although perhaps I am mis-understanding what you are doing to calculate these.

My suggestion would be to isolate the SQL of the two statements which are triggering the deadlock(s) through a profiling tool and then you can work your way back to what issued those SQL commands as this will give you a clear understanding on what is causing it (and likely what you need to do to resolve it!). Generally I find its much easier to diagnose than speculate on these types of issues! :)

Could you please give us an example of code that would cause a deadlock?

Hi Edward,

Its not really practical to give an explicit code example however also as mentioned above the basic idea is that you have two processes which are competing for resources which each other hold.

e.g. This sequence of events would cause a deadlock:

• process 1 begins a transaction
• process 2 begins a transaction
• process 1 inserts a row into table A (locking table A for writes)
• process 2 inserts a row into table B (locking table B for writes)
• process 1 wants to insert a row into table B and waits on a write lock
• process 2 wants to insert a row into table A and waits on a write lock

The two processes are now deadlocked waiting for locks that each other holds, there is no resolution for this, SQL Server will detect this condition and arbitrarily kill one of the processes to resolve the deadlock.

You could create a code example which mimics the above but it would need a timing collision to occur (or for one or both of the processes to be long running transactions) to trigger the deadlock. Again I would strongly recommend you review what SQL statements are causing the deadlock and then you can backtrace this to your calling code to better understand how the application is getting deadlocked. You will have a very hard time trying to determine this from looking at C# code :)

Jeremy