MySQL 使用的 Lock

全局鎖

要使用全局鎖，則要執行這條命令：

flush tables with read lock

執行後，整個資料庫就處於只讀狀態了，這時其他線程執行以下操作，都會被阻塞：

• 對資料的增刪改操作，比如 insert、delete、update 等語句
• 對表結構的更改操作，比如 alter table、drop table 等語句

如果要釋放全局鎖，則要執行這條命令：

unlock tables

當然，當會話斷開了，全局鎖會被自動釋放。

全局鎖主要應用於做全庫邏輯備份，這樣在備份資料庫期間，不會因為資料或表結構的更新，而出現備份文件的資料與預期的不一樣。

舉個例子大家就知道了。

在全庫邏輯備份期間，假設不加全局鎖的場景，看看會出現什麼意外的情況。

如果在全庫邏輯備份期間，有用戶購買了一件商品，一般購買商品的業務邏輯是會涉及到多張資料庫表的更新，比如在用戶表更新該用戶的餘額，然後在商品表更新被購買的商品的庫存。

那麼，有可能出現這樣的順序：

1. 先備份了用戶表的資料
2. 然後有用戶發起了購買商品的操作
3. 接著再備份商品表的資料

也就是在備份用戶表和商品表之間，有用戶購買了商品。

這種情況下，備份的結果是用戶表中該用戶的餘額並沒有扣除，反而商品表中該商品的庫存被減少了，如果後面用這個備份文件恢復資料庫資料的話，用戶錢沒少，而庫存少了，等於用戶白嫖了一件商品。

所以，在全庫邏輯備份期間，加上全局鎖，就不會出現上面這種情況了。

加上全局鎖，意味著整個資料庫都是只讀狀態。

如果資料庫裡有很多資料，備份就會花費很多的時間，備份期間，業務只能讀資料，而不能更新資料，這樣會造成業務停滯。

如果 storage engine 的 isolation level 是 Repeatable Read ，在使用 mysqldump 時加上 –single-transaction 參數，就會在備份資料庫之前先開啟事務。

表級鎖

表鎖

如果我們想對學生表（t_student）加表鎖，可以使用下面的命令：

//表級别的共享鎖，也就是讀鎖；
lock tables t_student read;

//表級别的獨佔鎖，也就是寫鎖；
lock tables t_student write;

需要注意的是，表鎖除了會限制別的線程的讀寫外，也會限制本線程接下來的讀寫操作。

要釋放表鎖，可以使用下面這條命令：

unlock tables

當會話退出後，會釋放所有表鎖。

不過盡量避免在使用 InnoDB 引擎的表使用表鎖，因為表鎖的粒度太大，會影響併發性能。

元資料鎖 (Mata Data Lock)

我們不需要顯性的使用 MDL，因為當我們對表進行操作時，會自動給表加上 MDL：

• 對一張表進行 CRUD 操作時，加的是 MDL 讀鎖
• 對一張表做結構變更操作的時候，加的是 MDL 寫鎖

MDL 是在事務提交後才會釋放。

MDL 是為了保證當用戶對表執行 CRUD 操作時，防止其他線程對表結構做了變更。

當有線程執行 select 語句時（加 MDL 讀鎖），如果有其他線程要更改該表的結構（申請 MDL 寫鎖），線程會被阻塞，直到 select 語句執行完（釋放 MDL 讀鎖）。

當有線程對表結構進行變更時（加 MDL 寫鎖），如果有其他線程執行了 CRUD 操作（申請 MDL 讀鎖），線程會被阻塞，直到表結構變更完成（釋放 MDL 寫鎖）。

如果資料庫有一個長事務，在對表結構做變更操作的時候，可能會發生意想不到的事情，比如下面這個順序的場景：

1. 首先，線程 A 先啟用了事務（但是一直不提交），然後執行一條 select 語句，此時就先對該表加上 MDL 讀鎖
2. 然後，線程 B 也執行了同樣的 select 語句，此時並不會阻塞，因為「讀讀」並不衝突
3. 接著，線程 C 修改了表字段，此時由於線程 A 的事務並沒有提交，也就是 MDL 讀鎖還在佔用著，這時線程 C 就無法申請到 MDL 寫鎖，就會被阻塞

