SQL  Deadlock Description for Server

catalogue1 Understanding Deadlocks2 Detect and end deadlock2.1

1 Understanding Deadlocks

Deadlocks are competitive concurrent locks in a database, typically in multi-step transactions.
When two or more tasks permanently block each other, each task locks the resources that other tasks are attempting to lock, a deadlock occurs. For example:

• Transaction A obtains the shared lock on line 1.
• Transaction B obtains the shared lock on line 2.
• Transaction A now requests an exclusive lock on line 2 and is blocked until transaction B completes and releases the shared lock on line 2.
• Transaction B now requests an exclusive lock on line 1 and is blocked until transaction A completes and releases its shared lock on line 1.

Transaction A cannot complete before transaction B completes, but transaction B is blocked by transaction A. This condition is also known as a circular dependency relationship: Transaction A depends on Transaction B, which closes the loop by relying on Transaction A.

Two transactions in a deadlock will always wait unless the deadlock is broken by an external process. The SQL Server Database Engine Deadlock Monitor regularly checks for tasks that are in a deadlock state. If the monitor detects a circular dependency, it will select one of the tasks as the victim and terminate its transaction in the event of an error. This allows other tasks to complete their transactions. Applications with transactions terminated due to errors can retry the transaction, which typically completes after another deadlock transaction completes.

Deadlocks are often confused with normal blocking. When a transaction requests to lock a resource locked by another transaction, the requesting transaction will wait until the lock is released. By default, unless LOCK is set_ TIMEOUT, otherwise SQL Server transactions will not time out. The request transaction is blocked instead of a deadlock, as the request transaction did not perform any action to block the transaction that owns the lock. Ultimately, the owning transaction will complete and release the lock, and then the requesting transaction will be granted the lock and continue. Deadlocks can be resolved almost immediately, while blocking can theoretically persist indefinitely. Deadlock is sometimes referred to as a fatal embrace.

Deadlocks are situations that can occur on any system with multiple threads, not only on relational database management systems, but also on resources other than locks on database objects. For example, threads in a multithreaded operating system may acquire one or more resources, such as memory blocks. If the resource to be obtained is currently owned by another thread, the first thread may have to wait for the thread to release the target resource. It is said that the waiting thread depends on the owning thread of that specific resource. In an instance of the SQL Server Database Engine, a session may deadlock when acquiring non database resources such as memory or threads.

In the figure, transaction T1 relies on transaction T2 of the table lock resource. Similarly, transaction T2 relies on transaction T1 of table lock resources. Due to these dependencies forming a loop, there is a deadlock between transactions T1 and T2.

When partitioning a table and setting it to AUTO, a deadlock also occurs. When set to AUTO, concurrency increases by allowing the SQL Server database engine to lock table partitions at the HoBT level instead of the table level. However, when a separate transaction holds a partition lock in a table and wishes to use the lock at a certain location on another transaction partition, this can lead to a deadlock. This type of deadlock can be set to; Although this setting will reduce concurrency by forcing a large number of updates to the partition to wait for table locking.

2 Detect and end deadlock

When two or more tasks permanently block each other, each task locks the resources that other tasks are attempting to lock, a deadlock occurs. The following figure shows an advanced view of the deadlock state, where:

• Task T1 has locked resource R1 (indicated by the arrow from R1 to T1) and requested to lock resource R2 (indicated by the arrow from T1 to R2).
• Task T2 has locked resource R2 (indicated by the arrow from R2 to T2) and requested to lock resource R1 (indicated by the arrow from T2 to R1).
• Due to the inability of both tasks to continue until resources are available, and the inability to release these two resources before the task continues, there is a deadlock state.

The SQL Server database engine automatically detects deadlock cycles in SQL Server. The SQL Server database engine selects one of the sessions as the deadlock victim, and the current transaction will terminate with an error to break the deadlock.

2.1. Possible Deadlock Resources

Each user session may represent running one or more tasks, where each task may acquire or wait to acquire various resources. The following types of resources may cause blockages, leading to deadlocks.

• Lock. Waiting for locks to acquire resources such as objects, pages, rows, metadata, and applications may lead to deadlocks. For example, transaction T1 has a shared (S) lock on row r1 and is waiting to acquire an exclusive (X) lock on r2. Transaction T2 has a shared (S) lock on r2 and is waiting to acquire an exclusive (X) lock on row r1. This will result in a locking loop, where T1 and T2 wait for each other to release the locked resources.
• Worker thread. Queued tasks waiting for available worker threads may cause deadlocks. If the queued task has resources to block all worker threads, it can cause a deadlock. For example, session S1 initiates a transaction and obtains a shared (S) lock on row r1, then enters a sleep state. The active session running on all available worker threads is attempting to acquire an exclusive (X) lock on row r1. Due to session S1 being unable to obtain the worker thread, it is unable to commit the transaction and release the lock on row r1. This can cause a deadlock.
• Memory. When concurrent requests are waiting for memory grants that cannot be met by available memory, a deadlock may occur. For example, two concurrent queries Q1 and Q2 are executed as user-defined functions, obtaining 10MB and 20MB of memory, respectively. If each query requires 30MB and the total available memory is 20MB, Q1 and Q2 must wait for each other to release memory, which can cause a deadlock.
• Resources related to parallel query execution. The coordinator, producer, or consumer threads associated with the exchange port may block each other, resulting in a deadlock, typically when containing at least one other process that is not part of the parallel query. In addition, when parallel queries begin to execute, SQL Server determines the degree of parallelism or the number of worker threads based on the current workload. If the system workload unexpectedly changes (such as new queries starting to run on the server or insufficient system worker threads), a deadlock may occur.
• Multiple Activity Result Set (MARS) resources. User resources, session mutexes, transaction mutexes, and other resources are used to control the interleaving of multiple active requests under MARS.

In order for a task to run under MARS, it must acquire a session mutex. If the task is running under a transaction, it must obtain a transaction mutex. This ensures that only one task is active at a time in a given session and given transaction. After obtaining the required mutex, the task can be executed. When a task is completed or generated in the middle of a request, it will first release the transaction mutex, and then release the session mutex in the opposite order of acquisition. However, these resources may experience deadlocks. In the following pseudocode, two tasks (user request U1 and user request U2) run in the same session.

The stored procedure executed from user request U1 has obtained session mutex. If a stored procedure takes a long time to execute, the SQL Server database engine assumes that the stored procedure is waiting for user input. User request U2 is waiting for session mutex, while user is waiting for result set from U2 and U1 is waiting for user resources. This is a deadlock state, logically explained as:

3 Dealing with Deadlocks

When a SQL Server database engine instance selects a transaction as a deadlock victim, it will terminate the current batch, roll back the transaction, and return error message 1205 to the application.

Since any application that submits Transact-SQL queries can be selected as a deadlock victim, the application should have an error handler that can capture error message 1205. If the application does not catch an error, it can continue operating without knowing that its transaction has been rolled back and that an error may have occurred.

Implementing an error handler that captures error message 1205 allows applications to handle deadlock situations and take remedial measures (such as automatically resubmitting queries involved in the deadlock). By automatically resubmitting the query, users do not need to know that a deadlock has occurred.

The application should pause briefly before resubmitting the query. This gives other transactions involved in the deadlock a chance to complete and release the locks that form part of the deadlock loop. This can minimize the possibility of a deadlock occurring again when a resubmitted query requests its lock.

4 Minimize deadlocks

Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the likelihood of deadlock generation. Minimizing deadlocks can improve transaction throughput and reduce overhead because there are fewer transactions:

• Roll back, undo all work executed by the transaction.
• Resubmitted by the application as they were rolled back during a deadlock.

To help minimize deadlocks to the greatest extent possible, do the following:

• Access objects in the same order.
• Avoiding user interaction in transactions; Keep transactions short and in bulk.
• Use a lower isolation level.
• Use isolation levels based on row version control. Set the database option to enable committed read transactions to use row versioning; Use snapshot isolation.
• Use binding connections.

4.1 Accessing objects in the same order

If all concurrent transactions access objects in the same order, it is unlikely that a deadlock will occur. For example, if two concurrent transactions acquire a lock on a table and then acquire a lock on the table, one transaction is blocked on the table until the other transaction completes. After the first transaction is committed or rolled back, the second transaction will continue and no deadlock will occur. Using stored procedures for all data modifications can standardize the order of accessing objects.

4.2. Avoiding User Interaction in Transactions

Avoid writing transactions that involve user interaction, as batch processing that can run without user intervention is much faster than the speed at which users must manually respond to queries (such as responding to parameter prompts for application requests). For example, if a transaction is waiting for user input and the user goes to lunch or even home on weekends, the user will delay the completion of the transaction. This will reduce system throughput, as any locks held by transactions are only released when the transaction is committed or rolled back. Even if there is no deadlock situation, other transactions accessing the same resource will be blocked while waiting for the transaction to complete.

4.3. Keep the transaction brief and within a batch

When multiple long-running transactions are executed simultaneously in the same database, a deadlock usually occurs. The longer the transaction time, the longer the holding time of exclusive or update locks, thereby preventing other activities and leading to possible deadlock situations.

Saving transactions in a batch can minimize network round-trip during transactions, thereby reducing potential delays when completing transactions and releasing locks.

4.4. Use lower isolation levels

Determine whether transactions can run at lower isolation levels. Implementing readcommit allows a transaction to read data that was previously read (unmodified) by another transaction without waiting for the first transaction to complete. Compared to higher isolation levels (such as serializability), retaining shared locks with lower isolation levels (such as committed reads) has a shorter duration. This reduces lock contention.

4.5. Using isolation levels based on row version control

When the database option is set to ON, transactions running at the read commit isolation level use row version control instead of shared locks during read operations.
Snapshot isolation also uses row version control and does not use shared locks during read operations. Before transactions can run under snapshot isolation, the database option ALLOW must be set_ SNAPSHOT_ ISOLATIONON

Implement these isolation levels to minimize possible deadlocks between read and write operations.

4.6. Using Binding Connections

By using binding connections, two or more connections opened by the same application can collaborate with each other. Any lock obtained by the auxiliary connection is saved as if obtained by the main connection, and vice versa. Therefore, they will not block each other.

4.7. Stopping Transactions

In the deadlock scheme, the victim transaction will automatically stop and roll back. There is no need to stop transactions in a deadlock situation.

summary

Some applications rely on the locking and blocking behavior of read commit isolation. For these applications, some changes need to be made before enabling the use of row version control based isolation levels.

The above is the detailed description of SQL Server deadlocks. For more information about SQL Server deadlocks, please pay attention to other related articles at Script Home!