What is an SQL Server Deadlock?
A deadlock is often confused with regular blocking. When a transaction requests a lock on a resource already locked by another transaction, the requesting transaction must wait until the lock is released. By default, SQL Server transactions do not time out unless LOCK_TIMEOUT is set. In such cases, the requesting transaction is blocked rather than deadlocked because it does not prevent the transaction holding the lock from proceeding. Once the lock is released, the blocked transaction can acquire the lock and continue. Unlike blocking, which can theoretically persist indefinitely, a deadlock is resolved almost immediately.
Deadlocks can occur in any system with multiple threads, not just in relational database management systems, and they may involve resources other than database object locks. For instance, in a multithreaded operating system, threads may acquire resources like memory blocks. If one thread needs a resource currently owned by another thread, it may wait until the other thread releases the resource. This creates a dependency between threads. In SQL Server, deadlocks may also occur when sessions compete for non-database resources like memory or threads.
For example, deadlocks may happen when partitioned tables are altered with the lock_escalation setting set to auto. When this setting allows the SQL Server Database Engine to lock table partitions at the HoBT (Heap or B-tree) level rather than the table level, concurrency increases. However, if different transactions hold partition locks and attempt to acquire locks on other partitions, a deadlock can occur. Setting lock_escalation to table can prevent this type of deadlock, although it reduces concurrency by forcing large updates on partitions to wait for a table lock.
Causes of SQL Server Deadlocks
Each user session may have one or more tasks running, and each task may acquire or wait for resources. The following types of resources can cause blocking, leading to deadlocks:
1. Locks
Deadlocks can occur when waiting for locks on resources like objects, pages, rows, metadata, or application resources.
2. Worker Threads
Queued tasks waiting for available worker threads may result in deadlocks if these tasks hold resources that block all worker threads.
For example, Session S1 starts a transaction and acquires a shared lock (S) on row r1, then goes idle. Meanwhile, active sessions running on all available worker threads attempt to acquire an exclusive lock (X) on r1. Since S1 cannot obtain a worker thread to commit and release the lock, a deadlock occurs.
3. Memory
Deadlocks occur when concurrent requests wait for memory grants that cannot be satisfied with the available memory.
For example, two concurrent queries Q1 and Q2 executed as user-defined functions may acquire 10 MB and 20 MB of memory respectively. If each query requires 30 MB, but only 20 MB of memory is available, both queries must wait for the other to release memory, causing a deadlock.
4. Parallel Query Execution Resources
Coordinator, producer, or consumer threads associated with exchange ports may block each other, leading to deadlocks. These are more likely when processes other than parallel queries are involved.
Deadlocks can also occur during parallel query execution if the system workload unexpectedly changes, such as when new queries start or system worker threads are depleted.
5. Multiple Active Result Sets (MARS) Resources
Resources used to manage interleaved requests under MARS can also result in deadlocks.
User Resources: If a thread waits for a resource controlled by a user application, it is treated as an external or user resource, effectively acting as a lock.
Session Mutex: Tasks within a session are interleaved, meaning only one task can run at a time. A task must acquire exclusive access to the session mutex before execution.
Transaction Mutex: Similarly, tasks within a transaction are interleaved. A task must acquire exclusive access to the transaction mutex to run.
For a task to run under MARS, it must acquire the session mutex. If running within a transaction, it must also acquire the transaction mutex. These mutexes ensure that only one task is active in a given session or transaction at a time. Once the task completes or is abandoned, it releases the transaction mutex first, followed by the session mutex in reverse order of acquisition. However, these resources can cause deadlocks.
