Using SQL Server 2008 R2:
I'm currently attempting to track down the cause of a deadlock issue we have. Not sure where to turn. Here's the setup.
TABLE Scores:
id INT IDENTITY(1,1),
first_name VARCHAR(50),
last_name VARCHAR(50),
player_id VARCHAR(50),
score INT
PRIMARY KEY/CLUSTERED INDEX:
id
NON-CLUSTERED INDEX:
first_name, last_name INCLUDES id, player_id, score
A multi-threaded application processes messages, then calls one of two stored procedures for every message.
If the application has not processed the user, it will call a stored procedure that inserts a row into the table.
If the application has processed the user, it will update any or all of first_name, last_name, scores, based on first_name and last_name player_id does not change.
There will be only one row in the table per player_id.
The thread that the application processes messages is chosen based on the player_id, so two threads will NOT process messages for the same player at the same time.
The INSERT and UPDATE statements are using the WITH (ROWLOCK) directive.
Here's the issue:
The process is causing deadlocks with thread 1 locking the primary clustered index and thread 2 locking the non-clustered index.
I do not understand why the locks are occurring – the UPDATE and INSERT statements are using row locks, and it's been tested and proven that threads are not accessing the same rows.
What could I do to fix this issue?
Caveats:
There must exist two stored procedures, one updating and one inserting. As much as I would like to switch to a single MERGE, that's not gonna happen. : /
Best Answer
Can't confirm it without a deadlock trace but it looks like you've already got the answer.
Likely that one of your procedures is taking a shared lock on the non-clustered index first, then the clustered index. The other is doing the reverse, taking a shared lock on the clustered first, followed by the non-clustered.