Understanding Database Deadlocks

During early stages of computers, operating system were designed for running only one program at a time so that all  resources of the system were available to a single program at same time. But later, operating systems were enhanced to run multiple programs at once, interleaving them. For that, programs were required to specify in advance what resources they needed so that they could avoid conflicts with other programs running at the same time. By resources, we mean another sub programs or system internal programs. Eventually, operating systems were made with dynamic allocation of resources so that Programs could request further allocations of resources after they had begun running, which made many programs to run parallel with usage of same resource and which sometimes led system to a hanged up stage, which is later called up as deadlocking of resources.

A deadlock can also be described in layman language as a situation in which two computer programs sharing the same resource in parallel within same time and also effectively preventing each other from accessing the resource in middle of their execution period, which results in both programs ceasing to function as these multiple programs have made access with same priority to same resources but waiting for each other to complete its changes and use latest updated information of shared resources. It can be better explained by below example:

• Program A needs to change information located at disk location X.

• Program B also needs to change same information from disk location X as required by Program A.

• When both programs have entered their request at exactly same time, they both made a share lock with that disk location but none had completed its information change

• Program A now requests an exclusive lock on disk location X, and is blocked until Program B finishes and releases the share lock it has on disk location X.

• Program B now requests an exclusive lock on disk location X, and is blocked until Program A finishes and releases the share lock it has on disk location X.

Program A cannot complete until Program B completes, but Program B is blocked by Program A. This condition is also called a cyclic dependency: Program A has a dependency on Program B, and Program B closes the circle by having a dependency on Program A.

Now, both Programs falls in a deadlock situation which will wait forever unless the deadlock is broken by an external process. The Microsoft SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its Program with an error. This allows the other task to complete its Program. The application with the Program that terminated with an error can retry the transaction, which usually completes after the other deadlocked Program has finished.

Deadlocking is often confused with normal locking but normal locking exists for specific time interval  as set with database settings and deadlock can never end until forcibly terminated by system / database utilities. Normal locking is a subset of deadlocks, which exists for short intervals with no dependency locks on other running transaction(s).