SQL Server 2008 R2 deadlock monitoring and solution

1. Deadlock principle

According to the definition in the operating system: a deadlock refers to a group of processes that occupy resources that are not released, however, a permanent waiting state is in which resources that are not released by other processes are requested from each other.

Four conditions for deadlock:
Mutual exclusion: resources cannot be shared and can only be used by one process.
Hold and wait: processes that have obtained resources can apply for new resources again.
No pre-emption: Allocated resources cannot be forcibly deprived from the corresponding process.
Loop wait condition (Circular wait): several processes in the system form a loop in which each process is waiting for resources occupied by adjacent processes.

Corresponding to SQL Server, when two or more tasks, if each task locks the resources that other tasks attempt to lock, these tasks will be permanently blocked, resulting in a deadlock; these resources may be: single row (RID, single row in the heap), KEY (KEY, row lock) in the index, Page (PAG, 8 kB), Partition structure (EXT, 8 consecutive pages), heap or B-tree (HOBT), table (TAB, including data and indexes), File (File, database File), dedicated application resources (APP) METADATA (METADATA), allocation unit (Allocation_Unit), and the entire database (DB ). A deadlock example is shown in the following figure:

 

Description: T1 and T2 indicate two tasks. R1 and R2 indicate two resources. The resource points to the task arrow (for example, R1-> T1, R2-> T2) indicates that the resource is held by the change Task. The arrow pointing to the resource by the task (such as T1-> S2, T2-> S1) indicates that the task is requesting the corresponding target resource;
It meets the four conditions for the above deadlock:
(1). Mutex: resources S1 and S2 cannot be shared and can only be used by one task at a time;
(2). Request and retention conditions: when T1 holds S1, request S2; when T2 holds S2, request S1;
(3). Non-deprivation conditions: T1 cannot deprive S2 of T2, and T2 cannot deprive S1 of T1;
(4). Cyclic waiting condition: the arrows in the preceding figure constitute a loop, and cyclic waiting exists.

 

Deadlock monitoring has been very troublesome, and I have not found a good method

If you have a good method, I would like to learn it.

My method is relatively simple:

1. sp_altermessage 1205 modify 1205 error so that it can write logs so that warnings in the proxy can be used

2. Of course, it is a warning in the startup proxy. Open a database email and send the deadlock error to the operator's mailbox. The disadvantage is that there is no detailed deadlock information.

3. system_health is enabled by default in the extended events that come with SQL server 2008 r2, and many values will be crawled.

You can use sys. dm_xe_session_events in combination with sys. dm_xe_sessions to view the captured values. Of course, one of them is the deadlock information.

What should you do when a deadlock occurs:

 

The code is as follows:

Copy code

SELECT REPLACE ( REPLACE (XEventData. XEvent. value ('(data/value) [1]', 'varchar (max )'), '<Victim-list>', '<deadlock> <victim-list> '), '<Process-list>', '</victim-list> <process-list> ') FROM (Select CAST (target_data as xml) as TargetData From sys. dm_xe_session_targets st Join sys. dm_xe_sessions s on s. address = st. event_session_address Where name = 'system _ health') AS Data Cross apply TargetData. nodes ('// RingBufferTarget/event') AS XEventData (XEvent) Where XEventData. XEvent. value ('@ name', 'varchar (4000)') = 'XML _ deadlock_report' Query all deadlock information. Of course, if the memory bottleneck occurs, I am not sure how long it will take to save it. If there are too many deadlocks, you cannot find them smoothly. You want to reduce the result by a bit, it can be used after each query deadlock: Alter event session system_health ON SERVER STATE = stop Go Alter event session system_health ON SERVER STATE = start

Close and open the session of this extended event, the records stored in the memory will be cleared. The next deadlock information is the latest

Monitoring instance 2

After running the following SQL statement, you can find the source of SQL Server deadlock and blocking.

Find the source of SQL Server deadlock and blocking-find the source of SQL Server deadlock and blocking

The code is as follows:

Copy code

