MySQL死锁案例InnoDB锁机制 Innodb中的锁在级别上一般分为两种，一种是共享锁（S锁），一种是排他锁（X锁）

InnoDB锁机制

Innodb中的锁在级别上一般分为两种，一种是共享锁（S锁），一种是排他锁（X锁）。按锁类型可以分为如下几种：

innobd-locking

共享锁&排他锁
意向锁
记录锁
间隙锁
临键锁
插入意向锁
自增锁
空间索引谓词锁

以下是Mysql官网给出的几种锁之间的冲突关系

数据库死锁介绍

数据库死锁问题是指在多个并发事务中，彼此之间出现了相互等待的情况，导致所有事务都无法继续执行。

常见原因

资源竞争
未释放资源
不同事务执行速度不同
操作的数量过大

如何解决

主动干预，通常选择回滚一个或多个事务
通过配置参数，让Mysql自己解决死锁

# 开启死锁检测-立刻解决
innodb_deadlock_detect = on 
# 设置事务等待锁超时时间，超过这个阈值，事务会进行回滚
innodb_lock_wait_timeout = 50 # 数值可根据业务需求调整，单位为秒

死锁案例

商品创建之后自动上架

@Transactional(rollbackFor = Exception.class)
public void createProduct() {

   // 第一版只保存商品
   saveProduct();
   // 第二版需要商品自动上架
   updateProduct();
}

CREATE TABLE product (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT,
    status VARCHAR(255)
) ENGINE=InnoDB;
-- 初始数据
INSERT product (product_id, status) VALUES (1001, "OFF");
INSERT product (product_id, status) VALUES (1002, "OFF");

并发事务执行流程

-- 会话1
START TRANSACTION;
INSERT product (id, product_id, status) VALUES (3, 1003, "OFF");
UPDATE product SET `status` = 'ON' WHERE product_id = 1003
ROLLBACK;

-- 会话2
START TRANSACTION;
INSERT product (id, product_id, status) VALUES (4, 1004, "OFF");
UPDATE product SET `status` = 'ON' WHERE product_id = 1004
ROLLBACK;

-- 查询系统持有的锁
SELECT 
   *
FROM performance_schema.data_locks 
WHERE ENGINE = 'INNODB';

-- 查询等待锁
SELECT 
    *
FROM performance_schema.data_locks 
WHERE ENGINE = 'INNODB' AND LOCK_STATUS = 'WAITING';

SELECT 
    *
FROM performance_schema.data_lock_waits;

