【一】Mysql中鎖分類和加鎖情況

【1】按鎖的粒度分類

全局鎖

加鎖情況：使用 FLUSH TABLES WITH READ LOCK 語句，它會(huì)對(duì)整個(gè)數(shù)據(jù)庫實(shí)例加鎖，使整個(gè)數(shù)據(jù)庫處于只讀狀態(tài)。常用于全量備份等場(chǎng)景，確保備份期間數(shù)據(jù)的一致性。

示例：

FLUSH TABLES WITH READ LOCK;
-- 進(jìn)行備份操作
UNLOCK TABLES;

表級(jí)鎖

1、表共享讀鎖（Table Read Lock）

特點(diǎn)

• 1、允許多個(gè)事務(wù)同時(shí)對(duì)同一個(gè)表加共享讀鎖，即可以有多個(gè)事務(wù)同時(shí)讀取該表的數(shù)據(jù)。
• 2、持有共享讀鎖的事務(wù)只能對(duì)表進(jìn)行讀操作，不能進(jìn)行寫操作。并且在持有該鎖期間，不能訪問其他未被鎖定的表。
• 3、其他事務(wù)也可以讀取該表，但如果要對(duì)該表進(jìn)行寫操作，則需要等待所有共享讀鎖釋放。

加鎖情況

• 使用 LOCK TABLES table_name READ 語句，其他事務(wù)可以讀取該表，但不能寫入，當(dāng)前持有讀鎖的事務(wù)也不能寫入其他表。
• 常用于多個(gè)事務(wù)同時(shí)讀取同一表，且不允許有寫操作的場(chǎng)景。

示例：

-- 會(huì)話 1
LOCK TABLES users READ;
SELECT * FROM users;
-- 若嘗試寫入，會(huì)報(bào)錯(cuò)
-- UPDATE users SET name = 'new_name' WHERE id = 1; 
UNLOCK TABLES;

-- 會(huì)話 2
SELECT * FROM users; -- 可以正常讀取

2、表獨(dú)占寫鎖（Table Write Lock）

特點(diǎn)

• 1、同一時(shí)間只有一個(gè)事務(wù)能對(duì)表加獨(dú)占寫鎖。
• 2、持有該鎖的事務(wù)可以對(duì)表進(jìn)行讀寫操作，在其釋放鎖之前，其他事務(wù)無法對(duì)該表進(jìn)行任何讀寫操作。

加鎖情況

• 使用 LOCK TABLES table_name WRITE 語句，持有該鎖的事務(wù)可以對(duì)表進(jìn)行讀寫操作，其他事務(wù)不能對(duì)該表進(jìn)行讀寫，直到鎖釋放。
• 用于對(duì)表進(jìn)行數(shù)據(jù)修改，需要保證數(shù)據(jù)一致性的場(chǎng)景。

示例：

-- 會(huì)話 1
LOCK TABLES users WRITE;
SELECT * FROM users;
UPDATE users SET name = 'new_name' WHERE id = 1;
UNLOCK TABLES;

-- 會(huì)話 2
-- 若在會(huì)話 1 持有寫鎖期間嘗試讀寫，會(huì)被阻塞
SELECT * FROM users; 

3、元數(shù)據(jù)鎖（MDL）

特點(diǎn)：

• 1、分為共享元數(shù)據(jù)鎖（Shared MDL）和排他元數(shù)據(jù)鎖（Exclusive MDL）。當(dāng)對(duì)表進(jìn)行 SELECT、INSERT、UPDATE、DELETE 等操作時(shí)，會(huì)自動(dòng)加共享 MDL 鎖；當(dāng)對(duì)表結(jié)構(gòu)進(jìn)行修改（如 ALTER TABLE）時(shí)，會(huì)加排他 MDL 鎖。
• 2、共享 MDL 鎖之間可以共存，即多個(gè)事務(wù)可以同時(shí)對(duì)同一個(gè)表加共享 MDL 鎖進(jìn)行讀寫操作。但排他 MDL 鎖與其他任何類型的 MDL 鎖都互斥，也就是說，當(dāng)一個(gè)事務(wù)持有排他 MDL 鎖時(shí)，其他事務(wù)無法對(duì)該表進(jìn)行任何操作，直到排他 MDL 鎖釋放。

