一、死锁案例
MySQL版本:Percona MySQL Server 5.7.19
隔离级别:可重复读(RR)
业务逻辑:并发下按某个索引字段先delete记录,再insert记录
比如:
begin;delete from tb where order_id = xxx;insert into tb(order_id) values(xxx); commit;
二、MySQL锁基本概念
S:共享锁(行级锁)
X:排他锁(行级锁)
IS:意向共享锁(表级锁)
IX:意向排他锁(表级锁)
以上4种锁的兼容性见下表:
锁模式兼容性表
gap锁与gap锁之间不冲突
rec insert intention(插入意向锁)与gap锁冲突。
三、模拟复现死锁
打开参数,从innodb status获取更多的锁信息。
set GLOBAL innodb_status_output_locks=ON;
表结构:
CREATE TABLE `tb` ( `order_id` int(11) DEFAULT NULL, KEY `idx_order_id` (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
表中数据:
mysql> select * from tb; +----------+| order_id |+----------+| 10 || 20 |+----------+2 rows in set (0.00 sec)
事务执行步骤:
session1 | session2 |
---|---|
begin | |
begin | |
delete from tb where order_id=15; | |
delete from tb where order_id=15; | |
insert into tb select 15;(等待锁) | |
insert into tb select 15;(死锁) |
当session1执行delete from tb where order_id=15;,由于条件order_id=15的记录不存在,session1 获得2个锁结构,分别是意向排他锁IX(表级锁)、gap锁(行级锁),如下:
---TRANSACTION 1055191443, ACTIVE 20 sec2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 315642, OS thread handle 139960342456064, query id 150462030 localhost root TABLE LOCK table `db`.`tb` trx id 1055191443 lock mode IX RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec
当session2执行delete from tb where order_id=15;,同样由于order_id=15的记录不存在,session2 也获得2个锁结构,分别是意向排他锁IX(表级锁)、gap锁(行级锁),如下:
---TRANSACTION 1055191444, ACTIVE 3 sec2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 315336, OS thread handle 139960562685696, query id 150462412 localhost root TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec
当session2执行insert into tb select 15;, session2 已经获取到IX锁,gap锁,等待 rec insert intention(插入意向锁)
---TRANSACTION 1055191444, ACTIVE 68 sec inserting mysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 315336, OS thread handle 139960562685696, query id 150462778 localhost root executing insert into tb select 15------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting ------------------ TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting
当session1执行insert into tb select 15;,session1 已获取到IX锁,gap锁, 等待rec insert intention(插入意向锁), session1, session2 都在等待插入意向锁, 插入意向锁与gap锁冲突,双方都没有释放gap锁,又都在等待插入意向锁,死锁发生。
LATEST DETECTED DEADLOCK ------------------------2018-11-03 17:15:11 0x7f4b0e7ea700*** (1) TRANSACTION: TRANSACTION 1055191444, ACTIVE 135 sec inserting mysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 315336, OS thread handle 139960562685696, query id 150462778 localhost root executing insert into tb select 15*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION 1055191443, ACTIVE 201 sec inserting, thread declared inside InnoDB 5000mysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 315642, OS thread handle 139960342456064, query id 150463172 localhost root executing insert into tb select 15*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION (2)
四、案例扩展
以上死锁案例,业务代码逻辑是多线程并发下,有可能多个线程会执行相同order_id的job,比如两个线程执行的order_id 都是15。
另外一种情况,多个线程间,不会执行到相同order_id的情况,也可能发生死锁。比如一个线程order_id=15,另外一个线程order_id=16,如下所示:
事务执行步骤:
session1 | session2 |
---|---|
begin | |
begin | |
delete from tb where order_id=15; | |
delete from tb where order_id=16; | |
insert into tb select 16;(等待锁) | |
insert into tb select 15;(死锁) |
锁情况与上述相同,不再赘述,死锁信息如下:
LATEST DETECTED DEADLOCK ------------------------2018-11-03 17:28:30 0x7f4b0e667700*** (1) TRANSACTION: TRANSACTION 1055191450, ACTIVE 18 sec inserting mysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 316221, OS thread handle 139960338228992, query id 150467652 localhost root executing insert into tb select 16*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191450 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION 1055191449, ACTIVE 28 sec inserting, thread declared inside InnoDB 5000mysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 316222, OS thread handle 139960340870912, query id 150467681 localhost root executing insert into tb select 15*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION (2)
作者:偏执的工匠
链接:https://www.jianshu.com/p/f8495015dace
共同学习,写下你的评论
评论加载中...
作者其他优质文章