本文說明的是MySQL鎖，和作業系統或者程式語言的鎖無關。
概念
作用：在併發情況下讓資料正確的讀寫。
優點：併發情況下對資料讀寫可控，防止出錯。
缺點：降低效能、增加難度。
分類
- 資料操作型別劃分
- 讀鎖（共享鎖、S鎖）
- 寫鎖（排它鎖、獨佔鎖、X鎖）
- 粒度劃分
- 表級鎖
- S鎖、X鎖
- 意向鎖
- 自增鎖
- 後設資料鎖
- 行級鎖
- 記錄鎖
- 間隙鎖
- 臨鍵鎖
- 插入意向鎖
- 頁級鎖
- 表級鎖
- 嚴格度劃分
- 悲觀鎖
- 樂觀鎖
- 加鎖方式
- 隱式鎖
- 顯示鎖
- 其它
- 全域性鎖
- 死鎖
測試用表
CREATE TABLE `cs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`num1` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '數字列1',
`num2` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '數字列2',
`s1` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字串列1',
`s2` varchar(2000) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '字串列2',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `temp`.`cs` (`id`, `num1`, `num2`, `s1`, `s2`) VALUES (1, 1, 1, 'abc', 'xyz');
INSERT INTO `temp`.`cs` (`id`, `num1`, `num2`, `s1`, `s2`) VALUES (2, 2, 2, 'ABC', 'XYZ');
讀鎖、共享鎖、S鎖
讀鎖、共享鎖、S鎖（Share Lock）是一個東西。
鎖的是：允許同時有多個事務，對資料只能讀，不能寫。
寫法：
select ... lock in share mode;
select ... for share; #MySQL8
測試：
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | select * from cs where id = 1 lock in share mode; | select * from cs where id = 1 lock in share mode; | 雙方新增共享鎖，都能成功新增 |
| 3 | commit; | commit; | 正常提交事務，無報錯 |
寫鎖、排它鎖、X鎖、獨佔鎖
寫鎖、排它鎖、X鎖（Exclusive Lock）、獨佔鎖是同一個東西。
鎖的是：僅允許同時有1個鎖獨佔該事務，具有排它性，不允許其它任何型別的鎖再佔用該事務。
select ... for update;
測試：
X鎖排斥S鎖1
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | select * from cs where id = 1 lock in share mode; | select * from cs where id = 1 for update; | 會話A新增S鎖，會話B新增X鎖 |
| 3 | / | 阻塞 | 會話B新增X鎖被阻塞，說明X鎖有排它性 |
| 4 | commit; | / | 會話B X鎖新增成功 |
| 5 | / | commit; | 會話B提交事務，結束流程 |
X鎖排斥S鎖2
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | select * from cs where id = 1 for update; | select * from cs where id = 1 lock in share mode; | 會話A新增X鎖，會話B新增S鎖 |
| 3 | / | 阻塞 | 會話B新增S鎖被阻塞，說明X鎖有排它性 |
| 4 | commit; | / | 會話B S鎖新增成功 |
| 5 | / | commit; | 會話B提交事務，結束流程 |
X鎖排斥X鎖
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | select * from cs where id = 1 for update; | select * from cs where id = 1 for update; | 雙方新增X鎖 |
| 3 | / | 阻塞 | 會話B新增X鎖阻塞，證明排它性 |
| 4 | commit; | / | 會話B X鎖新增成功 |
| 5 | / | commit; | 會話B提交事務，結束流程 |
S鎖、X鎖常見誤區
注意：在同一個事務裡面，行級X鎖或S鎖，允許其它SQL語句對此行的寫操作。
如下，每條SQL都能成功執行。
所以說鎖，鎖的是對外的事務，對內（當前會話）不做限制。
start transaction;
select * from cs where id = 1 for update;
update cs set num1 = 2 where id = 1;
commit;
start transaction;
select * from cs where id = 1 lock in share mode;
update cs set num1 = 2 where id = 1;
commit;
注意：在任意個事務裡面，可以對同一條不存在的資料共同新增X和S鎖，不會阻塞。
事務A
start transaction;
select * from cs where id = 1234 for update;
select * from cs where id = 1234 lock in share mode;
事務B
start transaction;
select * from cs where id = 1234 for update;
select * from cs where id = 1234 lock in share mode;
事務A
commit;
事務B
commit;
注意：事務A加了X鎖，不影響事務B讀這條資料。
事務A
start transaction;
select * from cs where id = 1 for update; #能正常讀出資料
事務B
start transaction;
select * from cs where id = 1;
事務A
commit;
事務B
commit;
對阻塞超時時間的最佳化
遇到X鎖會阻塞，預設阻塞50秒，50秒過後阻塞停止，但不會自動的回滾或者提交事務。MySQL提供了一些阻塞時間的最佳化方向
方式1：配置最佳化
檢視阻塞時間，單位秒
show variables like 'innodb_lock_wait_timeout';
或
select @@innodb_lock_wait_timeout;
修改
set session innodb_lock_wait_timeout = 10; #當前會話生效
或
set global innodb_lock_wait_timeout = 10; #全域性生效，重啟後新配置丟失
注意global的修改對當前執行緒是不生效的，只有建立新的連線才生效
或者修改配置檔案
vim /etc/my.cnf
[mysqld]
innodb_lock_wait_timeout = 10
方式2：SQL語句層面，MySQL8新特性
select ... nowait：會立即停止，但報錯。
select ...skip locked：會立即停止，但不會報錯，結果中不包含被鎖定的行。
表鎖、頁鎖、行鎖
這是鎖定的粒度，3個依次遞減，粒度越小越好。力度小意味著更少的資源被鎖定，可以提高併發性。
表鎖
表鎖就是鎖住了整張表，所以表鎖比行鎖，更不容易發生死鎖的情況。
注意：MyISAM引擎使用select，會給當前表加上S鎖，在寫操作時會加上X鎖。
檢視一些資料庫的表，都加了那些鎖
show open tables;
In_use: 是否正在被使用，如果為 1 則表示該表正在被某個執行緒使用。
Name_locked: 是否被上了表級鎖，如果為 1 則表示該表被鎖定。
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
+--------------------+------------------------------------------------------+--------+-------------+
為某個表新增X鎖
lock table 表名 write;
為某個表新增S鎖
lock table 表名 read;
取消表鎖，部分XS
unlock tables;
表鎖許可權如下（MySQL5.7）
| 表鎖型別 | 當前會話對當前表可讀 | 當前會話對當前表可寫 | 其它會話對當前表可讀 | 其它會話對當前表可寫 |
|---|---|---|---|---|
| S鎖 | 是 | 否 | 是 | 阻塞 |
| X鎖 | 阻塞 | 是 | 阻塞 | 阻塞 |
表級鎖->意向鎖、意向共享鎖、意向排它鎖、IS鎖、IX鎖
意向鎖（Intention Shared Lock）是自動維護的鎖，用於表級別上表明事務對錶中某個行的操作意向，意思是在表級別上宣告瞭某個行已經加鎖，最佳化事務之間的併發訪問。意向鎖分為兩種：意向共享鎖（IS）和意向排它鎖（IX）。
MyISAM引擎不支援，InnoDB引擎支援。
MySQL新增行級鎖，都會向上宣告新增了意向鎖。
注意意向排它鎖與意向排它鎖不衝突，意向排它鎖與意向共享鎖都不衝突，如果衝突了，鎖的靈活性將大大降低。距離說明：兩個不同的行X鎖各生成一個IX鎖，如果IX鎖衝突，則無法建立兩個行X鎖，這種巨大的bug不允許出現。
優點：
- 提高併發性：如果沒有意向鎖，在一個百萬級的大表中加一個鎖，可能需要逐行遍歷，看看有沒有加其它鎖，會影響當前新增的鎖，但是有了意向鎖，在表的層面就可以獲取先前的鎖，提高效能。
- 避免死鎖：透過意向鎖，可以快速判斷哪些鎖請求是互斥的，減少鎖任務堆積引起的複雜度增加，複雜度一上來，就容易有死鎖。
演示：行X鎖與表S鎖
| 步驟 | 會話A | 會話B | 說明 |
|---|---|---|---|
| 1 | start transaction; | / | 會話A開啟事務 |
| 2 | select * from cs where id = 1 for update; | / | 會話A加一個行X鎖，相當於自動新增了一個IX鎖 |
| 3 | / | lock table cs read; | 會話B新增表S鎖 |
| 4 | / | 阻塞 | 這個過程就是表鎖檢測到了事先新增的IX鎖，行級X鎖排斥表級S鎖，阻塞了 |
| 5 | commit; | 成功加上了表級S鎖 | 會話A事務提交，鎖資源釋放 |
| 6 | / | unlock tables; | 釋放表級S鎖 |
表級鎖->自增鎖、AI鎖
自增鎖（Auto Increment Lock）是指對自增長列（一般是主鍵）確保唯一性的一種鎖機，如果沒有自增鎖，多個事務併發的執行，該列的值就可能一致。
為了避免重複，自增鎖可能會導致插入操作的序列化，降低併發效能，這是它的缺點。
演示：
| 步驟 | 會話A | 會話B | 說明 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ'); | INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ'),(2, 2, 'ABC', 'XYZ'); | 雙方新增3條記錄 |
| 3 | commit | commit; | 提交事務 |
| 4 | select id from cs | select id from cs | 並未發現報錯，或者id重複的現象 |
表級鎖->後設資料鎖、MDL鎖
MDL（Metadata Lock）聽起來高大上，實際上就是DDL操作時自動加鎖，所以不需要手動處理。
當要對錶做增刪改查操作時，會獲取一個 MDL 讀鎖來阻止對錶結構的修改。
更改表結構時，會加MDL寫鎖，阻塞其它執行緒的讀寫操作，直到結構變更操作完成。
演示 MDL讀鎖：
| 步驟 | 會話A | 會話B | 說明 |
|---|---|---|---|
| 1 | start transaction; | / | 會話A開啟事務 |
| 2 | INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ'); | alter table cs add index (num1); | 會話B新增一個普通索引 |
| 3 | / | 阻塞 | 會話B被阻塞 |
| 4 | commit; | 索引新增成功 | MDL鎖釋放 |
演示 MDL寫鎖：
| 步驟 | 會話A | 會話B | 會話C | 說明 |
|---|---|---|---|---|
| 1 | start transaction; | start transaction; | / | AB方開啟事務 |
| 2 | INSERT INTO cs (num1, num2, s1, s2) VALUES (2, 2, 'ABC', 'XYZ'); | alter table cs add index (num2); | / | 會話B新增一個普通索引 |
| 3 | / | 阻塞 | / | 用會話A的MDL讀鎖去阻塞會話B |
| 4 | / | / | select * from cs | 用會話B的MDL寫鎖去阻塞會話C |
| 5 | / | 阻塞 | 阻塞 | MDL寫鎖阻塞了查詢操作 |
| 6 | rollback; | rollback; | 成功查詢出資料 | 事務回滾，鎖資源釋放。但undo log不記錄DDL語句，所以索引被新增無法回滾。 |
行鎖->記錄鎖、RL鎖
記錄鎖（Record Lock）就是普通的對某一行上的X或者S鎖。
注意InnoDB引擎下，支援行鎖MyISAM不支援。
測試：
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | select * from cs where id = 1 lock in share mode; | select * from cs where id = 1 lock in share mode; | 雙方新增共享鎖，都能成功新增 |
| 3 | commit; | commit; | 正常提交事務，無報錯 |
行鎖->間隙鎖、GL鎖
間隙鎖（Gap Lock）就是在資料間隙加的鎖，用於防止事務在一個範圍內插入新的符合條件的行，以避免中間插入的幻讀的問題（MySQL預設的RR隔離級別會產生幻讀問題）。
例如一個一個不連續的id列，1,6,10，如果在1~6之間新增一個鎖，就可以1~6防止幻讀的插入操作，10後面間隙鎖防止幻讀就失效了，需要再次宣告新的間隙鎖。1~6之間沒有具體的資料，所以X間隙鎖，或者是S間隙鎖，沒有什麼區別。
MySQL預設的RR級別會產生幻讀，幻讀是指在同一事務中，先後執行相同的查詢範圍，查詢到的數量不一致（mysql的select機制測不出來，兩個事務需要插入相同的id引起的報錯才能測出來），在併發環境中，幻讀通常是因為其他事務在同一範圍內插入新資料導致的。以下的演示，不演示幻讀，只演示間隙鎖阻止幻讀。
測試：
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | select * from cs where id = 4 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (3, 1, 1, 'abc', 'xyz'); | 會話A新增間隙鎖，會話B插入資料這個區間的資料 |
| 3 | / | 阻塞 | 可見間隙鎖可以阻止區間幻讀 |
| 4 | commit; | commit; | 會話A關閉事務，會話B提交插入資料 |
當前id最大值為10，測試大於10的幻讀區間
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | sselect * from cs where id = 14 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (10000, 1, 1, 'abc', 'xyz'); | 會話A新增間隙鎖，會話B插入id為10000的資料 |
| 3 | / | 阻塞 | 可見間隙鎖可以防止最大記錄 ~ +∞之間的幻讀 |
| 4 | commit; | commit; | 會話A關閉事務，會話B提交插入資料 |
當前id最大值為100，同樣的方法測試小於100的幻讀區間
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | sselect * from cs where id = 10001 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (9999, 1, 1, 'abc', 'xyz'); | 會話A新增間隙鎖，會話B插入id為9999的資料 |
| 3 | / | 成功插入 | 間隙鎖未能阻塞幻讀插入 |
| 4 | commit; | commit; | 會話A關閉事務，會話B提交插入資料 |
行鎖->臨鍵鎖、NKL鎖
臨鍵鎖（next-key lock）相當於記錄鎖+間隙鎖，用於防止幻讀的鎖。
測試：現表中有id為1,6,10三條資料。
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | select * from cs where id between 1 and 6 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (4, 1, 1, 'abc', 'xyz'); | 會話A新增臨鍵鎖，會話B插入id為4的資料 |
| 3 | / | 阻塞 | 臨鍵鎖阻塞幻讀插入 |
| 4 | commit; | commit; | 雙方提交事務，結束流程 |
行鎖->插入意向鎖、IIL鎖
插入意向鎖（Insert Intention Lock）是間隙鎖的一種，由insert行插入前的操作。該鎖表示插入的意圖，即插入同一索引間隙的多個事務如果沒有在間隙內的相同位置插入，則insert不需要相互等待。
通俗講就是如果間隙鎖得到了釋放，則需要insert的多個事務，不會阻塞。
測試：現表中有id為1,6,10三條資料。
| 步驟 | 會話A | 會話B | 會話C | 備註 |
|---|---|---|---|---|
| 1 | start transaction; | start transaction; | start transaction; | 三方開啟事務 |
| 2 | select * from cs where id = 5 lock in share mode; | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (4, 1, 1, 'abc', 'xyz'); | INSERT INTO cs (id, num1, num2, s1, s2) VALUES (4, 1, 1, 'abc', 'xyz'); | 事務A上間隙鎖，其餘事務插入資料 |
| 3 | / | 阻塞 | 阻塞 | 間隙鎖起作用阻塞B、C會話 |
| 4 | commit; | 成功插入 | 成功插入 | 插入意向鎖起作用，讓兩個插入的事務不發生阻塞 |
| 5 | commit; | commit; | commit; | 提交事務，結束流程 |
頁鎖
頁鎖是一種鎖定級別，用於控制對資料表中的頁（MySQL對錶資料讀寫的基本單位）的訪問。
由於SQL語句層面，DDL、DML、DQL，都是針對庫表或者表資料的讀寫操作，對於頁極少操作，頁鎖粒度較大，其它鎖範圍使用場景夠用，所以瞭解概念就行。
悲觀鎖
之前寫過詳細的文章可進行參考：MySQL樂觀鎖與悲觀鎖。
悲觀鎖比較悲觀，假設資料一定會造成衝突，屬於MySQL層面的鎖。透過加鎖阻塞其他事務，悲觀鎖可以保證在任何時刻，只有一個事務能夠修改或訪問共享資源，從而實現了強一致性。這意味著在悲觀鎖機制下，每個事務的讀寫操作都是有序、線性的。
需要事務的參與。
悲觀鎖，最經典的場景就是防超賣，一共10個庫存，由於併發情況下，兩個請求查詢到的庫存都是10，一個請求下單6個，一個請求下單8個，如果不加鎖使其在事務中阻塞（阻塞可以理解為強制讓事務序列執行），那就會有超賣的情況發生，即使是超賣，庫存也不會顯示為負，因為併發情況下兩個請求檢測到的庫存都是10,大於8和6，最終扣庫存的結果不是10-8-6=-4（這是序列請求的理論值），而是2或者4。
適用場景
寫多寫操作的前提，是保證資料不出錯，悲觀鎖的機制很符合。
優點
- 強一致性：基於事務又加鎖，一致性可以保證。
- 實現簡單：在事務中for update即可，開發者不需要在這上面關注太多。
缺點
- 死鎖風險：悲觀鎖在使用不當的情況下可能導致死鎖。如果多個事務持有鎖並相互等待對方釋放鎖的情況發生，就可能發生死鎖。
- 效能較低：悲觀鎖通常需要在整個事務過程中鎖定資源，這可能導致其他事務阻塞。
簡單測試：模擬併發情況下防超賣，假設cs表的num1欄位為庫存欄位
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | select num1 from cs where id = 1 for update; update cs set num1 = num1 - 6 where id = 1 and num1 >= 6; |
select num1 from cs where id = 1 for update; update cs set num1 = num1 - 8 where id = 1 and num1 >= 8; |
雙方新增X行鎖，並準備扣減庫存 |
| 3 | / | 阻塞 | 此時必有一個事務阻塞，等這個事務扣完庫存提交事務後，在執行另一個扣庫存的事務 |
| 4 | commit; | commit; | 雙方提交事務，雙方程式碼執行成功，但會話B庫存不足，最終剩餘庫存4，結束流程 |
注意，實際的開發邏輯，與以上示例況有偏差。
若前端檢測到庫存不足，直接攔截使用者的下單動作並提示，就不會有後端的流程。
若前端認為庫存充足，後端可能是下單時預扣庫存，預扣庫存的環節提前就檢測當前其庫存是否夠當前的購買數量，若不夠，則直接終止流程，返回使用者結果。
若庫存夠用但未支付，若超時30分鐘未支付則取消訂單（這個延時佇列可以用Redis Zset 或者RabbitMQ等其它訊息中介軟體實現），若支付，則算一筆成功的交易。
這是MySQL能抗住的情況，如果抗不住，就需要Redis+Lua的單執行緒事務來阻止超賣的發生。
樂觀鎖
之前寫過詳細的文章可進行參考：MySQL樂觀鎖與悲觀鎖
樂觀鎖很樂觀，假設資料一般情況不會造成衝突，屬於程式層面的邏輯鎖，在資料進行更新時，才進行鎖的檢測。是透過新增一個版本號的方式實現的，每當資料這一行所在的資料發生變化，則對應的版本號+1，更新資料時，將版本號作為查詢條件。
至於是否要加事務，看寫操作單條資料還是寫操作多條資料。
注意：網上很多解決方案用時間戳來做version欄位，我持反對意見，併發可能是一瞬間的事，不到一秒就有好多請求，用時間戳粒度太大，用隨機字串都比用這個強。
適用場景
- 讀多寫少：由於併發寫操作較少，樂觀鎖的修改資料受影響行數為0機率也較低。
允許一定量的重試或不需要重試的場景：這個要根據業務，否則來回重試會降低效能。
優點 - 實現簡單：樂觀鎖在程式碼上就可以實現，不需要額外對資料庫額外操作。
- 無死鎖風險：悲觀鎖有死鎖風險，樂觀鎖沒有。
無需重試情況下，效能較高：樂觀鎖機制在併發訪問情況下，不需要像悲觀鎖那樣阻塞其他事務，提供了更高的併發效能，前提當前業務需求能容忍寫操作失敗的情況。
缺點
- 併發衝突：多加了一個where條件，只能保證資料最終不會出錯，不能保證每條寫操作的SQL都執行成功（也就是受影響行數>0）。
- 不提供強一致性：強一致性要求資料的狀態在任何時刻都保持一致，悲觀鎖是到寫操作那一步才去驗證，期間只是做了個where條件的過濾。
- ABA問題：一個欄位的值在請求X中查詢出來是A，後續程式碼實現樂觀鎖，因為併發量大，同時過來一個Y請求，將A值改成了B，因為一些業務原因又改成了A，整個過程雖然不影響請求X的結果，且能正常執行，但是聯合其它資料，這個情況是否符合業務場景，不好說，所以最好的解決方案，就是專門做一個version欄位，且不會與之前的version重複，即可，把這個version欄位作為where條件，而不是存A或者B欄位的所在欄位作為where條件。
- 當前的請求中了上一個樂觀鎖的招，導致的版本號不一樣，需要重試，反覆的重試也降低效能，由於這個原因，所以用於讀操作多的場景。
簡單測試：模擬併發情況下防超賣，假設cs表的num1欄位為庫存欄位，s1為version欄位，一共10個庫存，兩個請求查詢到的庫存都是10，一個請求下單6個，一個請求下單8個。
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | select * from cs where id = 1 | select * from cs where id = 1 | 雙方獲取版本號和庫存 |
| 2 | update cs set num1 = num1 - 6,s1 = 'version2' where id = 1 and s1 = 'version1' and num1 >=6; | update cs set num1 = num1 - 8,s1 = 'version1' where id = 1 and s1 = 'version1' and num1 >=8; | 樂觀鎖無需事務，但是需要多個原子性的業務場景，仍推薦加事務 |
| 3 | 扣庫存成功 | 雖然是併發場景，但A執行略快於B，此時版本號已修改為version2 | 會話B執行成功，但庫存扣減失敗 |
| 4 | / | select * from cs where id = 1 | 會話B進行重試，並重新獲取版本號和庫存 |
| 5 | / | update cs set num1 = num1 - 8,s1 = 'version3' where id = 1 and s1 = 'version2' and num1 >=8; | 雙方程式碼執行成功，但會話B庫存不足，最終剩餘庫存4，結束流程 |
隱式鎖
隱式鎖不需要顯式地編寫鎖定關鍵詞，會自動加鎖或解鎖。
例如在未提交的事務中插入資料，就是透過隱式鎖的方式避免其它事務對插入資料的讀寫，如果能讀到了那就是髒讀，如果能寫那就是髒寫。
隱式鎖是一種延時載入的方式，在多個事務的情況下，在第二個事務訪問時，才會被動的加鎖，這樣可以減少鎖的數量。
由於不需要手動處理，瞭解機制就行。
| 步驟 | 會話A | 會話B | 會話C | 備註 |
|---|---|---|---|---|
| 1 | start transaction; | start transaction; | SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; | AB開啟事務，C檢測鎖 |
| 2 | insert into cs(num1, num2, s1, s2) values(1,2,3,4); | / | SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; | A插入資料，此時C未檢測到有鎖 |
| 3 | / | select * from cs lock in share mode; | SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; | insert操作會產生一個寫鎖，進而阻止事務B的讀鎖，此時事務C可以檢視的到 |
| 4 | / | 阻塞 | / | 在第二個事務訪問時，事務A被動的加鎖，透過事務C可以檢視的到 |
| 5 | commit; | commit; | / | 雙方提交事務，會話A成功插入一條資料 |
顯式鎖
顯式宣告的鎖，for update，lock in share mode這種的。
全域性鎖
全域性鎖（Global Lock）的作用是鎖定整個資料庫例項，讓整個庫只能讀不能寫，粒度很大。
flush tables with read lock：獲取全域性讀鎖，對所有事務只能讀不能寫。
unlock tables：釋放全域性鎖。
應用場景：備份資料庫或者匯出資料時，為了保證資料的一致性而需要鎖定整個資料庫例項，確保備份過程中資料不會發生變化。全域性鎖會影響資料庫的正常執行。
測試：
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | flush tables with read lock; | / | 會話A開啟全域性鎖 |
| 2 | / | select * from cs; desc cs; |
讀資料表和表資料都沒問題 |
| 3 | / | insert into cs(num1, num2, s1, s2) values(1,2,3,4); | 會話B插入資料 |
| 4 | / | 阻塞 | 全域性鎖禁止寫操作 |
| 5 | unlock tables; | 插入成功 | 釋放全域性鎖。資料插入成功 |
| 6 | flush tables with read lock; | / | 會話A開啟全域性鎖 |
| 7 | / | alter table cs add index(num1); | 會話B為表欄位加索引 |
| 8 | / | 阻塞; | 全域性鎖阻塞DDL操作 |
| 9 | unlock tables; | 索引新增成功 | 釋放全域性鎖，索引插入成功，結束會話 |
死鎖
之前寫過關於死鎖的詳細文章：MySQL死鎖。
MySQL 中的死鎖是指兩個或多個事務相互等待對方釋放鎖資源，導致它們永遠無法繼續執行的情況。（不會耕田,怎能下地 不會下地,怎麼耕田）
測試：先cs表中有id為1,6的兩條資料
| 步驟 | 會話A | 會話B | 備註 |
|---|---|---|---|
| 1 | start transaction; | start transaction; | 雙方開啟事務 |
| 2 | update cs set num1 = 1 where id = 1; | update cs set num1 = 60 where id = 6; | 更新資料預設會產生X鎖，這一步正常執行 |
| 3 | update cs set num1 = 6 where id = 6; | update cs set num1 = 10 where id = 1; | 更新資料預設會產生X鎖 |
| 4 | 阻塞 | / | 事務B對於id=6的寫鎖並未釋放，此時又新增了update請求，需要加X鎖，所以阻塞 |
| 5 | / | Deadlock found when trying to get lock; try restarting transaction | 事務AB互相需要等待，導致死鎖 |
| 6 | commit; | commit; | 雙方提交事務，結束流程，事務A正確執行，事務B被回滾 |
排查方法1
show engine innodb status;
執行以上命令會得到大量日誌，在LATEST DETECTED DEADLOCK與TRANSACTIONS之間尋找sql 語句，以此定位死鎖源頭。示例如下：
......
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-23 23:48:30 0x1f00
*** (1) TRANSACTION:
TRANSACTION 805714, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 8076, query id 762 localhost ::1 root updating
update test set name = 'C++語言' where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805714 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 6; hex 0000000c4b53; asc KS;;
2: len 7; hex 34000002a80923; asc 4 #;;
3: len 3; hex 432b2b; asc C++;;
*** (2) TRANSACTION:
TRANSACTION 805715, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 7936, query id 766 localhost ::1 root updating
delete from test where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805715 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 00000002; asc ;;
1: len 6; hex 0000000c4b53; asc KS;;
2: len 7; hex 34000002a80923; asc 4 #;;
3: len 3; hex 432b2b; asc C++;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2575 page no 3 n bits 72 index PRIMARY of table `temp`.`test` trx id 805715 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 0000000c4b46; asc KF;;
2: len 7; hex ad000002b10110; asc ;;
3: len 1; hex 43; asc C;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 805727
Purge done for trx's n:o < 805727 undo n:o < 0 state: running but idle
History list length 36
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283132143509864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143508992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143508120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283132143507248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
......
排查方法2
show status like 'innodb_row_lock%'
執行以上命令後會得到一個表格，
Innodb_row_lock_current_waits 0 如果這個數字非零，表示有事務正在等待其他事務持有的行級鎖。
Innodb_row_lock_time 18756 以毫秒為單位。它表示所有事務在競爭鎖資源時，所消耗的總時間
Innodb_row_lock_time_avg 3126 平均每個InnoDB行級鎖的持有時間，毫秒為單位。用總的鎖持有時間除以鎖請求次數來計算。
Innodb_row_lock_time_max 7921 單個InnoDB行級鎖的最大持有時間，毫秒為單位。這個值表示所有事務競爭鎖資源中的最長時間。
Innodb_row_lock_waits 6 這個指標表示有多少次事務在競爭鎖資源時需要等待。
排查方法3
該查詢是用來獲取當前正在執行的事務（INNODB_TRX表）、空閒狀態的執行緒（PROCESSLIST表，COMMAND為Sleep）、執行緒對應的程序資訊（threads表）、執行緒對應的當前執行中的SQL語句（events_statements_current表）的一些相關資訊。
SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b.USER, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO, c.PROCESSLIST_USER, c.PROCESSLIST_HOST, c.PROCESSLIST_DB, d.SQL_TEXT
FROM information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep'
LEFT JOIN performance_schema.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
解決
MySQL會自動解決死鎖問題，代價就是MySQL自行找到成本最低的事務，自動回滾。所以說解決方案不是解鎖，而是避免。
避免
- 降低事務粒度：輕量級的事務，鎖定更少的資源，不容易發生死鎖。
- 儘快提交事務：鎖能更快的釋放。
- 合理的索引設計: 合理設計資料庫表的索引可以減少鎖競爭，提高查詢效率。
- 一致的訪問順序: 當應用程式涉及多個表時，保持一致的訪問順序可以避免死鎖。例如，如果事務A先鎖定表X，再鎖定表Y，那麼事務B也應該按照相同的順序鎖定表X和表Y，從而避免死鎖。
什麼是最小成本回滾策略？
在 MySQL 中，當發生死鎖時，MySQL 使用一種叫做"最小成本回滾"（InnoDB 中稱為"最小編號事務回滾"）的策略來選擇一個事務作為犧牲者並進行回滾，最小成本回滾策略是 MySQL 的預設行為，它會自動選擇犧牲者並回滾事務。
最小成本回滾策略的原理是選擇最小成本的事務作為犧牲者。評估演算法如下：
- 回滾事務所涉及的運算元量：回滾操作的數量越小，成本越低。
- 回滾事務所佔用的系統資源：回滾事務佔用的系統資源越少，成本越低。
- 回滾事務已執行的工作量：已執行的工作量越少，成本越低。
鎖排查與監控
查詢語句用於檢視 InnoDB 儲存引擎中關於行鎖的相關統計資訊
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits 如果這個數字非零，表示有事務正在等待其他事務持有的行級鎖。
Innodb_row_lock_time 以毫秒為單位。它表示所有事務在競爭鎖資源時，所消耗的總時間
Innodb_row_lock_time_avg 平均每個InnoDB行級鎖的持有時間，毫秒為單位。用總的鎖持有時間除以鎖請求次數來計算。
Innodb_row_lock_time_max 單個InnoDB行級鎖的最大持有時間，毫秒為單位。這個值表示所有事務競爭鎖資源中的最長時間。
Innodb_row_lock_waits 這個指標表示有多少次事務在競爭鎖資源時需要等待。
檢視 InnoDB 儲存引擎中當前活動的事務資訊
SELECT * FROM information_schema.innodb_trx;
trx_id 事務的唯一識別符號。
trx_state 事務的狀態，如 RUNNING、LOCK WAIT、ROLLING BACK 等。
trx_started 事務啟動的時間。
trx_requested_lock_id 請求的鎖的識別符號。
trx_wait_started 等待鎖的開始時間。
trx_weight 事務的權重，用於死鎖檢測。
trx_mysql_thread_id MySQL 執行緒 ID。
trx_query 與事務相關的 SQL 查詢語句。
trx_operation_state 事務內部操作的狀態。
trx_tables_in_use 事務使用的表的數量。
trx_tables_locked 事務鎖定的表的數量。
trx_lock_structs 事務內部使用的鎖結構數量。
trx_lock_memory_bytes 用於事務鎖定的記憶體位元組數。
trx_rows_locked 事務鎖定的行數。
trx_rows_modified 事務修改的行數。
trx_concurrency_tickets 用於事務併發控制的票數。
trx_isolation_level 事務的隔離級別。
trx_unique_checks 是否啟用了唯一性檢查。
trx_foreign_key_checks 是否啟用了外來鍵約束檢查。
trx_last_foreign_key_error 最後一個外來鍵錯誤資訊。
trx_adaptive_hash_latched 是否適應性雜湊被鎖定。
trx_adaptive_hash_timeout 適應性雜湊鎖定超時次數。
trx_is_foreign_key_with_check 是否用於外來鍵約束檢查。
trx_is_foreign_key 是否用於外來鍵約束。
檢視 InnoDB 儲存引擎中當前正在被鎖定的物件（如表、行）的資訊
SELECT * FROM information_schema.innodb_locks;
lock_id 鎖的唯一識別符號。
lock_trx_id 持有該鎖的事務的唯一識別符號。
lock_mode 鎖的模式，如 S（共享鎖）或 X（獨佔鎖）等。
lock_type 鎖的型別，如 RECORD（行級鎖）或 TABLE（表級鎖）等。
lock_table 被鎖定的表名。
lock_index 被鎖定的索引名。
lock_space 被鎖定的表空間的識別符號。
lock_page 被鎖定的頁碼。
lock_rec 鎖定的記錄。
lock_data 與鎖相關的其他資料。
檢視 InnoDB 儲存引擎中當前存在的鎖等待情況
SELECT * FROM information_schema.innodb_lock_waits;
requesting_trx_id 請求鎖的事務的唯一識別符號。
requested_lock_id 請求的鎖的唯一識別符號。
blocking_trx_id 導致鎖等待的阻塞事務的唯一識別符號。
blocking_lock_id 導致鎖等待的鎖的唯一識別符號。