加鎖情況：

• 當(dāng)對(duì)表進(jìn)行 SELECT、INSERT、UPDATE、DELETE 等操作時(shí)，會(huì)自動(dòng)加共享 MDL 鎖；當(dāng)對(duì)表結(jié)構(gòu)進(jìn)行修改（如 ALTER TABLE）時(shí)，會(huì)加排他 MDL 鎖。
• 目的是保證在表結(jié)構(gòu)修改時(shí)，不會(huì)有其他事務(wù)對(duì)表進(jìn)行讀寫操作，避免數(shù)據(jù)不一致。

-- 會(huì)話 1
START TRANSACTION;
SELECT * FROM users; -- 自動(dòng)加共享 MDL 鎖
-- 此時(shí)會(huì)話 2 可以進(jìn)行讀操作，但不能進(jìn)行表結(jié)構(gòu)修改

-- 會(huì)話 2
-- 可以正常讀取
SELECT * FROM users; 
-- 若執(zhí)行 ALTER TABLE 會(huì)被阻塞
-- ALTER TABLE users ADD COLUMN new_column VARCHAR(255); 

-- 會(huì)話 1 提交事務(wù)釋放共享 MDL 鎖
COMMIT;

3、意向鎖（Intention Lock）

加鎖方式：

• 是一種表級(jí)別的鎖，在使用行級(jí)鎖時(shí)會(huì)自動(dòng)添加相應(yīng)的意向鎖。

特點(diǎn)：

• 1、分為意向共享鎖（IS）和意向排他鎖（IX）。當(dāng)事務(wù)要對(duì)表中的某一行加共享鎖時(shí)，會(huì)先對(duì)表加意向共享鎖；當(dāng)事務(wù)要對(duì)表中的某一行加排他鎖時(shí)，會(huì)先對(duì)表加意向排他鎖。
• 2、意向鎖的作用是表明某個(gè)事務(wù)正在對(duì)表中的行進(jìn)行加鎖操作，這樣在對(duì)表加更高級(jí)別的鎖（如表級(jí)共享鎖或表級(jí)排他鎖）時(shí)，可以快速判斷表中是否有行被鎖定，從而避免全表掃描。

示例

-- 會(huì)話 1
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 自動(dòng)對(duì)表加意向排他鎖
-- 會(huì)話 2 嘗試對(duì)表加表級(jí)共享讀鎖會(huì)被阻塞
-- LOCK TABLES users READ; 
COMMIT;

行級(jí)鎖

1、記錄鎖（Record Lock）

（1）定義

記錄鎖是對(duì)索引記錄的鎖定，也就是對(duì)表中某一行數(shù)據(jù)的索引項(xiàng)加鎖。需要注意的是，記錄鎖總是會(huì)鎖定索引記錄，如果表沒有設(shè)置索引，MySQL 會(huì)自動(dòng)創(chuàng)建一個(gè)隱藏的聚簇索引來使用。

（2）加鎖情況

在可重復(fù)讀或串行化隔離級(jí)別下，使用 SELECT … FOR UPDATE 或 UPDATE、DELETE 等語句對(duì)滿足條件的行記錄加鎖。例如：

SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

執(zhí)行 UPDATE、DELETE 語句時(shí)，也會(huì)對(duì)操作的行記錄加記錄鎖。示例如下：

UPDATE users SET name = 'John' WHERE id = 1;
DELETE FROM users WHERE id = 1;

2、間隙鎖（Gap Lock）

（1）定義

間隙鎖鎖定的是索引記錄之間的間隙，其目的在于防止其他事務(wù)在該間隙插入新記錄，從而避免幻讀問題。

（2）加鎖情況

在可重復(fù)讀隔離級(jí)別下，當(dāng)使用范圍查詢（如 WHERE id BETWEEN 1 AND 10）時(shí)，為了防止幻讀，會(huì)對(duì)查詢范圍的間隙加鎖。例如：

SELECT * FROM table_name WHERE id BETWEEN 1 AND 10 FOR UPDATE;