Use master Go Declare @ spid int, @ bl int DECLARE s_cur CURSOR Select 0, blocked From (select * from sysprocesses where blocked> 0) Where not exists (select * from sysprocesses where blocked> 0) B Where a. blocked = spid) Union select spid, blocked from sysprocesses where blocked> 0 OPEN s_cur Fetch next from s_cur INTO @ spid, @ bl WHILE @ FETCH_STATUS = 0 Begin If @ spid = 0 Which of the following causes a database deadlock in select: '+ CAST (@ bl as varchar (10) +' process number. The SQL statement executed is AS follows' Else Select 'process No. SPID: '+ CAST (@ spid as varchar (10) + 'be' +' Process ID: '+ CAST (@ bl as varchar (10) +' blocking. The SQL syntax executed by the current process is AS follows' Dbcc inputbuffer (@ bl) Fetch next from s_cur INTO @ spid, @ bl End CLOSE s_cur DEALLOCATE s_cur

View the current process, or deadlock process, and automatically kill the dead process-view the current process, or deadlock process, and automatically kill the dead process
-- Because it is intended for dead, if there is a deadlock process, you can only view the deadlock process. Of course, you can use parameter control to view only the deadlock process, whether there is a deadlock or not.

The code is as follows:

Copy code

Create proc p_lockinfo @ Kill_lock_spid bit = 1, -- whether to kill the deadlock process; 1: Kill; 0: only show @ Show_spid_if_nolock bit = 1 -- if there is no deadlock in the process, whether the normal process information is displayed, 1 is displayed, 0 is not displayed As Declare @ count int, @ s nvarchar (1000), @ I int Select id = identity (int, 1, 1), flag, Process ID = spid, thread ID = kpid, block process ID = blocked, database ID = dbid, Database name = db_name (dbid), user id = uid, user name = loginame, accumulated CPU time = cpu, Login Time = login_time, number of opened transactions = open_tran, process status = status, Workstation name = hostname, application name = program_name, workstation process ID = hostprocess, Domain name = nt_domain, Nic address = net_address Into # t from ( Select flag = 'deadlocked process ', Spid, kpid, a. blocked, dbid, uid, loginame, cpu, login_time, open_tran, Status, hostname, program_name, hostprocess, nt_domain, net_address, S1 = a. spid, s2 = 0 From master .. sysprocesses a join ( Select blocked from master... sysprocesses group by blocked ) B on a. spid = B. blocked where a. blocked = 0 Union all Select '| _ victim _> ', Spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran, Status, hostname, program_name, hostprocess, nt_domain, net_address, S1 = blocked, s2 = 1 From master .. sysprocesses a where blocked <> 0 ) A order by s1, s2 Select @ count = @ rowcount, @ I = 1 If @ count = 0 and @ show_spid_if_nolock = 1 Begin Insert # t Select flag = 'normal process ', Spid, kpid, blocked, dbid, db_name (dbid), uid, loginame, cpu, login_time, Open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address From master .. sysprocesses Set @ count = @ rowcount End If @ count> 0 Begin Create table # t1 (id int identity (1, 1), a nvarchar (30 ), B Int, EventInfo nvarchar (255 )) If @ kill_lock_spid = 1 Begin Declare @ spid varchar (10), @ sign varchar (10) While @ I <= @ count Begin Select @ spid = process ID, @ sign = sign from # t where id = @ I Insert # t1 exec ('dbcc inputbuffer ('+ @ spid + ')') If @ sign = 'deadlocked process' exec ('Kill '+ @ spid) Set @ I = @ I + 1 End End Else While @ I <= @ count Begin Select @ s = 'dbcc inputbuffer ('+ cast (process ID as varchar) + ')' From # t where id = @ I Insert # t1 exec (@ s) Set @ I = @ I + 1 End Select a. *, the SQL statement of the process = B. EventInfo From # t a join # t1 B on a. id = B. id End Go Exec p_lockinfo

Avoid deadlocks

