mysql4316_MySQL锁系列（八）之 死锁

能学到什么

什么是死锁

死锁有什么危害

典型的死锁案例剖析

如何避免死锁

一、什么是死锁

1.必须满足的条件

1. 必须有两个或者两个以上的事务

2. 不同事务之间都持有对方需要的锁资源。 A事务需要B的资源，B事务需要A的资源，这就是典型的AB-BA死锁

2.死锁相关的参数

* innodb_print_all_deadlocks

1. 如果这个参数打开，那么死锁相关的信息都会打印输出到error log

* innodb_lock_wait_timeout

1. 当MySQL获取row lock的时候，如果wait了innodb_lock_wait_timeout=N的时间，会报以下错误

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

* innodb_deadlock_detect

1. innodb_deadlock_detect = off 可以关闭掉死锁检测，那么就发生死锁的时候，用锁超时来处理。

2. innodb_deadlock_detect = on (默认选项)开启死锁检测，数据库自动回滚

* innodb_status_lock_output = on

1. 可以看到更加详细的锁信息

二、死锁有什么危害

死锁，即表明有多个事务之间需要互相争夺资源而互相等待。

如果没有死锁检测，那么就会互相卡死，一直hang死

如果有死锁检测机制，那么数据库会自动根据代价来评估出哪些事务可以被回滚掉，用来打破这个僵局

所以说：死锁并没有啥坏处，反而可以保护数据库和应用

那么出现死锁，而且非常频繁，我们应该调整业务逻辑，让其避免产生死锁方为上策

三、典型的死锁案例剖析

3.1 死锁案例一

典型的 AB-BA 死锁

session 1:

select * from tb_b where id_2 = 1 for update (A)

session 2:

select * from tb_a where id = 2 for update (B)

session 1:

select * from tb_a where id = 2 for update (B)

session 2:

select * from tb_b where id_2 = 1 for update (A)

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

1213的死锁错误，mysql会自动回滚

哪个回滚代价最小，回滚哪个(根据undo判断)

------------------------

LATEST DETECTED DEADLOCK

------------------------

2017-06-22 16:39:50 0x7f547dd02700

*** (1) TRANSACTION:

TRANSACTION 133601982, ACTIVE 48 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)

MySQL thread id 11900, OS thread handle 140000866637568, query id 25108 localhost dba statistics

select * from tb_a where id = 2 for update -----session1 持有tb_a中记录为2的锁

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 303 page no 3 n bits 72 index PRIMARY of table `lc_5`.`tb_a` trx id 133601982 lock_mode X locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000002; asc ;; --session 1 需要tb_a中记录为2的锁( session1 -> session2 )

1: len 6; hex 000007f69ab2; asc ;;

2: len 7; hex dc000027100110; asc ' ;;

*** (2) TRANSACTION:

TRANSACTION 133601983, ACTIVE 28 sec starting index read, thread declared inside InnoDB 5000

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1136, 2 row lock(s)

MySQL thread id 11901, OS thread handle 140000864773888, query id 25109 localhost dba statistics

select * from tb_b where id_2 = 1 for update

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 303 page no 3 n bits 72 index PRIMARY of table `lc_5`.`tb_a` trx id 133601983 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000002; asc ;; --session 2 持有tb_a中记录等于2的锁

1: len 6; hex 000007f69ab2; asc ;;

2: len 7; hex dc000027100110; asc ' ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 304 page no 3 n bits 72 index PRIMARY of table `lc_5`.`tb_b` trx id 133601983 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000001; asc ;; --session 2 需要tb_b中记录为1的锁 ( session2 -> session1 )

1: len 6; hex 000007f69ab8; asc ;;

2: len 7; hex e0000027120110; asc ' ;;

最终的结果：

死锁路径：[session1 -> session2 , session2 -> session1]

ABBA死锁产生

3.2 死锁案例二

同一个事务中，S-lock 升级为 X-lock 不能直接继承

* session 1:

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;

Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);

Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; --获取S-lock

+------+

| i |

+------+

| 1 |

+------+

* session 2:

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1; --想要获取X-lock，但是被session1的S-lock 卡住，目前处于waiting lock阶段

