SQL Server database deadlock reduction techniques

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:

▲Each time an application accesses server resources in the same order.

▲Prohibit any user input during the transaction. User input should be collected before the transaction starts.

▲Keep transactions as short and simple as possible.

▲If appropriate, set the isolation level as low as possible 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.

■ Run "identify the cause of
"Deadlock" trace to help identify deadlocks. It will help you find the source data of the cause of the database deadlock. [Applicable to 7.0, 2000]

■
If all deadlocks in the application cannot be eliminated, make sure that the program logic is provided that it can automatically submit the transaction at random intervals after the deadlock occurs and the user transaction is aborted. Here the waiting time
Randomness 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 new deaths.
Lock. [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 begin transaction and commit
Transaction statement. This helps reduce blocking locks in two aspects. First, it limits the client program and SQL
Server communication, so that any message between the two can only appear in non-transaction running time (reducing the transaction running time ). Second, because the stored procedure forces the transaction or
To prevent 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 with a certain amount of time, then it may update the data or not update the data, it is best not to lock the record during the entire record check period. Assume that data is checked most of the time.
Instead of updating data, one way to deal with this special situation is to select the output record first (without the update clause. The update clause adds a shared lock to the record) and sends it to the customer.
User.

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
Determine how to handle the problem. 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 minimum transaction isolation level 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 reduces 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 the cursor, try to use
Optimistic cursor options to reduce locking. 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 for this type of check.
Locks object: average wait time (MS) that measures the average wait time of various locks.

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 help avoid blocking locks: 1) use clustered indexes for frequently used tables; 2) try to avoid one-time T-SQL statements that affect a large number of records, especially
Insert and update statements; 3) Try to use indexes for update and delete statements; 4) Avoid commit and rollback conflicts when using nested transactions. [Applicable to 6.5,
7.0, 2000]

++ ++
The original URL is as follows: