top of page

Deadlock handling

  • Adam Thurgar
  • Apr 8, 2019
  • 1 min read

On production SQL Servers I usually like to deadlock trace flags -T1204 and -T1222 as startup parameters. When I review SQL Servers and I find deadlocks, I ask the question about what is being done to resolve these deadlocks to make sure they don't occur and what deadlock handling/retrying is done in the code. In my experience I have found that most organisations do nothing and believe that the next time the query or the job runs successfully that the data will be fine. I worry about data being lost and there being inconsistencies because with a deadlock there is a rollback and you need to know what has been rolled back. You could be left in a situation where some data has been inserted/updated/deleted but not the lot because other parts may have been rolled back by the deadlock. You need to handle deadlocks in your T-SQL using explicit transactions and try catch error handling. Ideally you would like not to have any deadlocks, but if they do occur then you should have some faith that your code has dealt with it properly and your data is in a consistent state.


 
 
 

Recent Posts

See All
Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a...

 
 
 
Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response...

 
 
 
Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete...

 
 
 

Comments

Couldn’t Load Comments
It looks like there was a technical problem. Try reconnecting or refreshing the page.
bottom of page