相關(guān)文章：

MySQL高性能表設(shè)計規(guī)范:http://www.reibang.com/p/f797bbe11d76
MySQL EXPLAIN詳解:http://www.reibang.com/p/ea3fc71fdc45
MySQL 鎖機制 常用知識點:http://www.reibang.com/p/0d5b7cd592f9

image.png

行鎖就轧、表鎖對比

開銷、加鎖速度田度、死鎖妒御、粒度、并發(fā)性能

• 表鎖：開銷小镇饺，加鎖快乎莉；不會出現(xiàn)死鎖；鎖定力度大奸笤，發(fā)生鎖沖突概率高惋啃，并發(fā)度最低

• 行鎖：開銷大，加鎖慢揭保；會出現(xiàn)死鎖肥橙；鎖定粒度小，發(fā)生鎖沖突的概率低秸侣，并發(fā)度高

MySQL表級鎖的鎖模式

• MySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）存筏。

• 對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求味榛，但會阻塞對同一表的寫請求椭坚；對 MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作搏色；MyISAM表的讀操作與寫操作之間善茎，以及寫操作之間是串行的！

MyISAM的并發(fā)插入（Concurrent Inserts）

MyISAM表的讀和寫是串行的频轿，但這是就總體而言的垂涯。在一定條件下烁焙，MyISAM表也支持查詢和插入操作的并發(fā)進行。

MyISAM存儲引擎有一個系統(tǒng)變量concurrent_insert耕赘，專門用以控制其并發(fā)插入的行為骄蝇，其值分別可以為0、1或2操骡。

• 當(dāng)concurrent_insert設(shè)置為0時九火，不允許并發(fā)插入。
• 當(dāng)concurrent_insert設(shè)置為1時册招，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行）岔激，MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄是掰。這也是MySQL的默認設(shè)置虑鼎。
• 當(dāng)concurrent_insert設(shè)置為2時，無論MyISAM表中有沒有空洞冀惭，都允許在表尾并發(fā)插入記錄震叙。

可以利用MyISAM存儲引擎的并發(fā)插入特性，來解決應(yīng)用中對同一表查詢和插入的鎖爭用散休。例如媒楼，將concurrent_insert系統(tǒng)變量設(shè)為2，總是允許并發(fā)插入戚丸；同時划址，通過定期在系統(tǒng)空閑時段執(zhí)行 OPTIMIZE TABLE語句來整理空間碎片，收回因刪除記錄而產(chǎn)生的中間空洞限府。

MyISAM的鎖調(diào)度

MyISAM存儲引擎的讀鎖和寫鎖是互斥的夺颤，讀寫操作是串行的。一個進程請求某個 MyISAM表的讀鎖胁勺，同時另一個進程也請求同一表的寫鎖世澜，寫進程先獲得鎖。即使讀請求先到鎖等待隊列署穗，寫請求后到寥裂，寫鎖也會插到讀鎖請求之前！
我們可以通過一些設(shè)置來調(diào)節(jié)MyISAM 的調(diào)度行為案疲。

• 通過指定啟動參數(shù)low-priority-updates封恰，使MyISAM引擎默認給予讀請求以優(yōu)先的權(quán)利。
• 通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1褐啡，使該連接發(fā)出的更新請求優(yōu)先級降低诺舔。
• 通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性低飒，降低該語句的優(yōu)先級许昨。

另外，MySQL也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突逸嘀，即給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個合適的值车要，當(dāng)一個表的讀鎖達到這個值后，MySQL就暫時將寫請求的優(yōu)先級降低崭倘，給讀進程一定獲得鎖的機會。

InnoDB的行鎖模式

InnoDB實現(xiàn)了以下兩種類型的行鎖类垫。

• 共享鎖（S）：允許一個事務(wù)去讀一行司光，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
• 排他鎖（X)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)悉患，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖残家。另外，為了允許行鎖和表鎖共存售躁，實現(xiàn)多粒度鎖機制坞淮，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖陪捷。
• 意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖回窘，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。
• 意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖市袖，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖啡直。

InnoDB的行鎖加鎖方法

意向鎖是InnoDB自動加的，不需用戶干預(yù)苍碟。對于UPDATE酒觅、DELETE和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X)微峰；對于普通SELECT語句舷丹，InnoDB不會加任何鎖；事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排他鎖蜓肆。

• 共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE颜凯。
• 排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

InnoDB鎖的互斥與兼容關(guān)系

鎖和鎖之間的關(guān)系症杏，要么是相容的装获，要么是互斥的。

• 鎖a和鎖b相容是指：操作同樣一組數(shù)據(jù)時厉颤，如果事務(wù)t1獲取了鎖a,另一個事務(wù)t2還可以獲取鎖b穴豫；
• 鎖a和鎖b互斥是指：操作同樣一組數(shù)據(jù)時，如果事務(wù)t1獲取了鎖a，另一個事務(wù)t2在t1釋放鎖a之前無法獲取鎖b精肃。

