Deadlocks and Transaction Blocking in Microsoft SQL Server

Here's a breakdown of the differences between deadlocks and blocking in Microsoft SQL Server:
Blocking

• Nature: A natural and expected part of database operations.
• Description: Blocking occurs when one transaction (Process A) holds a lock on a resource (like a table or row) and another transaction (Process B) tries to acquire a conflicting lock on the same resource. Process B has to wait until Process A releases the lock.
• Resolution: SQL Server usually resolves blocking automatically. The blocked process resumes once the blocking resource is released.
• Typical Duration: Often short-lived.

Deadlock

• Nature: A serious issue that can cripple database performance if not addressed.
• Description: A deadlock arises in a circular dependency scenario. For example:
  
  • Process A has a lock on Resource 1 and needs a lock on Resource 2.
  • Process B has a lock on Resource 2 and needs a lock on Resource 1.
  • Neither process can proceed, resulting in an indefinite wait.
• Resolution: SQL Server has a built-in deadlock detection mechanism. It chooses one of the processes as a "deadlock victim" and rolls back its transaction, breaking the circular dependency and allowing the other process to continue.
• Typical Duration: Can persist indefinitely until SQL Server intervention.

Table Summary:

Feature	Blocking	Deadlock
Nature	Normal database operation	Undesirable, system-breaking scenario
Cause	Conflicting lock requests	Circular dependency of lock requests
Resolution	Automatic (usually)	SQL Server intervention (chooses a deadlock victim)
Duration	Often temporary	Can persist until resolved

Key Points:

• Prevention is Key: While blocking is normal, deadlocks should be avoided through careful transaction design, query optimization, and potentially using lock hints in critical scenarios.
• Monitoring: SQL Server tools like Profiler and Dynamic Management Views (DMVs) can help identify deadlocks and their causes.

A deadlock^[1] is a situation where one transaction is blocking another from continuing, but that transaction is blocking the first from continuing. It is like a stalemate in chess.
A deadlock is a special situation that occurs when two or more processes are competing for the same set of resources; each prevents the other from obtaining the source it needs to complete its work. Deadlocks are not a relational database specific problem; they can occur in any system where there is potential for resource contention, such as operating systems. However, because this is course deals specifically with SQL Server, and we will focus on the deadlock nuances inside the database engine.

Following is a simple example of a common deadlock scenario:

1. Transaction 1 has a lock on data A and needs to lock data B to complete its transaction.
2. Transaction 2 has a lock on data B and needs to lock data A to complete its transaction.

Each transaction is stuck waiting for the other to release its lock, and neither can complete until the other does, and each process will not release the resource it already has for the other process to use. Deadlocks do not always only include two transactions. It is completely possible that Process A can be waiting on a resource held by Process B, which is, in turn, waiting on a resource held by Process C. If C is waiting on a resource that Process A or Process B has locked, a deadlock is created.

Blocking occurs when two connections need access to same element of data concurrently and one connection is blocked because at a specific time, only one connection can have access. Figure 2.7.1 depicts cars moving through a roundabout, where some cars must pause in order to allow the other cars to traverse the lane which is open.

Deadlock (as depicted in Figure 2.7.2) occurs when one connection is blocked while waiting for a second task to complete its work. The second connection is waiting for the first connection to release the lock and cannot proceed until this happens. This is similar to when you need to cross a traffic stop, but at the same time someone else from the opposite side also wants to cross the intersection. One automobile must pause in order to allow another vehcile to pass through its lane.

Once a deadlock occurs, the only way out is to continue to rollback prior transactions until the lock is cleared. MS SQL Server 2016 automatically does this for you when a deadlock occurs. SQL Server waits until a specified timeout period to keep trying to see if the lock is freed up. This time period is adjustable and can be viewed through the @@lock_timeout global variable, which will return the timeout period in milliseconds.

You set the timeout by using the SET LOCK_TIMEOUT Transact-SQL statement, followed by the number of milliseconds that the system should wait before rolling back blocked transactions. For example, if you wanted to set the timeout to 5 seconds (or 5000 milliseconds), use this Transact-SQL statement:

SET LOCK_TIMEOUT 5000

You can then test to see what the value of the lock timeout period is by issuing this Transact-SQL statement:

SELECT @@LOCK_TIMEOUT

If the value of @@LOCK_TIMEOUT is –1, this indicates that the value has not yet been set for the session. The next lesson will show you how to avoid deadlocks.

Beyond considering just what resource level you are locking, you also should consider what lock mode your query is going to acquire. Just as there are a variety of resources to lock, there are also a variety of lock modes. Some modes are exclusive of each other (which means they do not work together). Some modes do nothing more than essentially modify other modes. Whether modes can work together is based on whether they are compatible . Just as you did with lockable resources, you will next take a look at lock modes one by one.

• Understanding Shared Locks:
  This is the most basic type of lock there is. A shared lock is used when you need only to read the data. That is, you will not be changing anything. A shared lock wants to be your friend, as it is compatible with other shared locks. That does not mean that it still will not cause you grief , although a shared lock doesn’t mind any other kind of lock, there are other locks that do not like shared locks. Shared locks tell other locks that you are out there. They do not serve much of a purpose, yet they cannot really be ignored. However, one thing that shared locks do is prevent users from performing dirty reads.^[2]

[1] Deadlock: A deadlock is a situation wherein two or more competing actions are waiting for the other to finish, and thus neither ever does.

[2] dirty read: A dirty read (also known as uncommitted dependency) occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.