none
Why do I Suddenly get lots of deadlocks?

    Question

  • Hi!

    I have been developing for a SQL Server 2005 (and ASP.NET 2.0) website for some years. After some updates I did last week I suddenly get lots of deadlock when I do some database calls. I sporadically get the following error:

    "Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

    The strange thing is that I have never ever had those problems before. There are 3 things that I can think of relates to the problem:

    1) Some new indexes use ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON which I haven't used before. As for as I understand, these options should avoid deadlocks, and not produce them. So I cannot understand why this should produce the errors.

    2) I use the StateServer for storing the sessions in ASP.NET, which is completely new to me, and it sounds pretty far fetch that it could produce the errors.

    3. I have also added some new triggers. I sounds resonable that this can be a problem. I have however not managed to pinpoint the exact triggers that cause the problem.

    So my questions are:
    Does it sounds reasonable?
    Are there any additional things that might cause the problems? (remember that these are completely new problems for an alredy pretty large site)
    Is there any way to locate possible deadlock-problems before they occur?

    I hope you have any ideas. Any help would be appritiated. Thanks!

    Regards
    Olle
    Wednesday, September 03, 2008 10:11 AM

Answers

  • After some emailing with Jonathan he found out that I had set some rather stupid lock attributes on my indexes. This simple query solved my deadlock issues:

    sp_msforeachtable 'ALTER INDEX ALL ON ? SET (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'

    Thanks Jonathan!
    Thursday, September 11, 2008 7:23 AM
  • For future reference by others searching for this issue, the real issue was that both of the settings were turned off which was causing database engine to take the most restrictive locking in all scenarios:

     

    http://msdn.microsoft.com/en-us/library/ms188388.aspx

     

    From the above reference:

     

    Row and Page Locks Options

    When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-level,

    page-level, and table-level locks are allowed when you access the

    index. The Database Engine chooses the appropriate lock and can

    escalate the lock from a row or page lock to a table lock.

     

    When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a

    table-level lock is allowed when you access the index. For more

    information about configuring the locking granularity for an index,

    see Customizing Locking for an Index.

     

     

    The deadlock graph provided by Olle was quite long, with multiple SPIDs contributing to the deadlocks.  The key to finding the answer is an entry in the XML graph for each of the sessions like this:

    <objectlock lockPartition="0" objid="1234678565" subresource="FULL" dbid="6" objectname="SQL_Examples.dbo.SomeTable" id="lock5ab021c0" mode="IX" associatedObjectId="1234678565">

     

    This told me that every spid was pulling a full table lock, to do a single row insert or update.  In multithreaded environments this will most assuredly cause problems the larger the database gets.

     

    Thursday, September 11, 2008 12:51 PM
    Moderator