此語句不僅會(huì)對(duì) id 在 1 到 10 之間的行記錄加鎖，還會(huì)對(duì)這些記錄之間的間隙加鎖，防止其他事務(wù)插入新的 id 在這個(gè)范圍內(nèi)的記錄。

3、臨鍵鎖（Next-Key Lock）

（1）定義

臨鍵鎖是記錄鎖和間隙鎖的組合，它會(huì)鎖定索引記錄本身以及該記錄前面的間隙。

（2）加鎖情況

是記錄鎖和間隙鎖的組合，在可重復(fù)讀隔離級(jí)別下，對(duì)索引記錄和其前面的間隙加鎖。常用于范圍查詢和唯一性檢查，防止幻讀和插入異常。

SELECT * FROM users WHERE id > 10 FOR UPDATE;

這個(gè)語句會(huì)對(duì) id 大于 10 的行記錄及其前面的間隙加臨鍵鎖。

【2】按鎖的模式分類

共享鎖（S 鎖）

加鎖情況：使用 SELECT … LOCK IN SHARE MODE 語句對(duì)讀取的行記錄加共享鎖，多個(gè)事務(wù)可以同時(shí)對(duì)同一行記錄加共享鎖，但不能同時(shí)加排他鎖。例如：

SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;

排他鎖（X 鎖）

加鎖情況：使用 SELECT … FOR UPDATE、UPDATE、DELETE 等語句對(duì)操作的行記錄加排他鎖，一旦某事務(wù)對(duì)行記錄加了排他鎖，其他事務(wù)既不能讀取也不能修改該行記錄，直到排他鎖被釋放。

【二】加鎖方式的影響因素

（1）隔離級(jí)別（Innodb默認(rèn)：可重復(fù)讀-REPEATABLE READ）

不同的隔離級(jí)別對(duì)鎖的使用和加鎖范圍有影響。

例如，可重復(fù)讀隔離級(jí)別會(huì)使用間隙鎖和臨鍵鎖來防止幻讀，而讀提交隔離級(jí)別則不會(huì)。

（2）查詢語句

查詢條件、索引使用情況等會(huì)影響加鎖的范圍和粒度。如果使用索引進(jìn)行精確匹配，可能只對(duì)匹配的行記錄加鎖；如果是范圍查詢，可能會(huì)加間隙鎖或臨鍵鎖。

（3）事務(wù)操作

不同的事務(wù)操作（如 SELECT、INSERT、UPDATE、DELETE）會(huì)觸發(fā)不同類型的鎖。例如，INSERT 操作可能會(huì)對(duì)插入位置的間隙加鎖，UPDATE 和 DELETE 操作會(huì)對(duì)操作的行記錄加排他鎖。

【三】Mysql的死鎖情況

【1】事務(wù)交叉更新導(dǎo)致死鎖

情況描述

假設(shè)有兩個(gè)事務(wù) T1 和 T2，以及一個(gè)表 accounts 包含 id 和 balance 兩列。

-- 事務(wù) T1
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
COMMIT;

-- 事務(wù) T2
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

死鎖原因

（1）T1 先對(duì) id = 1 的行加排他鎖，然后嘗試對(duì) id = 2 的行加排他鎖；

（2）T2 先對(duì) id = 2 的行加排他鎖，然后嘗試對(duì) id = 1 的行加排他鎖。

（3）此時(shí)，T1 等待 T2 釋放 id = 2 的鎖，而 T2 等待 T1 釋放 id = 1 的鎖，從而形成死鎖。

【2】索引使用不當(dāng)導(dǎo)致死鎖

情況描述

有一個(gè)表 orders 包含 order_id 和 product_id 兩列，product_id 上有索引。

-- 事務(wù) T1
START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE product_id = 1;
UPDATE orders SET status = 'shipped' WHERE product_id = 2;
COMMIT;

-- 事務(wù) T2
START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE product_id = 2;
UPDATE orders SET status = 'shipped' WHERE product_id = 1;
COMMIT;

死鎖原因

由于 product_id 上有索引，更新操作會(huì)對(duì)索引記錄和間隙加鎖。T1 和 T2 按照不同的順序?qū)?product_id 進(jìn)行更新，導(dǎo)致鎖的獲取順序不一致，從而可能形成死鎖。

