Root Cause and solution of MSSQL deadlock, root cause of mssql deadlock

Root Cause and solution of MSSQL deadlock, root cause of mssql deadlock

1. What is deadlock?

A deadlock occurs when two or more processes compete for resources during execution. Without external force, they cannot be pushed forward. at this time, it is said that the system is in a deadlock state or the system has a deadlock. These processes that are always waiting for each other are called deadlock processes.

Ii. Four necessary conditions for deadlock

• Mutex condition: A process schedules the resources allocated to it, that is, a resource is only occupied by one process within a period of time. If there are other processes requesting resources at this time, the requester can only wait until the processes occupying the resources are released after use.

• Request and persistence conditions: a process has maintained at least one resource, but has made new resource requests, and the resource has been occupied by other processes. At this time, the request process is blocked, however, you cannot release other resources that you have obtained.

• Non-deprivation condition: resources obtained by a process cannot be deprived until they are used up. They can only be released by themselves when they are used up.

• Loop wait condition: When a deadlock occurs, there must be a process-a loop chain of resources, that is, a process set {P0, P1, P2 ,···, p0 in Pn} is waiting for resources occupied by P1; P1 is waiting for resources occupied by P2 ,......, Pn is waiting for resources occupied by P0

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

Iii. How to Handle deadlocks

1) Lock mode

1. Shared lock (S)

The lock created by the read operation prevents other transactions from updating data while reading data. Other transactions can read data concurrently. Shared locks can be added to tables, pages, index keys, or data rows. 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 through the lock prompt or a higher transaction isolation level.

2. exclusive lock (X)

If a process exclusively locks the requested data source, other processes cannot obtain any type of lock on the data source. The exclusive lock is held to the end of the transaction.

3. Update lock (U)

Update locks are not an independent lock, but a mixture of shared locks and exclusive locks. When SQL SERVER performs data modification, it first needs to search for a table to find the resource to be modified, and the update lock is obtained.

The update lock is compatible with the shared lock, but only one process can obtain the update lock from the current data source,

Other processes cannot obtain the update lock or exclusive lock of the resource. The update lock acts like a serialization gate and pushes requests that apply for an exclusive lock to the queue. The process holding the update lock can convert it to the exclusive lock on the resource. The update lock is not enough for data update-the exclusive lock is still required for actual data modification. Serialized access to exclusive locks can avoid deadlock conversion. Update locks are retained until the transaction ends or when they are converted to exclusive locks.

4. Intention lock (IX, IU, IS)

The intention lock is not an independent locking mode, but a mechanism for identifying which resources have been locked.

If an exclusive lock exists on a table page, the other process will not be able to obtain the shared table lock on the table. This hierarchical relationship is implemented using the intention lock. To obtain an exclusive page lock, Update page lock, or intention exclusive page lock, a process must first obtain the intention exclusive lock on the table. Similarly, to obtain the shared row lock, the process must first obtain the intention share lock of the table to prevent other processes from obtaining the exclusive table lock.

5. Special lock modes (Sch_s, Sch_m, BU)

SQL SERVER provides three additional lock modes: schema stability lock, schema modification lock, and large-capacity update lock.

6. Conversion lock (SIX, SIU, UIX)

The conversion lock is not directly requested by SQL SERVER, but caused by the conversion from one mode to another. SQL SERVER 2008 supports three types of conversion locks: SIX, SIU, and UIX. the most common among them is the SIX lock. If the transaction holds a shared lock (S) on the resource and then requires an IX lock, the SIX will appear.

7. Key range lock

A key range lock is a lock that locks data within a certain range at the serializable isolation level. Make sure that data cannot be inserted within the key range of the queried data.

