MySQL 你好，死锁

本篇文章向大家介绍《MySQL 你好，死锁》，主要包括MySQL、死锁、InnoDB、dead-lock，具有一定的参考价值，需要的朋友可以参考一下。

原文地址：MySQL 你好，死锁

其实上面生活案例中拥堵就类似于——

select @@global.tx_isolation,@@tx_isolation;

+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+

修改隔离级别：

set global transaction isolation level read committed; ## 全局的

set session transaction isolation level read committed; ## 当前会话(session)

创建数据表

CREATE TABLE `deadlock` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uniq_stu_num` (`stu_num`),
  KEY `idx_score` (`score`)
) ENGINE=InnoDB;

insert into deadlock(id, stu_num, score) values (1, 11, 111);
insert into deadlock(id, stu_num, score) values (2, 22, 222);
insert into deadlock(id, stu_num, score) values (3, 33, 333);

set autocommit=0; ## 关闭自动提交

START TRANSACTION; ## 开始事务

场景一：AB BA

# session A
select * from deadlock where id = 1 for update; 

# session B
select * from deadlock where id = 2 for update; 

# session A
select * from deadlock where id = 2 for update;
## 因为session2 已经给id=2分配了写锁

# session B
select * from deadlock where id = 1 for update;
## 1213 - Deadlock found when trying to get lock; try restarting transaction

场景二：同一个事务中，S-lock 升级为 X-lock

# session A
SELECT * FROM deadlock WHERE id = 1 LOCK IN SHARE MODE;   
## 获取S-Lock

# session B
DELETE FROM deadlock WHERE id = 1;   
## 想获取X-Lock，但被session A的S-lock 卡住，目前处于waiting lock阶段

# session A
DELETE FROM deadlock WHERE id = 1;   
## Error : Deadlock found when trying to get lock; try restarting transaction
## 想获取X-Lock，sessionA本身拥有S-Lock
## 但是由于sessionB 申请X-Lock再前##
## 因此sessionA不能够从S-lock 提升到 X-lock
## 需要等待sessionB 释放才可以获取，所以造成死锁

场景三：主键和二级索引的死锁

CREATE TABLE `deadlock_A` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_score` (`score`),
  KEY `idx_stu_num` (`stu_num`) USING BTREE
) ENGINE=InnoDB;

# deadlock_A 数据
# select * from deadlock_A
| id   | stu_num | score |
| ---- | ------- | ----- |
| 1    | 11      | 111   |
| 2    | 33      | 222   |
| 3    | 22      | 333   |
| 4    | 44      | 444   |

# session A
delete from deadlock_A where stu_num > 11;
## 锁二级索引（stu_num）的顺序：22->33->44  锁主键（id）索引的顺序：3->2->4

# session B
delete from deadlock_A where score > 111;
## 锁二级索引（score）的顺序：222->333->444  锁主键（id）索引的顺序：2->3->4

## sessionA锁主键3， sessionB锁主键2
## sessionA锁主键2， sessionB锁主键3
## 死锁产生-》AB BA
## 这个在并发场景，可能会产生。

场景四：间隙锁（Gap Lock）

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `v` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_v` (`v`) USING BTREE
) ENGINE=InnoDB;

# select * from t2
| id   | v       |
| ---- | ----- |
| 2    | 2     |
| 5    | 5     |
| 10   | 10    |

间隙锁案例

# session A
delete from test where v=5;

# session B
insert into t2 (id,v) values (3,3);
## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

insert into t2 (id,v) values (9,9);
## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

insert into t2 (id,v) values (5,11);
## ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

insert into t2 (id,v) values (1,1)
## Affected rows : 1, Time: 5.62sec

insert into t2(id,v) values (10, 10);
## Affected rows : 1, Time: 10.51sec

insert into t2 (id,v) values (9,11);
## Affected rows : 1, Time: 15.51sec

看得出锁的是 id=5 & k=[3,10)的记录。

通过上面案例，大概了解间隙锁的范围后，我们来看看死锁场景：

# session A
update t2 set v = 5 where v =5;
## Affected rows : 1, Time: 12.67sec

# session B
update t2 set v = 10 where v =10;
## Affected rows : 1, Time: 12.88sec

# session A
insert into t2 (id,v) values (7,7);
## waiting

# session B
insert into t2 (id,v) values (8,8);
## Error : Deadlock found when trying to get lock; try restarting transaction

死锁的处理策略

预防死锁

1. 同顺序：以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新数据的顺序 为1->2；事务B更新数据的顺序为2->1。这样更可能会造成死锁

• 尽量保持事务简短：大事务更倾向于死锁，如果业务允许，将大事务拆小
• 一次性锁定：在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率
• 降低隔离级别：如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁
• 细粒度锁定（行锁）：为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大

死锁的检测和解除

innodb_lock_wait_timeout 等待锁超时回滚事务：
直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。

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

参考文章

《高性能的MySQL 第三版》

http://hedengcheng.com/?p=771...

https://www.kancloud.cn/hangh...

https://blog.csdn.net/dqjyong...

今天关于《MySQL 你好，死锁》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！