【3】并發(fā)插入導(dǎo)致的死鎖

情況描述

在可重復(fù)讀隔離級(jí)別下，兩個(gè)事務(wù)同時(shí)向一張有唯一索引的表中插入數(shù)據(jù)，且插入的數(shù)據(jù)在唯一索引列上有沖突。InnoDB 為了保證數(shù)據(jù)的一致性，會(huì)使用間隙鎖，這可能導(dǎo)致死鎖。

-- 事務(wù)T1
START TRANSACTION;
INSERT INTO unique_table (id, value) VALUES (1, 'value1');

-- 事務(wù)T2
START TRANSACTION;
INSERT INTO unique_table (id, value) VALUES (1, 'value2');

解決方案

可以考慮將隔離級(jí)別調(diào)整為讀提交，但需要注意這可能會(huì)導(dǎo)致幻讀問題。

或者在插入數(shù)據(jù)前，先進(jìn)行唯一性檢查，避免插入沖突的數(shù)據(jù)。

【4】外鍵約束引發(fā)的死鎖

情況描述

有兩張表，主表 A 和從表 B，從表 B 有外鍵關(guān)聯(lián)到主表 A。當(dāng)兩個(gè)事務(wù)分別對(duì)主表和從表進(jìn)行插入和刪除操作時(shí)，由于外鍵約束的檢查，可能會(huì)導(dǎo)致死鎖。

示例代碼：

-- 事務(wù)T1
START TRANSACTION;
INSERT INTO tableA (id, name) VALUES (1, 'name1');
-- 假設(shè)這里有一些耗時(shí)的操作
DELETE FROM tableB WHERE id = 1;

-- 事務(wù)T2
START TRANSACTION;
INSERT INTO tableB (id, a_id, value) VALUES (1, 1, 'value1');
-- 假設(shè)這里有一些耗時(shí)的操作
DELETE FROM tableA WHERE id = 1;

解決方案

確保在進(jìn)行涉及外鍵關(guān)系的操作時(shí)，按照主表和從表的正確順序進(jìn)行操作，或者使用級(jí)聯(lián)操作來簡化事務(wù)中的操作，減少鎖的競(jìng)爭。

【5】??刪除不存在的數(shù)據(jù)導(dǎo)致間隙鎖

情況描述

??先delete，再insert，導(dǎo)致死鎖

實(shí)例的日志記錄表，實(shí)例在重跑的時(shí)候，會(huì)先根據(jù)instanceId去delete該實(shí)例關(guān)聯(lián)的全部舊的記錄信息，然后再陸續(xù)插入新的記錄信息，instanceId有索引，出現(xiàn)鎖超時(shí)的情況。在刪除的時(shí)候根據(jù)實(shí)例id刪除，但是記錄可能不存在，如果刪除的記錄在數(shù)據(jù)庫中存在，那么產(chǎn)生的就是普通的行鎖；當(dāng)刪除的這條記錄不存在，會(huì)在刪除記錄所在的區(qū)間加間隙鎖。

背景信息

MySQL版本：Percona MySQL Server 5.7.19

隔離級(jí)別：可重復(fù)讀（RR）

業(yè)務(wù)邏輯：并發(fā)下按某個(gè)索引字段先delete記錄，再insert記錄

begin;
delete from tb where order_id = xxx;
insert into tb(order_id) values(xxx);
commit;

mysql鎖基本概念

• S：共享鎖（行級(jí)鎖）
• X：排他鎖（行級(jí)鎖）
• IS：意向共享鎖（表級(jí)鎖），使用行級(jí)鎖時(shí)會(huì)自動(dòng)添加相應(yīng)的意向鎖
• IX：意向排他鎖（表級(jí)鎖），使用行級(jí)鎖時(shí)會(huì)自動(dòng)添加相應(yīng)的意向鎖

鎖模式兼容性表

