The root cause of MSSQL deadlock and its solution

1. What is a deadlock

Deadlock refers to the phenomenon that two or more processes are waiting for each other due to the competition for resources during the execution process. Without the action of external forces, they will not be able to advance. When a system is said to be in a deadlock state or the system has a deadlock, these processes that are always waiting on each other are called deadlock processes.

2. Four prerequisites for deadlock generation

The & # 8226; Mutual exclusion condition: Refers to the exclusive use of the resources allocated by the process, that is, a resource is occupied by only one process in a period of time. If there are other processes requesting the resource at this point, the requester can only wait until the process holding the resource is released

The & # 8226; Request and hold condition: A process that has already held at least one resource, but has made a new request for a resource that is already occupied by another process, when the requesting process is blocked but still holds on to other resources it has acquired

The & # 8226; Unstripping condition: Refers to the process has acquired the resources, until not used up, can not be deprived of, only when used up by themselves

The & # 8226; Loop wait condition: it means that when deadlock occurs, there must be a process -- the loop chain of resources, that is, P0 in process set {P0, P1, P2, · ·, Pn} is waiting for a resource occupied by P1; P1 is waiting for the resources occupied by P2... , Pn is waiting for the resource that has been occupied by P0

These four conditions are necessary for a deadlock. As long as a system deadlock occurs, these conditions must be true, but as long as 1 of the above conditions is not met, no deadlock will occur.

3. What to do about deadlocks

1) lock mode

1. Shared locks (S)

A lock created by a read operation to prevent other transactions from updating the data while it is being read; Other transactions can read the data concurrently. A shared lock can be added to a table, page, index key, or data row. The shared lock is released after the data is read at the default isolation level of SQL SERVER, but the release time of the shared lock can be changed by lock prompt or by setting a higher transaction isolation level.

2. Exclusive locks (X)

An exclusive lock on a resource where one process has an exclusive lock on the requested data source, no other process can obtain any type of lock on that data source. An exclusive lock 1 holds until the end of the transaction.

3. Update lock (U)

The update lock is not really a separate lock, but a mixture of shared and exclusive locks. When SQL SERVER performs a data modification operation but first needs to search the table to find the resource that needs to be modified, an update lock is obtained.

Update locks are compatible with shared locks, but only 1 process can obtain the update lock on the current data source,

No other process can obtain an update or exclusive lock for this resource, and the update lock acts as a serialization valve (serialization gate), queuing subsequent requests for an exclusive lock. A process holding an update lock can convert it to an exclusive lock on the resource. Update locks are not sufficient for updating data - actual data changes still require exclusive locks. Serialized access to an exclusive lock avoids a transformation deadlock, and update locks are retained until the end of the transaction or when they are converted to an exclusive lock.

4. Intention lock (IX,IU,IS)

Intentional locking is not a separate locking pattern, but a mechanism to indicate which resources have been locked.

If an exclusive lock exists on a table page, then another process cannot acquire a shared table lock on that table. This hierarchy is implemented using intention locks. In order for a process to obtain an exclusive page lock, update a page lock, or an intended exclusive page lock, it must first obtain an intended exclusive lock on the table. Similarly, in order for a process to acquire a shared row lock, it must first acquire the intended shared lock for the table to prevent another process from acquiring an exclusive table lock.

5. Special locking mode (Sch_s,Sch_m,BU)

SQL SERVER offers three additional lock modes: architecture stable lock, architecture modification lock, and bulk update lock.

6. Conversion lock (SIX,SIU,UIX)

The conversion lock is not directly requested by SQL SERVER, but is caused by switching from one mode to another. SQL SERVER 2008 supports three types of conversion locks: SIX, SIU, and UIX. The most common of these is the SIX lock, which occurs when a transaction holds a shared lock on a resource (S) and then needs another IX lock.

7. Key range lock

A key range lock is a lock that locks 1 range of data at the serializable isolation level. Ensure that no data is inserted within the key range of the query data.

