MySQL鎖機制_ZenDei技術網路在線

本文參考自MySQL官網5.6版本參考手冊的14.5.1,此小節說明MySQL的鎖分類，此外還有14.5.2小節和14.5.3小節詳述事務隔離級別和各SQL語句的加鎖模式，後兩節將單獨寫2篇筆記。 https://dev.mysql.com/doc/refman/5.6/en/innodb-lock ...

本文參考自MySQL官網5.6版本參考手冊的14.5.1,此小節說明MySQL的鎖分類，此外還有14.5.2小節和14.5.3小節詳述事務隔離級別和各SQL語句的加鎖模式，後兩節將單獨寫2篇筆記。
https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html 第一部分：概述 Myisam的鎖比較容易理解，無論是讀還是寫都只會加表鎖，表鎖又分為read鎖和write鎖，可以使用如下方式手動加鎖：

--加表鎖語句（同樣適用於InnoDB）：
lock tables
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
--解表鎖語句：
unlock tables
--如何觀察InnoDB鎖：
set @@global.innodb_status_output_locks=on; 
--這樣show engine innodb status\G可以顯示InnoDB額外的鎖信息（鎖太多時也無法完全顯示），標準情況下只顯示鎖數目。

Myisam的read、write表鎖其實可以看做一種元數據鎖。由於Myisam這樣的鎖機制，導致Myisam是一款讀性能較好，併發寫性能較差的存儲引擎，本文主要討論如今的MySQL預設存儲引擎InnoDB的鎖機制。 第二部分：InnoDB鎖分類 InnoDB存儲引擎在使用到索引時會使用行鎖，否則使用表鎖。InnoDB沒有頁鎖，只有表鎖和行鎖。 一、InnoDB表鎖有以下幾種： InnoDB也可以使用lock tables ... read/write來添加元數據表鎖。 InnoDB支持的事務表鎖有： S ：其實是行鎖，MySQL的行鎖不會有額外的鎖開銷，因此我更願意把這種全表的S行鎖稱作表S鎖。 X ：其實是行鎖，MySQL的行鎖不會有額外的鎖開銷，因此我更願意把這種全表的X行鎖稱作表X鎖。 IS：表級意向共用鎖，即表示事務有向底層資源加共用行鎖的意向。如select ... lock in share mode語句，在加行鎖之前會在表上現加IS鎖，這樣可以提高鎖衝突檢測的效率，同時也可以避免事務在表級添加會使其他事務行鎖失效的表級鎖。 IX：表級意向獨占鎖，即表示事務有向底層資源加獨占行鎖的意向。一般來說delete、update語句和select ... for update語句都會在加行鎖之前先加表級IX鎖，除非未用到索引（此時直接加表級X鎖）。 表鎖的相容性圖：