• gap鎖與gap鎖之間不沖突
• rec insert intention(插入意向鎖）與gap鎖沖突。

死鎖原因

打開參數(shù)，從innodb status獲取更多的鎖信息。

set GLOBAL innodb_status_output_locks=ON;

表結(jié)構(gòu)：

 CREATE TABLE `tb` (
  `order_id` int(11) DEFAULT NULL,
  KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中數(shù)據(jù)：

mysql> select * from tb;
+----------+
| order_id |
+----------+
|       10 |
|       20 |
+----------+
2 rows in set (0.00 sec)

事務(wù)執(zhí)行步驟：

（1）開啟兩個(gè)事務(wù)

（2）兩個(gè)事務(wù)分別刪除兩個(gè)個(gè)不存在的記錄

（3）兩個(gè)事務(wù)分別插入該記錄

當(dāng)session1執(zhí)行delete from tb where order_id=15;，由于條件order_id=15的記錄不存在，session1 獲得2個(gè)鎖結(jié)構(gòu)，分別是意向排他鎖IX（表級(jí)鎖）、gap鎖（行級(jí)鎖），如下：

---TRANSACTION 1055191443, ACTIVE 20 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 315642, OS thread handle 139960342456064, query id 150462030 localhost root
TABLE LOCK table `db`.`tb` trx id 1055191443 lock mode IX
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec

當(dāng)session2執(zhí)行delete from tb where order_id=15;，同樣由于order_id=15的記錄不存在，session2 也獲得2個(gè)鎖結(jié)構(gòu)，分別是意向排他鎖IX（表級(jí)鎖）、gap鎖（行級(jí)鎖），如下：

---TRANSACTION 1055191444, ACTIVE 3 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 315336, OS thread handle 139960562685696, query id 150462412 localhost root
TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec

當(dāng)session2執(zhí)行insert into tb select 15;， session2 已經(jīng)獲取到IX鎖，gap鎖，等待 rec insert intention（插入意向鎖）

---TRANSACTION 1055191444, ACTIVE 68 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 315336, OS thread handle 139960562685696, query id 150462778 localhost root executing
insert into tb select 15
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting
------------------
TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting

當(dāng)session1執(zhí)行insert into tb select 15;，session1 已獲取到IX鎖，gap鎖， 等待rec insert intention（插入意向鎖）， session1, session2 都在等待插入意向鎖， 插入意向鎖與gap鎖沖突，雙方都沒有釋放gap鎖，又都在等待插入意向鎖，死鎖發(fā)生。

LATEST DETECTED DEADLOCK
------------------------
2018-11-03 17:15:11 0x7f4b0e7ea700
*** (1) TRANSACTION:
TRANSACTION 1055191444, ACTIVE 135 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 315336, OS thread handle 139960562685696, query id 150462778 localhost root executing
insert into tb select 15
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 1055191443, ACTIVE 201 sec inserting, thread declared inside InnoDB 5000
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 315642, OS thread handle 139960342456064, query id 150463172 localhost root executing
insert into tb select 15
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

案例擴(kuò)展

以上死鎖案例，業(yè)務(wù)代碼邏輯是多線程并發(fā)下，有可能多個(gè)線程會(huì)執(zhí)行相同order_id的job，比如兩個(gè)線程執(zhí)行的order_id 都是15。

另外一種情況，多個(gè)線程間，不會(huì)執(zhí)行到相同order_id的情況，也可能發(fā)生死鎖。比如一個(gè)線程order_id=15，另外一個(gè)線程order_id=16，如下所示：

鎖情況與上述相同，不再贅述，死鎖信息如下：

LATEST DETECTED DEADLOCK
------------------------
2018-11-03 17:28:30 0x7f4b0e667700
*** (1) TRANSACTION:
TRANSACTION 1055191450, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 316221, OS thread handle 139960338228992, query id 150467652 localhost root executing
insert into tb select 16
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191450 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 1055191449, ACTIVE 28 sec inserting, thread declared inside InnoDB 5000
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 316222, OS thread handle 139960340870912, query id 150467681 localhost root executing
insert into tb select 15
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)
 

解決方案

1-修改隔離級(jí)別為提交讀（RC）

2-修改業(yè)務(wù)代碼邏輯，刪除記錄之前，先select，確認(rèn)該記錄存在，再執(zhí)行delete刪除該記錄。

【6】同一個(gè)事務(wù)中多條update修改同一條記錄

情況描述

數(shù)據(jù)庫是Mysql 5.7，引擎是InnoDB，事務(wù)隔離級(jí)別是讀提交（READ-COMMITED）。

死鎖日志

Transactions deadlock detected, dumping detailed information.2019-03-19T21:44:23.516263+08:00 5877341 [Note] InnoDB: 
*** (1) TRANSACTION:TRANSACTION 173268495, ACTIVE 0 sec fetching rowsmysql tables in use 1, locked 1LOCK WAIT 304 lock struct(s), heap size 41168, 6 row lock(s), undo log entries 1MySQL thread id 5877358, OS thread handle 47356539049728, query id 557970181 11.183.244.150 fin_instant_app updating
update `fund_transfer_stream` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'))2019-03-19T21:44:23.516321+08:00 5877341 [Note] InnoDB: 
*** (1) HOLDS THE LOCK(S):RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gapRecord lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
2019-03-19T21:44:23.516565+08:00 5877341 [Note] InnoDB: 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap waitingRecord lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 02019-03-19T21:44:23.517793+08:00 5877341 [Note] InnoDB: 
*** (2) TRANSACTION:TRANSACTION 173268500, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 81mysql tables in use 1, locked 1302 lock struct(s), heap size 41168, 2 row lock(s), undo log entries 1MySQL thread id 5877341, OS thread handle 47362313119488, query id 557970189 11.131.81.107 fin_instant_app updating
update `fund_transfer_stream_0056` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'))2019-03-19T21:44:23.517855+08:00 5877341 [Note] InnoDB: 
*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gapRecord lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
2019-03-19T21:44:23.519053+08:00 5877341 [Note] InnoDB: 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap waitingRecord lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
2019-03-19T21:44:23.519297+08:00 5877341 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

定位導(dǎo)致死鎖的兩條sql

update `fund_transfer_stream_0056` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'))

update `fund_transfer_stream_0056` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'))

索引情況如下

KEY `idx_seller` (`seller_id`),
KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))

