- 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.