MS SQL Indexing and Locking

[Seraphim]

Hi everyone,

I'm having a few troubles with a particular table in our MS SQL Server database, where the index is locked when being inserted into by many users at once - which is a frequent occurance, as it's a point in the application that many users tend to reach at once - which then leads to deadlocks

Does anyone have enough experience in SQL Server to suggest a better indexing/key strategy for a table like this? All the relevant information on the table structure will be revealed if someone feels up to the challenge. I'm getting conflicting information from just Googling, so if someone could help me out it'd be greatly appreciated, not least by the users receiving the deadlocks!

 

[Seraphim]

Well, here's the full story...

I've got an ASP.NET product which asks kids a set of questions in a quiz, and at the end of the quiz all their answers are taken from a 'draft' table where they're stored while being answered, to a 'final' table, which is a permenant record of what they answered to the questions. My trouble is that I'm getting a few deadlocks on the 'final' table when moving the rows from the draft table - all rows are taken from the draft table via an INSERT INTO ... SELECT from the draft table - no other operations (UPDATEs, DELETEs etc.) are performed on this final table.

An example of the SQL Server error log shows it's locking on the key index of the final table (id 53575229):

Deadlock encountered .... Printing deadlock information
2006-02-23 09:35:26.66 spid3     
2006-02-23 09:35:26.66 spid3     Wait-for graph
2006-02-23 09:35:26.66 spid3     
2006-02-23 09:35:26.66 spid3     Node:1
2006-02-23 09:35:26.66 spid3     KEY: 23:53575229:1 (ed003efd24a9) CleanCnt:1 Mode: X Flags: 0x0
2006-02-23 09:35:26.66 spid3      Grant List 2::
2006-02-23 09:35:26.66 spid3        Owner:0x7e4232a0 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:90 ECID:0
2006-02-23 09:35:26.66 spid3        SPID: 90 ECID: 0 Statement Type: INSERT Line #: 18
2006-02-23 09:35:26.66 spid3        Input Buf: RPC Event: pisp_Busrule_FinaliseUserQuestionAnswers;1
2006-02-23 09:35:26.66 spid3      Requested By: 
2006-02-23 09:35:26.66 spid3        ResType:LockOwner Stype:'OR' Mode: S SPID:141 ECID:0 Ec: (0x7E4A5590) Value:0x2ccb100 Cost: (0/5668)
2006-02-23 09:35:26.66 spid3     
2006-02-23 09:35:26.66 spid3     Node:2
2006-02-23 09:35:26.66 spid3     KEY: 23:53575229:1 (cf008b9a1fa3) CleanCnt:1 Mode: X Flags: 0x0
2006-02-23 09:35:26.66 spid3      Grant List 0::
2006-02-23 09:35:26.66 spid3        Owner:0x6fd05e80 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:141 ECID:0
2006-02-23 09:35:26.66 spid3        SPID: 141 ECID: 0 Statement Type: INSERT Line #: 32
2006-02-23 09:35:26.66 spid3        Input Buf: RPC Event: pisp_Busrule_FinaliseUserQuestionAnswers;1
2006-02-23 09:35:26.66 spid3      Requested By: 
2006-02-23 09:35:26.66 spid3        ResType:LockOwner Stype:'OR' Mode: S SPID:90 ECID:0 Ec: (0x0FBAD590) Value:0x7e422ce0 Cost: (0/237C)
2006-02-23 09:35:26.66 spid3     Victim Resource Owner:
2006-02-23 09:35:26.66 spid3      ResType:LockOwner Stype:'OR' Mode: S SPID:90 ECID:0 Ec: (0x0FBAD590) Value:0x7e422ce0 Cost:(0/237C)

Looking at the structure of the table we're INSERTing into we have:

QuestionAnswerID, int (PK, IDENTITY, CLUSTERED)
UserGUID, uniqueidentifier
QuestionID, int
AnswerID, int
QuizSessionID, uniqueidentifier
DateCreated, datetime
DateModified, datetime

The QuizSessionID is assigned on finalising the question answers, and uniquely identifies a single complete set of quiz answers. Now, in such a table which receives a high volume of inserts and nothing else, is a clustered index on the primary key (an identity int) a good idea? I think I'm getting locks on the key because everyone wants to insert into the last page, and despite using lock hints (e.g. WITH (ROWLOCK)) it's still locking the page. Maybe?

I've read and read and read various webpages, blogs and articles but they all seem to conflict on what makes a good clustered index choice. Should I dump the identity column altogether and use a composite key that's clustered? Should I not have a clustered index at all and leave it as a heap table? Should I have the clustered index on the quizsessionid, which is the field most often used in WHERE clauses?

I'm so confused

 

[Seraphim]

Thanks for the reply oneilldo - it's given me some food for thought...

Unfortunately, I can't tinker with the indexes on the production system, so I'm having to try and recreate the problem on a development machine, which is proving rather tricky as we don't have any load testing software available I've written a quick threaded Windows app to simulate a quiz being taken, but I'm getting different locking using that method!

There's only one place in which the table is inserted into, and it's within a stored procedure - so I'm unsure how two of these executing at the same time are generating these deadlocks. Looking at the indexes, there are some others that might also be slowing it all down and increasing the window for deadlocks, so I can try and get rid of them and see what effect it has.

Of course, without proper testing, I can't say for certain if these efforts I'm making will solve the problem - most irritating when the bosses ask why the users are still getting errors!