在線程 C 阻塞後，後續對該表的 select 語句，都會被阻塞。

這是因為申請 MDL 鎖的操作會形成一個隊列，隊列中寫鎖獲取優先級高於讀鎖，一旦出現 MDL 寫鎖等待，會阻塞後續該表的所有 CRUD 操作。

所以為了能安全的對表結構進行變更，要先看是否有長事務已經對表加上了 MDL 讀鎖。

意向鎖

• 在使用 InnoDB 引擎的表裡對某些記錄加上「共享鎖」之前，需要先在表級別加上一個「意向共享鎖」
• 在使用 InnoDB 引擎的表裡對某些紀錄加上「獨占鎖」之前，需要先在表級別加上一個「意向獨占鎖」

也就是，當執行插入、更新、刪除操作，需要先對表加上「意向獨占鎖」，然後對該記錄加獨占鎖。

而普通的 select 是不會加行級鎖的，普通的 select 語句是利用 MVCC 實現一致性讀，是無鎖的。

不過，select 也是可以對記錄加共享鎖和獨占鎖的，具體方式如下：

//先在表上加上意向共享鎖，然後對讀取的記錄加共享鎖
select ... lock in share mode;

//先在表上加上意向獨占鎖，然後對讀取的記錄加獨占鎖
select ... for update;

意向共享鎖和意向獨占鎖是表級鎖，不會和行級的共享鎖和獨占鎖發生衝突，而且意向鎖之間也不會發生衝突，只會和共享表鎖（lock tables ... read）和獨占表鎖（lock tables ... write）發生衝突。

表鎖和行鎖是滿足讀讀共享、讀寫互斥、寫寫互斥的。

如果沒有「意向鎖」，那麼加「獨占表鎖」時，就需要遍歷表裡所有記錄，查看是否有記錄存在獨占鎖，這樣效率會很慢。

由於在對記錄加獨占鎖前，會先加上表級別的意向獨占鎖，那麼在加「獨占表鎖」時，直接查該表是否有意向獨占鎖，如果有就意味著表裡已經有記錄被加了獨占鎖，這樣就不用去遍歷表裡的記錄。

所以，意向鎖的目的是為了快速判斷表裡是否有記錄被加鎖。

AUTO-INC 鎖

如果對字段加上 AUTO_INCREMENT 屬性，之後在插入資料時，資料庫會自動給予遞增的值。

AUTO-INC 鎖是特殊的表鎖機制，鎖不是在一個事務提交後才釋放，而是在執行完插入語句後釋放。

在插入資料時，會加一個表級別的 AUTO-INC 鎖，等插入語句執行完成後，才會把 AUTO-INC 鎖釋放掉，，這保證插入資料時，字段的值是連續遞增的。

但是， AUTO-INC 鎖在對大量資料進行插入的時候，會影響插入性能，因為另一個事務的插入會被阻塞。

因此， 在 MySQL 5.1.22 版本開始，InnoDB 存儲引擎提供了一種輕量級的鎖來實現自增。

插入資料的時候，會為被 AUTO_INCREMENT 修飾的字段加上鎖，給該字段一個自增的值，再把這個輕量級鎖釋放。 輕量級鎖不需要等待插入語句執行完後才釋放。

InnoDB 存儲引擎提供了個 innodb_autoinc_lock_mode 的系統變量，是用來控制選擇用 AUTO-INC 鎖，還是輕量級的鎖。

• 當 innodb_autoinc_lock_mode = 0，採用 AUTO-INC 鎖，語句執行結束後才釋放鎖
• 當 innodb_autoinc_lock_mode = 2，採用輕量級鎖，申請自增主鍵後就釋放鎖
• 當 innodb_autoinc_lock_mode = 1：
• 普通 insert 語句，自增鎖在申請之後就馬上釋放
• 類似 insert … select 這樣的批量插入資料的語句，自增鎖還是要等語句結束後才被釋放

innodb_autoinc_lock_mode = 2 是讓性能最好的方式，但是當 binlog 的日誌格式是 statement 的時候，在「主從複製的場景」中會發生資料不一致的問題。

舉個例子，考慮下面場景：

