明道洋 李斯娜

摘要：該文探討MySql鎖競爭產(chǎn)生的原因，采取哪些機制可以有效避免避免鎖競爭，減少MySQL用戶的等待時間。

關(guān)鍵詞：數(shù)據(jù)庫；MySql鎖競爭；死鎖

中圖分類號：TP311文獻標識碼：A文章編號：1009-3044(2012)20-4795-02

MySQL是一個小型關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，廣泛地應用于Internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MySQL作為網(wǎng)站數(shù)據(jù)庫。

MySQL中通過鎖機制，可以實現(xiàn)多線程同時對某個表進行操作。在某個時刻，用戶甲、用戶乙、用戶丙可能會同時或者先后(前面一個作業(yè)還沒有完成)對數(shù)據(jù)表A進行查詢或者更新的操作（如圖1所示）。當某個線程涉及到更新操作時，就需要獲得獨占的訪問權(quán)。在更新的過程中，所有其它想要訪問這個表的線程必須要等到其更新完成為止。此時就會導致鎖競爭的問題。從而導致用戶等待時間的延長。該文講探討采取哪些措施可以有效避免鎖競爭，減少MySQL用戶的等待時間。

1什么是鎖競爭

為了更加清楚的說明這個問題，讓我們先模擬一個日常的案例。

首先，用戶甲對數(shù)據(jù)表A發(fā)出了一個查詢請求。然后，用戶乙又對數(shù)據(jù)表A發(fā)出了一個更新請求。此時用戶乙的請求只有在用戶甲的作業(yè)完成之后才能夠得到執(zhí)行。最后，用戶丙又對數(shù)據(jù)表A發(fā)出了一個查詢請求。在MySQL數(shù)據(jù)庫中，更新語句的優(yōu)先級要比查詢語句的優(yōu)先級高，為此用戶丙的查詢語句只有在用戶乙的更新作業(yè)完成之后才能夠執(zhí)行。而用戶乙的更新作業(yè)又必須在用戶甲的查詢語句完成之后才能夠執(zhí)行，此時就存在比較嚴重的鎖競爭問題。

2如何避免鎖競爭

措施一：利用Lock Tables來提高更新速度

對于更新作業(yè)來說，在一個鎖定中進行許多更新要比所有鎖定的更新要來得快。為此如果一個表更新頻率比較高，如超市的收銀系統(tǒng)，那么可以通過使用Lock Tables選項來提高更新速度。更新的速度提高了，那么與Select查詢作業(yè)的沖突就會明顯減少，鎖競爭的現(xiàn)象也能夠得到明顯的抑制。

措施二：將某個表分為幾個表來降低鎖競爭

如一個大型的購物超市，如沃爾瑪，其銷售紀錄表每天的更新操作非常的多。此時如果用戶在更新的同時，另外有用戶需要對其進行查詢，顯然鎖競爭的現(xiàn)象會比較嚴重。針對這種情況，其實可以人為的將某張表分為幾個表。如可以為每一臺收銀機專門設置一張數(shù)據(jù)表。如此的話，各臺收銀機之間用戶的操作都是在自己的表中完成，相互之間不會產(chǎn)生干擾。在數(shù)據(jù)統(tǒng)計分析時，可以通過視圖將他們整合成一張表。

措施三：調(diào)整某個作業(yè)的優(yōu)先級

默認情況下，在MySQL數(shù)據(jù)庫中，更新操作比Select查詢有更高的優(yōu)先級。如案例所述，如果用戶乙先發(fā)出了一個查詢申請，然后用戶丙再發(fā)出一個更新請求。當用戶甲的查詢作業(yè)完成之后，系統(tǒng)會先執(zhí)行誰的請求呢?注意，默認情況下系統(tǒng)并不遵循先來后到的規(guī)則，即不會先執(zhí)行用戶乙的查詢請求，而是執(zhí)行用戶丙的更新進程。這主要是因為，更新進程比查詢進程具有更高的優(yōu)先級。

但是在有些特定的情況下，可能這種優(yōu)先級不符合企業(yè)的需求。此時數(shù)據(jù)庫管理員需要根據(jù)實際情況來調(diào)整語句的優(yōu)先級。如果確實需要的話，那么可以通過以下三種方式來實現(xiàn)。

一是通過LOW_PRIOITY屬性。這個屬性可以將某個特定的語句的優(yōu)先級降低。如可以調(diào)低某個特定的更新語句或者插入語句的優(yōu)先級。不過需要注意的是，這個屬性只有對特定的語句有用，即其作用域只針對某個特定的語句，而不會對全局造成影響。

