May 4, 2021 at 9:44 am
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.
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
May 4, 2021 at 9:57 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 4, 2021 at 11:12 am
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.
May 4, 2021 at 12:28 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 4, 2021 at 1:27 pm
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.
May 4, 2021 at 3:09 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply