MySQL實戰(zhàn) | 06/07 簡單說說MySQL中的鎖

鎖是計算機協(xié)調(diào)多個進程或純線程并發(fā)訪問某一資源的機制。

在數(shù)據(jù)庫中，除傳統(tǒng)的計算資源（CPU、RAM、I/O）的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。

如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所在有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。從這個角度來說，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜。

為什么要有鎖？

使用數(shù)據(jù)庫，避免不了并發(fā)問題，當(dāng)并發(fā)事務(wù)同時訪問一個資源時，有可能導(dǎo)致數(shù)據(jù)不一致，因此需要一種機制來將數(shù)據(jù)訪問順序化，以保證數(shù)據(jù)庫數(shù)據(jù)的一致性。

鎖就是其中的一種機制。

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

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

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

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

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

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

MySQL 的鎖管理機制

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

2、表級 table-level 數(shù)據(jù)鎖（SQL層）

3、Meta-data 元數(shù)據(jù)鎖：在 table cache 緩存里實現(xiàn)的，為 DDL（Data Definition Language）提供隔離操作。

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

相對其他數(shù)據(jù)庫而言，MySQL 的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。

MySQL 的鎖執(zhí)行流程

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

2、在每個表：打開表，從 table cache 緩存里得到 TABLE 對象，并在此表加上 meta-data 元數(shù)據(jù)鎖；

3、等待全局讀鎖后改變數(shù)據(jù)；

4、在每個表：鎖表，在表加上 table-level 數(shù)據(jù)鎖；

5、執(zhí)行語句：調(diào)用：handler::write_row()/read_rnd()/read_index() 等；隱式地調(diào)用引擎級 engine-level 鎖機制;

6、在每個表：釋放表的數(shù)據(jù)鎖；

7、在每個表：釋放表的 DDL 鎖并把表放回 table cache 緩存里；

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

全局鎖

加了全局鎖后，整個庫變?yōu)橹蛔x狀態(tài)，所有的寫操作都會被阻塞，包括：

• 數(shù)據(jù)的增刪改

• 表結(jié)構(gòu)的創(chuàng)建、修改

• 更新事務(wù)

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

全局鎖的主要使用場景是全庫的邏輯備份，加了全局鎖進行備份時有一定的使用風(fēng)險：

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

mysqldump --single-transaction

也許你還記得，我們在之前講事務(wù)的時候，有一個隔離級別叫做可重復(fù)讀，也就是設(shè)置了隔離級別進入事務(wù)后，別的事務(wù)更改數(shù)據(jù)不會影響當(dāng)前的讀取。

使用 mysqldump 命令，結(jié)合 --single-transaction 參數(shù)，可以將隔離級別設(shè)置為：REPEATABLE READ。

并且隨后再執(zhí)行一條 START TRANSACTION 語句，讓整個數(shù)據(jù)在 dump 過程中保證數(shù)據(jù)的一致性，這個選項對 InnoDB 的數(shù)據(jù)表很有用，且不會鎖表。

為了確保使用 --single-transaction 命令時，最終 dump 文件的有效性。需沒有下列語句 ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，因為一致性讀不能隔離上述語句。所以如果在 dump 過程中，使用上述語句，可能會導(dǎo)致 dump 出來的文件數(shù)據(jù)不一致或者不可用。

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

因為，有些引擎不支持事務(wù)啊，比如 MyISAM 引擎，所以，現(xiàn)在大家都在力推用 InnoDB 替代 MyISAM。

set global readonly=true？

set global readonly=true 也可以將全局表設(shè)為只讀狀態(tài)，有啥區(qū)別呢？

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

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

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

• readonly 狀態(tài)下，客戶端發(fā)生異常，數(shù)據(jù)庫會一直保持 readonly 狀態(tài)，導(dǎo)致整個庫長時間處于不可寫狀態(tài)

注意點

• FTWRL 前有讀寫的話 ，F(xiàn)TWRL 都會等待讀寫執(zhí)行完畢后才執(zhí)行

• FTWRL 執(zhí)行的時候要刷臟頁的數(shù)據(jù)到磁盤，要保持數(shù)據(jù)的一致性