The four conditions for deadlocks are listed in Table 1 above. We can avoid deadlocks by trying to break any or multiple conditions, generally, there are several methods (FROM SQL Server 2005 Books online ):
(1). Access objects in the same order. (Note: avoid loops)
(2) avoid user interaction in transactions. (Note: reduce the time required to hold resources and less lock competition)
(3) keep the transaction brief and in a batch. (Note: same as (2), reduce the resource holding time)
(4). Use a lower isolation level. (Note: Using a lower isolation level (such as committed read) is shorter than using a higher isolation level (such as Serializable) to hold shared locks, reducing lock competition)
(5 ). use the row-based version control isolation level: 2005 supports row-based version control for snapshot transaction isolation and specified READ_COMMITTED isolation level transactions, which can minimize the chance of deadlocks between read and write operations:
SET ALLOW_SNAPSHOT_ISOLATION ON -- the transaction can specify the SNAPSHOT transaction isolation level;
SET READ_COMMITTED_SNAPSHOT ON -- specifies that transactions at the READ_COMMITTED isolation level use row version control instead of locking. By default (this option is not enabled and the with nolock prompt is not added), the SELECT statement will apply the S Lock (shared lock) to the requested resource. After this option is enabled, SELECT does not apply the S lock to the requested resource.
Note: When the READ_COMMITTED_SNAPSHOT option is set, only connections that execute the alter database command are allowed in the DATABASE. Before the alter database is complete, there must be no other open connections in the DATABASE. The database does not have to be in single-user mode.
(6). Bind the connection. (Note: binding sessions facilitates coordination between multiple sessions on the same server. A bound session allows one or more sessions to share the same transaction and lock (but each session retains its own transaction isolation level) and can use the same data without lock conflicts. You can create and bind sessions from multiple sessions in the same application, or from multiple applications that contain different sessions. After starting a transaction (tran in tran) in a session, you can call exec sp_getbindtoken @ Token out; to obtain the Token, then pass in another session and execute EXEC sp_bindsession @ Token to bind the session (the binding connection is demonstrated in the final example ).

Two deadlock examples and solutions

5.1 SQL deadlock

(1). Basic data for testing:

The code is as follows:

Copy code

Create table Lock1 (C1 int default (0 )); Create table Lock2 (C1 int default (0 )); Insert into Lock1 VALUES (1 ); Insert into Lock2 VALUES (1 );   (2) open two query windows and execute the following two SQL statements: -- Query 1 Begin Tran Update Lock1 Set C1 = C1 + 1; WaitFor Delay '00: 01: 00 '; SELECT * FROM Lock2 Rollback Tran;   -- Query 2 Begin Tran Update Lock2 Set C1 = C1 + 1; WaitFor Delay '00: 01: 00 '; SELECT * FROM Lock1 Rollback Tran;

The preceding SQL statement WaitFor Delay '00: 01: 00' is used to wait for 1 minute to check the lock.

(3). View the lock status

During the preceding WaitFor statement execution, run the statement provided in Section 2 to view the lock information:

 

In Query1, it holds the row exclusive lock (RID: X) of the first row in Lock1 (the table has only one row of data) and the intention update lock (PAG: IX) of the page on which the row is located) the table's intention update lock (TAB: IX); in Query2, the row exclusive lock (RID: X) that holds the first row in Lock2 (the table has only one row of data ), and hold the intention update lock (PAG: IX) for the page on which the row is located, and the intention update lock (TAB: IX) for the table );

After Waitfor is executed, Query1 queries Lock2. The request adds the S lock to the resource, but the row has been added with the X lock by Query2. Query2 queries Lock1, and the request adds the S lock to the resource, however, the row has been added with the X lock by Query1. Therefore, the two queries hold different resources and constitute a deadlock.

(4). Solution

A). SQL Server automatically selects an SQL statement as the deadlock victim: After running the preceding two queries, we will find that one SQL statement can be executed normally, and the other SQL statement reports the following error:

