December 20, 2019 at 12:07 pm
Thanks Grant. I really appreciate for helping me with this. Yes it is a classic deadlock problem . Actually we have only one table and just one simple update. So adding the resources would be one option you think? Not sure if there is any room to do query tuning it's a simple adhoc update query coming from application.
Not enough details to tell you, sorry. It may. It may not. It depends on so much.
Despite the query being simple on the surface, there may be tuning opportunities. Check the execution plan.
Trust me, there's more going on than one simple update statement. If that's all there was, you'd see blocking, not deadlocks. There has to be multiple steps involved with more than one action to get a deadlock like this. If you're unsure of the steps involved, I'd suggest capturing sql_batch_started, sql_batch_completed and sql_statement_completed using Extended Events along with enabling Causality Tracking to be able to group together sets of behavior. You should then be able to see all the queries that are leading up to the deadlock. Be prepared, depending on how well you can filter the Extended Event session, you may be capturing a lot of data. Plan to run it in short bursts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 20, 2019 at 12:09 pm
The app may be handling the deadlock "gracefully" by retrying the failed process (try/catch) without the user even knowing, but ideally the app should be fixed to reduce the deadlocks. Using Try/Catch and not actually fixing the cause is just lazy, although there are occasionally places where fixing a deadlock is really difficult.
Now this is interesting, are we to say its safe to write apps that do not retry or recover after a deadlock? I know that oftentimes app developers will just ignore deadlocks or concurrency errors like doomed transactions, but I thought OMITTING the try/catch was the lazy path, not USING them.
Once the deadlock occurs, the transaction is bailed from, and the app continues with other work, isn't this sort of a loss of data situation?
I've been of the opinion to try / catch, because with query plans that get recompiled and changed, I thought it was a relatively tall order to predict every instance of deadlocks ahead of time, am I wrong here?
Your dev team should absolutely be catching the errors. Unhandled errors are actually a vector for hacking your system, so it's a must that all errors are trapped appropriately. Whether or not you issue a retry on a failed transaction is a different discussion than catching the errors. That's more of a business & performance & behavioral decision and has less to do with a single best practice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 20, 2019 at 12:11 pm
Yes to all the stuff @leo.Miller states. We simply don't have the data we need to tell you what the solution is. I'm pretty sure you don't have the data yet.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply