SQL Server Deadlock Troubleshooting

1. Deadlock Principle

According to the definition in the operating system: A deadlock is a permanent wait state in which each process in a set of processes occupies a resource that is not freed, but is placed in a form that is not freed by other processes.

The four necessary conditions for a deadlock:
Mutex condition (Mutual exclusion): A resource cannot be shared and can only be used by one process.
Request and hold condition (holds and wait): a process that has already received a resource can request a new resource again.
Non-deprivation condition (no pre-emption): The allocated resources cannot be forcibly stripped from the corresponding process.
Cyclic wait condition (Circular wait): Several processes in the system make up loops in which each process waits for resources that are being consumed by neighboring processes.

Corresponds to SQL Server, where, in two or more tasks, each task locks out resources that other tasks are trying to lock, it can cause these tasks to be permanently blocked, resulting in deadlocks, such as a single row (RID, a single row in the heap), a key in the index (key, row lock), a page (PAG, 8KB), District structure (EXT, continuous 8 pages), heap or B-tree (HOBT), table (TAB, including data and index), files (file, database files), application-specific resources (app), metadata (METADATA), allocation Unit (ALLOCATION_UNIT) , the entire database (db). A deadlock is shown for example:

Description: T1, T2 represents two tasks, R1 and R2 represent two resources, and the arrow (such as R1->T1,R2->T2) by which the resource points to a task indicates that the resource is owned by the task, and the arrow that the task points to the resource (such as T1->S2,T2->S1) Indicates that the task is requesting the corresponding target resource;
It satisfies the four necessary conditions for the above deadlock:
(1). Mutual exclusion: Resources S1 and S2 cannot be shared and can only be used by one task at a time;
(2). Request and hold condition: T1 holding S1, request S2;T2 hold S2 while request S1;
(3). Non-deprivation condition: T1 cannot deprive s2,t2 from T2 and can not deprive S1 from T1;
(4). Loop wait Condition: The arrows in the loop form the loops, there is a loop waiting.

2. Deadlock Troubleshooting

(1). Using the SQL Server system stored procedures sp_who and sp_lock, you can view the lock condition in the current database, and then according to Objectid (@objID) (SQL Server 2005)/object_name (@objID) ( SQL Server 2000) can see which resource is locked, using DBCC LD (@blk), to view the last SQL statement that occurred to SQL Server;

CREATE Table #Who (spid int,
ecid int,
Status nvarchar (50),
LoginName nvarchar (50),
Hostname nvarchar (50),
Blk int,
dbname nvarchar (50),
CMD nvarchar (50),
request_id int);

CREATE Table #Lock (spid int,
Dpid int,
ObjID int,
INDLD int,
[Type] nvarchar (20),
Resource nvarchar (50),
Mode nvarchar (10),
Status nvarchar (10)
);

INSERT into #Who
EXEC sp_who Active--see which caused the blocking, blk
INSERT into #Lock
EXEC sp_lock--look at the lock on that resource Id,objid

DECLARE @DBName nvarchar (20);
SET @DBName = ' nameofdatabase '

SELECT #Who. * from #Who WHERE [email protected]
SELECT #Lock. * FROM #Lock
JOIN #Who
On #Who. spid= #Lock. spid
and [email protected];

--The last statement sent to SQL Server
DECLARE CRSR Cursor for
SELECT Blk from #Who WHERE [email protected] and blk<>0;
DECLARE @blk int;
Open CRSR;
FETCH NEXT from CRSR to @blk;
while (@ @FETCH_STATUS = 0)
BEGIN;
DBCC INPUTBUFFER (@blk);
FETCH NEXT from CRSR to @blk;
END;
Close CRSR;
Deallocate CRSR;

--Locked resources
SELECT #Who. Spid,hostname,objid,[type],mode,object_name (ObjID) as objname from #Lock
JOIN #Who
On #Who. spid= #Lock. spid
and [email protected]
WHERE objid<>0;