时间点	事务 T1	事务 T2	锁状态
T1	START TRANSACTION;	START TRANSACTION;
T2	INSERT product (id, product_id, status) VALUES (3, 1003, "OFF");		T1 持有product_id=1003的记录锁
T3		INSERT product (id, product_id, status) VALUES (4, 1004, "OFF");	T2 持有product_id=1004的记录锁
T4	UPDATE product SET `status` = 'ON' WHERE product_id = 1003		product_id没有索引，要给访问到的对象加记录锁，等待1004锁释放
T5		UPDATE product SET `status` = 'ON' WHERE product_id = 1004	product_id没有索引，要给访问到的对象加记录锁，等待1003锁释放
T7			死锁形成：T1 和 T2 互相等待对方释放记录锁

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-12-01 15:47:56 0x16f933000
*** (1) TRANSACTION:
TRANSACTION 103291, ACTIVE 32 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 5 row lock(s), undo log entries 2
MySQL thread id 40, OS thread handle 6188756992, query id 3397 localhost 127.0.0.1 root updating
UPDATE product SET `status` = 'ON' WHERE product_id = 1003

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 80 index PRIMARY of table `dead_lock`.`product` trx id 103291 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 00000001935a; asc      Z;;
 2: len 7; hex 82000000ec0110; asc        ;;
 3: len 8; hex 80000000000003e9; asc         ;;
 4: len 3; hex 4f4646; asc OFF;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 00000001935b; asc      [;;
 2: len 7; hex 81000000ed0110; asc        ;;
 3: len 8; hex 80000000000003ea; asc         ;;
 4: len 3; hex 4f4646; asc OFF;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 80 index PRIMARY of table `dead_lock`.`product` trx id 103291 lock_mode X waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000004; asc         ;;
 1: len 6; hex 00000001937c; asc      |;;
 2: len 7; hex 82000001220110; asc     "  ;;
 3: len 8; hex 80000000000003ec; asc         ;;
 4: len 3; hex 4f4646; asc OFF;;


*** (2) TRANSACTION:
TRANSACTION 103292, ACTIVE 29 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 41, OS thread handle 6187642880, query id 3410 localhost 127.0.0.1 root updating
UPDATE product SET `status` = 'ON' WHERE product_id = 1004

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 80 index PRIMARY of table `dead_lock`.`product` trx id 103292 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000004; asc         ;;
 1: len 6; hex 00000001937c; asc      |;;
 2: len 7; hex 82000001220110; asc     "  ;;
 3: len 8; hex 80000000000003ec; asc         ;;
 4: len 3; hex 4f4646; asc OFF;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 80 index PRIMARY of table `dead_lock`.`product` trx id 103292 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 00000001935a; asc      Z;;
 2: len 7; hex 82000000ec0110; asc        ;;
 3: len 8; hex 80000000000003e9; asc         ;;
 4: len 3; hex 4f4646; asc OFF;;

*** WE ROLL BACK TRANSACTION (2)

优化方案

给product_id 添加普通索引
给product_id 添加唯一性索引（推荐）,减少锁定的范围

并发上传图片

LATEST DETECTED DEADLOCK
------------------------
2023-12-18 03:23:50 128112327091776
*** (1) TRANSACTION:
TRANSACTION 11162814, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 195977, OS thread handle 128110198220352, query id 70863884 10.169.20.37 appadmin update
INSERT INTO hfle_file (creation_date, created_by, last_update_date, last_updated_by, object_version_number, attachment_uuid, directory, file_url, file_type, file_name, file_size, bucket_name, file_key, tenant_id, storage_code, source_type) VALUES ('2023-12-18 11:23:50.613', 641, '2023-12-18 11:23:50.613', 641, 1, '$', 'cmt/images/ow-1/comment/zh_cn/', 'https://static-d2s-test.platform-loreal.cn/o2-resources/cmt/images/ow-1/comment/zh_cn/2/O2-PUB/cm_b2ce202312130032650030_cm_1_1702869830555.jpg', 'image/jpeg', 'cm_b2ce202312130032650030_cm_1_1702869830555.jpg', 293927, 'resources', 'cmt/images/ow-1/comment/zh_cn/2/O2-PUB/cm_b2ce202312130032650030_cm_1_1702869830555.jpg', 2, 'O2-PUB', '11')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 476 page no 1091 n bits 120 index hfle_file_n1 of table `hzero_platform`.`hfle_file` trx id 11162814 lock_mode X locks gap before rec
Record lock, heap no 40 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 30; hex 68747470733a2f2f7374617469632d6432732d746573742e706c6174666f; asc https://static-d2s-test.platfo; (total 143 bytes);
 1: len 9; hex 7265736f7572636573; asc resources;;
 2: len 8; hex 8000000000000002; asc         ;;
 3: len 8; hex 80000000000465c1; asc       e ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 476 page no 1091 n bits 128 index hfle_file_n1 of table `hzero_platform`.`hfle_file` trx id 11162814 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 40 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 30; hex 68747470733a2f2f7374617469632d6432732d746573742e706c6174666f; asc https://static-d2s-test.platfo; (total 143 bytes);
 1: len 9; hex 7265736f7572636573; asc resources;;
 2: len 8; hex 8000000000000002; asc         ;;
 3: len 8; hex 80000000000465c1; asc       e ;;


*** (2) TRANSACTION:
TRANSACTION 11162813, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 195958, OS thread handle 128110197462592, query id 70863893 10.169.20.37 appadmin update
INSERT INTO hfle_file (creation_date, created_by, last_update_date, last_updated_by, object_version_number, attachment_uuid, directory, file_url, file_type, file_name, file_size, bucket_name, file_key, tenant_id, storage_code, source_type) VALUES ('2023-12-18 11:23:50.612', 641, '2023-12-18 11:23:50.612', 641, 1, '$', 'cmt/images/ow-1/comment/zh_cn/', 'https://static-d2s-test.platform-loreal.cn/o2-resources/cmt/images/ow-1/comment/zh_cn/2/O2-PUB/cm_b2ce202312130032650030_cm_1_1702869830544.jpg', 'image/jpeg', 'cm_b2ce202312130032650030_cm_1_1702869830544.jpg', 231147, 'resources', 'cmt/images/ow-1/comment/zh_cn/2/O2-PUB/cm_b2ce202312130032650030_cm_1_1702869830544.jpg', 2, 'O2-PUB', '11')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 476 page no 1091 n bits 120 index hfle_file_n1 of table `hzero_platform`.`hfle_file` trx id 11162813 lock_mode X locks gap before rec
Record lock, heap no 40 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 30; hex 68747470733a2f2f7374617469632d6432732d746573742e706c6174666f; asc https://static-d2s-test.platfo; (total 143 bytes);
 1: len 9; hex 7265736f7572636573; asc resources;;
 2: len 8; hex 8000000000000002; asc         ;;
 3: len 8; hex 80000000000465c1; asc       e ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 476 page no 1091 n bits 128 index hfle_file_n1 of table `hzero_platform`.`hfle_file` trx id 11162813 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 40 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 30; hex 68747470733a2f2f7374617469632d6432732d746573742e706c6174666f; asc https://static-d2s-test.platfo; (total 143 bytes);
 1: len 9; hex 7265736f7572636573; asc resources;;
 2: len 8; hex 8000000000000002; asc         ;;
 3: len 8; hex 80000000000465c1; asc       e ;;

*** WE ROLL BACK TRANSACTION (2)

-- 创建测试表（非唯一索引）
CREATE TABLE file (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    url VARCHAR(512),
    KEY idx_url (url)  -- 非唯一索引
) ENGINE=InnoDB;

-- 插入初始数据（存在 a=3 和 a=5，但无 a=4）
INSERT INTO file (id, url) VALUES (1, "xxx"), (2, "lll");

并发事务执行流程

-- 会话1
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SELECT @@SESSION.transaction_isolation;
START TRANSACTION;
DELETE FROM file WHERE url = "zzz";
INSERT INTO file (id, url) VALUES (3, "zzz");
ROLLBACK;

-- 会话2
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SELECT @@SESSION.transaction_isolation;
START TRANSACTION;
DELETE FROM file WHERE url = "zzz4";
INSERT INTO file (id, url) VALUES (4, "zzz4");
ROLLBACK;

时间点	事务 T1	事务 T2	锁状态
T1	START TRANSACTION;	START TRANSACTION;
T2	DELETE FROM file WHERE url = "zzz";		T1 持有（2，+∞）的间隙锁
T3		DELETE FROM file WHERE url = "zzz4";	T2 持有（2，+∞）的间隙锁，与T1兼容
T4	INSERT INTO file (id, url) VALUES (3, "zzz");		T1要获取（2，+∞）的插入意向锁，与间隙锁不兼容，需要等待T2释放锁
T5		INSERT INTO file (id, url) VALUES (4, "zzz4");	T2要获取（2，+∞）的插入意向锁，与间隙锁不兼容，需要等待T1释放锁
T7			死锁形成：T1 和 T2 互相等待对方释放间隙锁


------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-12-01 16:24:47 0x16f933000
*** (1) TRANSACTION:
TRANSACTION 103393, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 48, OS thread handle 6188756992, query id 4211 localhost 127.0.0.1 root update
INSERT INTO file (id, url) VALUES (3, "zzz")

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_url of table `dead_lock`.`file` trx id 103393 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_url of table `dead_lock`.`file` trx id 103393 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 103394, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 49, OS thread handle 6187642880, query id 4215 localhost 127.0.0.1 root update
INSERT INTO file (id, url) VALUES (4, "zzz4")

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_url of table `dead_lock`.`file` trx id 103394 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_url of table `dead_lock`.`file` trx id 103394 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

优化方案：

降低事务隔离级别为RC（临时解决方案）
删除之前先执行SELECT查询，然后根据主键ID删除数据（建议）

总结

实际业务场景出现死锁大概率是间隙锁、间隙锁与插入意向锁互斥导致的。因此在同一个事务中我们要尽量避免间隙锁的存在，可以通过降低隔离级别、使用唯一性键等。

最佳实践：

delete/update 语句尽量使用主键id/唯一性索引列
delete/update 禁止使用非索引字段作为条件
为避免通过唯一性索引加锁时产生间隙锁，执行之前要先通过select判断数据是否存在。