The root cause of the deadlock caused by MSSQL and the solution method _mysql

One, what is a deadlock

A deadlock is a process in which two or more two processes are executed. As a result of competing for resources, the phenomenon of waiting for each other, without external forces, they will not be able to move forward. At this point the system is called a deadlock state or the system produces a deadlock, and these processes, which are always on each other, are called deadlock processes.

Ii. four necessary conditions for deadlock generation

• Mutex conditions: The process uses the allocated resources for exclusive use, that is, a resource is occupied by only one process over a period of time. If there are other processes requesting resources at this time, the requester can only wait until the process that owns the resource is released

• Request and retention conditions: The process has maintained at least one resource, but a new resource request has been made, and the resource is already occupied by another process, and the request process is blocked, but the other resources it has acquired remain

• Non-deprivation of condition: the resources that the process has acquired, which cannot be deprived until it is used, and released only at the end of use.

• Loop wait Condition: In the event of a deadlock, there must be a process-a circular chain of resources, that is, the P0 in the process set {p0,p1,p2,,pn} is waiting for a P1 resource to occupy; P1 is waiting for the resource occupied by P2, ..., Pn is waiting for resources that have been P0 occupied

These four conditions are necessary for deadlock, as long as the system is deadlocked, these conditions must be set up, and as long as one of the above conditions is not satisfied, the deadlock will not occur.

Iii. how to deal with deadlocks

1) Lock mode

1. Shared Lock (S)

A lock created by a read operation to prevent other transactions from updating data while reading data, and other transactions to 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 by locking prompts or by setting a higher transaction isolation level.

2. Exclusive Lock (X)

A lock on a resource exclusive, a process that locks the requested data source exclusively, so that no other process can acquire any type of lock on this data source. The exclusive lock is held uniformly to the end of the transaction.

3. Update Lock (U)

The update lock is not actually a separate lock, but rather a mixture of shared and exclusive locks. An update lock is obtained when SQL Server performs a data modification operation but first needs to search the table to find the resource that needs to be modified.

Update locks are compatible with shared locks, but only one process can get the update locks on the current data source.

Other processes cannot obtain an update or exclusive lock on the resource, and the update lock acts like a serialized valve (serialization gate), pressing the subsequent request for exclusive locks into the queue. A process that holds an update lock can convert it to an exclusive lock on that resource. Update locks are not sufficient to update data-the actual data modifications still require exclusive locks. Serialization access to exclusive locks avoids conversion of deadlocks, which are retained until the end of the transaction or when they are converted to exclusive locks.

4. Intent Lock (Ix,iu,is)

Intent locks are not independent locking patterns, but rather a mechanism for indicating which resources have been locked.

If an exclusive lock exists on a table page, then another process cannot obtain a shared table lock on the table, which is implemented with an intent lock. To obtain exclusive page locks, update page locks, or intent exclusive page locks, the process must first obtain an intent exclusive lock on the table. Similarly, to obtain shared row locks, a process must first obtain an intent shared lock on the table to prevent other processes from acquiring exclusive table locks.

5. Special lock Mode (SCH_S,SCH_M,BU)

SQL Server provides 3 additional lock modes: schema stability lock, schema modification lock, bulk update lock.

6. Conversion Lock (SIX,SIU,UIX)

Conversion locks are not directly requested by SQL SERVER, but are caused by conversion from one schema to another. SQL SERVER 2008 supports 3 types of conversion locks: SIX, SIU, Uix. The most common is the SIX lock, which occurs when a transaction holds a shared lock on a resource (S) and then an IX lock.

7. Key Range Lock

A key-range lock is a lock that locks a certain range of data in a serializable isolation level. Ensure that data is not allowed to be inserted within the key range of the query data.

