通過鎖機(jī)制，可以實(shí)現(xiàn)多線程同時(shí)對(duì)某個(gè)表進(jìn)行操作。如下圖所示，在某個(gè)時(shí)刻，用戶甲、用戶乙、用戶丙可能會(huì)同時(shí)或者先后(前面一個(gè)作業(yè)還沒有完成)對(duì)數(shù)據(jù)表A進(jìn)行查詢或者更新的操作。當(dāng)某個(gè)線程涉及到更新操作時(shí)，就需要獲得獨(dú)占的訪問權(quán)。在更新的過程中，所有其它想要訪問這個(gè)表的線程必須要等到其更新完成為止。此時(shí)就會(huì)導(dǎo)致鎖競爭的問題。從而導(dǎo)致用戶等待時(shí)間的延長。在這篇文章中，筆者將跟大家討論，采取哪些措施可以有效的避免鎖競爭，減少M(fèi)ySQL用戶的等待時(shí)間。

　　背景模擬：

　　為了更加清楚的說明這個(gè)問題，筆者先模擬一個(gè)日常的案例。通過案例大家來閱讀下面的內(nèi)容，可能條理會(huì)更加的清晰。現(xiàn)在MySQL數(shù)據(jù)庫遇到如上圖所示這種情況。

　　首先，用戶甲對(duì)數(shù)據(jù)表A發(fā)出了一個(gè)查詢請(qǐng)求。

　　然后，用戶乙又對(duì)數(shù)據(jù)表A發(fā)出了一個(gè)更新請(qǐng)求。此時(shí)用戶乙的請(qǐng)求只有在用戶甲的作業(yè)完成之后才能夠得到執(zhí)行。

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

　　現(xiàn)在數(shù)據(jù)庫工程師所要做的就是在數(shù)據(jù)庫設(shè)計(jì)與優(yōu)化過程中，采取哪些措施來降低這種鎖競爭的不利情況?

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

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

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

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

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

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

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

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

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

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

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

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

　　措施四：對(duì)于混合操作的情況，可以采用特定的選項(xiàng)

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

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

it知識(shí)庫：降低鎖競爭 減少M(fèi)ySQL用戶等待時(shí)間，轉(zhuǎn)載需保留來源！

鄭重聲明：本文版權(quán)歸原作者所有，轉(zhuǎn)載文章僅為傳播更多信息之目的，如作者信息標(biāo)記有誤，請(qǐng)第一時(shí)間聯(lián)系我們修改或刪除，多謝。