MySQL 死锁

什么是数据库层面的死锁呢?

数据库的死锁是指在数据库事务处理过程中，两个或多个事务因互相等待对方持有的锁而无法继续执行的现象。这种情况下，事务会无限期地等待，直到外部干预才能恢复正常。

死锁的必要条件

1. 互斥 A操作时不允许B操作，B操作时不允许A操作
2. 资源不能抢占，A获取了锁，B没办法强制让A把锁释放了，反之亦然
3. 循环等待情况，A等待B，B等待C，C等待A，形成了一个闭环

触发死锁

首先做个测试，主动在数据库中触发一次死锁，触发死锁很简单，在MySQL的InnoDB的存储引擎下，默认的UPDATE操作就会加入行锁，行锁的范围取决于我们的WHERE条件，假设WHERE条件为如下内容

UPDATE TABLE SET num = 100 WHERE id = '123';

那么行锁的范围就是id为123的这行数据，假设操作人A进行了上述UPDATE语句的操作，那么MySQL就会将id = '123’的这行数据加锁 (排他锁)，不允许其他的事务在锁未释放的前提下再次进行修改, 当然MySQL的默认情况下都是自动提交的，也就是没有进行手动管理事务，我们可以通过START TRANSATION(开启事务), COMMIT(提交事务), ROLLBACK(回滚事务), 进行手动事务管理，当然现在很多服务端框架也帮我们实现了事务管理，比如Spring的@Transactional注解, 也可以发现，MySQL InnoDB的存储引擎 所谓的默认加锁机制其实也是悲观锁的一种实现

我们在NAVICAT中模拟一下，死锁，假设我们现在有两个用户, A和B，他们同时进行了如下的操作

假设MySQL首先开始处理A的操作，为id = ‘1848958781925761026’ 的数据加了行锁，并且由于A的操作一段时间内没有执行完，当然我写的是根据id修改，这个正常情况下执行的速度都是非常快的，那么B触发的操作就会阻塞住，我们可以模拟一下让A阻塞住，非常简单就是把COMMIT去掉，不让A提交，那么A就会一直是RUNNING的状态, 接下来尝试一下

我们此时查看一下数据库的锁等待情况

SELECT * FROM information_schema.innodb_trx;

可以看到现在有两个任务，一个在执行 （A），一个在等待 (B)，已经发生死锁了

死锁处理 - （不重试）

1. 将长时间不释放的锁手动给它回滚了，怎么回滚呢，使用KILL命令，将对应任务的线程给杀掉，这里的KILL命令并不是我们所认知的类似Linux中的KILL命令，MySQL的InnoDB存储引擎默认遵守ACID原则，当使用KILL命令后，MySQL会尝试回滚该事务以保证数据的一致性和完整性。回滚操作意味着撤销该事务中所有已执行的更改，恢复到事务开始前的状态。
2. 以上图为例我们要杀的就是5742，一旦5742被杀死后，再次执行我们的B操作，5743就会执行并提交，我们来尝试一下

KILL 5742;

可以看到现在已经没有锁等待的信息了
数据库的记录也被B触发的操作给修改了

死锁处理 - （添加重试机制）

1. 上面虽然我们处理了死锁问题，但是又引发了另一个问题，也就是A任务虽然被KILL掉了，但是相当于A的操作也失了，压根没成功，我们就需要加入重试机制来让A任务在被KILL掉之后，进行重试
2. 重试我们分为两种，一种是立即重试，也就是A一旦执行失败了, 立刻通过编程的方式进行第二次执行，具体的重试次数我们可以自定义，第二种方式是将失败的信息进行持久化存储，后续再进行重试，比如定时任务触发，当然即使说立即重试也可能还是会失败，那就需要设置一个合理的次数了，如果是一直重试一直失败，那就没有意义了，所以当重试次数超过限制后，也应该主动将对应的任务信息保留下来，介入排查问题了，不要干扰到其他任务的执行

如何尽可能减少死锁发生的情况呢?

使用两阶段提交

举个例子，我们假设写如下的UPDATE 语句

UPDATE TABLE SET num = 991 WHERE name = '小明' and age = '18'; 

