目录
- 【一】mysql中锁分类和加锁情况
- 【1】按锁的粒度分类
- 全局锁
- 表级锁
- 行级锁
- 【2】按锁的模式分类
- 【二】加锁方式的影响因素
- 【三】Mysql的死锁情况
- 【1】事务交叉更新导致死锁
- 【2】索引使用不当导致死锁
- 【3】并发插入导致的死锁
- 【4】外键约束引发的死锁
- 【5】⭐️删除不存在的数据导致间隙锁
- 【6】同一个事务中多条update修改同一条记录
- 【四】排查线上死锁问题
- 【五】解决死锁问题
- 总结
【一】Mysql中锁分类和加锁情况
【1】按锁的粒度分类
全局锁
加锁情况：使用 FLUSH TABLES WITH READ LOCK 语句，它会对整个数据库实例加锁，使整个数据库处于只读状态。常用于全量备份等场景，确保备份期间数据的一致性。
示例：
FLUSH TABLES WITH READ LOCK; -- 进行备份操作 UNLOCK TABLES;
表级锁
1、表共享读锁（Table Read Lock）
特点
- 1、允许多个事务同时对同一个表加共享读锁，即可以有多个事务同时读取该表的数据。
- 2、持有共享读锁的事务只能对表进行读操作，不能进行写操作。并且在持有该锁期间，不能访问其他未被锁定的表。
- 3、其他事务也可以读取该表，但如果要对该表进行写操作，则需要等待所有共享读锁释放。
加锁情况
- 使用 LOCK TABLES table_name READ 语句，其他事务可以读取该表，但不能写入，当前持有读锁的事务也不能写入其他表。
- 常用于多个事务同时读取同一表，且不允许有写操作的场景。
示例：
-- 会话 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、同一时间只有一个事务能对表加独占写锁。
- 2、持有该锁的事务可以对表进行读写操作，在其释放锁之前，其他事务无法对该表进行任何读写操作。
加锁情况
- 使用 LOCK TABLES table_name WRITE 语句，持有该锁的事务可以对表进行读写操作，其他事务不能对该表进行读写，直到锁释放。
- 用于对表进行数据修改，需要保证数据一致性的场景。
示例：
-- 会话 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、元数据锁（MDL）
特点：
- 1、分为共享元数据锁（Shared MDL）和排他元数据锁（Exclusive MDL）。当对表进行 SELECT、INSERT、UPDATE、DELETE 等操作时，会自动加共享 MDL 锁；当对表结构进行修改（如 ALTER TABLE）时，会加排他 MDL 锁。
- 2、共享 MDL 锁之间可以共存，即多个事务可以同时对同一个表加共享 MDL 锁进行读写操作。但排他 MDL 锁与其他任何类型的 MDL 锁都互斥，也就是说，当一个事务持有排他 MDL 锁时，其他事务无法对该表进行任何操作，直到排他 MDL 锁释放。
加锁情况：
- 当对表进行 SELECT、INSERT、UPDATE、DELETE 等操作时，会自动加共享 MDL 锁；当对表结构进行修改（如 ALTER TABLE）时，会加排他 MDL 锁。
- 目的是保证在表结构修改时，不会有其他事务对表进行读写操作，避免数据不一致。
-- 会话 1 START TRANSACTION; SELECT * FROM users; -- 自动加共享 MDL 锁 -- 此时会话 2 可以进行读操作，但不能进行表结构修改 -- 会话 2 -- 可以正常读取 SELECT * FROM users; -- 若执行 ALTER TABLE 会被阻塞 -- ALTER TABLE users ADD COLUMN new_column VARCHAR(255); -- 会话 1 提交事务释放共享 MDL 锁 COMMIT;
3、意向锁（Intention Lock）
加锁方式：
- 是一种表级别的锁，在使用行级锁时会自动添加相应的意向锁。
特点：
- 1、分为意向共享锁（IS）和意向排他锁（IX）。当事务要对表中的某一行加共享锁时，会先对表加意向共享锁；当事务要对表中的某一行加排他锁时，会先对表加意向排他锁。
- 2、意向锁的作用是表明某个事务正在对表中的行进行加锁操作，这样在对表加更高级别的锁（如表级共享锁或表级排他锁）时，可以快速判断表中是否有行被锁定，从而避免全表扫描。
示例
-- 会话 1 START TRANSACTION; SELECT * FROM users WHERE id = 1 FOR UPDATE; -- 自动对表加意向排他锁 -- 会话 2 尝试对表加表级共享读锁会被阻塞 -- LOCK TABLES users READ; COMMIT;
行级锁
1、记录锁（Record Lock）
（1）定义
记录锁是对索引记录的锁定，也就是对表中某一行数据的索引项加锁。需要注意的是，记录锁总是会锁定索引记录，如果表没有设置索引，MySQL 会自动创建一个隐藏的聚簇索引来使用。
（2）加锁情况
在可重复读或串行化隔离级别下，使用 SELECT … FOR UPDATE 或 UPDATE、DELETE 等语句对满足条件的行记录加锁。例如：
SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
执行 UPDATE、DELETE 语句时，也会对操作的行记录加记录锁。示例如下：
UPDATE users SET name = 'John' WHERE id = 1; DELETE FROM users WHERE id = 1;
2、间隙锁（Gap Lock）
（1）定义
间隙锁锁定的是索引记录之间的间隙，其目的在于防止其他事务在该间隙插入新记录，从而避免幻读问题。
（2）加锁情况
在可重复读隔离级别下，当使用范围查询（如 WHERE id BETWEEN 1 AND 10）时，为了防止幻读，会对查询范围的间隙加锁。例如：
SELECT * FROM table_name WHERE id BETWEEN 1 AND 10 FOR UPDATE;
此语句不仅会对 id 在 1 到 10 之间的行记录加锁，还会对这些记录之间的间隙加锁，防止其他事务插入新的 id 在这个范围内的记录。
3、临键锁（Next-Key Lock）
（1）定义
临键锁是记录锁和间隙锁的组合，它会锁定索引记录本身以及该记录前面的间隙。
（2）加锁情况
是记录锁和间隙锁的组合，在可重复读隔离级别下，对索引记录和其前面的间隙加锁。常用于范围查询和唯一性检查，防止幻读和插入异常。
SELECT * FROM users WHERE id > 10 FOR UPDATE;
这个语句会对 id 大于 10 的行记录及其前面的间隙加临键锁。
【2】按锁的模式分类
共享锁（S 锁）
加锁情况：使用 SELECT … LOCK IN SHARE MODE 语句对读取的行记录加共享锁，多个事务可以同时对同一行记录加共享锁，但不能同时加排他锁。例如：
SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;
排他锁（X 锁）
加锁情况：使用 SELECT … FOR UPDATE、UPDATE、DELETE 等语句对操作的行记录加排他锁，一旦某事务对行记录加了排他锁，其他事务既不能读取也不能修改该行记录，直到排他锁被释放。
【二】加锁方式的影响因素
（1）隔离级别（Innodb默认：可重复读-REPEATABLE READ）
不同的隔离级别对锁的使用和加锁范围有影响。
例如，可重复读隔离级别会使用间隙锁和临键锁来防止幻读，而读提交隔离级别则不会。
（2）查询语句
查询条件、索引使用情况等会影响加锁的范围和粒度。如果使用索引进行精确匹配，可能只对匹配的行记录加锁；如果是范围查询，可能会加间隙锁或临键锁。
（3）事务操作
不同的事务操作（如 SELECT、INSERT、UPDATE、DELETE）会触发不同类型的锁。例如，INSERT 操作可能会对插入位置的间隙加锁，UPDATE 和 DELETE 操作会对操作的行记录加排他锁。
【三】Mysql的死锁情况
【1】事务交叉更新导致死锁
情况描述
假设有两个事务 T1 和 T2，以及一个表 accounts 包含 id 和 balance 两列。
-- 事务 T1 START TRANSACTION; UPDATE accounts SET balance = balance + 100 WHERE id = 1; UPDATE accounts SET balance = balance - 100 WHERE id = 2; COMMIT; -- 事务 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 等待 Tjs2 释放 id = 2 的锁，而 T2 等待 T1 释放 id = 1 的锁，从而形成死锁。
【2】索引使用不当导致死锁
情况描述
有一个表 orders 包含 order_id 和 product_id 两列，product_id 上有索引。
-- 事务 T1 START TRANSACTION; UPDATE orders SET status = 'paid' WHERE product_id = 1; UPDATE orders SET status = 'shipped' WHERE product_id = 2; COMMIT; -- 事务 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 按照不同的顺序对 product_id 进行更新，导致锁的获取顺序不一致，从而可能形成死锁。
【3】并发插入导致的死锁
情况描述
在可重复读隔离级别下，两个事务同时向一张有唯一索引的表中插入数据，且插入的数据在唯一索引列上有冲突。InnoDB 为了保证数据的一致性，会使用间隙锁，这可能导致死锁。
-- 事务T1 START TRANSACTION; INSERT INTO unique_table (id, value) VALUES (1, 'value1'); -- 事务T2 START TRANSACTION; INSERT INTO unique_table (id, value) VALUES (1, 'value2');
解决方案
可以考虑将隔离级别调整为读提交，但需要注意这可能会导致幻读问题。
或者在插入数据前，先进行唯一性检查，避免插入冲突的数据。
【4】外键约束引发的死锁
情况描述
有两张表，主表 A 和从表 B，从表 B 有外键关联到主表 A。当两个事务分别对主表和从表进行插入和删除操作时，由于外键约束的检查，可能会导致死锁。
示例代码：
-- 事务T1 START TRANSACTION; INSERT INTO tableA (id, name) VALUES (1, 'name1'); -- 假设这里有一些耗时的操作 DELETE FROM tableB WHERE id = 1; -- 事务T2 START TRANSACTION; INSERT INTO tableB (id, a_id, value) VALUES (1, 1, 'value1'); -- 假设这里有一些耗时的操作 DELETE FROM tableA WHERE id = 1;
解决方案
确保在进行涉及外键关系的操作时，按照主表和从表的正确顺序进行操作，或者使用级联操作来简化事务中的操作，减少锁的竞争。
【5】⭐️删除不存在的数据导致间隙锁
情况描述
⭐️先delete，再insert，导致死锁
实例的日志记录表，实例在重跑的时候，会先根据instanceId去delete该实例关联的全部旧的记录信息，然后再陆续插入新的记录信息，instanceId有索引，出现锁超时的情况。在删除的时候根据实例id删除，但是记录可能不存在，如果删除的记录在数据库中存在，那么产生的就是普通的行锁；当删除的这条记录不存在，会在删除记录所在的区间加间隙锁。
背景信息
MySQL版本：Percona MySQL Server 5.7.19
隔离级别：可重复读（RR）
业务逻辑：并发下按某个索引字段先delete记录，再insert记录
begin; delete from tb where order_id = xxx; insert into tb(order_id) values(xxx); commit;
mysql锁基本概念
- S：共享锁（行级锁）
- X：排他锁（行级锁）
- IS：意向共享锁（表级锁），使用行级锁时会自动添加相应的意向锁
- IX：意向排他锁（表级锁），使用行级锁时会自动添加相应的意向锁
锁模式兼容性表
- gap锁与gap锁之间不冲突
- rec insert intention(插入意向锁）与gap锁冲突。
死锁原因
打开参数，从innodb status获取更多的锁信息。
set GLOBAL innodb_status_output_locks=ON;
表结构：
CREATE TABLE `tb` ( `order_id` int(11) DEFAULT NULL, KEY `idx_order_id` (`order_id`) ) ENGINE=InnoDB DEFAULT javascriptCHARSET=utf8
表中数据：
mysql> select * from tb; +----------+ | order_id | +----------+ | 10 | | 20 | +----------+ 2 rows in set (0.00 sec)
事务执行步骤：
（1）开启两个事务
（2）两个事务分别删除两个个不存在的记录
（3）两个事务分别插入该记录
当session1执行delete from tb where order_id=15;，由于条件order_id=15的记录不存在，session1 获得2个锁结构，分别是意向排他锁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执行delete from tb where order_id=15;，同样由于order_id=15的记录不存在，session2 也获得2个锁结构，分别是意向排他锁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执行insert into tb select 15;， session2 已经获取到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执行insert into tb select 15;，session1 已获取到IX锁，gap锁， 等待rec insert intention（插入意向锁）， session1, session2 都在等待插入意向锁， 插入意向锁与gap锁冲突，双方都没有释放gap锁，又都在等待插入意向锁，死锁发生。
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)
案例扩展
以上死锁案例，业务代码逻辑是多线程并发下，有可能多个线程会执行相同order_id的job，比如两个线程执行的order_id 都是15。
另外一种情况，多个线程间，不会执行到相同ordejsr_id的情况，也可能发生死锁。比如一个线程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-修改业务代码逻辑，删除记录之前，先select，确认该记录存在，再执行delete删除该记录。
【6】同一个事务中多条update修改同一条记录
情况描述
数据库是Mysql 5.7，引擎是InnoDB，事务隔离级别是读提交（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 updatingupdate `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 02019-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), u编程客栈ndo log entries 1MySQL thread id 5877341, OS thread handle 47362313119488, query id 557970189 11.131.81.107 fin_instant_app updatingupdate `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 02019-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 02019-03-19T21:44:23.519297+08:00 5877341 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)
定位导致死锁的两条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）事务1，持有索引idx_seller_transNo的锁，在等待获取PRIMARY的锁。
（2）事务2，持有PRIMARY的锁，在等待获取idx_seller_transNo的锁。
（3）因事务1和事务2之间发生循环等待，故发生死锁。
事务1和事务2当前持有的锁均为： lock_mode X locks rec but not gap ，两个事务对记录加的都是X 锁，No Gap锁，即对当行记录加锁，并未加间隙锁。
死锁原因
首先，此次死锁一定是和Gap锁以及Next-Key Lock没有关系的。因为我们的数据库隔离级别是读提交（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'
可以看到，我们的同一个事务中执行了两条Update语句，这里分别查看下两条SQL的执行计划：
updateFundStreamId执行的时候使用到的是PRIMARY索引。
updateStatus执行的时候使用到的是idx_seller_transNo索引。
主要问题出在我们的idx_seller_transNo索引上面
索引创建语句中，我们使用了前缀索引，为了节约索引空间，提高索引效率，我们只选择了fund_transfer_order_no字段的前20位作为索引值。
因为fund_transfer_order_no只是普通索引，而非唯一性索引。又因为在一种特殊情况下，会有同一个用户的两个fund_transfer_order_no的前20位相同，这就导致两条不同的记录的索引值一样（因为seller_id 和fund_transfer_order_no(20)都相同 ）。
就如本文中的例子，发生死锁的两条记录的fund_transfer_order_no字段的值：99010015000805619031958363857和99010015000805619031957477256这两个就是前20位相同的。
原因汇总
在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。
（1）事务1执行update1占用PRIMARY = 1的锁
（2）事务2执行update1 占有PRIMARY = 2的锁；
（3）事务1执行update2占有idx_seller_transNo = (3111095611，99010015000805619031)的锁，尝试占有PRIMARY = 2锁失败（阻塞）javascript；
（4）事务2执行update2尝试占有idx_seller_transNo = (3111095611，99010015000805619031)的锁失败（死锁）；
解决方案
（1）修改索引：只要我们把前缀索引 idx_seller_transNo中fund_transfer_order_no的前缀长度修改下就可以了。比如改成50。即可避免死锁。
（2）解决办法就是改代码
所有update都通过主键ID进行。
在同一个事务中，避免出现多条update语句修改同一条记录。
【四】排查线上死锁问题
查看死锁日志
MySQL 会将死锁信息记录在错误日志中，可以通过查看错误日志找到死锁的详细信息，包括死锁发生的时间、涉及的事务和 SQL 语句等。
使用 SHOW ENGINE INNODB STATUS 命令
该命令可以显示 InnoDB 存储引擎的状态信息，其中包含最近一次死锁的详细信息，如死锁的事务 ID、持有和等待的锁等。
SHOW ENGINE INNODB STATUS;
开启 innodb_print_all_deadlocks 参数
将该参数设置为 ON，可以让 MySQL 记录所有的死锁信息到错误日志中，方便后续分析。
SET GLOBAL innodb_print_all_deadlocks = ON;
【五】解决死锁问题
1、优化事务逻辑
确保事务按照相同的顺序访问资源，避免交叉更新。例如，将上述事务 T1 和 T2 都按照 id 从小到大的顺序进行更新：
-- 事务 T1 START TRANSACTION; UPDATE accounts SET balance = balance + 100 WHERE id = 1; UPDATE accounts SET balance = balance - 100 WHERE id = 2; COMMIT; -- 事务 T2 START TRANSACTION; UPDATE accounts SET balance = balance + 100 WHERE id = 1; UPDATE accounts SET balance = balance - 100 WHERE id = 2; COMMIT;
2、减少事务持有锁的时间
尽量缩短事务的执行时间，减少锁的持有时间，降低死锁的概率。例如，将大事务拆分成多个小事务。
3、调整隔离级别
如果业务允许，可以将隔离级别从可重复读调整为读提交，减少间隙锁和临键锁的使用，降低死锁的可能性。
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
4、优化索引
确保 SQL 语句使用合适的索引，避免全表扫描和范围扫描，减少锁的范围和粒度。例如，为经常用于查询和更新的列添加索引。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。
加载中,请稍侯......
精彩评论