外文翻譯--關(guān)于數(shù)據(jù)庫死鎖的知識

1、<p><b>　　外文翻譯</b></p><p>　　About the database of the knowledge of the deadlock</p><p>　　Database itself provides lock management mechanism, but from a hand, database is the clie

2、nt 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 d

3、irect control, so, if we application design reasonable enough, then appear database is normal phenomenon dead lock.</p><p>　　Below are listed some easy to have locked application examples:</p><p&g

4、t;　　A, the client cancel inquires no roll back after practice.</p><p>　　Most of the application is inquires often happens homework. However, users through the front desk the client application inquires the b

5、ackend 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 w

6、hen cancel inquires, if not add rollback transaction statement, then at this time, because the user has to the server sends the inquiry's request</p><p>　　Second, the client not to get all the results of

7、 my query.</p><p>　　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 t

8、rip, 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 state

9、ments, the application must be extracted all results do. If the application does not follow t</p><p>　　Three, inquires the execution time too long.</p><p>　　Some inquires a relatively long time

10、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

11、 is involved in it, you will get a lot of lock. These locks whether finally upgrade to watch the lock, can block other inquiries.</p><p>　　So often, don't take long time running decision support search a

12、nd online transaction processing inquires the mixed together.</p><p>　　When database meet blocked, often need to check the application submitted to the SQL statement itself, and check and connection manageme

13、nt, 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.</p><p>　　Suggest a: after the comp

14、letion of the extraction of all query results do.</p><p>　　Some applications in order to improve the response speed of the user inquires, will have the option of extraction need record. The "smart"

15、 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.</p><p>　　So,

16、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 t

17、he inquires. At the same time, in the application level set reasonable cache, and can also be very significantly improved query efficiency.</p><p>　　Suggest two: in the transaction execution don't let th

18、e user input content.</p><p>　　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 t

19、end to do so. Because if the user in affairs during the execution 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

20、 fast. So, during the implementation of the user participation to let the process, will extend the affairs of wa</p><p>　　Suggest three: make affairs as far as possible the brief.</p><p>　　The a

21、uthor 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

22、 business affairs.</p><p>　　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

23、through a change affairs, the time is long. If it involves cascade update it, is time the meeting is longer.</p><p>　　In view of this situation, we can learn affairs brief words. If the product information,

24、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

25、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.</p><p>　　Suggest four: chil

26、d inquires the and list box, had better not use at the same time.</p><p>　　Sometimes in the application of design, through the list box can really improve user input speed and accuracy, but, if foreground ap

27、plication does not have buffer mechanism, you often can cause congestion.</p><p>　　As in a order management system, may need frequent input sales representatives. In order to user input convenience, sales re

28、presentative 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 h

29、and, 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 </p><p>　　So, in the application design, the child

30、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 sa

31、les representative information, will from application cache made, not every time to check the database.</p><p>　　At the same time, can be in the list box design "to search" function. When there is

32、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

33、. 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 </p><p>　　S

34、uggest five: in the set when cancel inquires back issues.</p><p>　　Foreground application is designed, should allow users to a temporary change in idea, cancel the query. Such as user inquires the all produc

35、t 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 pro

36、cess of inquires click this button cancel inquires at any time. Meanwhile, in the button affair, need to pay attention to join a rollback co</p><p>　　At the same time to the best lock or query timeout mechan

37、ism. 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 i

38、nquires after overtime, database server of related objects for automatic unlock. This is also the database administrator need to program developers negotiation of a problem.</p><p>　　In addition, explicit da

39、tabase 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

40、can effectively avoid the lock conflict obstruction. When database administrators found that blocking the symptoms, can from these aspect, looking for solutions.</p><p>　　From the above analysis can see, SQL

41、 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 databa

42、se 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,

43、need database management</p><p><b>　　中文</b></p><p>　　關(guān)于數(shù)據(jù)庫死鎖的知識</p><p>　　數(shù)據(jù)庫本身提供了鎖管理機(jī)制,但是從一方面,數(shù)據(jù)庫客戶端應(yīng)用程序的“傀儡”,這主要是由于客戶端到服務(wù)器的完全控制獲得的鎖的能力?？蛻魴C(jī)在請求在查詢的查詢處理的方法,往往有直接的控制,所以，如果我

44、們的應(yīng)用程序設(shè)計的不夠合理，那么出現(xiàn)的數(shù)據(jù)庫死鎖現(xiàn)象就很正常了。</p><p>　　下面列舉一些容易出現(xiàn)鎖死的應(yīng)用程序例子：</p><p>　　一、客戶端取消查詢后沒有回滾實務(wù)。</p><p>　　大多數(shù)應(yīng)用程序查詢通常發(fā)生的作業(yè)。然而,用戶通過前臺客戶端應(yīng)用程序的后端數(shù)據(jù)庫查詢,有時候會取消詢問因為各種原因。如果用戶打開窗戶口查詢之后,因為用戶發(fā)現(xiàn)反映緩慢崩