DROP Table #Who;
DROP Table #Lock;

(2). Use SQL Server Profiler to parse deadlocks: Add the Deadlock Graph event class to the trace. This event class uses the TextData data columns in the trace to populate the XML data for the processes and objects involved in the deadlock. SQL Server Profiler can extract an XML document into a deadlock XML (. xdl) file, which can be viewed later in SQL Server Management Studio.

3. Avoid deadlocks

The four necessary conditions for deadlocks are listed above in 1, and we can avoid deadlocks if we try to break any one or more of them, typically in the following ways (from SQL Server 2005 Books Online):
(1).access the object in the same order. (Note: avoid loops)
(2).Avoid user interaction in a transaction. (Note: Less time to hold resources, less lock-up competition)
(3).keep the transaction short and in a batch. (Note: Same (2), reduce the time of holding resources)
(4).use a lower isolation level. (Note: Use a lower isolation level (for example, read-committed) to hold shared locks more quickly than with higher isolation levels (such as serializable), reducing lock contention)
(5).using row versioning-based isolation levels2005 transactions that support snapshot transaction isolation and specify read_committed isolation levels use row versioning to minimize the chance of deadlocks occurring between read and write operations:
SET allow_snapshot_isolation on-transaction can specify SNAPSHOT transaction isolation level;
Set Read_committed_snapshot on--transactions that specify read_committed isolation level will use row versioning instead of locking. By default (this option is not turned on, without the hint with NOLOCK), the SELECT statement adds S lock (shared lock) to the requested resource, and when this option is turned on, select does not add s lock to the requested resource.
Note:When you set the Read_committed_snapshot option, only connections that perform the ALTER database command are allowed in the database. There must be no other open connections in the database until the ALTER database is complete. The database does not have to be in single-user mode.
(6).using a bound connection。 (Note: A binding session facilitates coordination of operations across multiple sessions on the same server.) A binding session allows one or more sessionsshare the same transactions and locks(but each reply retains its own level of transaction isolation), and you can use the same data without a lock conflict. You can create a binding session from multiple sessions within the same application, or you can create a binding session from multiple applications that contain different sessions. After opening a transaction in one session (BEGIN TRAN), call exec sp_getbindtoken @Token out, get Token, then pass in another session and execute the EXEC sp_bindsession @Token to bind ( The final example demonstrates a binding connection.

4. Deadlock handling method:

(1). Depending on the SQL provided in 2, view the SPID in the wait state and then use the kill spid to kill (the fourth necessary condition for a broken necrosis lock: loop wait); Of course, it's just a temporary solution. We should not be able to troubleshoot deadlocks, kill SP in the user's production environment when we encounter deadlocks, we should consider how to avoid deadlocks.

(2). Use Set Lock_timeout timeout_period (in milliseconds) to set the lock request timeout . By default, the database does not have a time-out period (the Timeout_period value is-1, which can be viewed with the SELECT @ @LOCK_TIMEOUT, that is, waiting indefinitely). When the request lock exceeds Timeout_period, an error is returned. A timeout_period value of 0 means no wait at all, and a message is returned when a lock is encountered. Setting the lock request timed out, breaking the second necessary condition of the deadlock (request and hold condition).

Server: Msg 1222, Level 16, State 50, line 1
The lock request time-out period has been exceeded.

(3). There is a lock monitor thread inside SQL server that performs a deadlock check that identifies the resource that the thread is waiting for when the lock monitor initiates a deadlock search for a particular thread, then locates the owner of a specific resource and recursively resumes the deadlock search for those threads. Until a loop that constitutes a deadlock condition is found. After a deadlock is detected, the database engine chooses the session that runs the transaction with the least rollback cost as the deadlock victim, returns a 1205 error, rolls back the transaction for the deadlock victim, and frees all locks held by the transaction so that the transaction of the other thread can request the resource and continue running.

5. Two deadlock examples and workarounds

5.1 SQL dead Lock

(1). Basic data for testing:

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, perform the following two paragraphs of SQL respectively

--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 above SQL has a sentence of waitfor Delay ' 00:01:00 ', which is used to wait 1 minutes to see the lock in a convenient condition.

(3). View Lock Condition

During the execution of the above WAITFOR statement, execute the statements provided in the second section to view the lock information:

Query1, holds the row exclusive lock (RID:X) in the first row of the Lock1 (only one row of data in the table), and holds the intent Update lock (PAG:IX) of the page on which the row is located, the intent Update lock (TAB:IX) of the table, and Query2, holding the first row in the Lock2 (only one row of data in the table) ), and holds the intent Update lock (PAG:IX) of the page on which the row is located, and the intent Update lock (TAB:IX) of the table rid:x;