(y表示兼容秤涩，n表示不兼容)

InnoDB行鎖實現(xiàn)方式

InnoDB行鎖是通過給索引上的索引項加鎖 來實現(xiàn)的，這一點MySQL與Oracle不同司抱，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的筐眷。

InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖习柠，否則匀谣，InnoDB將使用表鎖！

• 在不通過索引條件查詢的時候资溃，InnoDB確實使用的是表鎖武翎，而不是行鎖。
• 由于MySQL的行鎖是針對索引加的鎖溶锭，不是針對記錄加的鎖宝恶，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵趴捅，是會出現(xiàn)鎖沖突的垫毙。
• 當(dāng)表有多個索引的時候，不同的事務(wù)可以使用不同的索引鎖定不同的行拱绑，另外综芥，不論是使用主鍵索引、唯一索引或普通索引欺栗，InnoDB都會使用行鎖來對數(shù)據(jù)加鎖毫痕。
• 即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由MySQL通過判斷不同執(zhí)行計劃的代價來決定的迟几，如果MySQL認為全表掃描效率更高消请，比如對一些很小的表，它就不會使用索引类腮，這種情況下InnoDB將使用表鎖臊泰，而不是行鎖。

InnoDB間隙鎖（Next-Key鎖）

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)蚜枢，并請求共享或排他鎖時缸逃，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄厂抽，叫做“間隙（GAP)”需频，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）筷凤。

InnoDB使用間隙鎖的目的

• 一方面是為了防止幻讀昭殉，以滿足相關(guān)隔離級別的要求.
• 一方面是滿足其恢復(fù)和復(fù)制的需要.

恢復(fù)和復(fù)制的需要苞七，對InnoDB鎖機制的影響

MySQL通過BINLOG錄執(zhí)行成功的INSERT、UPDATE挪丢、DELETE等更新數(shù)據(jù)的SQL語句蹂风，并由此實現(xiàn)MySQL數(shù)據(jù)庫的恢復(fù)和主從復(fù)制。MySQL的恢復(fù)機制（復(fù)制其實就是在Slave Mysql不斷做基于BINLOG的恢復(fù)）有以下特點乾蓬。

• 一是MySQL的恢復(fù)是SQL語句級的惠啄，也就是重新執(zhí)行BINLOG中的SQL語句。這與Oracle數(shù)據(jù)庫不同任内，Oracle是基于數(shù)據(jù)庫文件塊的撵渡。
• 二是MySQL的Binlog是按照事務(wù)提交的先后順序記錄的，恢復(fù)也是按這個順序進行的死嗦。

從上面兩點可知姥闭，MySQL的恢復(fù)機制要求：在一個事務(wù)未提交前，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄越走，也就是不允許出現(xiàn)幻讀，這已經(jīng)超過了ISO/ANSI SQL92“可重復(fù)讀”隔離級別的要求靠欢，實際上是要求事務(wù)要串行化廊敌。這也是許多情況下，InnoDB要用到間隙鎖的原因门怪，比如在用范圍條件更新記錄時骡澈，無論在Read Commited或是Repeatable Read隔離級別下，InnoDB都要使用間隙鎖礼华，但這并不是隔離級別要求的.

InnoDB什么時候使用表鎖

對于InnoDB表渊涝，在絕大部分情況下都應(yīng)該使用行級鎖攘宙，因為事務(wù)和行鎖往往是我們之所以選擇InnoDB表的理由。但在個別特殊事務(wù)中护锤，也可以考慮使用表級鎖。

• 第一種情況是：事務(wù)需要更新大部分或全部數(shù)據(jù)酿傍，表又比較大烙懦，如果使用默認的行鎖，不僅這個事務(wù)執(zhí)行效率低赤炒，而且可能造成其他事務(wù)長時間鎖等待和鎖沖突氯析，這種情況下可以考慮使用表鎖來提高該事務(wù)的執(zhí)行速度。
• 第二種情況是：事務(wù)涉及多個表莺褒，比較復(fù)雜掩缓，很可能引起死鎖，造成大量事務(wù)回滾遵岩。這種情況也可以考慮一次性鎖定事務(wù)涉及的表你辣，從而避免死鎖、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。

InnoDB使用表鎖注意事項

• （1）使用LOCK TABLES雖然可以給InnoDB加表級鎖绢记，但必須說明的是扁达，表鎖不是由InnoDB存儲引擎層管理的，而是由其上一層──MySQL Server負責(zé)的蠢熄，僅當(dāng)autocommit=0跪解、innodb_table_locks=1（默認設(shè)置）時，InnoDB層才能知道MySQL加的表鎖签孔，MySQL Server也才能感知InnoDB加的行鎖叉讥，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖饥追；否則图仓，InnoDB將無法自動檢測并處理這種死鎖。
• （2）在用 LOCK TABLES對InnoDB表加鎖時要注意但绕，要將AUTOCOMMIT設(shè)為0救崔，否則MySQL不會給表加鎖；事務(wù)結(jié)束前捏顺，不要用UNLOCK TABLES釋放表鎖六孵，因為UNLOCK TABLES會隱含地提交事務(wù)；COMMIT或ROLLBACK并不能釋放用LOCK TABLES加的表級鎖幅骄，必須用UNLOCK TABLES釋放表鎖劫窒。