45、潰或被迫取消查詢。但是,當(dāng)客戶端當(dāng)取消問道,如果不添加回滾事務(wù)聲明,然后在這個時候,因為用戶只有服務(wù)器發(fā)送了查詢請求,因此,后端數(shù)據(jù)庫中涉及的表,所有已添加L鎖。因此,即使用戶取消查詢之后,所有的事務(wù)的鎖在將繼續(xù)。在這一點上,如果其他用戶需要檢查表或用戶打開窗口來輸入查詢的查詢條件,提高系統(tǒng)的響應(yīng)速度時發(fā)生堵塞現(xiàn)象。</p><p>　　二、客戶端沒有及時取得所有查詢的結(jié)果.</p><p&g

46、t;　　通常,用戶將被發(fā)送到服務(wù)器的查詢后,前臺應(yīng)用程序必須立刻進(jìn)行提取所有的結(jié)果。如果應(yīng)用程序沒有旅行中提取所有的結(jié)果,它就會產(chǎn)生問題。只要應(yīng)用程序沒有及時提取所有的結(jié)果,鎖可能呆在桌子和阻止其他用戶。由于應(yīng)用程序已被提交至服務(wù)器將平方語句,應(yīng)用程序必須提取所有結(jié)果。如果應(yīng)用程序沒有遵循原則詞(比如因為在那個時候,沒有監(jiān)督配置),不能從根本上解決擁堵。</p><p>　　三、查詢執(zhí)行時間過長。</p&g

47、t;<p>　　一些查詢將成本的一個相對較長的時間。至于查詢設(shè)計不合理或查詢設(shè)計觀察和記錄,將使查詢的執(zhí)行時間延長。如果有時需要更新用戶記錄或刪除操作,如果隊伍參與著它,你會得到很多的鎖。這些鎖升級看是否最后鎖可以阻止其他的查詢?！　?lt;/p><p>　　所以通常,不要把長時間運行決策支持搜索和在線事務(wù)處理,詢問混合在一起。</p><p>　　當(dāng)數(shù)據(jù)庫滿足封鎖,通常需要檢查

48、應(yīng)用程序提交的SQL語句本身,并檢查和連接管理,所有的結(jié)果進(jìn)行處理和其他相關(guān)的應(yīng)用程序行為。通常,為了避免沖突的擁塞在鎖上,作者提出以下的建議。</p><p>　　建議一：完成后提取所有查詢結(jié)果。</p><p>　　一些應(yīng)用程序為了提高用戶查詢的響應(yīng)速度,將有權(quán)選擇提取需要記錄?！爸悄堋笨雌饋砗芎侠?但是,卻會導(dǎo)致更大的浪費。因為沒有及時的話查詢結(jié)果提取、鎖不能被釋放。當(dāng)別人查詢數(shù)據(jù),

49、將發(fā)生阻塞的。</p><p>　　因此,筆者建議在應(yīng)用程序設(shè)計、數(shù)據(jù)庫查詢記錄來提取時間。通過其他手段,如添加查詢條件,或后臺的查詢時,提高查詢效率。同時,在應(yīng)用程序級別設(shè)置合理的緩存,也可以是非常顯著提高查詢效率。</p><p>　　建議二：在事務(wù)執(zhí)行時不要讓用戶輸入內(nèi)容。</p><p>　　盡管在事務(wù)的過程性,能讓用戶參與,為了提高交互性。但是,我們并不推

50、薦使用數(shù)據(jù)庫管理員傾向于這么做。因為如果用戶在事務(wù)執(zhí)行期間輸入?yún)?shù),并將擴(kuò)展事務(wù)的執(zhí)行時間。盡管人們變得更聰明,但是響應(yīng)速度仍然沒有電腦,那么快。因此,在實現(xiàn)用戶的參與讓這個過程中,將擴(kuò)展事務(wù)的等待時間。所以除非有特殊的需求,而不是在應(yīng)用程序的執(zhí)行過程中,讓用戶輸入?yún)?shù)。一些事務(wù)的執(zhí)行必須參數(shù),最好事先提供。如果能夠通過中的變量參數(shù)如需要進(jìn)去。</p><p>　　建議三：使事務(wù)盡可能的簡短。　</p>

