Problem is that when the deadlock occurs and control is transferred to the catch block, the transaction becomes a zombie (referred to as doomed). You have to roll it back there. Once you do that, your transaction is gone.
Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: www.sqldownunder.com |http://greglow.me *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] *On Behalf Of *David Burstin *Sent:* Thursday, 1 February 2018 1:23 PM *To:* ozDotNet <ozdotnet@ozdotnet.com> *Subject:* Re: C# SqlConnection Nested Stored Proc Deadlock Thanks Preet and Greg. Unfortunately I am working with legacy code so can only make minor changes. I have some more questions :) The article referenced in Preet's answer has a while loop to retry three times. The transaction is wholly contained within that loop: Structure without the noise: WHILE BEGIN BEGIN TRANSACTION Do some stuff that should be rolled back if transaction fails BEGIN TRY Do something that causes deadlock COMMIT BREAK END TRY BEGIN CATCH ROLLBACK CONTINUE END CATCH; END Say that there is a deadlock on the first iteration => the transaction rolls back (including [ Do some stuff that should be rolled back if transaction fails] and also [ Do something that causes deadlock ] ), loop continues, new transaction is started, all good But, what if the loop is inside the transaction? BEGIN TRANSACTION Do some stuff that should be rolled back if transaction fails WHILE BEGIN BEGIN TRY Do something that causes deadlock BREAK END TRY BEGIN CATCH CONTINUE END CATCH; END COMMIT >From what I understand, when the deadlock occurs and this sp is the victim, the transaction rolls back as before. But now the loop continues, only doing [ Do something that causes deadlock ]. This time that bit of code succeeds, but what happens when COMMIT is hit? The original transaction is gone. I assume the effect of [ Do something that causes deadlock ] remains. I'm not really clear about this. And back in the real world, what if the transaction was started in C# code on the connection, before calling: Do some stuff that should be rolled back if transaction fails WHILE BEGIN BEGIN TRY Do something that causes deadlock BREAK END TRY BEGIN CATCH CONTINUE END CATCH; END Now there is no transaction code at all in the SQL - the transaction is supplied on the connection. The deadlock kills that transaction, but then the SQL loops and tries again. Now there is no transaction? Or is there a new implicit transaction on the connection? What happens when the commit is attempted in the C# code? I know this is a long question and appreciate any answers received. Regards David On 1 February 2018 at 15:52, Greg Low <g...@greglow.com> wrote: Sorry, error 3960, not 3760 Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 <+61%20419%20201%20410> mobile│ +61 3 8676 4913 <+61%203%208676%204913> fax SQL Down Under | Web: www.sqldownunder.com |http://greglow.me *From:* Greg Low [mailto:g...@greglow.com] *Sent:* Thursday, 1 February 2018 12:40 PM *To:* 'ozDotNet' <ozdotnet@ozdotnet.com> *Subject:* RE: C# SqlConnection Nested Stored Proc Deadlock Hi Dave, Deadlocks are batch terminating errors and unwind your whole transaction stack, unrelated to inner or outer, unless you add explicit try/catch logic as Preet mentioned. Generally, I prefer to catch them in client code rather than in T-SQL, as there are other types of errors that should be retried at the client level anyway. Client code should cope with things like: - Deadlock (error 1205) - Snapshot concurrency violations (error 3760) - Server disconnection (can be due to network issues, failover of HA-based systems, etc.) - Various resource issues on the server I often see client organisations who've spent a fortune on highly-available systems and when they fail over, just like they're designed to do, all the apps in the building break. That's not reasonable. It's important to get into the habit of assuming that a transaction that you need to apply to the DB **might** work, rather than assuming that it * *will** work. Always apply it via logic like: while we haven't slipped the transaction into the server, and while the retry time/count hasn't expired, let's try to make it happen. If it doesn't, depending upon the error, we might back off for a while and try again. For things like deadlocks, it's good to have some sort of exponential back-off with a random component. Some errors are pointless to retry (ie: a primary key violation probably isn't ever going to work) Once you build it that way, things become much more robust and resilient. The user should be unaware of this stuff, apart from a possible delay. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 <+61%20419%20201%20410> mobile│ +61 3 8676 4913 <+61%203%208676%204913> fax SQL Down Under | Web: www.sqldownunder.com |http://greglow.me *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com <ozdotnet-boun...@ozdotnet.com>] *On Behalf Of *Preet Sangha *Sent:* Thursday, 1 February 2018 12:20 PM *To:* ozDotNet <ozdotnet@ozdotnet.com> *Subject:* Re: C# SqlConnection Nested Stored Proc Deadlock Can you use a try catch in the outewr proc and get the error number such as in this page? https://technet.microsoft.com/en-us/library/aa175791(v=sql.80).aspx ( *SQL Essentials: Using TRY/CATCH to Resolve Deadlocks in SQL Server 2005)* regards, Preet, in Auckland NZ On 1 February 2018 at 14:42, David Burstin <david.burs...@gmail.com> wrote: Hi folks, Hope everyone is having a great day and enjoyed the super blue blood moon last night. Quick question: Given a stored procedure "OuterProc" that calls another stored procedure internally "InnerProc" When I call "OuterProc" from C# using a connection with a started transaction, and "InnerProc" becomes a deadlock victim Is there a way that I can identify in my C# code that "InnerProc" was the deadlock victim, given that my C# code only knows about "OuterProc"? Cheers Dave
