怎么解决MySQL死锁问题

1.环境

本文的环境为MySQL8，事务的隔离级别为可重复读(RR)

2.什么是死锁

MySQL 死锁（Deadlock）是指两个或多个事务在执行过程中因互相持有对方所需要的资源而无法继续执行的情况，导致这些事务都在等待，最终无法完成

2-1.死锁产生的条件

• 互斥条件 (Mutual Exclusion)：

  • 在某一时刻，一个资源只能被一个进程占用。如果另一个进程请求该资源，则请求进程只能等待。

• 持有并等待条件 (Hold and Wait)：

  • 进程已经持有至少一个资源，同时还在等待获取其他被其他进程占用的资源。即进程在等待资源的同时不释放自己已经占有的资源。

• 无法强占 (No Preemption)：

  • 锁不能被强行占有。即一个资源只能在占有它的进程主动释放后才能被其他进程获得。

• 循环等待条件 (Circular Wait)：

  • 存在一个进程链，使得每个进程都在等待链中的下一个进程所占有的资源。即形成一个环形等待链，进程之间相互等待对方释放资源。

2-2.什么情况下会产生死锁

2-2-1.不同顺序的锁定

多个事务以不同的顺序请求相同的资源

事务A锁定A表，然后尝试锁定B表

事务B锁定B表，然后尝试锁定A表

这是如果两个事务同时执行，就会导致死锁

2-2-2.间隙锁

间隙锁主要用来防止幻读，在范围查询中，间隙锁可能导致死锁

事务A执行SELECT * FROM TABLE WHERE id > 1 FOR UPDATE

事务B执行INSERT INTO TABLE (id) values (15),插入记录

如果这两个事务并发执行就会导致死锁

2-2-3.自增列的死锁

在高并发情况下，多个事务列插入自增列时，可能会导致死锁

因为MySQL在分配自增值时可能会导致锁竞争，从而引发死锁

2-2-4.外键约束

事务A在表1删除一条记录，该表有一个外键引用表2

事务B在表2更新或删除与表1相关的记录

如果这两个事务并发执行，可能会导致死锁

2-2-5.锁升级

当MySQL从行级锁升到表级锁时，更新或删除操作可能会引发死锁

事务A和事务B分别锁定同一表中的不同行

如果某个操作需要将行级锁升级为表锁，可能会导致死锁

2-2-6.混合使用不同的锁

事务A持有一个读锁，尝试获取一个写锁

事务B持有一个写锁，尝试获取一个读锁

并发执行，可能会导致死锁

2-2-7.大量并发事务

大量事务操作统一资源，可能会导致死锁

事务之间互相等待资源释放

2-2-8.长时间占有锁

某个事务长时间占有某个锁。事务B尝试获取，但是被阻塞

如果事务A和事务B都在等待对方释放，就会导致死锁

3.怎么解决死锁

3-1.优化SQL语句

优化SQL语句，减少锁的持有时间，降低死锁发生概率

避免大事务，将大事务拆分为多个小事务，减少每个事务锁持有时间

索引优化，减少全盘扫描

3-2.调整事务顺序

统一访问顺序，确保所有事务以相同的顺序访问资源

减少循环等待，通过调整事务的执行顺序，避免事务之间形成循环等待

3-3.使用合理的隔离级别

降低隔离级别，来减少锁冲突，但是需要权衡数据一致性和并发性能

使用锁机制：显示使用LOCK IN SHARE MODE 或 FOR UPDATE来控制锁的粒度和范围

3-4.合理使用索引

使用覆盖索引，通过避免访问数据行，减少锁的冲突

通过执行计划：查询是否使用了索引，全盘扫描会导致大量锁竞争

3-5.监控和分析

启用死锁日志，找出导致死锁的SQL语句和事务，进行优化

3-6.重试机制

自动重试，维持程序运行

3-7.锁超时等待

通过设置合理的超时参数，避免长时间等待

3-8.分区表

将数据文件分区到不同的物理文件，可以减少事务之间的锁冲突

3-9.行级锁

使用行级锁降低锁的粒度

3-10.避免热点更新

分散更新压力，避免对同一页或同一行频繁的更新，可以使用分区分表的方式

4.如果发生了死锁怎么解决

• 大部分数据库检测到死锁的情况下会自动干预，通过回滚事务或多个事务来打破死锁

• MySQL开启死锁检测，通过终止事务或锁超时回滚事务来打破死锁

• 使用Navicat手动强制回滚

5.你会如何去排查死锁

5-1.获取死锁日志

show engine innodb status

找出导致死锁的SQL语句

5-2.通过explain查询执行计划

查询死锁发生那一刻的执行计划，判断是否是因为索引失效导致