此外表級鎖還有一種比較特殊的鎖：AUTO-INC Locks 這種鎖只在向自增主鍵中插入記錄時出現，由於自增主鍵在MySQL中較為常見，因此也算是經常會遇到的鎖，這種鎖是為自增主鍵設計的，無需和以上4鐘鎖檢測衝突。 AUTO-INC Locks的鎖機制：在向自增主鍵中插入記錄時，其他insert事務都需要等待直到本事務的插入完成才能繼續插入自增記錄，註意是插入完成而不是本事務完成。這很好理解，因為需要保證自增主鍵的連貫性。但是如果你有超高的插入併發，那麼肯定會帶來性能問題。因此InnoDB也提供了折中的方案，innodb_autoinc_lock_mode參數可以控制你是否使用這種鎖，如果你的自增主鍵不需要嚴格連貫而且需要更高的insert併發，那麼可以禁用掉這種鎖。但是如果你做了主從複製，而且使用的是statement模式的binlog，那麼禁用innodb_autoinc_lock_mode後可能造成主從自增主鍵不一致，尤其是遇到insert ... select ... from table_name;這種語句。此時需要改為row模式或mixed模式的binlog主從複製，因為row模式對SQL執行順序不敏感，而mixed模式也會將可能影響主從複製的statement改為row模式傳輸。那麼最後還有個問題就是既需要超高插入併發又需要連貫自增，那該怎麼辦？涼拌~ 二、InnoDB行鎖有以下四種： 1.Record lock 即在索引上加的鎖，lock_mode分為S和X兩種模式。例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;就會c1列的索引上添加S類型的Record lock。 Record lock一定是加在索引記錄上的，即便是一個沒有定義任何索引的表，InnoDB也會創建一個隱式的聚集索引，在用到此索引時加Record lock。 2.Gap lock 即間隙鎖，鎖定不存在的索引記錄，官方定義是：Gap lock用於鎖定2個索引記錄之間、或第一個索引記錄之前、或最後一個索引記錄之後的範圍。通常我們會把Record lock和Gap lock合起來用，稱為Next-key lock，因此Gap lock就不多說了。
之所以設計Gap lock主要是為瞭解決幻讀問題的，參考SQL Server的鍵範圍鎖。Gap鎖是可以禁用的，你可以將資料庫的全局隔離級別設置為read committed或者將innodb_locks_unsafe_for_binlog參數設置為1來禁用Gap lock，只是這樣就會出現幻讀，不過幻讀一般並不是什麼大問題，比如Oracle資料庫的預設隔離級別下就無法避免幻讀，不也大把人在用嗎。另外必須要說的一點是同一個gap上的Gap lock的S和X模式效果完全一樣的，就算你加了一個X模式的gap lock，其他事務也能在同一個gap上再加一個X模式的gap lock，不會阻塞，當然僅限於同一個gap。 3.Next-key lock 即Record lock和Gap lock的合體。例如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;會在[10,20]之間的c1 index record上加lock_mode為X的next-key lock，也就是說會在[10,20]之間的所有存在的index record上加X模式的record lock，同時也會用X模式的gap鎖鎖定不存在的index record防止幻讀，這兩種鎖加起來就稱作next-key lock。如果使用的索引是唯一索引，那麼不加next-key lock的，只加record lock。再次提醒的是next-key lock其實並不存在而是Record lock和Gap lock的合體，show engine innodb status\G顯示的結果也都是用Record lock來展示的，不過展示出的數目比較詭異看不懂源碼的話不建議深究，這點比Oracle和Sqlserver差太遠。這裡我就要順帶吐槽一下官網手冊了，畢竟是開源DB，一些前後矛盾和明顯有歧義的解釋也是讓人很無奈。 4.插入意向鎖（Insert Intention Locks） 這個鎖也是一個InnoDB的奇葩例子，不知道大家發現沒InnoDB在談IX IS還有行鎖這些鎖的時候基本不用insert語句來舉例，這點如果是熟悉Oracle和SQL Server的人就會很困惑，因為增刪改全都是DML語句，大家加鎖機制基本相似的，無非就是表級意向鎖+頁級or行級鎖的套路，但是InnoDB不是這樣！！！insert語句和delete、update完全不是一路人！！關於Insert語句的加鎖模式可以參考http://www.cnblogs.com/leohahah/p/8863422.html中的INSERT說明部分。這個鎖用於表明：只要不是插入相同的index record，多個事務向同一個gap插入記錄是不會阻塞的。 Insert語句的基本加鎖模式為：表級IX鎖--行級插入意向鎖--行級鎖。插入意向鎖其實是行級別的一種意向gap鎖，既然有意向兩字那麼可以認定就是用於檢測鎖衝突的，是為在行級別獲取X模式的record lock鎖提前做檢測。用一個例子來解釋更為明瞭：

--會話A執行：
CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
INSERT INTO child (id) values (90),(102);
START TRANSACTION;
SELECT * FROM child WHERE id > 100 FOR UPDATE;
--會話B執行：
INSERT INTO child (id) VALUES (101);

可以看到會話B被阻塞了，而show engine innodb status\G看到的鎖等待如下：

即insert語句想在(90,102)的gap上加個lock_mode=X的gap鎖，也就是Insert Intention Lock，但是會話A的select for update語句已經在(100,102)的gap上添加了X模式的gap鎖，這是一個與(90,102)不同但被包含在內的gap，於是被阻塞無法獲取X模式的Insert Intention Gap Lock。 三、總結 MySQL的鎖機制基本就如上所示了，但是瞭解InnoDB鎖只是初步的，還必須結合事務隔離級別的概念去判斷各種SQL的具體加鎖機制，因為事務隔離級別會影響SQL的預設加鎖模式。 MySQL的事務隔離級別定義也是遵循ANSI SQL92標準的，不過但凡是家資料庫廠商都會說自己遵循SQL92標準，而事實是早已加料加的面目全非。當然這全都是為了能夠提供更好的併發性能。例如Oracle也說自己遵循SQL92標準，結果四大隔離級別隻支持2個，SQL Server也說自己支持，結果又多造出來2個事務隔離級別。
同樣的MySQL也提供了4大基本的事務隔離級別，不同的隔離級別下加鎖機制區別很大，參考：http://www.cnblogs.com/leohahah/p/8857124.html。