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-bounces@
> 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
>
>
>