* session 1:

mysql> DELETE FROM t WHERE i = 1; --想要获取X-lock，session1本身拥有S-lock，但是由于session 2 获取X-lock再前，所以session1不能够从S-lock 提升到 X-lock，需要等待session2 释放才可以获取，所以造成死锁

ERROR 1213 (40001): Deadlock found when trying to get lock;

try restarting transaction

死锁路径：

session2 -> session1 , session1 -> session2

3.3 死锁案例三

唯一键死锁 (delete + insert)

关键点在于：S-lock

dba:lc_3> show create table uk;

+-------+--------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+--------------------------------------------------------------------------------------------------------------+

| uk | CREATE TABLE `uk` (

`a` int(11) NOT NULL,

UNIQUE KEY `uniq_a` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+--------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

dba:lc_3> select * from uk;

+---+

| a |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

session 1:

dba:lc_3> begin;

Query OK, 0 rows affected (0.00 sec)

dba:lc_3> delete from uk where a=1;

Query OK, 1 row affected (0.00 sec)

session 2:

dba:(none)> use lc_3;

Database changed

dba:lc_3> insert into uk values(1); --wait lock(想要加S-lock，却被sesson1的X-lock卡住)

sesson 3:

dba:(none)> use lc_3;

Database changed

dba:lc_3> insert into uk values(1); --wait lock(想要加S-lock，却被sesson1的X-lock卡住)

session 1:

commit; --session2和session3 都获得了S-lock，然后都想要去给记录1 加上X-lock，却互相被对方的S-lock卡住，死锁产生

再来看session 2 和 session 3 的结果：

session2：

Query OK, 1 row affected (7.36 sec)

session3：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

总结： 试想想，如果session 1 不是commit，而是rollback会是怎么样呢？ 大家去测测就会发现，结果肯定是唯一键冲突啊

3.4 死锁案例四

主键和二级索引的死锁

* primary key

1 2 3 4 --primary key col1

10 30 20 40 --idx_key2 col2

100 200 300 400 --idx_key3 col3

* idx_key2 select * from t where col2 > 10: 锁二级索引顺序为：20 =》30 ， 对应锁主键的顺序为：3 =》2

10 20 30 40

1 3 2 4

* idx_key3 select * from t where col3 > 100：锁二级索引顺序为：200 =》300 ， 对应锁主键的顺序为：2 =》3

100 200 300 400

1 2 3 4

死锁路径：

由于二级索引引起的主键加锁顺序： 3 =》2

由于二级索引引起的主键加锁顺序： 2 =》3

这个要求并发，且刚好

session 1 加锁3的时候 session 2 要加锁2.

session 1 加锁2的时候 session 3 要加锁3.

这样就产生了 AB-BA 死锁

3.5 死锁案例五

purge + unique key 引发的死锁

A表的记录： id = 1 10 40 100 200 500 800 900

session 1 :

delete from a where id = 10; ???

session 2 :

delete from a where id = 800; ???

session 1 :

insert into a select 800; ???

session 2 :

insert into a select 10; ???

* 如果大家去跑这两钟SQL语句的并发测试，是可以导致死锁的。

* 如何验证是由于purge导致的问题呢？这个本想用mysqld-debug模式去关闭purge线程，但是很遗憾我没能模拟出来。。。

3.6 死锁案例六

REPLACE INTO问题

* 这个问题模拟起来非常简单，原理非常复杂，这里不过多解释

* 详情请看姜老师的文章,据说看懂了年薪都100w了： http://www.innomysql.com/26186-2/

* 解决方案：

* 用insert into ... on duplicate key update 代替 replace into

* 此方案亲测有效

四、如何避免死锁

产生死锁的原因

1. 事务之间互相占用资源

方法和总结

1. 降低隔离级别，修改 RR -> RC , 如果这个调整了，可以避免掉60%的死锁场景和奇怪的锁等待

2. 调整业务逻辑和SQL，让其都按照顺序执行操作

3. 减少unique索引，大部分死锁的场景都是由于unique索引导致

4. 尽量不用replace into，用insert into ... on duplicate key update 代替