There are a few terms that often are confused in SQL Server. These are locking, blocking, and deadlocks. These are all very different from each other.
Locking is a normal and desirable process. Blocking is also normal, but is less desirable. Deadlocking is not a normal process and is never desirable.
A lock occurs when a resource is accessed by a user. For example, a user needs to update a row. Since this operation requires a data change, we would expect the connection to receive an exclusive lock on the row. This ensures that the row will be changed only by that user at that time. Locking protects the data in the database.
In a perfect world, each user could freely access data without overlapping any other user. However, this isn’t realistic. Blocking occurs when one user has a lock on a resource and another user needs the resource. The second user must wait until the first one is finished. This also protects the data. Blocking is related to the “isolation” property of the ACID properties.
Deadlocks are not normal and are not desirable. The deadlock or “deadly embrace” indicates that one process will not be able to finish because each process needs that other one’s resource.
Consider this scenario:
Let’s say that Bob is running a process that calculates and pays interest for checking and savings accounts. At the same time, Jill runs a process that transfers balances between checking and savings accounts. In Bob’s process, he acquires a lock on the checking resource first. In Jill’s process, she acquires a lock on the savings resource first. Bob’s process can’t finish because it must get the savings resource before it can complete. Jill’s process can’t finish because it must get the checking resource before it can complete. This is a deadlock. SQL Server will eventually choose a deadlock victim and one process will roll back. However, in the meantime, the two processes will hang while SQL Server determines (on a cost-based analysis) which process should be rolled back and issued a deadlock 1205 error.
Deadlocks are very harmful to server performance, and therefore, should be avoided. Here are a few ways to help avoid them.
- Access resources in the same order
- Make transactions short
- Have small units of work. If the resources do not need to be interdependent, consider a separate process for each task.