鎖是計算機協調多個程式或純執行緒併發訪問某一資源的機制。

在資料庫中，除傳統的計算資源（CPU、RAM、I/O）的爭用以外，資料也是一種供許多使用者共享的資源。

如何保證資料併發訪問的一致性、有效性是所在有資料庫必須解決的一個問題，鎖衝突也是影響資料庫併發訪問效能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。

為什麼要有鎖？

使用資料庫，避免不了併發問題，當併發事務同時訪問一個資源時，有可能導致資料不一致，因此需要一種機制來將資料訪問順序化，以保證資料庫資料的一致性。

鎖就是其中的一種機制。

我們可以用公廁做個比喻。

公廁是可供多個消費者使用的，因此可能出現多個人同時需要使用廁所的情況。

但是，廁所只有一個，總不能大家一起吧？

為了避免衝突，於是廁所裡裝了鎖，某一個人在上測試時，可以在裡面用鎖鎖住，其他人就不能再從外面開啟了，只能等待。

等裡面的人出來了，從裡面把鎖開啟，外面的人才能進去。

下面，帶你一起梳理下 MySQL 的鎖管理機制和鎖的執行流程，先有一個大致的脈絡。

MySQL 的鎖管理機制

1、全域性讀鎖 — FLUSH TABLES WITH READ LOCK（SQL層）

2、表級 table-level 資料鎖（SQL層）

3、Meta-data 後設資料鎖：在 table cache 快取裡實現的，為 DDL（Data Definition Language）提供隔離操作。

4、儲存引擎特有機制 — row locks行鎖，page locks頁鎖，table locks表級，版本控制（在引擎中實現）

相對其他資料庫而言，MySQL 的鎖機制比較簡單，其最顯著的特點是不同的儲存引擎支援不同的鎖機制。

MySQL 的鎖執行流程

1、計算語句使用到的所有表；

2、在每個表：開啟表，從 table cache 快取裡得到 TABLE 物件，並在此表加上 meta-data 後設資料鎖；

3、等待全域性讀鎖後改變資料；

4、在每個表：鎖表，在表加上 table-level 資料鎖；

5、執行語句：呼叫：handler::write_row()/read_rnd()/read_index() 等；隱式地呼叫引擎級 engine-level 鎖機制;

6、在每個表：釋放表的資料鎖；

7、在每個表：釋放表的 DDL 鎖並把表放回 table cache 快取裡；

下面，我們開始簡單針對每一種鎖，看下都有什麼特點。

全域性鎖

加了全域性鎖後，整個庫變為只讀狀態，所有的寫操作都會被阻塞，包括：

• 資料的增刪改

• 表結構的建立、修改

• 更新事務

加全域性鎖的命令：Flush tables with read lock，即 FTWRL。

全域性鎖的主要使用場景是全庫的邏輯備份，加了全域性鎖進行備份時有一定的使用風險：

1、若在主庫備份，備份期間只讀，會影響業務；
2、若在從庫備份，從庫只讀，無法及時同步主可以的更新，造成主從不一致；

mysqldump --single-transaction

也許你還記得，我們在之前講事務的時候，有一個隔離級別叫做可重複讀，也就是設定了隔離級別進入事務後，別的事務更改資料不會影響當前的讀取。

使用 mysqldump 命令，結合 --single-transaction 引數，可以將隔離級別設定為：REPEATABLE READ。

並且隨後再執行一條 START TRANSACTION 語句，讓整個資料在 dump 過程中保證資料的一致性，這個選項對 InnoDB 的資料表很有用，且不會鎖表。

為了確保使用 --single-transaction 命令時，最終 dump 檔案的有效性。需沒有下列語句 ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，因為一致性讀不能隔離上述語句。所以如果在 dump 過程中，使用上述語句，可能會導致 dump 出來的檔案資料不一致或者不可用。

為啥不直接使用 mysqldump --single-transaction 來備份？

因為，有些引擎不支援事務啊，比如 MyISAM 引擎，所以，現在大家都在力推用 InnoDB 替代 MyISAM。

set global readonly=true？

set global readonly=true 也可以將全域性表設為只讀狀態，有啥區別呢？

首先，修改 global 變數的方式影響面更大，不建議使用。

另外，異常處理機制上和 FTWRL 有差異：

• FTWRL 命令：客戶端異常斷開，MySQL 會自動釋放全域性鎖，整個庫回到正常更新的狀態

• readonly 狀態下，客戶端發生異常，資料庫會一直保持 readonly 狀態，導致整個庫長時間處於不可寫狀態

注意點

• FTWRL 前有讀寫的話 ，FTWRL 都會等待讀寫執行完畢後才執行

• FTWRL 執行的時候要刷髒頁的資料到磁碟，要保持資料的一致性

• 執行 FTWRL 時候會等待所有事務都提交完畢

表級鎖

表鎖

語法

LOCK TABLES tbl_name ; # 不影響其他表的寫操作

解鎖也是：

UNLOCK TABLES;

注意點：

• 這兩個語句在執行的時候都需要注意個特點，就是隱式提交的語句，在退出 mysql 終端的時候都會隱式的自動執行 unlock tables，也就是如果要讓表鎖定生效就必須一直保持對話。

• lock tables 除了會限制別的執行緒的讀寫外，也會限制本執行緒接下來的操作物件

