A deadlock occurs when two processes are each waiting for a locked resource that the other process currently holds. Neither process can move forward until it receives the requested lock on the resource, and neither process can release the lock it is currently holding until it can receive the requested lock. Essentially, neither process can move forward until the other one completes, and neither one can complete until it can move forward.
Two primary types of deadlocks can occur in SQL Server:
• Cycle deadlocks—A cycle deadlock occurs when two processes acquire locks on different resources, and then each needs to acquire a lock on the resource that the other process has. Figure 37.11 demonstrates an example of a cycle deadlock.