How to reduce SQL Server database deadlock

A deadlock occurs if two user processes lock different resources and then attempt to lock resources that are locked by the other. At this point, SQL Server automatically selects and aborts one of the processes to unlock the deadlock, allowing another process to continue processing. The system will rollback the aborted transaction and send an error message to the user who is being rolled back.

Most well-designed applications will resubmit the transaction after receiving this error message, and the likelihood of submitting success is high. However, this often occurs on the server, which can significantly degrade server performance. To avoid deadlocks, design applications should follow certain principles, including:

Let the application access server resources in the same order each time.

prohibits any user input during a transaction. User input should be collected before the transaction begins.

Try to keep the business short and simple.

If appropriate, specify the lowest possible isolation level for the user connection that is running the transaction. [Apply to 6.5,7.0,2000]

In addition, for the deadlock problem with SQL Server, here are some tips that are useful in practice.

Use SQL Server Profiler's Create Trace wizard to run the "identify the cause of a deadlock" trace to assist in identifying the deadlock problem, which will provide the raw data to help find the cause of the database's deadlock. [Apply to 7.0,2000]

If you cannot eliminate all deadlocks in your application, make sure that you provide a program logic that automatically resubmit transactions at random intervals after a deadlock occurs and a user transaction is aborted. The randomness of waiting time is important here because another competing transaction may be waiting, and we should not allow two competing transactions to wait for the same time and then execute them at the same time, which would result in a new deadlock. [Apply to 6.5,7.0,2000]

Simplify all T-SQL transactions as much as possible. This will reduce the number of locks of various types and help improve the overall performance of SQL Server applications. If possible, split more complex transactions into simpler transactions. [Apply to 6.5,7.0,2000]

All conditional logic, variable assignment, and other related preliminary setup operations should be done outside of the transaction and should not be placed within the transaction. Never suspend a transaction in order to accept user input, and user input should always be done outside of the transaction. [Applies to 6.5,7.0, 2000]

Encapsulates all transactions within a stored procedure, including the BEGIN TRANSACTION and COMMIT TRANSACTION statements. The move helps reduce blocked locks in two ways. First, it restricts communication between the transaction Run-time client and SQL Server, so that any message between the two can only occur in a non transactional run time (reducing the time the transaction runs). Second, it prevents the user from leaving an unfinished transaction (leaving an unlocked lock) because the stored procedure forces the transaction that it initiates or completes, or aborts. [Apply to 6.5,7.0,2000]

If the client program needs to check the data for a certain amount of time first, then it may update the data, or it may not update the data, it is best not to lock the record during the entire record check. Assuming that most of the time is checking the data rather than updating the data, one way to deal with this particular situation is to select the record (without the update clause). The UPDATE clause adds a shared lock to the record and then sends it to the customer.

If the user only views the record but never updates it, the program can do nothing; Conversely, if a user decides to update a record, he can check whether the current data is the same as the data that was previously extracted, and then execute update.

Similarly, we can also check the time identity column in the record, if it exists. If the data is the same, the update operation is performed; If the record has changed, the application should prompt the user so that the user decides what to do with it. Although this method needs to write more code, but it can reduce the lock time and times, improve the overall performance of the application. [Apply to 6.5,7.0,2000]

Specify the least restrictive transaction isolation level for user connections as much as possible, rather than always using the default Read committed. To avoid any other problems arising therefrom, you should refer to the effects of different isolation levels and carefully analyze the characteristics of the transaction. [Apply to 6.5,7.0,2000]

The use of cursors reduces concurrency. To avoid this, you should use the READ_ONLY cursor option if you can use a read-only cursor, otherwise, if you need to make an update, try using the optimistic cursor option to reduce the lock. Try to avoid using the scroll_locks cursor option, which increases the problem caused by record locking. [Apply to 6.5,7.0,2000]

If the user complains that they have to wait for the system to complete the transaction, you should check that the resource lockout on the server is the cause of the problem. You can use SQL Server Locks object:average wait Time (ms) for such checks to measure the average latency of various locks.

If one or more types of locks can be identified that result in a transaction delay, it is possible to further explore the possibility of determining which transaction produced the lock. Profiler is the best tool for this kind of concrete analysis. [Apply to 7.0,2000]

Using sp_who and Sp_who2 (SQL Server Books Online does not have a description of Sp_who2, but Sp_who2 provides more detailed information than sp_who) to determine which users may be blocking other users. [Apply to 6.5,7.0,2000]

Try one or more of the following recommendations to help avoid blocking locks: 1 Use clustered indexes for frequently used tables, 2 try to avoid a single T-SQL statement that affects a large number of records at once, especially insert and UPDATE statements, and 3 try to have the update and DELETE statements use the index; 4 Avoid commit and fallback conflicts when using nested transactions. [Apply to 6.5,7.0,2000]