August 31, 2015 at 1:39 am
Hi, Could any one help me to resolve the issue. We have around 5 SP’s which are inserting data into Table A,and these will run in parallel.From the temp tables in the SP,data will be loaded to Table A. We are getting deadlock here.No Begin and End Transaction used in the stored procedure.
What could be done to avoid deadlock.
August 31, 2015 at 1:53 am
sunnyepgc (8/31/2015)
Hi, Could any one help me to resolve the issue. We have around 5 SP’s which are inserting data into Table A,and these will run in parallel.From the temp tables in the SP,data will be loaded to Table A. We are getting deadlock here.No Begin and End Transaction used in the stored procedure.What could be done to avoid deadlock.
Quick thought, without more information one can only guess so please provide more details, i.e. procedure code, table structure etc..
😎
The rule of thumb for preventing deadlocks is to always access the resources in the same order.
I strongly recommend that you read Gail Shaw's article SQL Server Deadlocks by Example[/url]
August 31, 2015 at 2:49 am
Please post the deadlock graph and the definitions for all procedures and tables mentioned in it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2015 at 6:43 am
Without the code and the deadlock graphs all I can do is make some vague suggestions.
Make sure that the queries you're writing access the tables in question in the same order. Deadlocks are frequently caused when you access TableA then TableB in one query, but TableB then TableA in another. Make sure your queries are tuned. Deadlocks are fundamentally a performance issue. If your queries run fast enough, you'll never see a deadlock.
"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
September 2, 2015 at 4:54 am
I do not have sufficient permission to add deadlock trace.Actually I am inserting new records into Target table (say Table B) using Merge statement (WHEN NOT MATCHED), I have replaced this with Left Join and didn't get any deadlock error so far,while running parallel.I will keep you posted,in case of any.
September 2, 2015 at 5:22 am
sunnyepgc (9/2/2015)
I do not have sufficient permission to add deadlock trace.Actually I am inserting new records into Target table (say Table B) using Merge statement (WHEN NOT MATCHED), I have replaced this with Left Join and didn't get any deadlock error so far,while running parallel.I will keep you posted,in case of any.
You don't need to add trace to get deadlock information. It's automatically captured by the system_health extended event session which runs automatically. You can pull the information right out of there[/url].
It sounds like you may have tuned the query and that could 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