SQL SERVERLock mode
Abbreviations	Lock mode	Description
S	Shared	Allow other processes to read but cannot modify locked Resources
X	Exclusive	Prevent other processes from reading or modifying data in locked Resources
U	Update	Prevents other processes from obtaining update locks or exclusive locks; used when searching for data to be modified
IS	Intent shared	Indicates that a component of the resource is locked by the shared lock. This type of lock can be obtained only at the table or page level.
IU	Intent update	Indicates that a component of the resource is locked by the update lock. This type of lock can be obtained only at the table or page level.
IX	Intent exclusive	Indicates that a component of the resource is locked by an exclusive lock. This type of lock can be obtained only at the table or page level.
SIX	Shared with intent exclusive	Indicates that a resource holding a shared lock has another component (one page or one row) locked by the exclusive lock.
SIU	Shared with intent Update	Indicates that a resource holding a shared lock has another component (one page or one row) locked by the update lock.
UIX	Update with intent exclusive	Indicates that a resource holding the update lock has another component (one page or one row) locked by the exclusive lock.
Sch-S	Schema stability	Indicates that a query using this table is being compiled.
Sch-M	Schema modification	The table structure is being modified.
BU	Bulk Update	In a large-capacity copy operation, the data is imported into the table and the TABLOCK query is applied manually or automatically. Use when prompted

2) Lock Granularity

SQL SERVER can lock user data resources, that is, non-system resources, at the table, page, and row level (system resources are protected by latches ). In addition, SQL SERVER can lock the index key and index key range.

In the sys. dm_tran_locks view, you can view who is locked (such as rows, keys, pages), the lock mode, and the identifier of a specific resource. Create the following view based on sys. dm_tran_locks view to view the locked resources and lock mode (this view allows you to view the tables, pages, rows, and lock types added to the data resources that the transaction locks ).

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 deadlocks by selecting the following options in the SQL server profiler event.

4) deadlock Case Analysis

In this case, process65db88, process1d0045948 are the process of statement 1, process629dc8 is the process of Statement 2, and Statement 2 obtains the update lock on page 1689766, waiting for the update lock on page 1686247; statement 1 acquires the update lock on page 1686247 and waits for the update lock on page 1689766. The two statements wait for the resource to form a loop, causing a deadlock.

5) How to Solve deadlocks

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

The execution plan shows that index scanning is used to search for the data to be updated, which consumes a lot of performance. As a result, it takes too long to lock resources, increases the probability of deadlocks during concurrent statement execution.

Processing Method:

1. Create a clustered index on the table.

2. Create an index for the fields updated by the statement.

After optimization, the statement execution plan is as follows:

The optimized Execution Plan uses index search, which greatly improves the performance of the query statement, reduces the resource lock time, and reduces the resource lock range, this reduces the probability of lock resource loop wait events, which can prevent deadlocks.

Deadlocks cannot be completely avoided, but if the application properly handles deadlocks, the impact on any user involved and the rest of the system can be minimized (proper handling means that when error 1205 occurs, the application resubmit the batch processing, and the second attempt is successful. A process is killed, its transaction is canceled, its lock is released, and another process involved in the deadlock can complete its work and release the lock, therefore, it does not have the conditions for generating another deadlock .)

4. How to prevent deadlocks

To prevent deadlocks, we must follow the following principles during the development process:

1. Avoid concurrent execution of statements involving data modification.

2. Each transaction is required to lock all the data to be used at a time, otherwise execution is not allowed.

3. Define a locking order in advance. All transactions must block the data in this order. For example, the update execution sequence of objects in different processes within the transaction should be as consistent as possible.

4. the execution time of each transaction cannot be too long. You can consider splitting the transaction in the program segment into several transactions. Input is not required in the transaction. You should get the input before the transaction and then execute the transaction quickly.

5. Use the lowest possible isolation level.

6. Data Storage space discretization method. This method is used to distribute data in a table logically in several discrete spaces, so as to improve the table access performance. A large table can be divided into several small tables by row or column, or different user groups.

7. Write the application so that the process can hold the lock for as short as possible, so that other processes do not have to spend too long waiting for the lock to be released.

Articles you may be interested in:

• In-depth analysis of mssql in high frequency, high concurrency access key to find deadlocks