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

【1】按鎖的粒度分類

全局鎖

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

示例：

FLUSH TABLES WITH READ LOCK;
-- 進行備份操作
UNLOCK TABLES;

表級鎖

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

特點

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

加鎖情況

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

示例：

-- 會話 1
LOCK TABLES users READ;
SELECT * FROM users;
-- 若嘗試寫入，會報錯
-- UPDATE users SET name = 'new_name' WHERE id = 1; 
UNLOCK TABLES;

-- 會話 2
SELECT * FROM users; -- 可以正常讀取

2、表獨占寫鎖（Table Write Lock）

特點

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

加鎖情況

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

示例：

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

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

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

特點：

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

加鎖情況：

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

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

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

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

3、意向鎖（Intention Lock）

加鎖方式：

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

特點：

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

示例

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

行級鎖

1、記錄鎖（Record Lock）

（1）定義

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

（2）加鎖情況

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

SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

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

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

2、間隙鎖（Gap Lock）

（1）定義

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

（2）加鎖情況

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

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

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

3、臨鍵鎖（Next-Key Lock）

（1）定義

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

（2）加鎖情況

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

SELECT * FROM users WHERE id > 10 FOR UPDATE;

這個語句會對 id 大于 10 的行記錄及其前面的間隙加臨鍵鎖。

【2】按鎖的模式分類

共享鎖（S 鎖）

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

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

排他鎖（X 鎖）

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

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

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

不同的隔離級別對鎖的使用和加鎖范圍有影響。

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

（2）查詢語句

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

（3）事務(wù)操作

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

【三】Mysql的死鎖情況

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

情況描述

假設(shè)有兩個事務(wù) T1 和 T2，以及一個表 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 先對 id = 1 的行加排他鎖，然后嘗試對 id = 2 的行加排他鎖；

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

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

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

情況描述

有一個表 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 上有索引，更新操作會對索引記錄和間隙加鎖。T1 和 T2 按照不同的順序?qū)?product_id 進行更新，導(dǎo)致鎖的獲取順序不一致，從而可能形成死鎖。

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

情況描述

在可重復(fù)讀隔離級別下，兩個事務(wù)同時向一張有唯一索引的表中插入數(shù)據(jù)，且插入的數(shù)據(jù)在唯一索引列上有沖突。InnoDB 為了保證數(shù)據(jù)的一致性，會使用間隙鎖，這可能導(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');

解決方案

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

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

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

情況描述

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

示例代碼：

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

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

解決方案

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

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

情況描述

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

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

背景信息

MySQL版本：Percona MySQL Server 5.7.19

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

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

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

mysql鎖基本概念

• S：共享鎖（行級鎖）
• X：排他鎖（行級鎖）
• IS：意向共享鎖（表級鎖），使用行級鎖時會自動添加相應(yīng)的意向鎖
• IX：意向排他鎖（表級鎖），使用行級鎖時會自動添加相應(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）開啟兩個事務(wù)

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

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

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

---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

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

---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

當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

當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)

案例擴展

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

另外一種情況，多個線程間，不會執(zhí)行到相同order_id的情況，也可能發(fā)生死鎖。比如一個線程order_id=15，另外一個線程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-修改隔離級別為提交讀（RC）

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

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

情況描述

數(shù)據(jù)庫是Mysql 5.7，引擎是InnoDB，事務(wù)隔離級別是讀提交（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當前持有的鎖均為： lock_mode X locks rec but not gap ，兩個事務(wù)對記錄加的都是X 鎖，No Gap鎖，即對當行記錄加鎖，并未加間隙鎖。

死鎖原因

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

翻看代碼

@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());
}

該代碼的目的是先后修改同一條記錄的兩個不同字段，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'

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

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

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

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

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

因為fund_transfer_order_no只是普通索引，而非唯一性索引。又因為在一種特殊情況下，會有同一個用戶的兩個fund_transfer_order_no的前20位相同，這就導(dǎo)致兩條不同的記錄的索引值一樣（因為seller_id 和fund_transfer_order_no(20)都相同 ）。

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

原因匯總

在MySQL中，行級鎖并不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引；如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關(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)的鎖失?。ㄋ梨i）；

解決方案

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

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

所有update都通過主鍵ID進行。

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

【四】排查線上死鎖問題

查看死鎖日志

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

使用 SHOW ENGINE INNODB STATUS 命令

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

SHOW ENGINE INNODB STATUS;

開啟 innodb_print_all_deadlocks 參數(shù)

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

SET GLOBAL innodb_print_all_deadlocks = ON;

【五】解決死鎖問題

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

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

-- 事務(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ù)持有鎖的時間

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

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

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

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

4、優(yōu)化索引

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

總結(jié)

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

最新評論