降低鎖競爭 減少MySQL用戶等待時間
通過鎖機制，可以實現多線程同時對某個表進行操作。如下圖所示，在某個時刻，用戶甲、用戶乙、用戶丙可能會同時或者先后(前面一個作業還沒有完成)對數據表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選項來解決所遇到速度問題。
通常情況下，鎖競爭與死鎖不同，并不會對數據庫的運行帶來很大的影響。只是可能會延長用戶的等待時間。如果用戶并發訪問的機率并不是很高，此時鎖競爭的現象就會很少。那么采用上面的這些措施并不會帶來多大的收益。相反，如果用戶對某個表的并發訪問比較多，特別是不同的用戶會對表執行查詢、更新、刪除、插入等混合作業，那么采取上面這些措施可以在很大程度上降低鎖沖突，減少用戶的等待時間。
【編輯推薦】
