Avoid blocking: Make database performance secure

The SQL Server database itself provides a good lock management mechanism, though. However, in one respect, the database is just a "puppet" of some client applications. This is mainly because the client application has almost complete control over the locks acquired on the server. Client applications often have direct control over query requests and how they are handled. Therefore, if the application is slightly unreasonable in design, it will cause blocking because of the lock mechanism.

This can lead to blocking situations when there are several scenarios.

First, the client canceled the query did not roll back the practice.

Queries are jobs that occur frequently in most applications. However, when users query the backend database through the foreground client application, they sometimes cancel the query for a variety of reasons. If the user opened the query window, because the panic or the user feel slow response to force cancellation query. However, when the client cancels the query, if there is no statement to roll back the transaction, then the table involved in the background database is locked with L because the user has sent a query request to the server. So even if the user cancels the query, all locks acquired within the transaction will be retained. At this point, if other users also need to query these tables or users reopen the query window to enter query conditions to improve the system response speed, there will be blocking phenomenon.

Second, the client did not get all the results of the query in time.

Typically, after a user sends a query request to the server, the foreground application must immediately complete fetching all the result rows. If the application does not extract all the result rows, a problem arises. Because as long as the application does not extract all the results in a timely manner, the lock may stay on the table and block other users. Now that the application has submitted the SQ statement to the server, the application must fetch all the result rows. If the application does not follow this principle (if it is not configured because of a momentary omission), it cannot fundamentally solve the blocking problem.

Third, the query execution time is too long.

Some queries can take a long time to consume. If the query statement design is unreasonable or query design to the table and records for a long time, will make the query more lengthy execution. If there are times when a user needs to update or delete a record, a lot of locks will be acquired if the rows involved are more numerous. These locks block other queries, whether or not they are eventually upgraded to table locks.

In general, do not mix long running decision support queries with online transaction processing queries.

When a database encounters blocking, it is often necessary to check the SQL statements submitted by the application itself, and to check for application behavior related to connection management, processing of all result rows, and so on. Under normal circumstances, in order to avoid the blocking caused by the lock conflict, the author has the following suggestions.

Recommendation One: Extract all the result rows after the query is completed.

Some applications have the option to extract the required records in order to increase the response speed of the user's query. This "smart" looks reasonable, but it can cause even greater waste. Because the query results are not extracted in time, the lock can not be released. Blocking occurs when someone else queries the data.

Therefore, the author suggests that in the design of the application, the records of the query in the database should be extracted in time. You can increase the efficiency of your query by other means, such as adding a query condition, or a background query. At the same time, setting a reasonable cache at the application level can also significantly improve query efficiency.

Recommendation two: Do not let the user enter content while the transaction is executing.

Although in the process of transaction enforcement, users can participate in order to improve interactivity. However, our database administrators often do not recommend doing so. Because you want the user to enter parameters during the execution of a transaction, the execution time of the transaction is prolonged. Although people are more intelligent, but their reaction rate is still not as fast as the computer. Therefore, adding the process of engaging the user during execution can prolong the waiting time for the transaction. So unless you have special needs, do not alert the user to input parameters during the execution of the application. Some of the required parameters for transaction execution are best provided in advance. If the required parameters can be passed in beforehand through the variables.

Recommendation III: Make the transaction as short as possible.

The author thinks that the database administrator should simplify some problems. When a requirement requires a lot of SQL statements to complete, you might want to decompose the task. At the same time, the transaction is broken down into a few short transactions.

such as a product information table in the database, its record number is 2 million. It is now in the management's need to change one of the 1.5 million records at once. If you make a change through a transaction, it will take a long time. If cascading updates are involved, the time will be longer.

In this case, we can learn to brief the business. As in this product information, there may be a product type field. So when we update the data, we can not update it at once. Instead, it is controlled by the Product category field, and the record is updated in a separate time. So the time consumed by the update transactions for each category can be significantly shortened. So while you're working, you'll need multiple steps. However, it is often effective to avoid blocking and improve the performance of the database.