)mysql的優先順序鎖

http://publish.itpub.net/a2011/0304/1162/000001162908.shtml

通過鎖機制，可以實現多線程同時對某個表進行操作。如所示，在某個時刻，使用者甲、使用者乙、使用者丙可能會同時或者先後(前面一個作業還沒有完成)對資料表A進行查詢或者更新的操作。當某個線程涉及到更新操作時，就需要獲得獨佔的訪問權。在更新的過程中，所有其它想要訪問這個表的線程必須要等到其更新完成為止。此時就會導致鎖競爭的問題。從而導致使用者等待時間的延長。在這篇文章中，筆者將跟大家討論，採取哪些措施可以有效避免鎖競爭，減少MySQL使用者的等待時間。

背景類比：

　　為了更加清楚的說明這個問題，筆者先類比一個日常的案例。通過案例大家來閱讀下面的內容，可能條理會更加的清晰。現在MySQL資料庫遇到如所示這種情況。

　　首先，使用者甲對資料表A發出了一個查詢請求。

　　然後，使用者乙又對資料表A發出了一個更新要求。此時使用者乙的請求只有在使用者甲的作業完成之後才能夠得到執行。

　　最後，使用者丙又對資料表A發出了一個查詢請求。在MySQL資料庫中，更新語句的優先順序要比查詢語句的優先順序高，為此使用者丙的查詢語句只有在使用者乙的更新作業完成之後才能夠執行。而使用者乙的更新作業又必須在使用者甲的查詢陳述式完成之後才能夠執行。此時就存在比較嚴重的鎖競爭問題。

　　現在資料庫工程師所要做的就是在資料庫設計與最佳化過程中，採取哪些措施來降低這種鎖競爭的不利情況?

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

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

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

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

　　措施三：調整某個作業的優先順序

　　預設情況下，在MySQL資料庫中，更新操作比Select查詢有更高的優先順序。如所示，如果使用者乙先發出了一個查詢申請，然後使用者丙再發出一個更新要求。當使用者甲的查詢作業完成之後，系統會先執行誰的請求呢?注意，預設情況下系統並不遵循先來後到的規則，即不會先執行使用者乙的查詢請求，而是執行使用者丙的更新進程。這主要是因為，更新進程比查詢進程具有更高的優先順序。

　　但是在有些特定的情況下，可能這種優先順序不符合企業的需求。此時資料庫管理員需要根據實際情況來調整語句的優先順序。如果確實需要的話，那麼可以通過以下三種方式來實現。

　　一是通過LOW_PRIOITY屬性。這個屬性可以將某個特定的語句的優先順序降低。如可以調低某個特定的更新語句或者插入語句的優先順序。不過需要注意的是，這個屬性只有對特定的語句有用。即其範圍只針對某個特定的語句，而不會對全域造成影響。

　　二是通過HIGH_PRIOITY屬性。與通過LOW_PRIOITY屬性對應，有一個HIGH_PRIOITY屬性。顧名思義，這個屬性可以用來提高某個特定的Select查詢語句的優先順序。如上面這個案例，在使用者丙的查詢語句中加入HIGH_PRIOITY屬性的話，那麼使用者甲查詢完畢之後，會立即執行使用者丙的查詢語句。等到使用者丙執行完畢之後，才會執行使用者乙的更新操作。可見，此時查詢語句的優先順序得到了提升。這裡需要注意，跟上面這個屬性一樣，這個範圍也只限於特定的查詢語句。而不會對沒有加這個參數的其他查詢語句產生影響。也就是說，其他查詢語句如果沒有加這個屬性，那麼其優先順序別仍然低於更新進程。

　　三是通過Set LOW_PRIORIT_UPDATES=1選項。以上兩個屬性都是針對特定的語句，而不會造成全域的影響。如果現在資料庫管理員需要對某個串連來調整優先順序別，該如何?呢?如上例，現在使用者需要將使用者丙串連的查詢語句的優先順序別提高，而不是每次查詢時都需要使用上面的屬性。此時就需要使用Set LOW_PRIORIT_UPDATES=1選項。通過這個選項可以制定具體串連中的所有更新進程都是用比較低的優先順序。注意這個選項只針對特定的串連有用。對於其他的串連，就不適用。

　　四是採用Low_Priority_updates選項。上面談到的屬性，前面兩個針對特定的語句，後面一個是針對特定的串連，都不會對整個資料庫產生影響。如果現在需要在整個資料庫範圍之內，降低更新語句的優先順序，是否可以實現?如上面這個案例，在不使用其他參數的情況下，就讓使用者丙的查詢語句比使用者乙的更新具有更先執行?如果使用者有這種需求的話，可以使用Low_Priority_updates選項來啟動資料庫。採用這個選項啟動資料庫時，系統會給資料庫中所有的更新語句比較低的優先順序。此時使用者丙的查詢語句就會比使用者使用者乙的更新要求更早的執行。而對於查詢作業來說，不存在鎖定的情況。為此使用者甲的查詢請求與使用者丙的查詢請求可以同時進行。為此通過調整語句執行的優先順序，可以有效降低鎖競爭的情況。

　　可見，可以利用屬性或者選項來調整某條語句的優先順序。如現在有一個應用，主要供使用者來進行查詢。更新的操作一般都是有管理員來完成，並且對於使用者來說更新的資料並不敏感。此時基於使用者優先的原則，可以考慮將查詢的優先順序別提高。如此的話，對於使用者來說，其遇到鎖競爭的情況就會比較少，從而可以縮短使用者的等待時間。在調整使用者優先順序時，需要考慮其調整的範圍。即只是調整特定的語句、還是調整特定的串連，又或者對整個資料庫生效。

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

　　有時候會遇到混合操作的作業，如即有更新操作又有插入操作又有查詢操作時，要根據特定的情況，採用特定的選項。如現在需要對資料表同時進行插入和刪除的作業，此時如果能夠使用Insert Delayed選項，將會給使用者帶來很大的協助。再如對同一個資料表執行Select和Delete語句會有鎖競爭的情況。此時資料庫管理員也可以根據實際情況來選擇使用Delete Limint選項來解決所遇到速度問題。

　　通常情況下，鎖競爭與死結不同，並不會對資料庫的運行帶來很大的影響。只是可能會延長使用者的等待時間。如果使用者並發訪問的機率並不是很高，此時鎖競爭的現象就會很少。那麼採用上面的這些措施並不會帶來多大的收益。相反，如果使用者對某個表的並發訪問比較多，特別是不同的使用者會對錶執行查詢、更新、刪除、插入等混合作業，那麼採取上面這些措施可以在很大程度上降低鎖衝突，減少使用者的等待時間。