死鎖

MyISAM表鎖是deadlock free的，這是因為MyISAM總是一次獲得所需的全部鎖拆座，要么全部滿足主巍，要么等待，因此不會出現(xiàn)死鎖挪凑。但在InnoDB中孕索，除單個SQL組成的事務(wù)外，鎖是逐步獲得的岖赋，這就決定了在InnoDB中發(fā)生死鎖是可能的檬果。

發(fā)生死鎖后，InnoDB一般都能自動檢測到唐断，并使一個事務(wù)釋放鎖并回退选脊，另一個事務(wù)獲得鎖，繼續(xù)完成事務(wù)脸甘。但在涉及外部鎖恳啥，或涉及表鎖的情況下，InnoDB并不能完全自動檢測到死鎖丹诀，這需要通過設(shè)置鎖等待超時參數(shù) innodb_lock_wait_timeout來解決钝的。需要說明的是翁垂，這個參數(shù)并不是只用來解決死鎖問題，在并發(fā)訪問比較高的情況下硝桩，如果大量事務(wù)因無法立即獲得所需的鎖而掛起沿猜，會占用大量計算機資源，造成嚴重性能問題碗脊，甚至拖跨數(shù)據(jù)庫啼肩。我們通過設(shè)置合適的鎖等待超時閾值，可以避免這種情況發(fā)生衙伶。

避免死鎖的常用方法

• （1）在應(yīng)用中祈坠，如果不同的程序會并發(fā)存取多個表，應(yīng)盡量約定以相同的順序來訪問表矢劲，這樣可以大大降低產(chǎn)生死鎖的機會赦拘。在下面的例子中，由于兩個session訪問兩個表的順序不同芬沉，發(fā)生死鎖的機會就非常高躺同！但如果以相同的順序來訪問，死鎖就可以避免丸逸。
• （2）在程序以批量方式處理數(shù)據(jù)的時候笋籽，如果事先對數(shù)據(jù)排序，保證每個線程按固定的順序來處理記錄椭员，也可以大大降低出現(xiàn)死鎖的可能。
• （3）在事務(wù)中笛园，如果要更新記錄隘击，應(yīng)該直接申請足夠級別的鎖，即排他鎖研铆，而不應(yīng)先申請共享鎖埋同，更新時再申請排他鎖，因為當(dāng)用戶申請排他鎖時棵红，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖凶赁，從而造成鎖沖突，甚至死鎖逆甜。
• （4）前面講過虱肄，在REPEATABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT...FOR UPDATE加排他鎖交煞，在沒有符合該條件記錄情況下咏窿，兩個線程都會加鎖成功。程序發(fā)現(xiàn)記錄尚不存在素征，就試圖插入一條新記錄集嵌，如果兩個線程都這么做萝挤，就會出現(xiàn)死鎖。這種情況下根欧，將隔離級別改成READ COMMITTED怜珍，就可避免問題。
• （5）當(dāng)隔離級別為READ COMMITTED時凤粗，如果兩個線程都先執(zhí)行SELECT...FOR UPDATE酥泛，判斷是否存在符合條件的記錄，如果沒有侈沪，就插入記錄揭璃。此時，只有一個線程能插入成功亭罪，另一個線程會出現(xiàn)鎖等待瘦馍，當(dāng)?shù)?個線程提交后，第2個線程會因主鍵重出錯应役，但雖然這個線程出錯了情组，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖箩祥，也會出現(xiàn)死鎖院崇。

如果出現(xiàn)死鎖，可以用SHOW INNODB STATUS命令來確定最后一個死鎖產(chǎn)生的原因袍祖。返回結(jié)果中包括死鎖相關(guān)事務(wù)的詳細信息底瓣，如引發(fā)死鎖的SQL語句，事務(wù)已經(jīng)獲得的鎖蕉陋，正在等待什么鎖捐凭，以及被回滾的事務(wù)等。據(jù)此可以分析死鎖產(chǎn)生的原因和改進措施凳鬓。

參考資料

• 《深入淺出MySQL》

個人介紹：

高廣超：多年一線互聯(lián)網(wǎng)研發(fā)與架構(gòu)設(shè)計經(jīng)驗茁肠，擅長設(shè)計與落地高可用、高性能互聯(lián)網(wǎng)架構(gòu)缩举。

本文首發(fā)在 高廣超的簡書博客 轉(zhuǎn)載請注明垦梆！

image.png