Shulou(Shulou.com)05/31 Report--

This article focuses on "how to reduce SQLServer database deadlock", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to reduce SQLServer database deadlocks"!

In order to avoid deadlocks, design applications should follow certain principles, including:

Let the application 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 begins. Try to keep the transaction short and simple. If appropriate, specify the lowest possible isolation level for the user connection running the transaction. [applicable to 6.5, 7.0, and 2000].

How to reduce SQLServer database deadlock

Run the "IdentifyTheCauseofaDeadlock" trace using SQLServerProfiler's CreateTraceWizard to assist in identifying deadlock problems, which will provide raw data to help find the cause of the deadlock in the database. [for 7.0 2000] if you cannot eliminate all deadlocks in the application, make sure that you provide a program logic that automatically resubmits the transaction at random intervals after the deadlock occurs and the user transaction is aborted.

The randomness of the wait time is very important here, because another competing transaction may also be waiting, and we should not let two competing transactions wait the same time and then execute them at the same time, which will lead to a new deadlock. [for 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 SQLServer applications. If possible, more complex transactions should be divided into simpler transactions.

All conditional logic, variable assignments, and other related preparatory setting operations should be done outside the transaction, not within the transaction. Never pause a transaction in order to accept user input, which should always be completed outside the transaction. [for 6.5, 7.0, 2000] encapsulates all transactions, including BEGINTRANSACTION and COMMITTRANSACTION statements, within a stored procedure. This helps reduce clogged locks in two ways. First, it restricts the communication between the transaction runtime client and the SQLServer, so that any message between the two can only appear at the non-transaction run time (reducing the transaction run time).

Second, because the stored procedure forces the transaction it started to either complete or abort, it prevents the user from leaving unfinished transactions (leaving undone locks). If the client program needs to check the data for a certain amount of time, then it may or may not update the data, then it is best not to lock the record throughout the record check period. Assuming that data is checked rather than updated most of the time, one way to deal with this particular case is to select the record first (without the UPDATE clause). The UPDATE clause will add a shared lock to the record and then send it to the customer.

If the user only looks at the record but never updates it, the program can do nothing; conversely, if the user decides to update a record, he can check through a WHERE clause to see if the current data is the same as the previously extracted data, and then execute UPDATE.

Similarly, we can also check the time stamp 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 can decide what to do with it. Although this method requires more code, it can reduce the locking time and times, and improve the overall performance of the application. [applicable to 6.5, 7.0, 2000]

Specify a transaction isolation level with the least restrictions for user connections as much as possible instead of always using the default READCOMMITTED. To avoid any other problems arising from this, you should carefully analyze the characteristics of the transaction with reference to the effects of different isolation levels. [for 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 read-only cursors, or try the OPTIMISTIC cursor option to reduce locking if updates are needed. Try to avoid using the SCROLL_LOCKS cursor option, which increases problems caused by record locking. If users complain that they have to wait for the system to complete the transaction, they should check whether resource locking on the server is the cause of the problem.

You can use SQLServerLocksObject:AverageWaitTime (ms) for such checks, which measures the average wait time for various locks. If you can determine that one or more types of locks cause transaction delays, you can further explore whether it is possible to determine exactly which transaction created the lock.

Profiler is the best tool for this kind of concrete analysis. [for 7.0 2000] use sp_who and sp_who2 (SQLServerBooksOnline has no description of sp_who2, but sp_who2 provides more detailed information than sp_who) to determine which users may be blocking other users.

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 T-SQL statements that affect a large number of records at one time, especially INSERT and UPDATE statements

3) try to make UPDATE and DELETE statements use indexes

4) avoid commit and fallback conflicts when using nested transactions. [applicable to 6.5, 7.0, 2000]

At this point, I believe that everyone on "how to reduce SQLServer database deadlock" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.