SQL SERVER 锁模式
缩写	锁模式	说明
S	Shared	允许其他进程读取但不能修改锁定的资源
X	Exclusive	防止别的进程读取或者修改锁定资源中的数据
U	Update	防止其它进程获取更新锁或独占锁；在搜索要修改的数据时使用
IS	Intent shared	表示该资源的1个组件被共享锁锁定了。只有在表或页级别才能获得这类锁
IU	Intent update	表示该资源的1个组件被更新锁锁定了。只有在表或页级别才能获得这类锁
IX	Intent exclusive	表示该资源的1个组件被独占锁锁定了。只有在表或页级别才能获得这类锁
SIX	Shared with intent exclusive	表示1个正持有共享锁的资源还有1个组件（1页或1行）被独占锁锁定了
SIU	Shared with intent Update	表示1个正持有共享锁的资源还有1个组件（1页或1行）被更新锁锁定了
UIX	Update with intent exclusive	表示1个正持有更新锁的资源还有1个组件（1页或1行）被独占锁锁定了
Sch-S	Schema stability	表示1个使用该表的查询正在被编译
Sch-M	Schema modification	表示表的结构正在被修改
BU	Bulk Update	在1个大容量复制操作将数据导入表中并且（手动或自动）应用了TABLOCK查 询提示时使用

2) lock granularity

SQL SERVER locks the user's data resources, or non-system resources, at the table, page, row, and so on (system resources are protected with latches). SQL SERVER can also lock index keys and index key ranges.

The ES137en.dm_tran_ES140en view allows you to see who is locked (e.g., row, key, page), the mode of the lock, and the identifier for a particular resource. Create the following view based on the ES141en.dm_ES143en_ES144en view to view the locked resources and the lock mode (this view allows you to view the tables, pages, rows, and lock types that are locked on the data resources).

CREATE VIEW dblocks AS 
SELECT request_session_id AS spid, 
DB_NAME(resource_database_id) AS dbname, 
CASE WHEN resource_type='object' 
THEN OBJECT_NAME(resource_associated_entity_id) 
WHEN resource_associated_entity_id=0 THEN 'n/a' 
ELSE OBJECT_NAME(p.object_id) END AS entity_name, 
index_id, 
resource_type AS RESOURCE, 
resource_description AS DESCRIPTION, 
request_mode AS mode, 
request_status AS STATUS 
FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p ON p.partition_id=t.resource_associated_entity_id 
WHERE resource_database_id=DB_ID()

3) How to track deadlocks

You can trace the statements generated by the deadlock by selecting the following option in the sql server profiler event.

4) Deadlock case analysis

In this case, process65db88 and process1d0045948 are the process of statement 1 and process629dc8 are the process of statement 2. Statement 2 obtains the update lock on page 1689766 and waits for the update lock on page 1686247. While statement 1 obtains the update lock on page 1686,247 and the update lock on page 1689766. The resources waiting for the two statements form a loop, causing a deadlock.

5) How to resolve deadlocks

According to the deadlock case above, the corresponding statement execution plan is analyzed as follows:

It can be seen from the execution plan that index scanning is used to find the data that needs to be updated, which is quite costly in performance, resulting in excessively long locking time and increasing the probability of deadlock when statements are executed concurrently.

Treatment method:

1. Create a clustered index on the table.

2. Create an include index for the related fields of the statement update.

The optimized statement execution plan is as follows:

The optimized execution plan uses index lookup, which will greatly improve the performance of the query statement, reduce the time of locking resources, and also reduce the scope of locking resources, so as to reduce the probability of waiting for events in the loop of locking resources and play a certain role in preventing the occurrence of deadlock.

Deadlocks cannot be completely avoided, but if the application handles them properly, the impact on any users involved and the rest of the system can be minimized (proper handling means that the application resubmits the batch when error 1205 occurs, and the second attempt is mostly successful. A process is killed, its transaction is canceled, its lock is released, and the other process involved in the deadlock can do its job and release the lock, so there is no condition for another deadlock.

How to prevent deadlocks

The way to prevent deadlocks is to avoid situations that meet the deadlock conditions. To do so, we need to follow the following principles during the development process:

1. Avoid concurrent execution of statements that involve modifying data.

2. Require all data to be locked once for every transaction, otherwise execution is not allowed.

3. Predefine a locking sequence in which all transactions must block data. If different procedures perform updates to objects within a transaction, the order of execution should be as close as possible to 1.

4. The execution time of each transaction should not be too long, and the transactions of the program segment may be considered to be divided into several transactions. Input is not required in a transaction; it should be taken before the transaction and then executed quickly.

5. Use the lowest possible isolation level.

6. Discrete method of data storage space. In order to improve the performance of access to the table, the method is to use various means to logically disperse the data in a table into a number of discrete Spaces. This can be achieved by splitting a large table into smaller tables by rows or columns, or by using two methods for different user groups.

7. Write an application that keeps the lock as short as possible so that other processes don't have to wait too long for the lock to be released.