Hi
Don't mix c# transactions and sql transactions. You will certainly have dead
locks if you do. Pick one and stick with it.
Check that you are not trying to read the same line that the inner stored
procedure is trying to modify. Or the inverse. Then check for triggers and see
what they are doing, and also any in line functions.If you can't identify the
problem, try rewriting the procedures so that any ids are requested up front
and not included in joins within the update / insertIf none of this works try
removing the indexes and rebuild them based what the execution plan tells you.
Personally I would never do logic like this in a stored procedure. If the
client wants to retry then they should call again. While the database is doing
this kind of fail / retry code other connections are hitting locks on the table
and failing as well. Leading to all sorts of errors that will be even harder to
debug.
In the dal you should only be retrying what you know you can correct. Ie
missing ids. But in that case you should have checked before anyway. If there
are soo many quries being executed that the db is failing to provide
connections. Move all updates / inserts into a queued dispatch system that can
be throttled. Try the query. If it completes flag the caller. If it fails pop
it back on the queue x number of times then fail gracefully.
.02c Davy
Sent from my Samsung Galaxy smartphone.-------- Original message --------From:
David Burstin <david.burs...@gmail.com> Date: 01/02/2018 06:23 (GMT+01:00)
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; ENDCOMMIT
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.
RegardsDavid
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 mobile│ +61 3 8676 4913 faxSQL 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 mobile│ +61 3 8676 4913 faxSQL Down Under |
Web: www.sqldownunder.com |http://greglow.me From:
ozdotnet-boun...@ozdotnet.com [mailto: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 victimIs 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"? CheersDave