二是通過HIGH_PRIOITY屬性。與通過LOW_PRIOITY屬性對應，有一個HIGH_PRIOITY屬性，顧名思義，這個屬性可以用來提高某個特定的Select查詢語句的優(yōu)先級。如上面這個案例，在用戶丙的查詢語句中加入HIGH_PRIOITY屬性的話，那么用戶甲查詢完畢之后，會立即執(zhí)行用戶丙的查詢語句，等到用戶丙執(zhí)行完畢之后，才會執(zhí)行用戶乙的更新操作。可見，此時查詢語句的優(yōu)先級得到了提升。這里需要注意，跟上面這個屬性一樣，這個作用域也只限于特定的查詢語句，而不會對沒有加這個參數(shù)的其他查詢語句產(chǎn)生影響。也就是說，其他查詢語句如果沒有加這個屬性，那么其優(yōu)先級別仍然低于更新進程。

三是通過Set LOW_PRIORIT_UPDATES=1選項。以上兩個屬性都是針對特定的語句，而不會造成全局的影響。如果現(xiàn)在數(shù)據(jù)庫管理員需要對某個連接來調(diào)整優(yōu)先級別，該如何實現(xiàn)呢?如上例，現(xiàn)在用戶需要將用戶丙連接的查詢語句的優(yōu)先級別提高，而不是每次查詢時都需要使用上面的屬性，此時就需要使用Set LOW_PRIORIT_UPDATES=1選項。通過這個選項可以制定具體連接中的所有更新進程都是用比較低的優(yōu)先級。注意這個選項只針對特定的連接有用，對于其他的連接，就不適用。[1]

四是采用Low_Priority_updates選項。上面談到的屬性，前面兩個針對特定的語句，后面一個是針對特定的連接，都不會對整個數(shù)據(jù)庫產(chǎn)生影響。如果現(xiàn)在需要在整個數(shù)據(jù)庫范圍之內(nèi)，降低更新語句的優(yōu)先級，是否可以實現(xiàn)?如上面這個案例，在不使用其他參數(shù)的情況下，就讓用戶丙的查詢語句比用戶乙的更新具有更先執(zhí)行?如果用戶有這種需求的話，可以使用Low_Priority_updates選項來啟動數(shù)據(jù)庫。采用這個選項啟動數(shù)據(jù)庫時，系統(tǒng)會給數(shù)據(jù)庫中所有的更新語句比較低的優(yōu)先級。此時用戶丙的查詢語句就會比用戶用戶乙的更新請求更早的執(zhí)行。而對于查詢作業(yè)來說，不存在鎖定的情況。為此用戶甲的查詢請求與用戶丙的查詢請求可以同時進行。為此通過調(diào)整語句執(zhí)行的優(yōu)先級，可以有效的降低鎖競爭的情況。

可見，可以利用屬性或者選項來調(diào)整某條語句的優(yōu)先級。如現(xiàn)在有一個應用，主要供用戶來進行查詢。更新的操作一般都是有管理員來完成，并且對于用戶來說更新的數(shù)據(jù)并不敏感。此時基于用戶優(yōu)先的原則，可以考慮將查詢的優(yōu)先級別提高。如此的話，對于用戶來說，其遇到鎖競爭的情況就會比較少，從而可以縮短用戶的等待時間。在調(diào)整用戶優(yōu)先級時，需要考慮其調(diào)整的范圍：即只是調(diào)整特定的語句還是調(diào)整特定的連接，又或者對整個數(shù)據(jù)庫生效。

措施四：對于混合操作的情況，可以采用特定的選項

有時候會遇到混合操作的作業(yè)，如既有更新操作又有插入操作又有查詢操作時，要根據(jù)特定的情況，采用特定的選項。如現(xiàn)在需要對數(shù)據(jù)表同時進行插入和刪除的作業(yè)，此時如果能夠使用Insert Delayed選項，將會給用戶帶來很大的幫助。再如對同一個數(shù)據(jù)表執(zhí)行Select和Delete語句會有鎖競爭的情況，此時數(shù)據(jù)庫管理員也可以根據(jù)實際情況來選擇使用Delete Limint選項來解決所遇到速度問題。

3小結(jié)

通常情況下，鎖競爭與死鎖不同，并不會對數(shù)據(jù)庫的運行帶來很大的影響。只是可能會延長用戶的等待時間。如果用戶并發(fā)訪問的機率并不是很高，此時鎖競爭的現(xiàn)象就會很少。那么采用上面的這些措施并不會帶來多大的收益。相反，如果用戶對某個表的并發(fā)訪問比較多，特別是不同的用戶會對表執(zhí)行查詢、更新、刪除、插入等混合作業(yè)，那么采取上面這些措施可以在很大程度上降低鎖沖突，減少用戶的等待時間。

參考文獻：

[1]吉爾摩. PHP與Mysql5程序設計[M].2版.朱濤江,等,譯.北京:人民郵電出版社,2007.