• 鎖住整個表的影響面較大

P.S. MYSQL 的 read lock 和 wirte lock

read-lock：允許其他併發的讀請求，但阻塞寫請求，即可以同時讀，但不允許任何寫，也叫共享鎖
write-lock：不允許其他併發的讀和寫請求，是排他的（exclusive），也叫獨佔鎖

後設資料鎖（MDL：metadata lock）

後設資料鎖不需要顯式使用，在訪問一個表的時候會自動加上。

它的作用主要是保證讀寫的正確性。

• 表的增刪改查操作，需要先加 MDL 讀鎖；

• 表結構變更操作，需要先加 MDL 寫鎖

• MDL 讀鎖之間不互斥，多個執行緒可以同時對一張表增刪改查。

• MDL 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。

如果有兩個執行緒要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行。

事務中的 MDL 鎖，在語句執行開始時申請，但是語句結束後並不會馬上釋放，而會等到整個事務提交後再釋放。

因此，需要避免長事務，因為長事務會造成鎖一直不能釋放，後續的操作會堆積，這個庫的執行緒很快就會爆滿。

行鎖

行鎖是引擎層實現的，像 MyISAM 引擎就直接不支援行鎖，這些引擎在併發控制只能用表鎖！

InnoDB 的行鎖

兩階段協議：

• 需要的時候加上

• 事務結束時釋放

當需要鎖多個行時，儘量把影響併發的鎖往後放，這樣可以最大程度的減少事務之間的鎖等待，提升併發度。

另外，InnoDB 的 行鎖建立在索引的基礎上，鎖的是索引。因此，如果更新的列沒建索引會鎖住整個表。

死鎖

不同執行緒出現迴圈資源依賴，涉及的執行緒都在等待別的執行緒釋放資源。

死鎖對策

1、主動等待超時，由引數 innodb_lock_wait_timeout 設定，但是業務無法等待；

2、主動死鎖檢測（innodb_deadlock_detect=on）

發生死鎖後，InnoDB 一般都可以檢測到，並使一個事務釋放鎖回退，另一個則可以獲取鎖完成事務。

另外，我們可以採取以下方式避免死鎖：

• 透過表級鎖來減少死鎖產生的機率；

• 多個程式儘量約定以相同的順序訪問表（這也是解決併發理論中哲學家就餐問題的一種思路）；

• 同一個事務儘可能做到一次鎖定所需要的所有資源。

另外，死鎖檢測也非常耗費資源，判斷會不會由於自己的加入導致了死鎖，這是一個時間複雜度是 O(n) 的操作。

比如有 1000 個併發執行緒要同時更新同一行，那麼死鎖檢測操作就是 100 萬這個量級的，這將消耗大量的 CPU 資源。

如何解決死鎖檢測耗費資源的情況？

1、關掉死鎖檢測，需要保證不會發生死鎖；
2、控制併發，對應相同行的更新，在進入引擎之前排隊；

• 資料庫服務端實現，中介軟體實現

• 不要在客戶端實現，因為客戶端的數量未知

• 改 MySQL 原始碼

• 將熱更新的行資料拆分成邏輯上的多行來減少鎖衝突，但是業務複雜度可能會大大提高

更新一條記錄時具體什麼時候用行鎖什麼時候是表鎖

引擎支援行鎖就行鎖，比如 innodb；

引擎不支援行鎖就表鎖，比如 myisam；

Online DDL 的過程

在 MySQL5.6 中，開始支援更多的 alter table 型別操作來避免 copy data，同時支援了線上上 DDL 的過程中不阻塞 DML 操作，真正意義上的實現了 Online DDL。

1、拿 MDL 寫鎖
2、降級成 MDL 讀鎖
3、真正做 DDL
4、升級成 MDL 寫鎖
5、釋放 MDL 鎖

1、2、4、5 如果沒有鎖衝突，執行時間非常短。
第 3 步佔用了 DDL 絕大部分時間，這期間這個表可以正常讀寫資料，是因此稱為「online」

總結

• 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的機率最高，併發度最低。

• 這些儲存引擎透過總是一次性同時獲取所有需要的鎖以及總是按相同的順序獲取表鎖來避免死鎖。

• 表級鎖更適合於以查詢為主，併發使用者少，只有少量按索引條件更新資料的應用，如 Web 應用

• 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的機率最低，併發度也最高。

• 最大程度的支援併發，同時也帶來了最大的鎖開銷。

• 在 InnoDB 中，除單個 SQL 組成的事務外，鎖是逐步獲得的，這就決定了在 InnoDB 中發生死鎖是可能的。

• 行級鎖只在儲存引擎層實現，而 Mysql 伺服器層沒有實現。

• 行級鎖更適合於有大量按索引條件併發更新少量不同資料，同時又有併發查詢的應用，如一些線上事務處理（OLTP）系統

上述特點來看，很難說哪種鎖更好，只能相對於所處的業務場景來選擇更加適合的鎖機制。

如果僅從鎖的角度來看，表級鎖更適合以查詢為主的應用場景，而行級鎖則更適合於大量按索引條件併發更新少量資料的應用場景。

對於平時常用的儲存引擎，MyISAM 採用的是表級鎖，InnoDB 採用的是行級鎖加表級鎖。

參考：