Server: message 1205, level 13, status 50, row 1
The transaction (process ID xx) and the other process have been deadlocked on the lock resource, and the transaction has been selected as the deadlock victim. Run the transaction again.

This is the lock monitor described in section 4 above.

B). Access objects in the same order: reversing the order of Update and SELECT statements in any SQL statement. For example, modify the second SQL statement as follows:

The code is as follows:	Copy code
-- Query2 Begin Tran SELECT * FROM Lock1 -- apply for S lock on Lock1 WaitFor Delay '00: 01: 00 '; Update Lock2 Set C1 = C1 + 1; -- Lock2: RID: X Rollback Tran;

Of course, this modification also has a price. This will cause the second SQL to remain in the blocking state until the execution of the first SQL statement is completed. It takes about 1 minute to execute Query1 or Query2 separately. If Query2 is executed immediately when Query1 is executed, it takes 2 minutes to complete Query2 execution; this type of ordered request resources reduces concurrency to a certain extent.

C ). note: By default, the SELECT statement will apply the S Lock (shared lock) to the queried resources. The S lock is incompatible With the X lock (exclusive lock; however, With (NoLock), the SELECT statement does not lock the queried resources (or the Sch-S lock is applied. The Sch-S lock can be compatible With any lock ); therefore, these two sqls can concurrently access the same resource. Of course, this method is suitable for solving read and write concurrency deadlocks, but adding With (NoLock) may cause dirty read.

The code is as follows:	Copy code
SELECT * FROM Lock2 WITH (NOLock) SELECT * FROM Lock1 WITH (NOLock)

D) use a lower isolation level. SQL Server 2000 supports four transaction isolation levels (TIL): read uncommitted, read committed, repeatable read, and SERIALIZABLE; SQL Server 2005 adds SNAPSHOT TIL. By default, SQL Server uses READ COMMITTED TIL. We can add a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the preceding two sqls to reduce the TIL to avoid deadlocks. In fact, for a transaction running on read uncommitted til, the SELECT statement does not lock the result resource or apply the Sch-S lock, but does not apply the S lock. However, note that: read uncommitted til allows dirty reads. Although the above two SQL statements do not report errors during execution after the TIL is reduced, the execution result is one return 1 and one returns 2, that is, we read dirty data. Maybe this is not what we expected.

E). Add SET LOCK_TIMEOUT timeout_period before the SQL statement. When the request lock exceeds the SET timeout_period time, the execution of the current SQL statement will be terminated, at the expense of yourself, to fulfill others' needs.

F ). use the row-based version-based isolation level (supported by SQL Server 2005): After the following options are enabled, SELECT does not apply the S lock to the requested resource, and does not apply the S lock or the Sch-S lock, this minimizes the chance of deadlocks between read and write operations, and prevents dirty reads. Ah

SET ALLOW_SNAPSHOT_ISOLATION ON
SET READ_COMMITTED_SNAPSHOT ON

 

G). Use the bound connection (see the following example .)

 

5.2 program deadlock (SQL blocking)

Let's look at an example: a typical database operation transaction deadlock analysis. According to my own understanding, I think this should be a deadlock in the C # program, rather than a deadlock in the database; the following code simulates the database operation process in this article:

 

The code is as follows:

Copy code

// Omitted irrelevant code SqlConnection conn = new SqlConnection (connectionString ); Conn. Open (); SqlTransaction tran = conn. BeginTransaction (); String sql1 = "Update Lock1 SET C1 = C1 + 1 "; String sql2 = "SELECT * FROM Lock1 "; ExecuteNonQuery (tran, sql1); // use the transaction: the Table is locked in the transaction. ExecuteNonQuery (null, sql2); // A new connection is created to read the Table Public static void ExecuteNonQuery (SqlTransaction tran, string SQL) { SqlCommand cmd = new SqlCommand (SQL ); If (tran! = Null)     { Cmd. Connection = tran. Connection; Cmd. Transaction = tran; Cmd. ExecuteNonQuery ();     } Else     { Using (SqlConnection conn = new SqlConnection (connectionString ))         { Conn. Open (); Cmd. Connection = conn; Cmd. ExecuteNonQuery ();         }     } }