SQL SERVER Lock Mode
Abbreviation	Lock mode	Description
S	Shared	Allow other processes to read but not modify locked resources
X	Exclusive	Prevent other processes from reading or modifying data in locked resources
U	Update	Prevents other processes from acquiring update locks or exclusive locks, using the when searching for data to be modified
Is	Intent shared	Indicates that a component of the resource is locked by a shared lock. This type of lock can only be obtained at the table or page level
IU	Intent Update	Indicates that a component of the resource is locked by an update lock. This type of lock can only be obtained 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 only be obtained at the table or page level
SIX	Shared with Intent Exclusive	Represents a resource that is holding a shared lock and a component (one page or a row) is locked by an exclusive lock
SIU	Shared with Intent Update	Represents a resource that is holding a shared lock and a component (one page or a row) is locked by an update lock
Uix	Update with Intent Exclusive	Represents a resource that is holding an update lock and a component (a page or row) locked by an exclusive lock
Sch-s	Schema stability	Indicates that a query using the table is being compiled
Sch-m	Schema modification	Indicates that the structure of the table is being modified
BU	Bulk Update	In a bulk copy operation the data is imported into the table and (manually or automatically) applied Tablock Use when soliciting prompts

2) The size of the lock

SQL SERVER can lock a user's data resources, such as non-system resources, at the table, page, and row levels (system resources are protected by latches). Additionally, SQL SERVER can lock the index key and the index key range.

The Sys.dm_tran_locks view allows you to see who is locked out (such as rows, keys, pages), the mode of the lock, and the identifiers for specific resources. The following view is created based on the Sys.dm_tran_locks view to view locked resources and lock mode (this view allows you to view the tables, pages, rows, and lock types on the data resource) that are locked by the transaction.

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 track the related statements generated by deadlocks by selecting the following options in the SQL Server Profiler event.

4 Deadlock Case Analysis

In the case process65db88, process1d0045948 is the process of statement 1, PROCESS629DC8 is the process of statement 2, and statement 2 gets the update lock on 1689766 pages and waits for the update lock on 1686247 pages and statement 1 Gets the update lock on the 1686247 page of the update lock on the waiting 1689766 page, and two statements wait for the resource to form a loop that causes a deadlock.

5 How to resolve the deadlock

For example, a deadlock case, the analysis of its corresponding statement execution plan is as follows:

It can be seen from the execution plan that the index scan is used to find the data that needs to be updated, which is more performance-intensive, thus resulting in too long locking resources and increasing the probability of deadlock when the statement is executed concurrently.

Treatment mode:

1. Set up a clustered index on the table.

2. Establishes the inclusion index for the related field of the statement update.

After optimization, the statement execution plan is as follows:

The optimized execution plan uses index lookup, which greatly improves the performance of the query statement, reduces the time to lock resources, and reduces the scope of locking resources, thus reducing the probability of the lock resource cycling waiting for events, which will have a certain effect on preventing the occurrence of deadlocks.

Deadlocks cannot be completely avoided, but if the application handles deadlocks appropriately, the impact on any user and the rest of the system can be minimized (appropriate handling is when error 1205 occurs and the application resubmit the batch, and the second attempt is mostly successful.) A process is killed, its transaction is canceled, its locks are freed, and another process involved in the deadlock can complete its work and release the lock, so there is no condition for creating another deadlock. ）

Iv. How to prevent deadlock

The way to block deadlocks is to avoid situations where deadlock conditions are met, and we need to follow the following guidelines in our development process:

1. Try to avoid concurrent execution of statements that involve modifying data.

2. Each transaction is required to lock all data to be used at one time, otherwise it is not allowed to execute.

3. Advance a lock order, all transactions must be carried out in this order to the data blockade. The order in which objects are updated in transactions within a transaction should be as consistent as possible, if different procedures are in effect.

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

5. Use the lowest possible isolation level.

6. Data storage space discretization method. This method refers to the use of various means, logically in a table of data scattered in a number of discrete space to improve access to the table performance. Mainly by the large table by row or column into a number of small tables, or according to different user groups two ways to achieve.

7. Write the application so that the process holds the lock for as short a time as possible so that other processes do not have to spend too long waiting for the lock to be freed.