两个小工具,MySQL死锁分析

Insert into select语句引发的生产事故

死锁相关命令

show full processlist

通过show full processlist查看死锁进程(如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程),然后可以kill id杀死进程

SHOW ENGINE INNODB STATUS

可以通过SHOW ENGINE INNODB STATUS;来查看死锁日志

多个事务并发执行update出现的数据库死锁问题排除

可以通过show full processlist查看死锁进程(如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程),然后可以kill id杀死进程

可以通过SHOW ENGINE INNODB STATUS;来查看死锁日志

死锁日志:

** (1) TRANSACTION:
TRANSACTION 6648945293, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 5 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 7953966, OS thread handle 0x7f5b58350700, query id 8422437535 10.129.128.237 promcenter Searching rows for update
update pc_coupon_0200
        set
        used_time = null,
        gmt_modified = '2017-08-31 00:00:00.841',
        status = 'NOT_USED',
        trade_no = null
        where
        code = '4ab5bf23-d09e-4947-8e83-4e6619c1f750'
        and user_id = 29096550200
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 285 page no 33715 n bits 152 index `PRIMARYof table `promcenter`.`pc_coupon_0200` trx id 6648945293 lock_mode X locks rec but not gap waiting
Record lock, heap no 79 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
 0: len 8; hex 8000000000192b58; asc       +X;;
 1: len 6; hex 000188c2bcfa; asc       ;;
 2: len 7; hex 1e00001dd70680; asc        ;;
 3: len 8; hex 80000000000009e2; asc         ;;
 4: len 28; hex e696b0e4babae4b893e4baab35e58583e697a0e997a8e6a79be588b8; asc             5               ;;
 5: len 8; hex 80000006c64a1f38; asc      J 8;;
 6: SQL NULL;
 7: SQL NULL;
 8: SQL NULL;
 9: len 30; hex 30626430373532632d636638662d346264352d383961302d373064313266; asc 0bd0752c-cf8f-4bd5-89a0-70d12f; (total 36 bytes);
 10: SQL NULL;
 11: len 4; hex 55534544; asc USED;;
 12: len 5; hex 999d6e0a7b; asc   n {;;
 13: len 5; hex 999d795e39; asc   y^9;;
 14: len 5; hex 999d6e0a7b; asc   n {;;
 15: len 5; hex 999d8d7efb; asc    ~ ;;
 16: len 5; hex 999d6e0a7b; asc   n {;;
 17: len 5; hex 999d795e39; asc   y^9;;
 18: len 30; hex 66646635363862312d663036632d346232382d383935382d636463316433; asc fdf568b1-f06c-4b28-8958-cdc1d3; (total 36 bytes);
 19: len 7; hex 74726964656e74; asc trident;;
 20: len 27; hex 75736572696e766974653a32393039363535303230303a32353330; asc userinvite:29096550200:2530;;
 21: len 3; hex 414c4c; asc ALL;;
*** (2) TRANSACTION:
TRANSACTION 6648945294, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 7953138, OS thread handle 0x7f5b3e8b7700, query id 8422437534 10.129.129.119 promcenter Searching rows for update
update pc_coupon_0200
        set
        used_time = null,
        gmt_modified = '2017-08-31 00:00:00.841',
        status = 'NOT_USED',
        trade_no = null
        where
        code = '0bd0752c-cf8f-4bd5-89a0-70d12fd26dd3'
        and user_id = 29096550200
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 285 page no 33715 n bits 152 index `PRIMARYof table `promcenter`.`pc_coupon_0200` trx id 6648945294 lock_mode X locks rec but not gap
Record lock, heap no 79 PHYSICAL RECORD: n_fields 22; compact format; info bits 0
 0: len 8; hex 8000000000192b58; asc       +X;;
 1: len 6; hex 000188c2bcfa; asc       ;;
 2: len 7; hex 1e00001dd70680; asc        ;;
 3: len 8; hex 80000000000009e2; asc         ;;
 4: len 28; hex e696b0e4babae4b893e4baab35e58583e697a0e997a8e6a79be588b8; asc             5               ;;
 5: len 8; hex 80000006c64a1f38; asc      J 8;;
 6: SQL NULL;
 7: SQL NULL;
 8: SQL NULL;
 9: len 30; hex 30626430373532632d636638662d346264352d383961302d373064313266; asc 0bd0752c-cf8f-4bd5-89a0-70d12f; (total 36 bytes);
 10: SQL NULL;
 11: len 4; hex 55534544; asc USED;;
 12: len 5; hex 999d6e0a7b; asc   n {;;
 13: len 5; hex 999d795e39; asc   y^9;;
 14: len 5; hex 999d6e0a7b; asc   n {;;
 15: len 5; hex 999d8d7efb; asc    ~ ;;
 16: len 5; hex 999d6e0a7b; asc   n {;;
 17: len 5; hex 999d795e39; asc   y^9;;
 18: len 30; hex 66646635363862312d663036632d346232382d383935382d636463316433; asc fdf568b1-f06c-4b28-8958-cdc1d3; (total 36 bytes);
 19: len 7; hex 74726964656e74; asc trident;;
 20: len 27; hex 75736572696e766974653a32393039363535303230303a32353330; asc userinvite:29096550200:2530;;
 21: len 3; hex 414c4c; asc ALL;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 285 page no 33489 n bits 792 index `ix_user_id` of table `promcenter`.`pc_coupon_0200` trx id 6648945294 lock_mode X locks rec but not gap waiting
Record lock, heap no 342 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 80000006c64a1f38; asc      J 8;;
 1: len 8; hex 8000000000192b58; asc       +X;;
*** WE ROLL BACK TRANSACTION (2)
 

mysql 事务具有acid属性,分别是代表原子性,隔离性,一致性,持久性。

根据原子性可以知道事务操作是不可再分的,每个事务要么全部成功要么全部失败,通过隔离性可以知道:事务之间不会相互影响。

但是本次为什么还出现了并发事务出现了死锁问题呢?

 

根据死锁日志可以提取几个有用信息:

 
trx1(事务1)
trx2(事务2)
id 6648945293 6648945294
特征 活跃0秒,正在index读 活跃0秒,正在index读
原因 Searching rows for update Searching rows for update
sql

update pc_coupon_0200
set
used_time = null,
gmt_modified = '2017-08-31 00:00:00.841',
status = 'NOT_USED',
trade_no = null
where
code = '4ab5bf23-d09e-4947-8e83-4e6619c1f750'
and user_id = 29096550200

update pc_coupon_0200
set
used_time = null,
gmt_modified = '2017-08-31 00:00:00.841',
status = 'NOT_USED',
trade_no = null
where
code = '0bd0752c-cf8f-4bd5-89a0-70d12fd26dd3'
and user_id = 29096550200

锁定行数 4 3

正在等待的锁特性/

正在持有锁特性

等待:{聚集索引id:285,页码:33715,锁类型:排他锁,非GAP(间隙)锁,

事务号:6648945293,索引:主键索引}

 

持有:idx_user_id的锁

持有:{锁id:285,页码:33715,锁类型:共享锁,非GAP(间隙)锁,

事务号:6648945293,索引:主键索引}

等待:

{聚集索引id:285,页码:33489,锁类型:排他锁,非GAP(间隙)锁,

事务号:6648945293,索引:idx_user_id}

是否回滚 否   是

首先mysql默认的隔离级别是可重复读,事务未提交之前总是读到相同的记录,该隔离级别就是为了避免读已提交出现的幻读现象,采用的是GAP间隙锁实现。

根据上表可以得到信息,两个事务都未提交,或者说行锁锁定的记录之外没有其他事务提交的与之有关的记录,所以都未用到gap锁,有点绕。。。

根据日志可以发现update语句其实就是select xxx for update,这个语句会持有排他锁(共享锁是in share mode)。

事务1等待排他锁,事务2持有事务1的共享锁,并且等待排他锁。这样就能死锁了??为什么事务1没有持有事务2的共享锁

mysql官方有个bug帖子,如下:

https://bugs.mysql.com/bug.php?id=77209

建议:

Do not use index merge when single index is good enough
Try to avoid using index merge in UPDATE to not provoke deadlocks

 

所以在写sql的时候能用一个索引尽量不要使用两个混合索引去更新,可以先根据索引查询出结果,再执行更新。

 

另:

解决方案

  1. 使用组合索引(a+b)
  2. 关闭index merge优化器

mysql innodb行级锁的前提条件是建立索引,行级锁并不是直接锁记录,而是锁索引。

a先根据code锁住了index然后锁住了primary key,然后根据user_id取锁定对应的index

b同时根据user_id锁住了index然后取锁定primary key,再去锁code的index和primary key

当a根据code锁定住primary key的时候,b锁定了user_id的index

这时候a没办法锁定user_id,b没办法锁定primary key->导致死锁

字符串与整数之间的强制类型转换,行锁升级为表锁

环境准备:事务隔离级别RR

set session autocommit=0;

create table t (
id int(20) primary key AUTO_INCREMENT,
cell varchar(20) unique
)engine=innodb;

事务数据:
Session A:
start transaction;

insert into t(cell)values(44444444444);  [1]
update t set cell=123 where cell=44444444444; [3] 

Session B:
start transaction;

insert into t(cell) values(55555555555); [2] 
update t set cell=456 where cell=55555555555; [4]

[1][2][3][4]为执行时序

现象:

insert into t(cell)values(44444444444);  [1]
事务A插入数据,最先执行

结果:插入成功

insert into t(cell) values(55555555555); [2]
事务B插入数据,第二执行

结果:插入成果

update t set cell=123 where cell=44444444444; [3]
事务A修改[1]中插入的数据,第三执行

结果:阻塞,等待执行结果

update t set cell=456 where cell=55555555555; [4]
事务B修改[2]中插入的数据,最后执行

结果:
(1)事务B死锁,事务B被回滚;
(2)事务A中,[3]语句阻塞结束,执行成功;

结果分析:

两个事务,各自修改自己插入的数据,却产生了死锁,确实诡异。

分析见原文。

结论:

整数强制转换为字符串,导致update因为没有命中索引,导致对每一行都要加锁。并发时,互相等待对方insert时的行锁而导致dead lock。

Insert into select语句锁表

事故原因、现象

由于数据数据库中order_today数据量过大,当时好像有700W了并且每天在以30W的速度增加。所以上司命令xxx将order_today内的部分数据迁移到order_record中,并将order_today中的数据删除。这样来降低order_today表中的数据量。
由于考虑到会占用数据库I/O,为了不影响业务,计划是9:00以后开始迁移,但是xxx在8:00的时候,尝试迁移了少部分数据(1000条),觉得没啥问题,就开始考虑大批量迁移。

现象:

在迁移的过程中,应急群是先反应有小部分用户出现支付失败,随后反应大批用户出现支付失败的情况,以及初始化订单失败的情况,同时腾讯也开始报警。

以为停止迁移就就可以恢复了,但是并没有。

订单表sql:

CREATE TABLE `order_today` (  
`id` varchar(32) NOT NULL COMMENT '主键',  
`merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商户编号',  
`amount` decimal(15,2) NOT NULL COMMENT '订单金额',  
`pay_success_time` datetime NOT NULL COMMENT '支付成功时间',  
`order_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付状态  S:支付成功、F:订单支付失败',  
`remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',  
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间 -- 修改时自动更新',  
PRIMARY KEY (`id`) USING BTREE,  KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商户编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE order_record like order_today;

迁移sql:INSERT INTO order_record SELECT    * FROM    order_today WHERE    pay_success_time < '2020-03-08 00:00:00';

在navicat中运行迁移的sql,同时开另个一个窗口插入数据,模拟下单。

从上面可以发现一开始能正常插入,但是后面突然就卡住了,并且耗费了23s才成功,然后才能继续插入。这个时候已经迁移成功了,所以能正常插入了。

出现的原因

在默认的事务隔离级别下:insert into order_record select * from order_today 加锁规则是:order_record表锁,order_today逐步锁(扫描一个锁一个)。

分析执行过程。

 

 

通过观察迁移sql的执行情况你会发现order_today是全表扫描,也就意味着在执行insert into select from 语句时,mysql会从上到下扫描order_today内的记录并且加锁,这样一来不就和直接锁表是一样了。

  这也就可以解释,为什么一开始只有少量用户出现支付失败,后续大量用户出现支付失败,初始化订单失败等情况,因为一开始只锁定了少部分数据,没有被锁定的数据还是可以正常被修改为正常状态。由于锁定的数据越来越多,就导致出现了大量支付失败。最后全部锁住,导致无法插入订单,而出现初始化订单失败。

解决方案

由于查询条件会导致order_today全表扫描,什么能避免全表扫描呢,很简单嘛,给pay_success_time字段添加一个idx_pay_suc_time索引就可以了,由于走索引查询,就不会出现扫描全表的情况而锁表了,只会锁定符合条件的记录。

sql: INSERT INTO order_record SELECT * FROM

    order_today FORCE INDEX (idx_pay_suc_time)
WHERE
    pay_success_time <= '2020-03-08 00:00:00';

执行过程:

 

posted on 2019-07-26 20:28  反光的小鱼儿  阅读(873)  评论(0编辑  收藏  举报