Sorry, error 3960, not 3760
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:* Greg Low [mailto:[email protected]] *Sent:* Thursday, 1 February 2018 12:40 PM *To:* 'ozDotNet' <[email protected]> *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 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: www.sqldownunder.com |http://greglow.me *From:* [email protected] [mailto:[email protected] <[email protected]>] *On Behalf Of *Preet Sangha *Sent:* Thursday, 1 February 2018 12:20 PM *To:* ozDotNet <[email protected]> *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 <[email protected]> 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
