mysql-死锁

1、概念

两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。
举例1：

1.1、创建表 account

drop table if exists account;
create table account(
id int primary key AUTO_INCREMENT comment 'ID',
name varchar(10) comment '姓名',
money double(10,2) comment '余额'
) comment '账户表';
insert into account(name, money) VALUES ('A',2000), ('B',2000);

1.2、id 自动创建 主键索引 primary

mysql> select * from account;
+----+------+--------+
| id | name | money  |
+----+------+--------+
|  1 | A   | 100.00 |
|  2 | B   | 200.00 |
+----+------+--------+
2 rows in set (0.00 sec)

举例2：

用户A给用户B转账100，在此同时，用户B也给用户A转账100。这个过程，可能导致死锁。

mysql> select * from account;
+----+------+---------+
| id | name | money   |
+----+------+---------+
|  1 | A    | 2000.00 |
|  2 | B    | 2000.00 |
+----+------+---------+
2 rows in set (0.00 sec)

1.3、name 没有创建索引

因为name没有创建索引，所以此时行锁升级为表锁，此时 account表 被锁定，所以 update account set money= money - 100 where name = ‘B’;和update account set money= money + 100 where name = ‘A’;执行不成功，发生阻塞。

mysql> select * from account;
+----+------+---------+
| id | name | money   |
+----+------+---------+
|  1 | A    | 1900.00 |
|  2 | B    | 2100.00 |
+----+------+---------+
2 rows in set (0.00 sec)

2、产生死锁的必要条件

1. 两个或者两个以上事务
2. 每个事务都已经持有锁并且申请新的锁
3. 锁资源同时只能被同一个事务持有或者不兼容
4. 事务之间因为持有锁和申请锁导致彼此循环等待

死锁的关键在于：两个（或以上）的Session加锁的顺序不一致。

此时不会发生死锁。

mysql> select * from account;
+----+------+---------+
| id | name | money   |
+----+------+---------+
|  1 | A    | 2000.00 |
|  2 | B    | 2000.00 |
+----+------+---------+
2 rows in set (0.00 sec)

2.1、此时 name 没有创建 索引

因为name没有创建索引，所以此时行锁升级为表锁，此时 account表 被锁定，所以 update account set money = money + 100 where name = ‘A’;和update account set money = money - 100 where name = ‘B’;执行不成功，发生阻塞。

mysql> select * from account;
+----+------+---------+
| id | name | money   |
+----+------+---------+
|  1 | A    | 1900.00 |
|  2 | B    | 2100.00 |
+----+------+---------+
2 rows in set (0.00 sec)

3、如何处理死锁

3.1、方式1：等待，直到超时（innodb_lock_wait_timeout=50s）

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.04 sec)

等待，直到超时（innodb_lock_wait_timeout=50s）。
即当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其 回滚，另外事务继续进行。
这种方法简单有效，在innodb中，参数 innodb_lock_wait_timeout 用来设置超时时间。

缺点：对于在线服务来说，这个等待时间往往是无法接受的。
那将此值修改短一些，比如 1s，0.1s 是否合适？ 不合适，容易误伤到普通的锁等待。

3.2、方式2：使用 死锁 检测进行死锁处理

方式1 检测死锁太过被动，innodb还提供了 wait-for graph 算法 来主动进行死锁检测，每次加锁请求无法立即满足需要并进入等待时，wait-for graph 算法 都会被触发。

这是一种较为 主动的死锁检测机制 ，要求数据库保存 锁的信息链表 和 事务等待链表 两部分信息。

基于这两个信息，可以绘制 wait-for graph （等待图）

死锁检测的原理 是构建一个以事务为顶点、锁 为边的有向图，判断有向图是否存在环，存在既有死锁。

一旦检测到回路、有死锁，这时候 InnoDB 存储引擎 会选择 回滚undo量最小的事务，让其他事务继续执行（innodb_deadlock_detect=on 表示开启这个逻辑）。

缺点：每个新的被阻塞的线程，都要判断是不是由于自己的加入导致了死锁，这个操作时间复杂度是O（n）。如果100个并发线程同时更新同一行，意味着要检测100*100=1万次，1万个线程就会有1千万次检测。

3.3、如何解决？

• 方式1：关闭死锁检测，但意味着可能会出现大量的超时，会导致业务有损。
• 方式2：控制并发访问的数量。比如在中间件中实现对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量的死锁检测工作。

3.4、进一步的思路：

可以考虑通过将一行改成逻辑上的多行来减少 锁冲突。比如，连锁超市账户总额的记录，可以考虑放到多条记录上。账户总额等于这多个记录的值的总和。

4、如何避免死锁

• 合理设计索引，使业务SQL尽可能通过索引定位更少的行，减少锁竞争。
• 调整业务逻辑SQL执行顺序，避免 update/delete 长时间持有锁的 SQL 在事务前面。
• 避免大事务，尽量将大事务拆成多个小事务来处理，小事务缩短锁定资源的时间，发生锁冲突的几率也更少。
• 在并发比较高的系统中，不要显示加锁，特别是是在事务里显示加锁。比如 select … for update 语句，如果是在事务运行了 start transaction 或设置了 autocommit 等于 0，那么就会锁定所查找到的记录。
• 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从 RR 调整为 RC，可以避免掉很多因为 gap 锁造成的死锁。