这个UPDATE语句我们可以改成两部分操作

# 先把满足条件的id查出来 
1. List<String> ids = SELECT id FROM TABLE WHERE name = '小明' and age = '18';
# 根据id进行修改操作 这里图方便就直接写了 实际上应该是循环标签嵌套的 也可以代码处理成字符串传进去
2. UPDATE TABLE SET num = 991 where id in (#{ids});

上面的两部分实际上我们是在代码层面就行了两次IO操作, 一次读, 一次写
注意: 不能误解两阶段提交，不能写成如下的语句

# 这个不算两阶段提交，这还是算一个整体的SQL
UPDATE TABLE SET num = 991 where id in (SELECT id FROM TABLE WHERE name = '小明' and age = '18');

我们看似直接修改跟两阶段提交锁定的内容应该都是一样的呀，为什么两阶段提交就可以减少死锁的情况呢?

1. 实际上，这两者执行的过程有一些细微但是非常重要的差别，主要体现在锁的竞争，持久时间，和并发性能上
2. 当直接更新时，数据库会锁定所有满足 name = 'XXX' AND age = 'XXX' 条件的行, 这些行在整个更新过程中都会被锁定，直到事务提交或回滚。如果满足条件的行很多，或者条件复杂，锁的持有时间会比较长
3. 先查询id后再根据id修改，在查询阶段通常不会锁定行（除非使用了 FOR UPDATE 等锁提示, 数据库只是读取满足条件的行的 id，而更新阶段只会锁定那些具体的 ID 对应的行，并且呢由于根据主键id修改，主键索引是数据库中最高效的索引类型，因此它更新的速度也快
4. 两阶段要确保设置了正确的隔离级别，避免一阶段读取到了脏数据，同时要保证一，二阶段在同一个事务里

合理设置锁的超时时间

1. 临时设置

SET innodb_lock_wait_timeout = 10;

2. 永久设置 (修改MySQL的my.cnf或者my.ini配置文件)

[mysqld]
innodb_lock_wait_timeout = 10

3. 比较特殊的: 分布式锁的超时时间设置，以Redis举例

# 60秒过期 实际程序中也不需要这么写 比如Spring提供了Redis的标准API接口使用
SET KEY "value" EX 60 ;

分布式锁呢，它不是数据库层面的锁，而是我们业务逻辑上的锁，比如说以下的业务需求: 机床排产，根据到料情况进行预计工时的计算，这个需求要保证无论何时只能有一个人触发操作，而且肯定是异步的，计算是需要时间的，前端不可能一直在那儿等后端返回结果，并且一旦A触发了排产操作那么别的B,C,D…就不能再操作了，无论是时间计算还是机床的工序顺序调整都不能进行了，因为如果A在进行逻辑处理的时候，B又把数据改了，那A计算的时间就不一定对了，只能等A处理完，其他人再次操作，假设我们的系统水平部署了很多台，那么分布式锁就是必要的实现，保证多个实例共享一个锁，常见的就是用Redis实现，A操作存入Redis对应的Key，其他人只要拿到这个Key就直接拒绝操作了，但是这个Key的超时时间往往不好设置，不能设置过早释放, A还在计算呢，锁就释放了，结果B就可以在A计算期间进行操作了，设置太长的时间也不合理，因为一直在执行说明本身的解决问题的方案就不对，我们一般是异步执行完进行主动释放，并且在测试阶段，就应该进行暴力测试，进行大批量数据覆盖，做最坏的情况打算

写操作的条件范围尽可能减小，避免过大范围的行锁

使用乐观锁实现

1. 添加版本号字段
2. 每次进行写操作前 先读取当前版本号
3. 修改操作携带上版本号作为条件，当发现版本号不一致了，说明记录已经被别的事务修改了，自然也就执行失败了，执行成功了版本号也要做对应的修改，比如当前版本 + 1
4. 高并发场景下乐观锁并不是特别合适，假设我们现在有100个人同时修改一条记录，只要其中任何一个人修改了当前记录，那么剩余的99个人获取的版本号一定就不能用了，那么就需要重试，一重试结果98个人又冲突了，又进行重试了，如此高频率的重试，会大量增加系统开销