How to reduce the occurrence of SQL Server deadlocks

A deadlock occurs when two or more threads compete for a certain resource during execution. Without external force, they will not be able to push forward, and deadlocks may occur when they die. These processes that are always waiting for each other are called deadlocks. Simply put, process A waits for process B to release its resources, and process B waits for process A again.

A deadlock occurs when two or more threads compete for a certain resource during execution. Without external force, they will not be able to push forward, and deadlocks may occur when they die. These processes that are always waiting for each other are called deadlocks. Simply put, process A waits for process B to release its resources, and process B waits for process A again.

A deadlock occurs when two or more threads compete for a certain resource during execution. Without external force, they will not be able to push forward, and deadlocks may occur when they die. These processes that are always waiting for each other are called deadlocks. Simply put, process A waits for process B to release its resources, and process B waits for process A to release its resources. In this way, mutual waiting forms A deadlock.

For example, to modify a piece of data in a database, the database management system locks the data to ensure that only one transaction can be modified at a time. For example, thread T1 of transaction 1 has the exclusive lock on Table A, thread T2 of transaction 2 has the exclusive lock on table B, and the lock on Table A is later required. Transaction 2 cannot obtain this lock because transaction 1 already owns it. Transaction 2 is blocked and waits for transaction 1. Then, transaction 1 requires the Lock of Table B, but cannot obtain the lock because transaction 2 locks it. The lock held by the transaction cannot be released before it is committed or rolled back. Because transactions require locks controlled by the other party to continue operations, they cannot be committed or rolled back, so that the database will be deadlocked.

For example, when writing A stored procedure, because transaction operations are performed frequently in some stored procedures, if Table A is first locked and table B is then locked, lock them in this order in all stored procedures. If you accidentally lock table B in A stored procedure and then lock Table A, this may lead to A deadlock. Also, deadlocks are generally not easy to detect.

If such deadlocks often occur on the server, the server performance will be reduced. Therefore, when using applications, we need to track them, use sp_who and sp_who2 to determine which users may block other users. We can also use the following stored procedure 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

# 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 the temporary table select

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

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

'No blocking and deadlock information'

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

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

'The cause of database deadlock is: '+ CAST (@ bl as varchar (10) +' process number,

Its SQL syntax is as follows: 'elseselect

'Process ID: '+ 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) end --

Loop pointer move down set @ intCounter = @ intCounter + 1 enddrop table # tmp_lock_who

Return 0

We only need to execute sp_who_lock in the query analyzer to capture the blocked processes, in this case, we can improve and design the performance of corresponding SQL statements or stored procedures.

Therefore, although we cannot completely avoid deadlocks during database design, we can minimize the number of deadlocks. Increase transaction throughput and reduce system overhead. because there are only a few transactions, you must follow the following principles:

Access objects in the same order

If all concurrent transactions access objects in the same order, the possibility of deadlock will be reduced. When writing SQL statements or stored procedures, you must first obtain the lock on Table A in two concurrent transactions in sequence, and then obtain the lock on table B, before the first transaction is completed, another transaction is blocked on Table. After the first transaction is committed or rolled back, the second transaction continues, instead of getting the lock on table B first in the statement, and then getting the lock on Table.

Avoid user interaction in transactions

Avoid writing transactions that contain user interaction, because running batch processing without user interaction is much faster than manually responding to queries, for example, responding to application request parameter prompts. For example, if the transaction is waiting for user input and the user does something else, the user suspends the transaction so that it cannot be completed. This will reduce the system throughput, because any lock held by the transaction will be released only when the transaction is committed or rolled back. Even if no deadlock occurs, other transactions accessing the same resource will be blocked, waiting for the transaction to complete.

Keep the transaction brief and in a batch

A life-and-death lock is often used to concurrently execute multiple transactions that require long running in the same database. The longer the transaction runs, the longer it takes to hold the exclusive lock or update the lock, blocking other activities and possibly causing a deadlock. Keeping transactions in one batch can minimize the number of network communications to and from the transaction, reduce the possible latency of completing the transaction and release the lock.

Low isolation level

Determine whether the transaction can run at a lower isolation level. The committed read operation allows the transaction to read the data that has been read (not modified) by another transaction without waiting for the completion of the first transaction. Using a lower isolation level (for example, commit read) instead of a higher isolation level (for example, serializable read) can shorten the time for holding shared locks, thus reducing lock contention.

Bind a connection

Bind the connection so that two or more connections opened by the same application can cooperate with each other. Any lock obtained by the secondary connection can be held as the lock obtained by the primary connection, and vice versa, so it will not block each other.

The following are some suggestions for deadlocks:

1) use clustered indexes for frequently used tables;

2) try to avoid affecting a large number of record T-SQL statements at a time, especially INSERT and UPDATE statements;

3) Try to make the UPDATE and DELETE statements use indexes;

4) Avoid commit and rollback conflicts when using nested transactions;

5) when writing SQL statements to tables that do not need to read updated values in a timely manner, add (nolock) to the table background, for example: Select * from tableA (nolock)