How to solve insert/delete deadlock on non-clustered index?

Question

I got a deadlock problem and I found that it's caused by two stored procedures that is called by different threads (2 called web services).

1. Insert sp that insert data in X table.
2. Delete sp that delete data in X table.

Moreover, I got result that told me about deadlock happened in non-unique and non-clustered index of X table. Do you have any idea for solve this problem?

Update

From Read/Write deadlock, I think it error because of the following statements.

• In insert statement, it get id(clustered index) and then non-clustered index.
• In delete statment, it get non-clustered index before id.

So, I need to select id for delete statment like the following statement.

SELECT id FROM X WITH(NOLOCK) WHERE [condition]

PS. Both stored procedures are called in transaction.

Thanks,

Answer 1

Do the stored procedures modify anything, or just do reads? If the modify something, are there where clauses on the updates to that they're sufficiently granular? If you can try to update the rows in smaller batches, SQL Server is less likely to deadlock, since it will only lock small amounts of the index, instead of the index as a whole.

If it's possible, can you post the code here that's deadlocking? IF the stored procedures are too long, can you post the offending statements within them (if you know which they are)?

Answer 2

Without the deadlock info is more of a guess than a proper answer... Could be an index access order issue similar to the read-write deadlock.

Answer 3

It could be that the select queries are the actual problem, especially if they are the same tables in both stored procedures, but in a different order. It's important to remember that reading from a table will create (shared) locks. You might want to read up on lock types.

The same can happen at the index level, as Remus posted about. The article he linked offers a good explanation, but unfortunately no one hit wonder solution, because there isn't a single best solution for each case.

I'm not exactly an expert in this field myself really, but using lock hints, you may be able to ensure the same resources get locked in the same order, preventing a deadlock. You will probably need more information from your testers to effectively solve this though.

Answer 4

We'd have to see some kind of code... you mention a transaction; what isolation level is it at? One thing to try is adding the (UPDLOCK) hint to any query that you use to find the row (or check existence); so you'll take out a write lock (rather than a read lock) from the start.

When contested, this should then cause (very brief) blocking rather than a deadlock.

Answer 5

The quick way to get your application back doing what it's supposed to is to detect the deadlock error (1205) and rerun the transaction. Code for this can be found in the "TRY...CATCH" section of Books Online.

If you're deleting and inserting, then that's affecting the clustered index, and every non-clustered index on the table also needs to have an insert/delete. So it's definitely very possible for deadlocks to occur. I would start by looking at what your clustered indexes are - try having a surrogate key for your clustered index, for example.

Unfortunately it's almost impossible to completely solve your deadlock problem without more information.

Rob