Type of Deadlocks
By : Kasim Wirama, MCDBA
When concurrent accesses go to shared resource, there might be potentially deadlock issue. There are 2 conditions whether a deadlock might happen. First, deadlock needs at least two tasks or more and at least one exclusive lock exists to initiate deadlock. Remember that the longer your transaction, the more probability that deadlock happens. Principally deadlock problem is incompatibility lock of writer.
There 4 types of deadlocks. So this article will explain those types of them below.
1. Conversion deadlock
Here is the illustration. There are 2 connection, that each of them accesses same resource with shared lock, initially it has no problem because shared lock is compatible each other. Deadlock happens when each of the resource changes their lock from shared lock to exclusive lock.
2. Writer-writer deadlock
This kind of deadlock happens when two connection access two resource in different order when do writing activities. Connection A accesses resource A with exclusive lock, connection B accesses resource B with exclusive lock, then connection A accesses resource B with exclusive lock and connection B accesses resource A with exclusive lock as well. The final condition will result none of the connections will release their resource except SQL Server must to choose one of them as a deadlock victim.
3. Reader-writer deadlock
That’s the most common deadlock. The situation happens when two connection hold exclusive locks on different resource and then each of the connection requests shared lock on their partner connection. For example:
Connection A holds exclusive locks on resource A.
Connection B holds exclusive lock on resource B.
Connection A requests shared lock on resource B.
Connection B requests shared lock on resource A.
4. Cascading deadlock
This deadlock happens when more than two connections involved in deadlock. Even though SQL Server has choose one of them as deadlock victim, but another deadlock still exists by other connections, so SQL Server repeats the action to kill the other connection until no deadlock found.