MySQL锁系列（八）之 死锁

点击查看全文

能学到什么

1. 什么是死锁
2. 死锁有什么危害
3. 典型的死锁案例剖析
4. 如何避免死锁

一、什么是死锁

• 1.必须满足的条件

1. 必须有两个或者两个以上的事务2. 不同事务之间都持有对方需要的锁资源。 A事务需要B的资源，B事务需要A的资源，这就是典型的AB-BA死锁

• 2.死锁相关的参数

* innodb_print_all_deadlocks1. 如果这个参数打开，那么死锁相关的信息都会打印输出到error log* innodb_lock_wait_timeout1. 当MySQL获取row lock的时候，如果wait了innodb_lock_wait_timeout=N的时间，会报以下错误ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction* innodb_deadlock_detect1. innodb_deadlock_detect = off  可以关闭掉死锁检测，那么就发生死锁的时候，用锁超时来处理。2. innodb_deadlock_detect = on  （默认选项）开启死锁检测，数据库自动回滚* innodb_status_lock_output = on1. 可以看到更加详细的锁信息

二、死锁有什么危害

1. 死锁，即表明有多个事务之间需要互相争夺资源而互相等待。
2. 如果没有死锁检测，那么就会互相卡死，一直hang死
3. 如果有死锁检测机制，那么数据库会自动根据代价来评估出哪些事务可以被回滚掉，用来打破这个僵局
4. 所以说：死锁并没有啥坏处，反而可以保护数据库和应用
5. 那么出现死锁，而且非常频繁，我们应该调整业务逻辑，让其避免产生死锁方为上策

三、典型的死锁案例剖析

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 transaction1213的死锁错误，mysql会自动回滚哪个回滚代价最小，回滚哪个（根据undo判断）------------------------LATEST DETECTED DEADLOCK------------------------2017-06-22 16:39:50 0x7f547dd02700*** (1) TRANSACTION:TRANSACTION 133601982, ACTIVE 48 sec starting index readmysql tables in use 1, locked 1LOCK 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 statisticsselect * 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 waitingRecord 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 5000mysql tables in use 1, locked 14 lock struct(s), heap size 1136, 2 row lock(s)MySQL thread id 11901, OS thread handle 140000864773888, query id 25109 localhost dba statisticsselect * 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 gapRecord 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 waitingRecord 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 不能直接继承

点击查看全文