相關文章：MySQL高性能表設計規範:http://www.jianshu.com/p/f797bbe11d76MySQL EXPLAIN詳解:http://www.jianshu.com/p/ea3fc71fdc45MySQL 鎖機制 常用知識點:http://www.jianshu.com/p/0d5b7cd592f9
image.png
行鎖、表鎖對比
| 存儲引擎 | 行鎖 | 表鎖 |
|---|---|---|
| MyISAM | √ | |
| InnoDB | √ | √ |
開銷、加鎖速度、死鎖、粒度、併發性能
表鎖：開銷小，加鎖快；不會出現死鎖；鎖定力度大，發生鎖衝突概率高，併發度最低
行鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度小，發生鎖衝突的概率低，併發度高
MySQL表級鎖的鎖模式
MySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨佔寫鎖（Table Write Lock）。
對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對 MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的！
MyISAM的併發插入（Concurrent Inserts）
MyISAM表的讀和寫是串行的，但這是就總體而言的。在一定條件下，MyISAM表也支持查詢和插入操作的併發進行。
MyISAM存儲引擎有一個系統變量concurrent_insert，專門用以控制其併發插入的行為，其值分別可以為0、1或2。
當concurrent_insert設置為0時，不允許併發插入。
當concurrent_insert設置為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的默認設置。
當concurrent_insert設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾併發插入記錄。
可以利用MyISAM存儲引擎的併發插入特性，來解決應用中對同一表查詢和插入的鎖爭用。例如，將concurrent_insert系統變量設為2，總是允許併發插入；同時，通過定期在系統空閒時段執行 OPTIMIZE TABLE語句來整理空間碎片，收回因刪除記錄而產生的中間空洞。
MyISAM的鎖調度
MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的。一個進程請求某個 MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，寫進程先獲得鎖。即使讀請求先到鎖等待隊列，寫請求後到，寫鎖也會插到讀鎖請求之前！
我們可以通過一些設置來調節MyISAM 的調度行為。
通過指定啟動參數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利。
通過執行命令SET LOW_PRIORITY_UPDATES=1，使該連接發出的更新請求優先級降低。
通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先級。
另外，MySQL也提供了一種折中的辦法來調節讀寫衝突，即給系統參數max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值後，MySQL就暫時將寫請求的優先級降低，給讀進程一定獲得鎖的機會。
InnoDB的行鎖模式
InnoDB實現了以下兩種類型的行鎖。
共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
排他鎖（X)：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。
意向共享鎖（IS）：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖（IX）：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。
InnoDB的行鎖加鎖方法
意向鎖是InnoDB自動加的，不需用戶干預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數據集加排他鎖（X)；對於普通SELECT語句，InnoDB不會加任何鎖；事務可以通過以下語句顯示給記錄集加共享鎖或排他鎖。
共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。
InnoDB鎖的互斥與兼容關係
鎖和鎖之間的關係，要麼是相容的，要麼是互斥的。
鎖a和鎖b相容是指：操作同樣一組數據時，如果事務t1獲取了鎖a,另一個事務t2還可以獲取鎖b；
鎖a和鎖b互斥是指：操作同樣一組數據時，如果事務t1獲取了鎖a，另一個事務t2在t1釋放鎖a之前無法獲取鎖b。
(y表示兼容，n表示不兼容)
| - | X | S | IX | IS |
|---|---|---|---|---|
| X | n | n | n | n |
| S | n | y | n | y |
| IX | n | n | y | y |
| IS | n | y | y | y |
InnoDB行鎖實現方式
InnoDB行鎖是通過給索引上的索引項加鎖 來實現的，這一點MySQL與Oracle不同，後者是通過在數據塊中對相應數據行加鎖來實現的。
InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！
在不通過索引條件查詢的時候，InnoDB確實使用的是表鎖，而不是行鎖。
由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖衝突的。
當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數據加鎖。
即便在條件中使用了索引字段，但是否使用索引來檢索數據是由MySQL通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。
InnoDB間隙鎖（Next-Key鎖）
當我們用範圍條件而不是相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。
InnoDB使用間隙鎖的目的
一方面是為了防止幻讀，以滿足相關隔離級別的要求.
一方面是滿足其恢復和複製的需要.
恢復和複製的需要，對InnoDB鎖機制的影響
MySQL通過BINLOG錄執行成功的INSERT、UPDATE、DELETE等更新數據的SQL語句，並由此實現MySQL數據庫的恢復和主從複製（可以參見本書“管理篇”的介紹）。MySQL的恢復機制（複製其實就是在Slave Mysql不斷做基於BINLOG的恢復）有以下特點。
一是MySQL的恢復是SQL語句級的，也就是重新執行BINLOG中的SQL語句。這與Oracle數據庫不同，Oracle是基於數據庫文件塊的。
二是MySQL的Binlog是按照事務提交的先後順序記錄的，恢復也是按這個順序進行的。
從上面兩點可知，MySQL的恢復機制要求：在一個事務未提交前，其他併發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀，這已經超過了ISO/ANSI SQL92“可重複讀”隔離級別的要求，實際上是要求事務要串行化。這也是許多情況下，InnoDB要用到間隙鎖的原因，比如在用範圍條件更新記錄時，無論在Read Commited或是Repeatable Read隔離級別下，InnoDB都要使用間隙鎖，但這並不是隔離級別要求的.
InnoDB什麼時候使用表鎖
對於InnoDB表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們之所以選擇InnoDB表的理由。但在個別特殊事務中，也可以考慮使用表級鎖。
第一種情況是：事務需要更新大部分或全部數據，表又比較大，如果使用默認的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖衝突，這種情況下可以考慮使用表鎖來提高該事務的執行速度。
第二種情況是：事務涉及多個表，比較複雜，很可能引起死鎖，造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖、減少數據庫因事務回滾帶來的開銷。
InnoDB使用表鎖注意事項
（1）使用LOCK TABLES雖然可以給InnoDB加表級鎖，但必須說明的是，表鎖不是由InnoDB存儲引擎層管理的，而是由其上一層──MySQL Server負責的，僅當autocommit=0、innodb_table_locks=1（默認設置）時，InnoDB層才能知道MySQL加的表鎖，MySQL Server也才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖；否則，InnoDB將無法自動檢測並處理這種死鎖。
（2）在用 LOCK TABLES對InnoDB表加鎖時要注意，要將AUTOCOMMIT設為0，否則MySQL不會給表加鎖；事務結束前，不要用UNLOCK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務；COMMIT或ROLLBACK並不能釋放用LOCK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖。
死鎖
MyISAM表鎖是deadlock free的，這是因為MyISAM總是一次獲得所需的全部鎖，要麼全部滿足，要麼等待，因此不會出現死鎖。但在InnoDB中，除單個SQL組成的事務外，鎖是逐步獲得的，這就決定了在InnoDB中發生死鎖是可能的。
發生死鎖後，InnoDB一般都能自動檢測到，並使一個事務釋放鎖並回退，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB並不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數 innodb_lock_wait_timeout來解決。需要說明的是，這個參數並不是只用來解決死鎖問題，在併發訪問比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會佔用大量計算機資源，造成嚴重性能問題，甚至拖跨數據庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況發生。
避免死鎖的常用方法
（1）在應用中，如果不同的程序會併發存取多個表，應儘量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。在下面的例子中，由於兩個session訪問兩個表的順序不同，發生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可以避免。
（2）在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。
（3）在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖，更新時再申請排他鎖，因為當用戶申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖衝突，甚至死鎖。
（4）前面講過，在REPEATABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT...FOR UPDATE加排他鎖，在沒有符合該條件記錄情況下，兩個線程都會加鎖成功。程序發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這麼做，就會出現死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可避免問題。
（5）當隔離級別為READ COMMITTED時，如果兩個線程都先執行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第1個線程提交後，第2個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖，也會出現死鎖。
如果出現死鎖，可以用SHOW INNODB STATUS命令來確定最後一個死鎖產生的原因。返回結果中包括死鎖相關事務的詳細信息，如引發死鎖的SQL語句，事務已經獲得的鎖，正在等待什麼鎖，以及被回滾的事務等。據此可以分析死鎖產生的原因和改進措施。
《深入淺出MySQL》
個人介紹：
高廣超：多年一線互聯網研發與架構設計經驗，擅長設計與落地高可用、高性能互聯網架構。
本文首發在 高廣超的簡書博客 轉載請註明！
閱讀更多 互聯網技術棧 的文章
