外文翻译--关于数据库死锁的知识

外文翻译About the database of the knowledge of the deadlock Database itself provides lock management mechanism, but from a hand, database is the client applications "puppet", this is mainly because the client to the server has complete control of the gain of locks ability. The client in enquiries in the request and the way to query processing tend to have direct control, so, if we application design reasonable enough, then appear database is normal phenomenon dead lock.Below are listed some easy to have locked application examples:A, the client cancel inquires no roll back after practice.Most of the application is inquires often happens homework. However, users through the front desk the client application inquires the backend database, sometimes will cancel inquires for any variety of reasons. If the user to open the window after mouth query, because users find reflect crash or slow compelled to cancel the query. But, when the client when cancel inquires, if not add rollback transaction statement, then at this time, because the user has to the server sends the inquiry's request, so, the backend database involved in the table, all have been added L locked. So even if the user cancel after inquires, all in the affairs for the locks within will remain. At this point, if other users need to check on the table or the user to open the window through input inquires to query conditions to improve the system response speed occurs when the jam phenomenon.Second, the client not to get all the results of my query.Usually, the user will be sent to the server after queries, foreground application must be done at once extraction all the results do. If the application did not extract all the results trip, it produces a problem. For as long as the application did not withdraw promptly all the results, the lock may stay at table and block other users. Since the application has been submitted to the server will SQ statements, the application must be extracted all results do. If the application does not follow the principle words (such as because at that time and no oversight configuration), can't fundamentally solve congestion.Three, inquires the execution time too long.Some inquires a relatively long time will cost. As for the query design is not reasonable or query design to watch and record it is, will make inquires the execution time lengthen. If sometimes need to Update on users record or Delete operation, if the line is involved in it, you will get a lot of lock. These locks whether finally upgrade to watch the lock, can block other inquiries.So often, don't take long time running decision support search and online transaction processing inquires the mixed together.When database meet blocked, often need to check the application submitted to the SQL statement itself, and check and connection management, all the results do processing and other relevant application behavior. Usually, the lock for to avoid the conflict in the jam, the author has the following Suggestions.Suggest a: after the completion of the extraction of all query results do.Some applications in order to improve the response speed of the user inquires, will have the option of extraction need record. The "smart" looks very reasonable, but, but will cause more waste. Because inquires not timely and fruit extraction of words, the lock cannot be released. When others inquires the data, will be happening.So, the author suggest in application design, database query for record to the extraction of in time. Through other means, such as adding inquires the conditions, or the way backstage inquires, to improve the efficiency of the inquires. At the same time, in the application level set reasonable cache, and can also be very significantly improved query efficiency.Suggest two: in the transaction execution don't let the user input content.Although in the affairs of the process with sex, can let the user participation, in order to improve the interactivity. But, we don't recommend the database administrator tend to do so. Because if the user in affairs during the exec ution of the input and number, will extend the affairs of the execution time. Although people smarter, but the response speed still don't have a computer so fast. So, during the implementation of the user participation to let the process, will extend the a ffairs of waiting time. So unless there is a special needs, not in the application's execution process, reminds the user input parameters. Some affairs of the executive must parameters, best provide beforehand. If can through the variables in the parameters such as need to go in.Suggest three: make affairs as far as possible the brief.The author thinks that, database administrator should put some problem is simplified. When a need to many SQL statements to complete, might as well take the task decomposition. At the same time, it breaks down into some brief business affairs.If the database a product information table, its record number two million. Now in a management needs, the one-time change one of the one million five hundred thousand record. If through a change affairs, the time is long. If it involves cascade update it, is time the meeting is longer.In view of this situation, we can learn affairs brief words. If the product information, may have a product type field. So in the update data, can we not one-time updates. But through the product category fields to control, to record the iteration points. So every category of update firm consumption of time may be greatly reduces. So although operation, will need more steps. But, can effectively avoid to go to the occurrence of congestion, and improve the performance of the database. Suggest four: child inquires the and list box, had better not use at the same time.Sometimes in the application of design, through the list box can really improve user input speed and accuracy, but, if foreground application does not have buffer mechanism, you often can cause congestion.As in a order management system, may need frequent input sales representatives. In order to user input convenience, sales representative often design into a list box. Every time need to input, foreground application from the background of all sales representative inquires information (if the application is not involved in the cache). On one hand, the son of nature, would be speed query slow; Second, the list box have growth time operation of the inquiry. The two parties face touch together, may causethe application of improving the running time process query. And the other user queries, such as the system administrator need to maintain customer information, and cause congestion.So, in the application design, the child inquires the best less. And the child inquires the list box and use at the same time, more need to ban. If you can't avoid it, should be in application realize caching mechanism. That way, the applications need to sales representative information, will from application cache made, not every time to check the database.At the same time, can be in the list box design "to search" function. When there is a change to the user information, such as the system administrator to join a new sales representatives. In no again before inquires, because of their application is achieved in the cache data, so not just updated content. At this time, users will need to run to inquires the function, let the foreground application from a database query information again. This kind of design, can increase the list box and the son of the execution time inquires, effectively avoid congestion.Suggest five: in the set when cancel inquires back issues.Foreground application is designed, should allow users to a temporary change in idea, cancel the query. Such as user inquires the all product information, may feel response time is long, hard to bear. At this time, they will think of cancel inquires the. In this case, the application design need to design a cancel inquires the button. The user can in the process of inquires click this button cancel inquires at any time. Meanwhile, in the button affair, need to pay attention to join a rollback command. Let the database server can prompt to records or table to unlock.At the same time to the best lock or query timeout mechanism. This is largely because, sometimes also can cost a lot inquires user host to a large number of resources, and cause client crash. At this time, to be able to lock the inquires the or overtime mechanisms, namely in inquires after overtime, database server of related objects for automatic unlock. This is also the database administrator need to program developers negotiation of a problem.In addition, explicit database connection to take control in the concurrent users, is expected to full load next use application to bear ability test, use the link, each inquires to set use inquires and lock exceeds the overtime, these methods can effectively avoid the lock conflict obstruction. When database administrators found that blocking the symptoms, can from these aspect, looking for solutions.From the above analysis can see, SQL Server database lock is a double-edged sword. The security database data consistency at the same time, they will give the database caused some negative effect. How do these negative influence to the least, is our database administrators task. In application design, follow the advice above, can effectively solve the problems for the lock blockages, improve the performance of the database. Visible, to basically solve congestion problem, need database management personnel and program developers work together.中文关于数据库死锁的知识数据库本身提供了锁管理机制,但是从一方面,数据库客户端应用程序的“傀儡”,这主要是由于客户端到服务器的完全控制获得的锁的能力。