DB事故(死锁、锁表等)

两个小工具，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 `PRIMARY` of 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 `PRIMARY` of 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  反光的小鱼儿  阅读(895)  评论(0)    收藏  举报