How to reduce the occurrence of SQL Server deadlock

A deadlock is a group of resources in which two or more than two threads in the execution process, in contention for a resource sometimes caused by waiting for each other, without external forces, they will not be able to advance, death may be a deadlock, these are always waiting for each other process known as the deadlock thread. Simply put, process a waits for process B to release his resources, b waits for a to release his resources, and waits for each other to form a deadlock.

In a database, if you need to modify a piece of data, first the database management system will be locked above to ensure that only one transaction can be modified at the same time. If the thread T1 of transaction 1 has an exclusive lock on table A, the thread T2 of transaction 2 has an exclusive lock on table B, and then a lock on table A is required. Transaction 2 cannot get this lock because transaction 1 already owns it. Transaction 2 is blocked, waiting for transaction 1. Then, transaction 1 requires a lock on table B, but the lock cannot be obtained because transaction 2 locks it. A transaction cannot release a held lock until it is committed or rolled back. Because transactions require a lock that is controlled by each other to continue, they cannot be committed or rolled back so that a database deadlock occurs.

When writing stored procedures, because some stored procedures transactional operations are more frequent, if the first lock table A, and then lock table B, then in all the stored procedures in this order to lock them. If you accidentally lock table B in a stored procedure and then lock table A, this can cause a deadlock. And deadlocks are generally less likely to be found.

If this deadlock occurs frequently on the server, the performance of the server is reduced, so when the application is in use, we need to keep track of it, using sp_who and Sp_who2 to determine which users may be blocking other users, We can also use the following stored procedures to track the impact of specific deadlock execution:

CREATE PROCEDURE Sp_who_lock

As

Begin

declare @spid int, @bl int, @intTransactionCountOnEntry

int, @intRowcount

int, @intCountProperties

int, @intCounter

int CREATE TABLE

#tmp_lock_who

(ID int identity (1,1), spid smallint,bl smallint) IF @ @ERROR <>0 return

@ @ERRORinsert into

#tmp_lock_who (SPID,BL) Select

0, Blockedfrom (SELECT * from sysprocesses where

BLOCKED>0)

A Where NOT EXISTS (SELECT * from sysprocesses where blocked>0)

b where a.blocked=spid) union select spid,blocked from sysprocesses where

Blocked>0if

@ @ERROR <>0 return @ @ERROR--Find the number of records in a temporary table select

@intCountProperties = Count (*), @intCounter = 1from #tmp_lock_whoIF

@ @ERROR <>0 return @ @ERROR if @intCountProperties =0select

' Now there is no blocking and deadlock information '

As message--loop start while @intCounter <= @intCountPropertiesbegin--Take first record select

@spid = spid, @bl = blfrom #tmp_lock_who where id = @intCounter beginif @spid =0 Select

' Cause the database deadlock is: ' + CAST (@bl as VARCHAR (10)) + ' process number,

The SQL syntax for its execution is as follows ' Elseselect

' Process number spid: ' + CAST (@spid as VARCHAR (10)) + ' is ' +

' Process number spid: ' + CAST (@bl as VARCHAR (10)) + ' blocked,

The current process executes the following SQL syntax ' DBCC inputbuffer (@bl) End--

The loop pointer moves down the set @intCounter = @intCounter + 1enddrop table #tmp_lock_who

return 0

We only need to execute Sp_who_lock in the Query Analyzer, we can specifically capture the execution of the blocking process, then we can the corresponding SQL statements or stored procedures for performance improvement and design.

So we are in the database design, although not completely avoid deadlock, but can make the number of deadlocks to minimize. Increase transaction throughput and reduce system overhead because there are few transactions, so follow these guidelines:

Access objects in the same order

If all concurrent transactions access the object in the same order, the likelihood of a deadlock is reduced. When writing SQL statements or stored procedures, it is necessary to obtain the lock on table A in two concurrent transactions sequentially, then obtain the lock on table B, and another transaction is blocked on table A before the first transaction completes. After the first transaction commits or rolls back, the second transaction continues, and cannot be written in the statement to obtain the lock on table B before obtaining the lock on table A.

Avoid user interaction in a transaction

Avoid writing transactions that contain user interaction, because batches that run without user interaction are much faster than users manually respond to queries, such as prompts to reply to application request parameters. For example, if a transaction is waiting for user input and the user is doing something else, the user suspends the transaction and makes it impossible to complete. This reduces system throughput because any locks held by a transaction are released only when the transaction commits or rolls back. Even if a deadlock is not present, other transactions that access the same resource are blocked waiting for the transaction to complete.

Keep transactions short and in a batch

Deadlocks usually occur when multiple long-running transactions are executed concurrently in the same database. The longer the transaction is run, the longer it will hold the exclusive lock or update the lock, blocking other activities and possibly causing deadlocks. Keep transactions in a batch, you can minimize the network traffic round-trip to the transaction, reduce the possible delay in completing the transaction, and release the lock.

Use low isolation Level

Determines whether a transaction can run at a lower isolation level. Performing a commit read allows the transaction to read data that has been read (unmodified) by another transaction without waiting for the first transaction to complete. The use of lower isolation levels (for example, commit reading) without high isolation levels (such as serializable reading) reduces the time to hold shared locks, thereby reducing lock contention.

Using Bound connections

Use a bound connection to enable two or more connections that are open by the same application to cooperate with one another. Any locks acquired by a secondary connection can be held in the same way as locks obtained by the primary connection, and vice versa, and therefore do not block each other.

Some of the following recommendations for deadlocks occur:

1 Use clustered indexes for frequently used tables;

(2) Try to avoid the one-time impact of a large number of records of T-SQL statements, especially INSERT and UPDATE statements;

3 Try to get the update and DELETE statements to use the index;

4 Avoid submitting and fallback conflicts when using nested transactions;

5 for some data does not need to read the updated value of the table in time to write SQL in the table in the background plus (NOLOCK), such as: Select * from TableA (NOLOCK)