Help with deadlocks

  • Hi

    A subsystem I created for users to upload data from CSV then create reports has proven much more popular than I ever thought.  Now the problem is frequent deadlocks.  Essentially, the CSV is read into an application, then bulk inserted to a temporary table - in blocks of 250 records.  Once the entire CSV has been inserted a second process is run: to apply a set of rules on the temporary table; and convert the data to a common format.  This uses various stored procedures - and INSERTs into a central table.  This central table is the source of the deadlocks.

    Users can then report from the central table on what they have uploaded.

    However its so popular the users frequently upload several files at the same time, leading to deadlocks on the central table.

    I have several questions.

    1. We are using SQL Server Express - I know this has limitations.  Will SQL Server Standard Edition cope better?

    2. I have read that SNAPSHOT_ISOLATION can help with deadlocks - I'm not sure if it will if multiple INSERT statements the cause of deadlocking - I thought this could only help with READs while another process is INSERTing

    3. Could table partitioning help?  I had an idea to use a GUID key for each file upload and partition on ranges of that key: The table currently has an IDENTITY key - which I am guessing would not partition well because all the INSERT activity is on the most recent key values.  Changing the key on this central table would be a lot of work, and I really need to know its going to help before trying it out.

    4. Is there a way for a stored procedure detect if another is currently running from a list of other stored procedures and if so, delay its own execution until that procedure has finished.  Or maybe I need some kind of queueing system in the application

    Any suggestions gratefully received

     

     

    • This topic was modified 1 week, 2 days ago by  tom 69406.
  • Are you sure that these are deadlocks, and not just locks? As you are inserting, I would not expect deadlocks & suggest that more investigation is needed.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • The errors I am getting are all like this:

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

     

     

  • The key to deadlocks is usually, but not always, that you have multiple different queries, not the same query, that are accessing the various tables in different orders. Meaning, one batch inserts into TABLEA and then selects from TABLEB. The other batch selects from TABLEB, then inserts into TABLEA. Each one has to wait on the other to complete, but neither can complete until the other is finished. A deadly embrace, a deadlock.

    Deadlocks are also performance related. Even with the example above, if everything is completed really quickly, you'll never notice that the code is out of order.

    So, to fix deadlocks, first, get the code so that everything goes in the same order all the time. Next, performance tune that code and your structures. Between the two, you should eliminate most deadlocks. Yes, snapshot isolation helps because the shared locks needed for reads are not taken out in the same way, reducing the chances of deadlocks radically. However, snapshot isolation can add considerable load to your tempdb, so be cautious here.

    Search the articles here on SSC. Tons and tons of information on deadlocks, how to get the deadlock graphs (extended events, specifically system_health, will be your friend, don't go old school and use traceflags), how to read the graphs and how to fix the issues.

     

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Great tip about system_health.  I didn't realise so much more information was recorded.

    I have extracted the xml_deadlock_report events - and the results are unexpected. And probably much easier to fix.

    Thanks for the help.

     

  • Happy to help!

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply