August 6, 2014 at 8:48 am
Morning All.
This is probably my first post (or maybe second). I am sure it will be obvious to most but I cannot seem to find a definitive answer.
My Problem. I have a stored procedure created by a third party. Some commands constantly deadlock with other commands. This seems to cause the entire stored procedure to not finish the rest of the commands.
Question 1. In a stored procedure I understand the commands are executed sequentially but does each subsequent command wait for the previous to complete or do they just execute one after the other. I suspect the latter which would account for the deadlock.
Question 2. If a deadlock does occur, does it stop the execution of the rest of the commands? This would seem to go against my previous assumption but I have no other explanation as to why the final commands are never called once a dead lock occurs.
There is no error checking or anything in the SP, just a series of update commands one after the other.
I would be grateful for some advice or some links that might explain this process better than I can find.
August 6, 2014 at 9:00 am
(1) In the absence of any flow control logic (GOTO commands etc), the second command will start when the first has finished, and so on.
(2) A deadlock will cause one of the commands involved to be rolled back. If you're getting a deadlock with your stored procedure, there'll be a separate process involved. You need to look at the deadlock graph to see what happened. You can get that with an extended event, a trace, or by switching on a trace flag.
Deadlock analysis isn't an entry-level thing, so you may need to get someone in to help you with this.
John
August 6, 2014 at 9:01 am
A stored procedure (or any process in a batch) runs each statement one at a time waiting for each one to complete.
Deadlocks occur between different sessions, so a single SP called once won't cause a deadlock by itself, it needs another process.
August 6, 2014 at 9:14 am
To be chosen as a deadlock victim, two threads must be inside transactions. If thread one updates tableA then tableB while thread two updates tableB then tableA AND both threads are at step two they will be blocking each other infinitely so sqlserver will have to kill one thread or the other to move forward. It is impossible for a single thread to block itself since locks are for a thread. Regular blocking will just cause you to wait; sql recognizes this case will eventually complete.
Some people try to do updates in alphabetical order to alleviate the problem; that just adds more confusion. Smaller, faster updates will greatly improve the situation. Locking all your tables at the beginning of the proc might prevent you from being chosen as the victim but greatly increases the complexity of your proc.
August 6, 2014 at 11:39 am
Thanks for all your assistance. It has pointed me in the right direction. The deadlock graph does show that it is the same SP but there is a different server process id and owner id between the victim and the successful command, which tells me multiple routines are calling the SP.
The only thing that puzzles me now is why the SP does not complete the rest of the commands following the command that was the victim. I thought that perhaps the entire SP was aborted but judging from your responses that is not the case, the SP should simply continue to process the remaining commands? In fact the only way to prevent the continued processing (in the event that the failed command was crucial to the remaining stack) was to include try-catch statements? But by default the SP will continue to the end?
August 6, 2014 at 12:02 pm
A deadlock victim message/error is an expected situation. SQL Server rolls back your transaction and expects you to try again with likely success. Because it is a recoverable condition your connection is not killed. You'll need plenty of error coding to recover, retry, give up, etc. You can always choose to keep plowing through code if you like though it isn't the smartest thing to do.
August 6, 2014 at 5:23 pm
Bill Talada (8/6/2014)
To be chosen as a deadlock victim, two threads must be inside transactions. If thread one updates tableA then tableB while thread two updates tableB then tableA AND both threads are at step two they will be blocking each other infinitely so sqlserver will have to kill one thread or the other to move forward. It is impossible for a single thread to block itself since locks are for a thread. Regular blocking will just cause you to wait; sql recognizes this case will eventually complete.Some people try to do updates in alphabetical order to alleviate the problem; that just adds more confusion. Smaller, faster updates will greatly improve the situation. Locking all your tables at the beginning of the proc might prevent you from being chosen as the victim but greatly increases the complexity of your proc.
Be aware that every data modification (DELETE|INSERT|UPDATE) in SQL Server is "inside [a] transaction". Thus, any modification has a potential to be in a deadlock situation.
If you want to affect your status in a deadlock, use the statement:
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH }
Definitely don't try to "pre-lock" resources just to affect your deadlock status.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 7, 2014 at 2:17 am
Also, read about SET XACT_ABORT ON/OFF to learn more about different ways SQL Server can handle errors.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply