mysql死锁分析
什么是死锁
死锁是指在两个或多个事务在执行过程中，因争夺资源而互相等待的现象。如果没有外力作用，这些事务将永远处于等待状态，无法继续执行。简单来说，就是事务A等待事务B释放资源，而事务B又在等待事务A释放资源，最终导致双方都无法完成。
死锁的原因
- 表的行级锁冲突: 在InnoDB存储引擎中，行级锁容易导致死锁，特别是在多表更新操作时。
从上图实例中可以看到, 事务A和事务B都在等待对方释放资源, 导致双方都无法完成。银耳导致了死锁.产生这种现象原因如下: - 两个事务执行过程时间上有交集，并且过程发生在两者提交之前
- 事务1更新id=1的记录，事务2更新id=2的记录，在
RR级别，由于uid是唯一索引，因此两个事务将分别持有id=1和2所在行的独占锁 - 事务1执行到第二条更新语句时，发现id=2的行被锁住，进入阻塞等待锁释放
- 事务2执行到第二条语句时发现id=1的行被锁，同样进入阻塞
- 两个事务互相等待，死锁产生
- 资源竞争：多个事务同时竞争同一资源，导致互相等待。本质上也是行锁冲突导致, 只是出发行所冲突的场景可能不同
- 不同事务的执行顺序: 事务的执行顺序不同也可能引发死锁。例如，事务A按照顺序1、2、3锁住资源，而事务B按照顺序3、2、1锁住资源时，很可能发生死锁。本质上也是行锁冲突导致, 只是出发行所冲突的场景可能不同
如何检测MySQL死锁
- 错误日志: 当MySQL检测到死锁时，会在错误日志中记录相关信息。你可以通过查看MySQL错误日志来确认死锁的存在。
- 死锁检测工具: MySQL提供了一些工具来检测死锁，如
SHOW ENGINE INNODB STATUS命令可以显示当前InnoDB存储引擎的状态，包括死锁信息。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-05-20 22:12:06 0x70000f671000
*** (1) TRANSACTION:
TRANSACTION 98161, ACTIVE 44 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 71895, OS thread handle 123145574592512, query id 294094 localhost root updating
update people set name = '2' where id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 296 page no 4 n bits 80 index PRIMARY of table `blog_test`.`people` trx id 98161 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 0000000000000001; asc ;;
1: len 6; hex 000000017f71; asc q;;
2: len 7; hex 01000001982132; asc !2;;
3: len 3; hex 313031; asc 101;;
4: len 4; hex 00000015; asc ;;
5: len 1; hex 31; asc 1;;
6: len 4; hex 000000af; asc ;;
7: len 6; hex e9a699e6b8af; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 296 page no 4 n bits 80 index PRIMARY of table `blog_test`.`people` trx id 98161 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 0000000000000002; asc ;;
1: len 6; hex 000000017f72; asc r;;
2: len 7; hex 020000018a2bcd; asc + ;;
3: len 3; hex 313032; asc 102;;
4: len 4; hex 00000012; asc ;;
5: len 1; hex 32; asc 2;;
6: len 4; hex 000000ad; asc ;;
7: len 6; hex e58fb0e6b9be; asc ;;
*** (2) TRANSACTION:
TRANSACTION 98162, ACTIVE 18 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 71896, OS thread handle 123145573527552, query id 294095 localhost root updating
update people set name = '1' where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 296 page no 4 n bits 80 index PRIMARY of table `blog_test`.`people` trx id 98162 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 0000000000000002; asc ;;
1: len 6; hex 000000017f72; asc r;;
2: len 7; hex 020000018a2bcd; asc + ;;
3: len 3; hex 313032; asc 102;;
4: len 4; hex 00000012; asc ;;
5: len 1; hex 32; asc 2;;
6: len 4; hex 000000ad; asc ;;
7: len 6; hex e58fb0e6b9be; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 296 page no 4 n bits 80 index PRIMARY of table `blog_test`.`people` trx id 98162 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 8; hex 0000000000000001; asc ;;
1: len 6; hex 000000017f71; asc q;;
2: len 7; hex 01000001982132; asc !2;;
3: len 3; hex 313031; asc 101;;
4: len 4; hex 00000015; asc ;;
5: len 1; hex 31; asc 1;;
6: len 4; hex 000000af; asc ;;
7: len 6; hex e9a699e6b8af; asc ;;
*** WE ROLL BACK TRANSACTION (2)通过明亮航查看最近一次死所信息示例如上, 列出了死锁的两个事务，以及事务ID + 导致思索的sql信息, 同时最后一行说明, 自动回滚了事务2, 令事务1可以正常执行, 避免了死锁长时间阻塞的发生。
MySQL死锁的解决方法
- 外力介入, 手动解决死锁: 检测到死锁后，可以手动回滚其中一个事务，以释放资源，打破死锁局面。
- 自动死锁检测和回滚: InnoDB存储引擎具有
自动检测死锁并回滚其中一个事务的机制。你可以通过配置innodb_lock_wait_timeout参数来设置锁等待的超时时间。
避免MySQL死锁的策略
- 避免大事务: 尽量避免长时间运行的大事务，将大事务拆分为多个小事务，可以有效减少死锁的发生概率。但是注意小失误拆分后, 数据一致性上对业务的影响
- 保持一致的锁定顺序: 确保所有事务按照相同的顺序请求资源，这样可以避免不同事务间的资源竞争。
- 合理设计索引: 合理的索引设计可以减少表扫描，降低锁冲突的概率。
- 使用乐观锁: 通过乐观锁机制，避免数据库级别的锁竞争。例如，使用版本号控制并发更新。
- 减少锁的粒度: 减少锁的粒度可以降低锁冲突的概率，例如使用行级锁而不是表级锁。