locked
SQL Server DeadLock prevention / minimizing RRS feed

  • Question

  • Please help me solve deadlock in SQL server!!

    the scenario:

     We have a database server running SQL server 2000 Win Server 2003 which manages couple of huge databases and hundreds of simultaneously connected clients to them.  Number of transactions done by multiple large size applications on these databases are enormous and we did not have much problems for a long period with partially same data and transaction volume.

    Recently, server has encountered lots of blockings in its processes and finally deadlocks which made the server halt… the number of deadlocks is increasing day after day and server halts are becoming a serious headache: before we had once a month, now we have twice a day.

     We have set multiple trace flags on server to generate and capture logs, used profiler and installed advanced SQL analysis monitoring tools. Finally, we found out that deadlocks are mostly caused by similar simultaneous queries blocking each other’s in table access, but the queries and the tables are different for each deadlock and each query may come from a different application or client. Following is a typical blocking session that caused deadlock and server halt:

     -          Blocking Process A ran at  9:36:11, Waiting time(ms): 469313, SQL Query:  UPDATE TableX

    -          Blocked Processes  B ran at  1: 9:36:12, Waiting time (ms): 436094, SQL Query: INSERT INTO TableX

    The SELECT statements are locked and unfortunately we cannot change them by adding 'nolock' access modifier since they are all called from third party applications. The waiting times for most of blocked processes are above 400,000 mille seconds (more than 6 minutes!). Our servers are high performance, wealthy in CPU and Memory resources and the SQL has a huge memory allocated to itself that never reached to its limit! All we can do is to set changes directly on database, SQL Server settings, network and hardware.

     So far, we have come up with the following solutions (which are not the ultimate cure) and we still did not apply them since the database is huge in size and design detail and we better get sure before any action:

    1-      reduce the LOCK_TIMEOUT in db that applications do not wait a long time 

    2-      Index all fields in blocking tables to shorten query times

     

    If anybody can suggest an avenue of search or an alternative to prevent or minimize this error. Thanks...

     A.Oveissian

    Wednesday, September 15, 2010 9:35 PM

Answers

  • http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
    http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx

    See if the below (taken from the above links) helps you to understand

    /*

    To summarize: the SELECT used the nonclustered
    index to find a qualifying row.  While holding a Shared lock
    on the nonclustered index, it needs to jump over to the clustered index
    and retrieve some columns that aren’t part of the nonclustered index.
     While it’s doing this, the UPDATE is busy doing a seek on the clustered index.
     It finds a row, locks it and modifies it. 
    But because one of the columns being modified is a key column in
    the nonclustered index, it then has to move to the nonclustered index
    and update that index, too.  This requires a second X key lock on the
    nonclustered index.  So,
     the SELECT ends up blocked waiting for the
     UPDATE to release his X lock on the clustered index,
    while the UPDATE winds up blocked and waiting for the
    SELECT to release his S lock on the nonclustered index. 

    */


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, September 16, 2010 7:26 AM
  • Hi,

       If this started only 2 weeks back means .. then there may be huge data growth OR data alterations happened in the database ... Either of this options will FRAGMMENT the data /index , which may cause DELAY in querying .. Please also put one eye on the Index fragmentation , on the main transaction tables AND the tables those are involved in the DEADLOCK CHAIN  .... 

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Friday, September 17, 2010 1:15 AM

All replies

  • Hi Oveissian,

     Thank you for the clear explantion about the problem .. As you said, we CAN'T do much with the query tuning, because of the SELECTs are from third party tools. As per the given data, your DML statements INSERT and UPDATEs are blocking .. Did you aobserve any LOCK - ESCALATION is happening ... If the query is acting on majority of the records, automatically, record level locks are escalated to page level or table level locks, causing the dead lock with the other processes accessing the same resource.

    As per reducing the LOCK_TIMEOUT may solve this type of problems, but always there are chances of negatively effecting the other queries...

    Indexing can be done on the important columns(not all the columns), as per the execution plan. Unfortunaltely, we can't use any advanced indexing as the DB is on 2000 version ... But , based on the critical analysis, if we can create the indexes, definately, we can see some +ve improvement ...

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Thursday, September 16, 2010 4:08 AM
  • http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
    http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx

    See if the below (taken from the above links) helps you to understand

    /*

    To summarize: the SELECT used the nonclustered
    index to find a qualifying row.  While holding a Shared lock
    on the nonclustered index, it needs to jump over to the clustered index
    and retrieve some columns that aren’t part of the nonclustered index.
     While it’s doing this, the UPDATE is busy doing a seek on the clustered index.
     It finds a row, locks it and modifies it. 
    But because one of the columns being modified is a key column in
    the nonclustered index, it then has to move to the nonclustered index
    and update that index, too.  This requires a second X key lock on the
    nonclustered index.  So,
     the SELECT ends up blocked waiting for the
     UPDATE to release his X lock on the clustered index,
    while the UPDATE winds up blocked and waiting for the
    SELECT to release his S lock on the nonclustered index. 

    */


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, September 16, 2010 7:26 AM
  • http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
    http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx

    See if the below (taken from the above links) helps you to understand

    /*

    To summarize: the SELECT used the nonclustered
    index to find a qualifying row.  While holding a Shared lock
    on the nonclustered index, it needs to jump over to the clustered index
    and retrieve some columns that aren’t part of the nonclustered index.
     While it’s doing this, the UPDATE is busy doing a seek on the clustered index.
     It finds a row, locks it and modifies it. 
    But because one of the columns being modified is a key column in
    the nonclustered index, it then has to move to the nonclustered index
    and update that index, too.  This requires a second X key lock on the
    nonclustered index.  So,
     the SELECT ends up blocked waiting for the
     UPDATE to release his X lock on the clustered index,
    while the UPDATE winds up blocked and waiting for the
    SELECT to release his S lock on the nonclustered index. 

    */


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, September 16, 2010 7:26 AM
  • Thanks Reddy for answer,

    I think suggested ways are the last solutions to be tested and may reduce deadlocks but I want to know if there is something else that I must check? this problem has started since 2 weeks ago only, the data and transaction volume are the same...

    I did not check if escalation flag has been set or  not.  Would you mind let me know what happens in both cases: if there is escalation or if there is not?

    Anyways I will set the flag and let you know...

     

     

     

     

    Thursday, September 16, 2010 1:33 PM
  • Hi,

       If this started only 2 weeks back means .. then there may be huge data growth OR data alterations happened in the database ... Either of this options will FRAGMMENT the data /index , which may cause DELAY in querying .. Please also put one eye on the Index fragmentation , on the main transaction tables AND the tables those are involved in the DEADLOCK CHAIN  .... 

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Friday, September 17, 2010 1:15 AM
  • Thanks for helps, All remarks were helpful but our problem still stands and we ended up with finding and killing the scheduled processes. Please let me know if you have any suggestion to proactively predict the deadlock issues. I have got some other suggestions in, another thread. Not tried all of them but they may be useful: http://www.dbforums.com/microsoft-sql-server/1660328-sql-server-deadlock-problem.html#post6478772 Thanks,
    Monday, September 27, 2010 1:11 PM