Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. : /

share|improve this question
    
A couple of question may help track this down: 1) how many rows are in your table? 2) how many rows are inserted at a time by the insert proc? 3) how do you determine which proc to call? –  Jeff Feb 27 '12 at 21:39
1  
Can you switch on the trace flags detailed in Detecting and Ending Deadlocks and post the output to Gist. –  Mark Storey-Smith Feb 27 '12 at 21:39
    
In reply to @Jeff: 1- Testing starts with an empty table, and I see deadlocks almost instantly; 2- One row at a time; 3- The application keeps track of the player_id. If it was previously handled, update, otherwise insert. –  Nick Vaccaro Feb 27 '12 at 21:42
2  
even if "There must exist two stored procedures, one updating and one inserting.", you can have those two stored procedures under the hood to call one and the same procedure with MERGE. I would just serialize on sp_getapplock. I wrote an example here: simple-talk.com/sql/t-sql-programming/… –  A-K Feb 28 '12 at 2:12
1  
@Norla - You mention that the application keeps track of player_id; I'm wondering if for the update the application provides the player_id to the update proc and whether or not there is a unique non-clustered index on player_id. –  Jeff Feb 29 '12 at 15:51

2 Answers 2

up vote 2 down vote accepted

Can't confirm it without a deadlock trace but it looks like you've already got the answer.

The process is causing deadlocks with thread 1 locking the primary clustered index and thread 2 locking the non-clustered index.

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.

share|improve this answer
    
That is what is occurring, but I'm not quite sure why. Both threads are operating on different rows, each using the ROWLOCK directive. –  Nick Vaccaro Feb 27 '12 at 22:04

Updating any of the NC index keys is an update that has potential to deadlock. This is the typical read-write deadlock due to different access paths. Even if you update different keys, that doesn't eliminate conflicts because the locks are on key hashes, see %%lockres%% collision probability magic marker: 16,777,215. However, you describe the problem as appearing 'immediately' which strongly suggest that some other factors are at play, and we can only speculate about in lack of actual information.

Of course it would be much more useful to capture the deadlock graph and post it here. Discussing on a mere description of the deadlock has only limited value, as your description may or may not be factual, your understanding may or may not be correct and our interpretation may or may not be accurate.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.