[RESOLVED] SQL Server: deadlocks caused by DELETE statement

[visualAd]

I am looking into a deadlock problem on an application server which is affecting a particular part of the system.

The part of the system in question is a scoring engine which runs a few dozen stored procedures and carries out multiple update / insert statements across several tables. For various reasons the quality of this T-SQL code is questionable as none of it is error trapped and transactions are not used; hence if an error does occur you can get a half scored transactions.

The text of the error message is below. The stored procedure name varies but always references a stored proc in the scoring part of the system. I have been able to replicate the error by running the top level scoring procedure across two discrete sets of transactions simultaneously.

Running a trace when the error occurs reveals that the deadlock is occurring on the following statement which is common to all the test procedures:

DELETE FROM dbo.TestResults WHERE (TestID = @TestID AND GroupID = 1 AND TransactionID = @TransactionID AND Config= @config)

In each case the transaction ID and test ID are different so different rows are targeted by the operation. This does not seem like something which is vulnerable to a deadlock situation.

Three examples of the error:

Msg 1205, Level 13, State 52, Procedure spGroup2, Line 42
Transaction (Process ID 67) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Msg 1205, Level 13, State 52, Procedure spGroup3, Line 42
Transaction (Process ID 68) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Msg 1205, Level 13, State 52, Procedure spTest10, Line 92
Transaction (Process ID 68) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The SQLProfiler deadlock graph event shows that the deadlock might be occurring at the page level. Again, I am not sure why this would be the case as different rows from different parts of the table are being deleted . Another thing I am looking into is parallelism which I hear can cause problems on OLTP type systems; but I am not sure if switching this off globally would have an impact on performance.

I still need to find out what is causing the problem an come up with a workaround which doesn't require re-writing the whole lot if possible. Sigh, such is workplace politics. Is there anything I am missing here? Anything else I could try?

[GaryMazzone]

Turning off parellism can make a difference. Does the plan show that it is being used? You can also trun off using a MAXDOP=1 hint on the query to test.

[visualAd]

Thanks Gary, I did look into using the MADOP hint, however, there are some 30 test procedures which will require changing. It would no doubt trigger a system test which unfortunately equates to time an money which the PM would be unwilling to spend.

Something I did do however was analyse the indexes on the TestResults table. The only index was on the primary key. Given there are 100,000 rows in the transactions table and 20 tests results per page; I was surprised there were no indexes other than the primary key. I went ahead and created an index on the TransactionID field in the TestResults table and re-ran the test queries; as yet, no deadlocks.

I am not sure this solves the underlying problem (i.e. the lack of error handling in the test procedures). And I am not sure how an index (which increases the complexity of the table especially when there are multiple inserts and deleted being run simultaneously) is preventing these deadlocks.

Any ideas?

[GaryMazzone]

The index maybe (probably) is allowing an index scan or hopefully an index seek on the table. That will allow for less pages/rows geing required to be lock for the search of the record to delete. That will help to reduce the deadlocks since you will not be required to do a table scan on that table.