MS SQL Indexing and Locking

Seraphim · 27 Feb 2006 at 10:23

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!

happytechie · 27 Feb 2006 at 10:47

how are the users attaching to the database and performing Update/Inserts?

I've never had any problems with too much locking form SQL Server. If all your users are fetching big updatable record sets then they will probebly lock big chunks of the database at a time.

I assume that you have an autoid field somewhere in the table? perhaps you need to look at your key generation strategy?

HT

Seraphim · 27 Feb 2006 at 11:07

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):

Code:

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:

Code:

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 :confused:

oneilldo · 27 Feb 2006 at 21:32

As with most things SQL Server a lot of things can contribute to the locking you are experiencing. How big is this table, how much memory have you allowed SQL server to use. Is the box just running SQL Server? Have you got a lot more indexes on that table slowing down inserts? Have you previously set a level of locking on that table?
If you can afford to have a 'play' with it then I would suggest trying to change the index to a non clustered on the key field - you won't get a significant reduction in select performance and it will help with inserts.
NB: Removing or creating a clustered index will lock the table un til the operation completes (a normal index will just lock updates, inserts and deletes). You are right the auto incrementing clustered index will mean that everyone will be trying to insert into the last page(s).
You only need an index to speed up SQL running on that table or to enforce integrity checks, if you don't need an index then don't add one as inserting to a heap will be faster.

How many inserts happen in say a 1 minute period, can you catch the SQL and retry it? Couldn't you allocate the unique ID in the draft table and then you wouldn't need to do this in the final table. Is only one bit of SQL inserting to the final table at any one time? What transaction isolation level are you using?

Seraphim · 28 Feb 2006 at 12:37

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!