• Adam Thurgar

Deadlock handling


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.


8 views

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 compatibility level of 110 (SQL Server 2012), but when run unde

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 from the software provider is always the same - you have index f

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 from. They also knew that deleting large amounts of data causes t