After executing the WAITFOR,QUERY1 query Lock2, request to add S lock on the resource, but the row has been Query2 plus x lock, Query2 query Lock1, request to add S lock on the resource, but the row has been Query1 added x lock; So two queries hold resources and do not , which constitutes a deadlock.

(4). Workaround

a). SQL Server automatically selects a SQL as a deadlock victim : After running the two queries above, we will find that one SQL will execute properly, and the other SQL will report the following error:

Server: Msg 1205, Level 13, State 50, line 1
The transaction (Process ID xx) and another process have been deadlocked on the lock resource, and the transaction has been selected as the deadlock victim. Please rerun the transaction.

This is the lock monitor described in section fourth above working.

b). Access Objects in the same order: reverses the order of the update and SELECT statements in any SQL. For example, modify the second SQL to read as follows:

--query2
Begin Tran
SELECT * FROM lock1--apply S lock on Lock1
WaitFor Delay ' 00:01:00 ';
Update Lock2 Set c1=c1+1;--lock2:rid:x
Rollback Tran;

Of course, there is a cost to this modification, which causes the second SQL to be blocked until the first SQL is executed. It takes about 1 minutes to execute the Query1 or Query2 alone, but if Query2 is executed at the same time when the Query1 is started, the QUERY2 takes 2 minutes to complete, and this sequential request of resources reduces concurrency to some extent.

c). Select statement Plus with (NoLock) hint : By default The SELECT statement adds S lock (shared lock) to the queried resource, and S lock is incompatible with the X lock (exclusive lock); NoLock), select does not locking the queried resource (or sch-s lock, sch-s lock can be compatible with any lock), so that the two SQL can access the same resource concurrently. Of course, this method is suitable for reading and writing concurrent deadlocks, but the addition of with (NoLock) may cause dirty reads.

SELECT * from Lock2 with (NOLock)
SELECT * from Lock1 with (NOLock)

d). use a lower isolation level. SQL Server 2000 supports four types of transaction isolation levels (TIL), namely READ UNCOMMITTED, read COMMITTED, repeatable read, Serializable;sql Server The snapshot TIL is added in 2005. by default, SQL Server using read COMMITTED til, we can add a set TRANSACTION isolation level READ uncommitted to the above two SQL to reduce the til in order to avoid deadlocks ; in fact, a transaction running on the READ uncommitted til, where the SELECT statement does not lock or add sch-s locks to the resulting resource, does not add the S lock; but there is one more thing to note:READ Uncommitted til allow dirty read , although added to reduce the Til statement, above two SQL in the execution process will not error, but the execution result is a return 1, a return 2, that is, read the dirty data, perhaps this is not what we expected.

e). Before SQL adds set Lock_timeout timeout_period, when the request lock exceeds the set timeout_period time, it terminates the execution of the current SQL, sacrificing itself and fulfilling others.

f). using row versioning-based isolation levels (SQL Server 2005 support): When the following option is turned on, select does not add S lock, lock, or sch-s lock to the requested resource. This minimizes the chance of deadlocks occurring between read and write operations, and no dirty reads occur. AH