• 執(zhí)行 FTWRL 時候會等待所有事務(wù)都提交完畢

表級鎖

表鎖

語法

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

解鎖也是：

UNLOCK TABLES;

注意點：

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

• lock tables 除了會限制別的線程的讀寫外，也會限制本線程接下來的操作對象

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

P.S. MYSQL 的 read lock 和 wirte lock

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

元數(shù)據(jù)鎖（MDL：metadata lock）

元數(shù)據(jù)鎖不需要顯式使用，在訪問一個表的時候會自動加上。

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

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

• 表結(jié)構(gòu)變更操作，需要先加 MDL 寫鎖

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

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

如果有兩個線程要同時給一個表加字段，其中一個要等另一個執(zhí)行完才能開始執(zhí)行。

事務(wù)中的 MDL 鎖，在語句執(zhí)行開始時申請，但是語句結(jié)束后并不會馬上釋放，而會等到整個事務(wù)提交后再釋放。

因此，需要避免長事務(wù)，因為長事務(wù)會造成鎖一直不能釋放，后續(xù)的操作會堆積，這個庫的線程很快就會爆滿。

行鎖

行鎖是引擎層實現(xiàn)的，像 MyISAM 引擎就直接不支持行鎖，這些引擎在并發(fā)控制只能用表鎖！

InnoDB 的行鎖

兩階段協(xié)議：

• 需要的時候加上

• 事務(wù)結(jié)束時釋放

當(dāng)需要鎖多個行時，盡量把影響并發(fā)的鎖往后放，這樣可以最大程度的減少事務(wù)之間的鎖等待，提升并發(fā)度。

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

死鎖

不同線程出現(xiàn)循環(huán)資源依賴，涉及的線程都在等待別的線程釋放資源。

死鎖對策

1、主動等待超時，由參數(shù) innodb_lock_wait_timeout 設(shè)置，但是業(yè)務(wù)無法等待；

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

發(fā)生死鎖后，InnoDB 一般都可以檢測到，并使一個事務(wù)釋放鎖回退，另一個則可以獲取鎖完成事務(wù)。

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

• 通過表級鎖來減少死鎖產(chǎn)生的概率；

• 多個程序盡量約定以相同的順序訪問表（這也是解決并發(fā)理論中哲學(xué)家就餐問題的一種思路）；

• 同一個事務(wù)盡可能做到一次鎖定所需要的所有資源。

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

比如有 1000 個并發(fā)線程要同時更新同一行，那么死鎖檢測操作就是 100 萬這個量級的，這將消耗大量的 CPU 資源。

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

1、關(guān)掉死鎖檢測，需要保證不會發(fā)生死鎖；
2、控制并發(fā)，對應(yīng)相同行的更新，在進入引擎之前排隊；

• 數(shù)據(jù)庫服務(wù)端實現(xiàn)，中間件實現(xiàn)

• 不要在客戶端實現(xiàn)，因為客戶端的數(shù)量未知

• 改 MySQL 源碼

• 將熱更新的行數(shù)據(jù)拆分成邏輯上的多行來減少鎖沖突，但是業(yè)務(wù)復(fù)雜度可能會大大提高

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

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

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

Online DDL 的過程

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

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

1、2、4、5 如果沒有鎖沖突，執(zhí)行時間非常短。
第 3 步占用了 DDL 絕大部分時間，這期間這個表可以正常讀寫數(shù)據(jù)，是因此稱為「online」

總結(jié)

• 表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。

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

• 表級鎖更適合于以查詢?yōu)橹?，并發(fā)用戶少，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如 Web 應(yīng)用

• 行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

• 最大程度的支持并發(fā)，同時也帶來了最大的鎖開銷。

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

• 行級鎖只在存儲引擎層實現(xiàn)，而 Mysql 服務(wù)器層沒有實現(xiàn)。

• 行級鎖更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)

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

如果僅從鎖的角度來看，表級鎖更適合以查詢?yōu)橹鞯膽?yīng)用場景，而行級鎖則更適合于大量按索引條件并發(fā)更新少量數(shù)據(jù)的應(yīng)用場景。

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

參考：
https://dwz.cn/oudQ7cM9