How to Reduce SQL Server database deadlocks

If two user processes lock different resources and attempt to lock the resources locked by the other user, a deadlock will occur. At this time, SQL Server automatically selects and terminates one of the processes to remove the deadlock, so that another process can continue to process. The system will roll back the aborted transaction and send an error message to the user of the rolled back transaction.

Most well-designed applications will resubmit the transaction after receiving the error message, which is highly likely to be committed successfully. However, if this happens frequently on the server, the server performance will be significantly reduced. To avoid deadlocks, design applications should follow certain principles, including:

◆ Allow applications to access server resources in the same order each time.

◆ Any user input is prohibited during the transaction. User input should be collected before the transaction starts.

◆ Keep transactions as short and simple as possible.

◆ If appropriate, specify the minimum isolation level for the user connection that runs the transaction. [Applicable to 6.5, 7.0, 2000]

In addition, the following are some useful tips for SQL Server deadlock.

◆ Use The Create Trace Wizard of SQL Server Profiler to run The "Identify The Cause of a Deadlock" Trace to help Identify The Deadlock problem. It will help you find The original data for The Cause of The database Deadlock. [Applicable to 7.0, 2000]

◆ If all deadlocks in the application cannot be eliminated, ensure that the program logic is provided: It can automatically submit the transaction at random intervals after the deadlock occurs and the user transaction is aborted. The random wait time here is very important because another competing transaction may also be waiting. We should not let two competing transactions wait for the same time and then execute them at the same time, this will lead to a new deadlock. [Applicable to 6.5, 7.0, 2000]

◆ Simplify all T-SQL transactions as much as possible. This will reduce the number of various types of locks and help improve the overall performance of SQL Server applications. If possible, more complex transactions should be divided into multiple simple transactions. [Applicable to 6.5, 7.0, 2000]

◆ All the conditional logic, variable assignment, and other related preparatory settings should be completed outside the transaction, and should not be placed in the transaction. Never pause a transaction to accept user input. user input should always be completed outside the transaction. [Applicable to 6.5, 7.0, 2000]

◆ Encapsulate all transactions in the stored procedure, including the begin transaction and commit transaction statements. This helps reduce blocking locks in two aspects. First, it limits the communication between the client program and SQL Server during the transaction run, so that any message between the two can only appear in non-transaction run time (reducing the transaction run time ). Second, because the stored procedure forces the transaction it starts to be completed or aborted, it prevents the user from leaving unfinished transactions (leaving unundone locks ). [Applicable to 6.5, 7.0, 2000]

◆ If the customer program needs to check the data for a certain period of time, then the data may be updated or not updated, it is best not to lock the record during the entire record check period. Assume that most of the time data is checked rather than updated, one way to deal with this special case is to select the record first (without the UPDATE clause. The UPDATE clause adds a shared lock to the record) and sends it to the customer.

If the user only views the record but never updates it, the program can do nothing. If the user decides to update a record, then, you can use a WHERE clause to check whether the current data is the same as the previously extracted data, and then execute UPDATE.

Similarly, we can also check the time ID column in the record (if it exists ). If the data is the same, perform the UPDATE operation. If the record has changed, the application should prompt the user so that the user can decide how to handle it. Although this method requires more code, it can reduce the lock time and number of times and improve the overall performance of the application. [Applicable to 6.5, 7.0, 2000]

◆ Specify the transaction isolation level with the minimum limit for the user connection as much as possible, instead of always using the default read committed. To avoid any other problems, you should carefully analyze the features of transactions based on the effects of different isolation levels. [Applicable to 6.5, 7.0, 2000]

◆ Using a cursor will reduce concurrency. To avoid this, if you can use a read-only cursor, you should use the READ_ONLY cursor option. Otherwise, if you need to update it, try to use the OPTIMISTIC cursor option to reduce the lock. Try to avoid using the SCROLL_LOCKS cursor option, which will increase the issue caused by record locking. [Applicable to 6.5, 7.0, 2000]

◆ If users complain that they have to wait for the system to complete the transaction, they should check whether the resource lock on the server is the cause of the problem. You can use SQL Server Locks Object: Average Wait Time (MS) to measure the Average Wait Time of various Locks when performing this check.

If one or more types of locks cause transaction latency, You can further explore whether you can determine which transaction generates the lock. Profiler is the best tool for such specific analysis. [Applicable to 7.0, 2000]

◆ Use sp_who and sp_who2 (SQL Server Books Online does not provide a description of sp_who2, but sp_who2 provides more detailed information than sp_who) to determine which users may block other users. [Applicable to 6.5, 7.0, 2000]

◆ Try one or more of the following suggestions to avoid blocking locks: 1) use clustered indexes for frequently used tables; 2) try to avoid T-SQL statements that affect a large number of records at a time, especially INSERT and UPDATE statements; 3) Try to make UPDATE and DELETE statements use indexes; 4) Avoid commit and rollback conflicts when using nested transactions. [Applicable to 6.5, 7.0, 2000]

1. SQL Server 2005 FAQs
2. Solve four common failures of SQL Server through transaction logs
3. Management and Application of SQL Server metadata