ExecuteNonQuery (null, sql2) throws an SQL execution timeout exception. The following figure shows the problem from the database perspective:

 
When the code is executed from top to bottom, Session 1 holds the X lock of table Lock1, and the transaction has not ended, Session 1 will always hold the X lock and will not be released; Session 2 will execute the select operation, the request adds the S lock to table Lock1, but the S lock is not compatible with the X lock. Therefore, session 2 is blocked and waiting. If it is not waiting, it will get the resource while waiting, waiting for timeout... We can see that there is no deadlock, but the SELECT operation is blocked. Because it is not a database deadlock, the SQL Server lock monitor cannot detect the deadlock.

Let's look at this problem from the perspective of C # program:
         

C # The program holds the X lock on table Lock1 and opens another SqlConnection to request an S lock on the table. The figure already forms a loop. It is too greedy, as a result, I locked myself...

Although this is not a database deadlock, it is a deadlock caused by database resources. The solution mentioned in the above example is also applicable here, mainly to prevent read operations from being blocked, the solution is as follows:

A) put the SELECT statement before the Update statement: The SELECT statement is not in the transaction, and the S lock will be released after execution;
B). Add the SELECT statement to the transaction: ExecuteNonQuery (tran, sql2 );
C). When SELECT is added With (NOLock), the following error occurs: Dirty reads may occur;
D). Reduce the transaction isolation level: set transaction isolation level read uncommitted is added before the SELECT statement; the same as above, dirty reads may occur;
E). Use the row version-based isolation level (same as above ).
G ). use bound connection: Get the token of the transaction in the session, and then pass in the new connection; execute EXEC sp_bindsession @ Token and bind the connection, and finally execute exec sp_bindsession null to cancel binding; note the following four points:
(1). Multiple connections bound to the connection are used to share the same transaction and the same lock, but each of them retains its own transaction isolation level;
(2 ). if "exec sp_bindsession null" in the sql3 string is changed to "commit tran" or "rollback tran", the entire transaction is committed, and the last line of C # code tran is submitted. you don't need to execute Commit () (an error will be reported during execution because the transaction has ended -,-).
(3 ). after starting the transaction (tran in tran), you can call exec sp_getbindtoken @ Token out to obtain the Token. If you do not want to end the original transaction in the new connection, before the connection is closed, you must execute "exec sp_bindsession null" to unbind the connection, or terminate the transaction (commit/tran) before the new connectoin close ).
(4). (SQL server 2005 Books online) later versions of Microsoft SQL Server will delete this function. Avoid using this function in new development work, and start to modify the application that is currently using this function. Use multiple active result sets (MARS) or distributed transactions instead.

 

The code is as follows:

Copy code

Tran = connection. BeginTransaction (); String sql1 = "Update Lock1 SET C1 = C1 + 1 "; ExecuteNonQuery (tran, sql1); // use transaction: the Test table Lock1 is locked in the transaction. String sql2 = @ "DECLARE @ Token varchar (255 ); Exec sp_getbindtoken @ Token out; SELECT @ Token ;"; String token = ExecuteScalar (tran, sql2). ToString (); String sql3 = "EXEC sp_bindsession @ Token; Update Lock1 SET C1 = C1 + 1; exec sp_bindsession null ;"; SqlParameter parameter = new SqlParameter ("@ Token", SqlDbType. VarChar ); Parameter. Value = token; ExecuteNonQuery (null, sql3, parameter); // A new connection is created to operate the Test table Lock1. Tran. Commit ();

Appendix: lock compatibility (from SQL Server 2005 Books online)

Lock compatibility controls whether multiple transactions can simultaneously obtain the locks on the same resource. If the resource has been locked by another transaction, a new lock request is granted only when the request lock mode is compatible with the existing lock mode. If the request lock mode is not compatible with the existing lock mode, the transaction requesting the new lock will wait for the release of the existing lock or wait for the lock timeout interval to expire.