session A 往表 t 中插入了 4 行資料，然後創建了一個相同結構的表 t2，然後兩個 session 同時向表 t2 中插入資料。

如果 innodb_autoinc_lock_mode = 2，意味著「申請自增主鍵後就釋放鎖，不必等插入語句執行完」。那麼就可能出現這樣的情況：

• session B 先插入了兩個記錄，(1,1,1)、(2,2,2)
• 然後，session A 來申請自增 id 得到 id=3，插入了（3,5,5)
• 之後，session B 繼續執行，插入兩條記錄 (4,3,3)、 (5,4,4)

可以看到 session B 的 insert 語句，生成的 id 不連續。

當「主庫」發生了這種情況，binlog 面對 t2 表的更新只會記錄這兩個 session 的 insert 語句，如果 binlog_format=statement，記錄的語句就是原始語句。記錄的順序要馬先記 session A 的 insert 語句，要馬先記 session B 的 insert 語句。

但不論是哪一種，這個 binlog 拿去「從庫」執行，這時從庫是按「順序」執行語句的，只有當執行完一條 SQL 語句後，才會執行下一條 SQL。因此，在從庫上「不會」發生像主庫那樣兩個 session 「同時」向表 t2 中插入資料的場景。所以，在從庫上執行了 session B 的 insert 語句，生成的結果裡面，id 都是連續的。這時，主從庫就發生了資料不一致。

要解決這問題，binlog 日誌格式要設置為 row，這樣在 binlog 裡面記錄的是主庫分配的自增值，到從庫執行的時候，主庫的自增值是什麼，從庫的自增值就是什麼。

當 innodb_autoinc_lock_mode = 2 且 binlog_format = row，既能提升併發性，又不會出現資料一致性問題。

行級鎖

記錄鎖 (Record Lock)

Record Lock 稱為記錄鎖，鎖住的是一條記錄。而且記錄鎖是有 S 鎖和 X 鎖之分的：

• 當一個事務對一條記錄加了 S 型記錄鎖後，其他事務也可以繼續對該記錄加 S 型記錄鎖（S 型與 S 鎖兼容），但是不可以對該記錄加 X 型記錄鎖（S 型與 X 鎖不兼容）
• 當一個事務對一條記錄加了 X 型記錄鎖後，其他事務既不可以對該記錄加 S 型記錄鎖（S 型與 X 鎖不兼容），也不可以對該記錄加 X 型記錄鎖（X 型與 X 鎖不兼容）

舉個例子，當一個事務執行了下面這條語句：

對 t_test 表中主鍵 id 為 1 的這條記錄加上 X 型的記錄鎖，其他事務就無法對這條記錄進行修改了。

當事務執行 commit 後，事務過程中生成的鎖都會被釋放。

間隙鎖 (Gap Lock)

假設表中有一個範圍 id 為（3，5）間隙鎖，其他事務就無法插入 id = 4 這條記錄。

間隙鎖雖然存在 X 型間隙鎖和 S 型間隙鎖，但是並沒有區別，間隙鎖之間是兼容的，即兩個事務可以同時持有包含共同間隙範圍的間隙鎖，並不存在互斥關係。

臨鍵鎖 (Next-Key Lock)

Next-Key Lock 稱為臨鍵鎖，是 Record Lock + Gap Lock 的組合，如果一個事務獲取了 X 型的 next-key lock，另外一個事務在獲取相同範圍的 X 型的 next-key lock 時，是會被阻塞的。

雖然相同範圍的間隙鎖是多個事務相互兼容的，但對於記錄鎖，我們需要考慮 X 型與 S 型關係，X 型的記錄鎖與 X 型的記錄鎖是衝突的。

插入意向鎖 (Insert Intention Lock)

一個事務在插入一條記錄的時候，需要判斷插入位置是否已被其他事務加了間隙鎖（next-key lock 也包含間隙鎖）。

如果有的話，插入操作就會發生阻塞，直到擁有間隙鎖的那個事務提交為止（釋放間隙鎖的時刻），在此期間會生成一個插入意向鎖，表明有事務想在某個區間插入新記錄，但是現在處於等待狀態。

Reference

• MySQL 有哪些鎖？