All replies

  • Jonathan Kehayias authored a good article on SQLExamples for troubleshooting deadlocks.
    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20Deadlocking%20in%20SQL%20Server&referringTitle=Home

    So did Madhu Nair.
    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20Deadlocking%20in%20SQL%20Server%202005%20using%20Profiler&referringTitle=Home

    And, Brad McGehee has a good article on Simple-Talk that demonstrates how to use profiler to track down deadlocks. Have a look here to see if it helps. 
    http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

    HTH...

    Joe

    Wednesday, September 03, 2008 12:18 PM
    Moderator
  • Thanks!

    However, I have already read through those articles. I have even managed to which
    queries that are involved in the deadlocks. My problem is that I haven't been able to eleminate some of the locks, and I don't realize why they are causing a problem. I am also anxious the prevent further problems with new queries I create. Until now it had never happened before, so I do not understand why the problems suddenly arises.

    :-(
    Wednesday, September 03, 2008 12:43 PM
  • Looks like it is the triggers as long as you are not modifying index in the middle of the process.

    Take a look closely at the triggers look at the updates and the transaction locks that are being held and you should have an answer to this . Look at sysprocesses table for more info. See that the transaction locks are not held for too long.

    Thanks

    shilpi

     

    Wednesday, September 03, 2008 4:11 PM
  •  oller999 wrote:
    Thanks!

    However, I have already read through those articles. I have even managed to which
    queries that are involved in the deadlocks. My problem is that I haven't been able to eleminate some of the locks, and I don't realize why they are causing a problem. I am also anxious the prevent further problems with new queries I create. Until now it had never happened before, so I do not understand why the problems suddenly arises.

     

    If you have the deadlock graphs, then post them here and I would be happy to analyze them to help you better understand where exactly the problem might be.  The triggers could be the problem, but the new indexes also could be the problem if they cause bookmark lookups to occur.  If you see the deadlock on a SELECT batch with a INSERT/UPDATE/DELETE batch, the the indexes are the issue, not the trigger.  You can read about this kind of deadlock on the following article which is not up publicly yet, while I finish it off, but still available through the following link:

     

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DeadlockAnatomy

     

    The details held by the deadlock graphs will help determine which kind of deadlock you have and how to resolve it.

    Wednesday, September 03, 2008 6:07 PM
    Moderator
  • Thanks, both of you!

    Jonathan, your article is really interesting.
    Actually at least some of the problems have been (INSERT/UPDATE) vs SELECT locks.

    One of the remaining problems is an INSERT/INSERT deadlock (I looked at the deadlock graps) Both locks are on the same table. It's a table with 14 different indexes and about 2.2 million rows. The inserts are simple INSERT INTO xxx(a,b,c) VALUES (@a, @b, @c) inserts.

    Is it possible that the deadlock occurs while at least the clustered index and one of the non-clustered indexes are updated? (Pretty much like in the SELECT/INSERT deadlock example you describe).

    Is there a way to find out which one the indexes are causing the problem?

    Best regards
    Olle
    Wednesday, September 03, 2008 7:30 PM
  • One addition to my last post:

    Is it reasonable to assume that the non-clustered-indexes
    that contain the primary key column as an index key column, might be that causes the problems, and the ones that does NOT contain the PK-column doesn't?

    The other ones should try to lock the clustered index. Right?

    /Olle
    Wednesday, September 03, 2008 8:10 PM
  •  oller999 wrote:

    Is there a way to find out which one the indexes are causing the problem?

     

    If you have turned on Trace Flags 3605,1205, -1 for SQL 2000 or 3605,1222, -1 for SQL 2005 following the articles posted by Joe Webb, then you can easily get the index information from the deadlock graph.  I put two links to Bart Duncans Blog in the article I wrote that show exactly how to do this for SQL 2000 with 1205, and SQL 2005 with 1222.  In SQL 2005, it is already a part of the 1222 deadlock graph information.  Madhu's article includes information for exporting the deadlock events from Profiler to files which will then have the necessary information to apply the fundamentals in Bart Duncans Blog posting.

     

     oller999 wrote:

    Is it reasonable to assume that the non-clustered-indexes that contain the primary key column as an index key column, might be that causes the problems, and the ones that does NOT contain the PK-column doesn't?

    The other ones should try to lock the clustered index. Right?

     

    Non-clustered indexes by their nature include the primary key column as a part of the index keys even if it isn't specified, so you don't really need to specify them explicitly.  The reason for this, is that if the index doesn't cover a specific query completely, but still provides a optimized data path, the database engine will perform a Key Lookup (Bookmark Lookup) by using the RID of the row in the clustered index.  I can only assume that in your case the PK is also the clustered index since this is the default configuration when you create a primary key.

     

    EDIT:

    The details are all in the deadlock graph output from the deadlocking processes.  If you can post one of the graphs, then we can provide better information back as to what is or isn't happening.  Otherwise, you really have to dig into those articles and the links in them to figure it out on your own.

    Wednesday, September 03, 2008 8:28 PM
    Moderator
  • Thanks for the great advice you give me!

     

    I do get the deadlocks graphs, and the ovals describing the processes are there, and I also can see the queries if I hoover over the ovals. But if I separate the ovals, there are no boxes describing the locked objects, like illustrated in one of the articles.

     

    I have tried with DBCC TRACEON (1222, 3605, -1) (by running it from Management Studio) and also to save the graphs as XML using Events Extraction Settings. But the boxes are still missing.

     

    What do I do wrong?

     

    Monday, September 08, 2008 7:49 AM
  • You have to post the deadlock graph for me to help you any further.  Without it all I can do is shoot in the dark at what your problem may or may not be.   If you would rather email it, my email address is in my profile.

     

    Monday, September 08, 2008 10:44 AM
    Moderator
  • Ok, here is what I get:

     

    http://i38.tinypic.com/ibz80o.gif

     

    I can see the queries when I hoover on each graph, but that doesn't help med much. One of the queries is a simple INSERT INTO () VALUES () and the other one calls a quite large SP. The boxes in the between are missing.

    Monday, September 08, 2008 5:25 PM
  • Follow this step and post the deadlock XML details:

     

    You can also export the deadlock event. Steps are as follows
    Profiler – File – Export – Extract SQL Server Events – Extract Deadlock events and save to some files

     

     

    Monday, September 08, 2008 6:52 PM
    Moderator
  • After some emailing with Jonathan he found out that I had set some rather stupid lock attributes on my indexes. This simple query solved my deadlock issues:

    sp_msforeachtable 'ALTER INDEX ALL ON ? SET (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'

    Thanks Jonathan!
    Thursday, September 11, 2008 7:23 AM
  • For future reference by others searching for this issue, the real issue was that both of the settings were turned off which was causing database engine to take the most restrictive locking in all scenarios:

     

    http://msdn.microsoft.com/en-us/library/ms188388.aspx

     

    From the above reference:

     

    Row and Page Locks Options

    When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-level,

    page-level, and table-level locks are allowed when you access the

    index. The Database Engine chooses the appropriate lock and can

    escalate the lock from a row or page lock to a table lock.

     

    When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a

    table-level lock is allowed when you access the index. For more

    information about configuring the locking granularity for an index,

    see Customizing Locking for an Index.

     

     

    The deadlock graph provided by Olle was quite long, with multiple SPIDs contributing to the deadlocks.  The key to finding the answer is an entry in the XML graph for each of the sessions like this:

    <objectlock lockPartition="0" objid="1234678565" subresource="FULL" dbid="6" objectname="SQL_Examples.dbo.SomeTable" id="lock5ab021c0" mode="IX" associatedObjectId="1234678565">

     

    This told me that every spid was pulling a full table lock, to do a single row insert or update.  In multithreaded environments this will most assuredly cause problems the larger the database gets.

     

    Thursday, September 11, 2008 12:51 PM
    Moderator