（1）事務(wù)1，持有索引idx_seller_transNo的鎖，在等待獲取PRIMARY的鎖。

（2）事務(wù)2，持有PRIMARY的鎖，在等待獲取idx_seller_transNo的鎖。

（3）因事務(wù)1和事務(wù)2之間發(fā)生循環(huán)等待，故發(fā)生死鎖。

事務(wù)1和事務(wù)2當(dāng)前持有的鎖均為： lock_mode X locks rec but not gap ，兩個(gè)事務(wù)對(duì)記錄加的都是X 鎖，No Gap鎖，即對(duì)當(dāng)行記錄加鎖，并未加間隙鎖。

死鎖原因

首先，此次死鎖一定是和Gap鎖以及Next-Key Lock沒有關(guān)系的。因?yàn)槲覀兊臄?shù)據(jù)庫隔離級(jí)別是讀提交（READ-COMMITED）的，這種隔離級(jí)別是不會(huì)添加Gap鎖的，gap鎖只有在讀未提交會(huì)用。前面的死鎖日志也提到這一點(diǎn)。

翻看代碼

@Transactional(rollbackFor = Exception.class)public int doProcessing(String sellerId, Long id, String fundTransferOrderNo) {    
    fundTreansferStreamDAO.updateFundStreamId(sellerId, id, fundTransferOrderNo);
    return fundTreansferStreamDAO.updateStatus(sellerId, fundTransferOrderNo, FundTransferStreamState.PROCESSING.name());
}

該代碼的目的是先后修改同一條記錄的兩個(gè)不同字段，updateFundStreamId SQL：

update fund_transfer_stream        set gmt_modified=now(),fund_transfer_order_no = #{fundTransferOrderNo}        where id = #{id} and seller_id = #{sellerId}
update fund_transfer_stream    set gmt_modified=now(),state = #{state}    where fund_transfer_order_no = #{fundTransferOrderNo} and seller_id = #{sellerId}    and state = 'NEW'

可以看到，我們的同一個(gè)事務(wù)中執(zhí)行了兩條Update語句，這里分別查看下兩條SQL的執(zhí)行計(jì)劃：

updateFundStreamId執(zhí)行的時(shí)候使用到的是PRIMARY索引。

