死锁-MySQL

2021-11-11 01:49:05

1、死锁

在操作系统中，死锁是指两个或两个以上的进程，每个进程都无限等待被改组进程中另一个进程所占有的资源，因而永远无法得到资源，这种现象称为进程死锁。

那么在数据库中，我们将操作系统对死锁的定义进行简单替换，将进程替换为事务即可。

数据库死锁是指两个或两个以上的事务，每个事务都在无限等待被改组事务中另一个事务所占有的资源，因而无法得到资源。

2、死锁必要条件

只要发生了死锁，这四个条件一定存在。

互斥使用（独占资源）

在数据库中，互斥使用的资源大部分都是表和行。

占有且等待

一个事务先锁定了行A，然后又申请锁定行B；另一个事务先申请锁定了行B，然后有申请锁定行A；当这两个事务以一种时序执行时就会产生死锁。

不可抢占

事务与事务之间是隔离的，肯定不可以一个事务抢占另一个事务的资源，必须要等待事务资源释放资源。

循环等待

当事务和资源形成一条有向有环图时。使用资源分配图来表示一下（蓝色先表示有回路）：

3、SQL示例


-- 创建用户余额表
CREATE TABLE `user_balance` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `balance` int NOT NULL COMMENT '余额',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '用户余额';

-- 插入2条记录
INSERT INTO `user_balance` (`balance`) VALUES (1);
INSERT INTO `user_balance` (`balance`) VALUES (1);


-- 事务A
BEGIN;
UPDATE `user_balance` SET `balance`=`balance`+1 where id=1;
UPDATE `user_balance` SET `balance`=`balance`+1 where id=2;
COMMIT;


-- 事务B
BEGIN;
UPDATE `user_balance` SET `balance`=`balance`+1 where id=2;
UPDATE `user_balance` SET `balance`=`balance`+1 where id=1;
COMMIT;

如果恰好事务A和事务B都执行了第一行UPDATE,接着事务A和事务B都去执行第二条UPDATE语句，此时发现id=1和id=2的数据都被锁定且不是自己，那么事务A和B都在相互等待锁释放，则陷入了死锁状态。

4、解决方案

为了解决死锁问题，数据库实现了各种死锁检测和死锁超时机制。InnoDB存储引擎可以检测到死锁的循环依赖并立即返回操作。

InnoDB目前处理死锁的方式是将持有最少行排他锁的事务进行回滚。

5、死锁复现

按照使用上面的SQL在MySQL 8.0版本执行SQL进行死锁复现，截图如下：

事务A正常执行

事务B被回滚

6、总结

MySQL 的死锁并不可怕，死锁后只需要在业务代码进行重试即可（官方建议）

7、参考

高性能MySQL（第三版）

码农公寓