明道洋 李斯娜

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

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

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

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

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

2如何避免鎖競爭

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

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

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

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

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

默認(rèn)情況下，在MySQL數(shù)據(jù)庫中，更新操作比Select查詢有更高的優(yōu)先級。如案例所述，如果用戶乙先發(fā)出了一個查詢申請，然后用戶丙再發(fā)出一個更新請求。當(dāng)用戶甲的查詢作業(yè)完成之后，系統(tǒng)會先執(zhí)行誰的請求呢?注意，默認(rèn)情況下系統(tǒng)并不遵循先來后到的規(guī)則，即不會先執(zhí)行用戶乙的查詢請求，而是執(zhí)行用戶丙的更新進(jìn)程。這主要是因為，更新進(jìn)程比查詢進(jìn)程具有更高的優(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屬性對應(yīng)，有一個HIGH_PRIOITY屬性，顧名思義，這個屬性可以用來提高某個特定的Select查詢語句的優(yōu)先級。如上面這個案例，在用戶丙的查詢語句中加入HIGH_PRIOITY屬性的話，那么用戶甲查詢完畢之后，會立即執(zhí)行用戶丙的查詢語句，等到用戶丙執(zhí)行完畢之后，才會執(zhí)行用戶乙的更新操作?？梢姡藭r查詢語句的優(yōu)先級得到了提升。這里需要注意，跟上面這個屬性一樣，這個作用域也只限于特定的查詢語句，而不會對沒有加這個參數(shù)的其他查詢語句產(chǎn)生影響。也就是說，其他查詢語句如果沒有加這個屬性，那么其優(yōu)先級別仍然低于更新進(jìn)程。

三是通過Set LOW_PRIORIT_UPDATES=1選項。以上兩個屬性都是針對特定的語句，而不會造成全局的影響。如果現(xiàn)在數(shù)據(jù)庫管理員需要對某個連接來調(diào)整優(yōu)先級別，該如何實現(xiàn)呢?如上例，現(xiàn)在用戶需要將用戶丙連接的查詢語句的優(yōu)先級別提高，而不是每次查詢時都需要使用上面的屬性，此時就需要使用Set LOW_PRIORIT_UPDATES=1選項。通過這個選項可以制定具體連接中的所有更新進(jìn)程都是用比較低的優(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è)來說，不存在鎖定的情況。為此用戶甲的查詢請求與用戶丙的查詢請求可以同時進(jìn)行。為此通過調(diào)整語句執(zhí)行的優(yōu)先級，可以有效的降低鎖競爭的情況。

可見，可以利用屬性或者選項來調(diào)整某條語句的優(yōu)先級。如現(xiàn)在有一個應(yīng)用，主要供用戶來進(jì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ù)表同時進(jìn)行插入和刪除的作業(yè)，此時如果能夠使用Insert Delayed選項，將會給用戶帶來很大的幫助。再如對同一個數(shù)據(jù)表執(zhí)行Select和Delete語句會有鎖競爭的情況，此時數(shù)據(jù)庫管理員也可以根據(jù)實際情況來選擇使用Delete Limint選項來解決所遇到速度問題。

3小結(jié)

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

參考文獻(xiàn)：

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