updateStatus執(zhí)行的時(shí)候使用到的是idx_seller_transNo索引。

主要問題出在我們的idx_seller_transNo索引上面

索引創(chuàng)建語句中，我們使用了前綴索引，為了節(jié)約索引空間，提高索引效率，我們只選擇了fund_transfer_order_no字段的前20位作為索引值。

因?yàn)閒und_transfer_order_no只是普通索引，而非唯一性索引。又因?yàn)樵谝环N特殊情況下，會(huì)有同一個(gè)用戶的兩個(gè)fund_transfer_order_no的前20位相同，這就導(dǎo)致兩條不同的記錄的索引值一樣（因?yàn)閟eller_id 和fund_transfer_order_no(20)都相同 ）。

就如本文中的例子，發(fā)生死鎖的兩條記錄的fund_transfer_order_no字段的值：99010015000805619031958363857和99010015000805619031957477256這兩個(gè)就是前20位相同的。

原因匯總

在MySQL中，行級(jí)鎖并不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會(huì)鎖定這條主鍵索引；如果一條語句操作了非主鍵索引，MySQL會(huì)先鎖定該非主鍵索引，再鎖定相關(guān)的主鍵索引。

（1）事務(wù)1執(zhí)行update1占用PRIMARY = 1的鎖

（2）事務(wù)2執(zhí)行update1 占有PRIMARY = 2的鎖；

（3）事務(wù)1執(zhí)行update2占有idx_seller_transNo = (3111095611，99010015000805619031)的鎖，嘗試占有PRIMARY = 2鎖失?。ㄗ枞?；

（4）事務(wù)2執(zhí)行update2嘗試占有idx_seller_transNo = (3111095611，99010015000805619031)的鎖失敗（死鎖）；

解決方案

（1）修改索引：只要我們把前綴索引 idx_seller_transNo中fund_transfer_order_no的前綴長度修改下就可以了。比如改成50。即可避免死鎖。

（2）解決辦法就是改代碼

所有update都通過主鍵ID進(jìn)行。

在同一個(gè)事務(wù)中，避免出現(xiàn)多條update語句修改同一條記錄。

【四】排查線上死鎖問題

查看死鎖日志

MySQL 會(huì)將死鎖信息記錄在錯(cuò)誤日志中，可以通過查看錯(cuò)誤日志找到死鎖的詳細(xì)信息，包括死鎖發(fā)生的時(shí)間、涉及的事務(wù)和 SQL 語句等。

使用 SHOW ENGINE INNODB STATUS 命令

該命令可以顯示 InnoDB 存儲(chǔ)引擎的狀態(tài)信息，其中包含最近一次死鎖的詳細(xì)信息，如死鎖的事務(wù) ID、持有和等待的鎖等。

SHOW ENGINE INNODB STATUS;

開啟 innodb_print_all_deadlocks 參數(shù)

將該參數(shù)設(shè)置為 ON，可以讓 MySQL 記錄所有的死鎖信息到錯(cuò)誤日志中，方便后續(xù)分析。

SET GLOBAL innodb_print_all_deadlocks = ON;

【五】解決死鎖問題

1、優(yōu)化事務(wù)邏輯

確保事務(wù)按照相同的順序訪問資源，避免交叉更新。例如，將上述事務(wù) T1 和 T2 都按照 id 從小到大的順序進(jìn)行更新：

-- 事務(wù) T1
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
COMMIT;


-- 事務(wù) T2
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
COMMIT;

2、減少事務(wù)持有鎖的時(shí)間

盡量縮短事務(wù)的執(zhí)行時(shí)間，減少鎖的持有時(shí)間，降低死鎖的概率。例如，將大事務(wù)拆分成多個(gè)小事務(wù)。

3、調(diào)整隔離級(jí)別

如果業(yè)務(wù)允許，可以將隔離級(jí)別從可重復(fù)讀調(diào)整為讀提交，減少間隙鎖和臨鍵鎖的使用，降低死鎖的可能性。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

4、優(yōu)化索引

確保 SQL 語句使用合適的索引，避免全表掃描和范圍掃描，減少鎖的范圍和粒度。例如，為經(jīng)常用于查詢和更新的列添加索引。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論