In SQL, a deadlock occurs when two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs. Deadlocks are a natural mechanism in SQL Server to avoid the session holding locks and waiting for other resources. SQL Server monitors deadlock situations periodically using the deadlock monitor thread, which checks the processes involved in a deadlock and identifies if a session has become a deadlock victim. When a deadlock is detected, the SQL Server Database Engine ends the deadlock by choosing one of the threads as a deadlock victim, rolling back the transaction of the deadlock victim, and returning a 1205 error to the application.
To avoid deadlocks, some useful tips include keeping transactions short, capturing deadlock queries and optimizing them so that they do not conflict with one another, and creating an index. Additionally, it is recommended to apply application code changes or carefully inspect the resources in a deadlock graph.
