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
scores, based on
player_id does not change.
There will be only one row in the table per
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?
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. : /