How to decide to insert / remove a dead end in a non-clustered index?

Question

I have a lock problem, and I found that it was called by two stored procedures that are called by different threads (2 are called web services).

• Insert sps that insert data into table X.
• Delete sps that delete data in table X.

In addition, I got a result that told me about the deadlock that occurred in the unhistorical and non-clustered index of table X. Do you have an idea to solve this problem?

Update

From the Read / Write Deadlock , I think this is a mistake due to the following statements.

• In the insert statement, it gets the identifier (cluster index), and then the non-clustered index.
• In deletion status, it receives a non-clustered index before the identifier.

So, I need to select id to remove statment, as the next statement.

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

PS. Both stored procedures are called in a transaction.

Thanks,

Answer 1

We will need to see some code ... you mentioned a transaction; at what level of isolation? It's one thing to add a tooltip (UPDLOCK)to any query that you use to search for a string (or check for existence); therefore, from the very beginning, you will choose write lock (rather than read lock).

In the case of a dispute, this should cause a (very short) lock, not a dead end.

Answer 2

- ? - , , ? , SQL Server , , .

, , ? , ( , )?

Answer 3

, ... , deadlock.

Answer 4

, - , , . , () . , .

, Remus. , , , , , -, .

, , , . , , .

Answer 5

, , , (1205) . "TRY... CATCH" .

If you delete and insert, then this affects the clustered index, and each non-clustered index in the table must also have an insert / delete. So it is definitely very possible for deadlocks. I would start by looking at your clustered indexes - for example, try using a surrogate key for your clustered index.

Unfortunately, it is almost impossible to completely solve your deadlock problem without additional information.

Rob