none
Deadlock/blocking: which index promotes better concurency during update, insert, Delete

    Question

  • Hello all,

    I work on the project where in 90% of the situation i have to change code due to excessive blocking or deadlocks. Often I change clustered index for non-clustered and deadlock are going away. I know that because I sometimes can re-produce deadlock and it allows me to determine if change was effective.

    Here is what I see:

    note                                                             cl. index                                                       non. cl. index

    deadl. resource                                            cl. index                                                       n/a

    plan                                                              80% effort spent on cl.index update           80% effort spent on table update

    bookmark lookup(some queries)                 higher chances of deadlock on cl. index       no chances on cl. ind. deadl.

    Since I worked on many cases where I saw above, I am tempted to draw general conclusion that cl. index, while having unique performance qualities, is more prone to cause deadlock behavior.

    Please comment on it. I am particulary interested in the situation when cl. index update is replaced by table update. My speculations show better concurency. Do you think it make sence?


    gene

    Tuesday, August 28, 2012 2:00 PM

Answers

  • Hi Gene,

    Deadlocks are caused by poor database design, inappropriate isolation level, inefficient code etc. In this article, we learned about the different ways to minimise deadlocks on SQL Server. Check out my following article that is published recently on SQL-SERVER-PERFORMANCE.COM. In this article, I've given some tips to minimise deadlocks. 

    Tips For Minimizing Deadlocks in SQL Server



    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, August 28, 2012 4:44 PM
  • 'non-clustered indexes support better concurency then clustered'. It is my experience and I would like to find theorethical explanation for it.

    I wouldn't have any argument to support your statement. If you have a table with a clustered index, then locking works the same as on a heap with the exact same nonclustered index.

    There are roughly two reasons for a deadlock: through lock escalation or through different access paths.

    If you only have one index, you can never have deadlocks because of different access paths.

    I don't know if a bigger index will have its locks escalated faster (or slower) than a smaller index. Typically, a nonclustered index is much smaller than the clustered index.

    Does hip udpate causes smaller lock scope or anything else which explaines better concurency for it in comparison to cl. index update.

    hip update?

    There is a default setting for the locking granularity. You can overrule it for individual queries. If many locks are needed, using page level locking will decrease the chance of lock escalation.


    Gert-Jan

    Wednesday, August 29, 2012 8:49 PM

All replies

  • Deadlocks can only occur when you have concurrent processes. Here it strongly depends on the actual context. There are situations where changing the clustered key makes sense, but there are others where using a different transaction isolation level would be better.

    The update costs may come from the same operation: adding or updating data on the pages. Here you should consider testing fill-factors < 100.

    When a bookmark-lookup results in a dead-lock then you should compare the situation with an additional covering index for that lookup.

    Tuesday, August 28, 2012 2:37 PM
  • Stefan, thank you for answering.

    I likely had to be more specific with my questions:

    Question 1:

    do you know any facts in regard to the way lock promotion, progress, release or scope which support statement:

    'non-clustered indexes support better concurency then clustered'. It is my experience and I would like to find theorethical explanation for it.

    Question 2:

    When I compare deadlocked statements and for 1 of them I change cl. index to non-cl, I see that most of the effort goes to hip update opposing to cl. index update.

    Does hip udpate causes smaller lock scope or anything else which explaines better concurency for it in comparison to cl. index update.


    gene

    Tuesday, August 28, 2012 4:38 PM
  • Hi Gene,

    Deadlocks are caused by poor database design, inappropriate isolation level, inefficient code etc. In this article, we learned about the different ways to minimise deadlocks on SQL Server. Check out my following article that is published recently on SQL-SERVER-PERFORMANCE.COM. In this article, I've given some tips to minimise deadlocks. 

    Tips For Minimizing Deadlocks in SQL Server



    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, August 28, 2012 4:44 PM
  • 1. I don't think that this can be generalized. E.g. consider massive parallel inserts/updates and some reads. All based on a kind of entity id. Then a clustered index on that entity id with a fill factor < 100 will avoid locks and dead locks in most cases as the operations are a kind of page aligned. Now change the clustered key to another column or create a heap table. You will get randomly distributed entity id's over all new pages.

    2. hip?

    Tuesday, August 28, 2012 5:23 PM
  • 'non-clustered indexes support better concurency then clustered'. It is my experience and I would like to find theorethical explanation for it.

    I wouldn't have any argument to support your statement. If you have a table with a clustered index, then locking works the same as on a heap with the exact same nonclustered index.

    There are roughly two reasons for a deadlock: through lock escalation or through different access paths.

    If you only have one index, you can never have deadlocks because of different access paths.

    I don't know if a bigger index will have its locks escalated faster (or slower) than a smaller index. Typically, a nonclustered index is much smaller than the clustered index.

    Does hip udpate causes smaller lock scope or anything else which explaines better concurency for it in comparison to cl. index update.

    hip update?

    There is a default setting for the locking granularity. You can overrule it for individual queries. If many locks are needed, using page level locking will decrease the chance of lock escalation.


    Gert-Jan

    Wednesday, August 29, 2012 8:49 PM