51、;<p>　　數(shù)據(jù)庫管理員應(yīng)該放一些問題簡單化。當(dāng)一個需要很多的SQL語句完成,不妨把任務(wù)分解。同時,它分解成一些簡短的業(yè)務(wù)事務(wù)。</p><p>　　如果數(shù)據(jù)庫產(chǎn)品信息表,它記錄編號二百萬?，F(xiàn)在在一個管理需求,一次性修改其中一個十億零五十萬年的紀(jì)錄。如果通過改變事務(wù)的時間較長。如果涉及到級聯(lián)更新它,時間會更長。</p><p>　　鑒于這種情況,我們可以學(xué)習(xí)事務(wù)簡短的話。如

52、果產(chǎn)品信息,可能有一個產(chǎn)品類型字段。所以在更新數(shù)據(jù),我們能不能一次性更新。但通過產(chǎn)品類別字段來控制,來記錄迭代點。所以每一類更新公司的消費時間可能會極大地降低。所以,雖然操作,將需要更多的步驟。但是,通常,可以有效地避免擁塞的發(fā)生,提高數(shù)據(jù)庫的性能。</p><p>　　建議四：子查詢與列表框，最好不要同時使用。</p><p>　　??有時在應(yīng)用程序設(shè)計,通過列表框真的可以改善用戶輸入的

53、速度和準(zhǔn)確性,但是,如果前臺應(yīng)用程序沒有緩沖機(jī)制,你往往會引起交通擁堵。</p><p>　　在一個訂單管理系統(tǒng),可能需要經(jīng)常輸入銷售代表。為了方便用戶輸入,銷售代表經(jīng)常設(shè)計成一個列表框。每次需要輸入,前臺應(yīng)用程序從背景的查詢將銷售代表信息(如果應(yīng)用程序沒有涉及緩存)。一方面,自然的孩子,將查詢速度緩慢;第二,列表框有成長時間運行的查詢。這兩方面聯(lián)系在一起,可能導(dǎo)致應(yīng)用程序提高運行的時間過程查詢。和其他的用戶查詢

54、,例如系統(tǒng)管理員需要維護(hù)客戶信息,充血。</p><p>　　所以,在應(yīng)用程序設(shè)計、孩子提問最好的少。和孩子提問列表框和同時使用,更需要禁止。如果你無法避免,應(yīng)該在應(yīng)用程序?qū)崿F(xiàn)緩存機(jī)制。這樣,應(yīng)用程序需要的銷售代表的信息,將從應(yīng)用程序緩存中創(chuàng)造出來的,而不是每次檢查數(shù)據(jù)庫。</p><p>　　同時,可以在列表框設(shè)計“搜索”功能。當(dāng)有一個變化給用戶的信息,如系統(tǒng)管理員加入一個新的銷售代表。

55、在沒有之前再提問,因為他們的應(yīng)用程序就能實現(xiàn)緩存數(shù)據(jù),所以不僅僅是更新的內(nèi)容。此時,用戶將需要運行查詢功能,讓前臺應(yīng)用程序從一個數(shù)據(jù)庫查詢信息。這種設(shè)計,可以增加列表框和兒子的執(zhí)行時間的查詢時,有效地避免擁擠。</p><p>　　建議五：在取消查詢時設(shè)置回退事務(wù)。</p><p>　　??前臺應(yīng)用程序設(shè)計,應(yīng)該允許用戶臨時改變主意,取消查詢。如果用戶查詢所有產(chǎn)品的信息,可能會覺得響應(yīng)時間

56、長,難以忍受。在這段時間,他們會想到取消,問。在這種情況下,應(yīng)用程序設(shè)計需要設(shè)計一個cancel按鈕,問。用戶可以查詢過程點擊該按鈕取消瀏覽。同時,在按鈕事件,需要注意加入一個回滾命令。讓數(shù)據(jù)庫服務(wù)器可以提示來記錄或表來解鎖。</p><p>　　在同一時間或最好的鎖定超時機(jī)制查詢。這很大程度上是因為,有時需要花費很多的要求用戶主機(jī)大量的資源,并導(dǎo)致客戶端崩潰。這時,就可以鎖定超時工作或查詢機(jī)制,即在后來的要求加

57、班,數(shù)據(jù)庫服務(wù)器相關(guān)的對象自動解鎖。這也是數(shù)據(jù)庫管理員需要規(guī)劃發(fā)展問題的談判。</p><p>　　此外,顯式連接到并發(fā)用戶控制數(shù)據(jù)庫,負(fù)載預(yù)計將使用此應(yīng)用程序來承受能力測試,使用了鏈接,每個查詢和加班鎖定設(shè)置使用查詢加班,等等,該方法可以有效地避免阻塞鎖沖突。當(dāng)數(shù)據(jù)庫管理員發(fā)現(xiàn),阻斷這些癥狀,能從這些方面,尋找解決方案。</p><p>　　從上面的分析可以看到,SQL Server數(shù)據(jù)