Database Reference
In-Depth Information
-- Check error number. If deadlock victim error, then reduce retry count
-- for next update retry. If some other error occurred, then exitretry WHILE loop.
if (error_number() = 1205)
set @retry = @retry - 1
else
set @retry = 0
-- Log an error if needed
if xact_state() <> 0
rollback;
end catch
end
Summary
With the exception of intra-query parallelism deadlocks, which are considered to be a bug in the SQL Server code,
deadlocks occur when multiple sessions are competing for the same set of resources. Sometimes this happens when
sessions are updating the same data. In those cases, you need to analyze and possibly refactor the database schema
and application code. More often than not, deadlocks happen due to excessive locking because of the scans in
non-optimized queries. Performance tuning and query refactoring can be used to resolve these cases. Either way, the
key point in troubleshooting deadlocks is finding the root cause of the problem. A deadlock graph and code analysis
helps you identify and fix these issues.