SET allow_snapshot_isolation on
SET Read_committed_snapshot on

g). Use a bound connection (see the next example using the method.) )

5.2 program Deadlock (SQL blocking)

See an example: A typical database operation transaction deadlock analysis, according to my own understanding, I think this should be considered as a C # program deadlock, rather than a deadlock in the database; The following code simulates the process of working with the database in this article:

The irrelevant code that was omitted
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 transaction: Lock table in transaction
ExecuteNonQuery (null, SQL2); Open a new connection 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 ();
}
}
}

Executes an exception that throws a SQL execution timeout to ExecuteNonQuery (null, SQL2), from the perspective of the database:

The code is executed from top to bottom, Session 1 holds the X lock of table Lock1, and the transaction does not end, 1 has been holding X lock is not released, and Session 2 performs a select operation, request to add S lock on table Lock1, but s lock and X lock is incompatible, so the reply 2 is blocked waiting, not waiting, Just wait to get the resource, just timeout in wait ... As we can see, there is no deadlock inside, but the select operation is blocked. The lock monitor for SQL Server cannot detect deadlocks because it is not a database deadlock.

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

The C # program holds the X lock on the table Lock1, while the other SqlConnection wants to request an S lock on the table, which already forms a loop; too greedy, the result oneself to lock oneself to die ...

Although this is not a database deadlock, but it is due to the database resources caused by the deadlock, the above example is mentioned in the resolution of the deadlock method here also basically applies, mainly to avoid the read operation is blocked, the workaround is as follows:

a).Put the Select before the UPDATE statement:Select is not in a transaction, and the S lock is released after execution;
b).Add the Select to the transaction:ExecuteNonQuery (Tran, SQL2);
c).SELECTPlus with (NOLock)Tips:may produce dirty reads;
D).To reduce the transaction isolation level:SELECT statement Plus set TRANSACTION isolation level read UNCOMMITTED; ibid., may produce dirty read;
e).using row versioning-based isolation levels(IBID.).
g).To use a bound connection:Obtain the token of the transaction in the session, and then pass in the newly opened connection, after executing exec sp_bindsession @Token bind the connection, and finally execute EXEC sp_bindsession null; to unbind The last four points to note are:
(1). Multiple connection using a bound connection share the same transaction and the same lock, but each retains its own transaction isolation level;
(2). If "exec sp_bindsession null" in the SQL3 string is replaced with "Commit tran" or "rollback tran", the entire transaction is committed, and the last line of C # code Tran. Commit () will not be executed (execution will error, because the transaction has ended-,-).
(3). After opening the transaction (BEGIN TRAN), you can call exec sp_getbindtoken @Token out to get Token, and if you do not want to end the original transaction in the newly opened connection, this connection Before close, you must perform "exec sp_bindsession null" to unbind the connection, or end the transaction (Commit/tran) before the newly opened Connectoin close.
(4). (SQL Server 2005 Books Online) Subsequent versions of Microsoft SQL Server will remove the feature. Avoid using this feature in new development work, and start modifying applications that are currently using the feature. Use more than one active result set (MARS) or distributed transaction instead.

Tran = connection. BeginTransaction ();
String sql1 = "Update Lock1 SET c1=c1+1";
ExecuteNonQuery (Tran, SQL1); Using transactions: Lock the Test table in the transaction Lock1
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); Open a new connection to operate the test table Lock1
Tran.commit ();

attached: Lock compatibility (from SQL Server 2005 Books Online )

Lock compatibility Controls whether multiple transactions can acquire locks on the same resource at the same time. If a resource is locked by another transaction, a new lock request is granted only if the mode of the request lock is compatible with the mode of the existing lock. If the mode of the request lock is incompatible with the mode of the existing lock, the transaction requesting the new lock waits for the existing lock to be freed or the lock timeout interval to expire.

SQL Server Deadlock Troubleshooting