美文网首页
mysql生产环境死锁案例1

mysql生产环境死锁案例1

作者: GAOCHAO_DBA | 来源:发表于2017-10-17 09:39 被阅读0次

    实例基础信息：

    • 数据库版本：MariaDB10.0.27
    • 隔离级别：RR（Repeatable Read）
    • 表结构
     `report_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accessKey` varchar(50) NOT NULL COMMENT '数据来源',
  `insertTime` datetime NOT NULL COMMENT '数据插入时间',
  `readTime` datetime NOT NULL COMMENT '报表数据统计时间(业务端传入)',
  `fieldName` varchar(100) NOT NULL COMMENT '报表数据的key',
  `fieldType` varchar(100) NOT NULL COMMENT '报表数据的唯一值(字典表内有体现)',
  `balance` decimal(10,2) DEFAULT NULL COMMENT '余额(任意的)',
  `total` bigint(20) DEFAULT NULL COMMENT '总数(任意的)',
  `successNum` bigint(20) DEFAULT NULL COMMENT '成功条数(任意的)',
  `fail` bigint(20) DEFAULT NULL COMMENT '失败条数(任意的)',
  `successRatio` double DEFAULT NULL COMMENT '成功率(任意的)',
  `failRatio` double DEFAULT NULL COMMENT '失败率(任意的)',
  `cost` decimal(10,2) DEFAULT NULL COMMENT '费用(任意)',
  `ratio` double DEFAULT NULL COMMENT '比率(任意)',
  `value` varchar(200) DEFAULT NULL COMMENT '报表数据对应的value(扩展使用)',
  `onlyDate` datetime NOT NULL COMMENT '每小时存一次数据',
  `array` text,
  `srcChannel` varchar(20) DEFAULT '',
  `updateTime` datetime DEFAULT CURRENT_TIMESTAMP,
  `DataRange` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_report_detail_onlydate_fieldtype_srcf` (`fieldType`,`onlyDate`,`srcChannel`)
) ENGINE=InnoDB AUTO_INCREMENT=377337 DEFAULT CHARSET=utf8

    原始死锁信息

    现象：每天凌晨5点左右代码跑定时任务,数据库报死锁

    MariaDB [(none)]> show engine innodb status\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-10-17 05:07:03 7f16f19cd700

*** (1) TRANSACTION:
TRANSACTION 63532812, ACTIVE 2 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 19 lock struct(s), heap size 2936, 1463 row lock(s)
MySQL thread id 6405341, OS thread handle 0x7f16a2ca8700, query id 115074926 192.168.30.41 baobiao_rw updating

update report_detail set `value` = '53100.0000', onlyDate = '2017-10-16 23:59:00',array = null,updateTime= now(),DataRange= '2017-10-16 23:59:59'
where srcChannel = 'swqian' and readTime = '2017-10-16 23:59:00' and fieldType = 'JK_REAL_TIME_LOAN_MONEY_CURRENT_NEW'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 26 n bits 176 index `PRIMARY` of table `reportform`.`report_detail` trx table locks 1 total table locks 7  trx id 63532812 lock_mode X waiting lock hold time 0 wait time before grant 0

*** (2) TRANSACTION:
TRANSACTION 63532815, ACTIVE 1 sec fetching rows
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 6404346, OS thread handle 0x7f16f19cd700, query id 115074957 192.168.30.41 baobiao_rw Searching rows for update

update report_detail set `value` = '0', onlyDate = DATE_FORMAT('2017-10-12 00:00:00','%Y-%m-%d'),array = null,updateTime= now(),DataRange= '2017-10-12 05:07:00' 
where srcChannel = 'btdai' and readTime = '2017-10-12 00:00:00' and fieldType = 'JK_LC_TODAY_DAOQI_TODAY_HUANKAUN_MONEY'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 19 page no 26 n bits 176 index `PRIMARY` of table `reportform`.`report_detail` trx table locks 1 total table locks 7  trx id 63532815 lock_mode X locks rec but not gap lock
hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 26 n bits 176 index `PRIMARY` of table `reportform`.`report_detail` trx table locks 1 total table locks 7  trx id 63532815 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0

*** WE ROLL BACK TRANSACTION (2)

    死锁分析

    • 查看死锁SQL发现两个update更改同一张表的不同记录造成死锁，分析SQL如下：

    获取表记录数量

    MariaDB [reportform]> select count(*) from report_detail;
+----------+
| count(*) |
+----------+
|   366489 |
+----------+

    explain transaction1 sql

    MariaDB [reportform]> explain update report_detail set `value` = '53100.0000', onlyDate = '2017-10-16 23:59:00',array = null,updateTime= now(),DataRange= '2017-10-16 23:59:59' where srcChannel = 'swqian' and readTime = '2017-10-16 23:59:00' and fieldType = 'JK_REAL_TIME_LOAN_MONEY_CURRENT_NEW';
+------+-------------+---------------+-------+------------------------------------------+---------+---------+------+--------+-------------+
| id   | select_type | table         | type  | possible_keys                            | key     | key_len | ref  | rows   | Extra       |
+------+-------------+---------------+-------+------------------------------------------+---------+---------+------+--------+-------------+
|    1 | SIMPLE      | report_detail | index | uk_report_detail_onlydate_fieldtype_srcf | PRIMARY | 4       | NULL | 363020 | Using where |
+------+-------------+---------------+-------+------------------------------------------+---------+---------+------+--------+-------------+ 
MariaDB [reportform]> select count(*) from report_detail where srcChannel = 'swqian' and readTime = '2017-10-16 23:59:00' and fieldType = 'JK_REAL_TIME_LOAN_MONEY_CURRENT_NEW';
+----------+
| count(*) |
+----------+
|        1 |
+----------+

    explain transaction2 sql

    MariaDB [reportform]> explain update report_detail set `value` = '0', onlyDate = DATE_FORMAT('2017-10-12 00:00:00','%Y-%m-%d'),array = null,updateTime= now(),DataRange= '2017-10-12 05:07:00'  where srcChannel = 'btdai' and readTime = '2017-10-12 00:00:00' and fieldType = 'JK_LC_TODAY_DAOQI_TODAY_HUANKAUN_MONEY';
+------+-------------+---------------+-------+------------------------------------------+------------------------------------------+---------+------+------+---------------------------+
| id   | select_type | table         | type  | possible_keys                            | key                                      | key_len | ref  | rows | Extra                     |
+------+-------------+---------------+-------+------------------------------------------+------------------------------------------+---------+------+------+---------------------------+
|    1 | SIMPLE      | report_detail | range | uk_report_detail_onlydate_fieldtype_srcf | uk_report_detail_onlydate_fieldtype_srcf | 302     | NULL |  163 | Using where; Using buffer |
+------+-------------+---------------+-------+------------------------------------------+------------------------------------------+---------+------+------+---------------------------+
MariaDB [reportform]> select count(*) from report_detail where srcChannel = 'btdai' and readTime = '2017-10-12 00:00:00' and fieldType = 'JK_LC_TODAY_DAOQI_TODAY_HUANKAUN_MONEY';
+----------+
| count(*) |
+----------+
|        1 |
+----------+

    原始死锁第一次解决（采用第二种方案）

    • 分析SQL发现update只更改的是一条记录，但是影响行数确比较多，第一个事物影响更大即将接近表锁
    • 解决思路为降低更新语句的影响行数
      第一种方案：代码改写SQL
      1）先通过where条件求出主键id
      2）然后通过主键id进行更改
      优点：通过主键更新可以最大程度的减少影响行数
      缺点：需要更改代码，代码多处使用了该类SQL更改比较复杂
      第二种方案：给update语句where后面的条件创建多列索引
      优点：不用更改代码
      缺点：采用二级索引更新，虽然能减少影响行数，但是影响行数不能降低到最少
    • 执行第二种方案解决死锁步骤
      增加多列索引
    MariaDB [test]> alter table report_detail add index `idx_multi` (`fieldType`,`readTime`,`srcChannel`);

    查看增加索引后到表结构

    CREATE TABLE `report_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accessKey` varchar(50) NOT NULL COMMENT '数据来源',
  `insertTime` datetime NOT NULL COMMENT '数据插入时间',
  `readTime` datetime NOT NULL COMMENT '报表数据统计时间(业务端传入)',
  `fieldName` varchar(100) NOT NULL COMMENT '报表数据的key',
  `fieldType` varchar(100) NOT NULL COMMENT '报表数据的唯一值(字典表内有体现)',
  `balance` decimal(10,2) DEFAULT NULL COMMENT '余额(任意的)',
  `total` bigint(20) DEFAULT NULL COMMENT '总数(任意的)',
  `successNum` bigint(20) DEFAULT NULL COMMENT '成功条数(任意的)',
  `fail` bigint(20) DEFAULT NULL COMMENT '失败条数(任意的)',
  `successRatio` double DEFAULT NULL COMMENT '成功率(任意的)',
  `failRatio` double DEFAULT NULL COMMENT '失败率(任意的)',
  `cost` decimal(10,2) DEFAULT NULL COMMENT '费用(任意)',
  `ratio` double DEFAULT NULL COMMENT '比率(任意)',
  `value` varchar(200) DEFAULT NULL COMMENT '报表数据对应的value(扩展使用)',
  `onlyDate` datetime NOT NULL COMMENT '每小时存一次数据',
  `array` text,
  `srcChannel` varchar(20) DEFAULT '',
  `updateTime` datetime DEFAULT CURRENT_TIMESTAMP,
  `DataRange` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_report_detail_onlydate_fieldtype_srcf` (`fieldType`,`onlyDate`,`srcChannel`),
  KEY `idx_multi` (`fieldType`,`readTime`,`srcChannel`)
) ENGINE=InnoDB AUTO_INCREMENT=377337 DEFAULT CHARSET=utf8

    查看增加索引后更新语句影响行数
    explain transaction1 sql

    explain update report_detail set `value` = '53100.0000', onlyDate = '2017-10-16 23:59:00',array = null,updateTime= now(),DataRange= '2017-10-16 23:59:59' where srcChannel = 'swqian' and readTime = '2017-10-16 23:59:00' and fieldType = 'JK_REAL_TIME_LOAN_MONEY_CURRENT_NEW';
+------+-------------+---------------+-------+----------------------------------------------------+-----------+---------+------+------+-------------+
| id   | select_type | table         | type  | possible_keys                                      | key       | key_len | ref  | rows | Extra       |
+------+-------------+---------------+-------+----------------------------------------------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | report_detail | range | uk_report_detail_onlydate_fieldtype_srcf,idx_multi | idx_multi | 373     | NULL |    1 | Using where |
+------+-------------+---------------+-------+----------------------------------------------------+-----------+---------+------+------+-------------+

    explain transaction2 sql

    MariaDB [test]> explain update report_detail set `value` = '0', onlyDate = DATE_FORMAT('2017-10-12 00:00:00','%Y-%m-%d'),array = null,updateTime= now(),DataRange= '2017-10-12 05:07:00'  where srcChannel = 'btdai' and readTime = '2017-10-12 00:00:00' and fieldType = 'JK_LC_TODAY_DAOQI_TODAY_HUANKAUN_MONEY';
+------+-------------+---------------+-------+----------------------------------------------------+-----------+---------+------+------+-------------+
| id   | select_type | table         | type  | possible_keys                                      | key       | key_len | ref  | rows | Extra       |
+------+-------------+---------------+-------+----------------------------------------------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | report_detail | range | uk_report_detail_onlydate_fieldtype_srcf,idx_multi | idx_multi | 373     | NULL |    1 | Using where |
+------+-------------+---------------+-------+----------------------------------------------------+-----------+---------+------+------+-------------+

    观察死锁效果

    • 执行该方案后连续3天没有出现死锁
    • 第4天开始出现同一张表的另外一种死锁，该死锁也是定时任务，为每小时执行一次，死锁索引信息里包含第一次解决方案创建的多列索引idx_multi

    采用第二种方案解决原始死锁导致的新的死锁信息

    现象：每个小时的第15分钟跑定时任务造成死锁

    MariaDB [pt_tool]> select * from deadlocks\G
*************************** 99. row ***************************
   server: xxx.xxx.xxx.xxx
       ts: 2017-10-16 21:15:01
   thread: 6343102
   txn_id: 0
 txn_time: 1
     user: xxx
 hostname:
       ip: xxx.xxx.xxx.xxx
       db: reportform
      tbl: report_detail
      idx: idx_multi
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 0
    query: insert into report_detail(accessKey,insertTime,readTime,fieldName,fieldType,balance, total,successNum,fail,successRatio,failRatio,cost,ratio,value,onlyDate,array,srcChannel,updateTime,DataRange) values ('ZTViZGRiN2Q2OTJlZTQw', now(), '2017-10-09 20:00:00','?????????????????????', 'JK_PASS_HOUR_LOAN_SUC_MONEY_HKYH',null,null,null, null,null,null,null, null,'0',DATE_FORMAT('2017-10-09 20:00:00', '%Y-%m-%d %H:00:00'), null,'ycfu' , now(), '2017-10-09 20:59:59') ON DUPLICATE KEY UPDATE readTime=VALUES(readTime),balance=VALUES(balance),total=VALUES(total), successNum=VALUES(successNum),fail=VALUES(fail),successRatio=VALUES(successRatio),failRatio=VALUES(failRatio), cost=VALUES(cost),ratio=VALUES(ratio),value=VALUES(value),array=VALUES(array),updateTime=NOW()
*************************** 100. row ***************************
   server: xxx.xxx.xxx.xxx
       ts: 2017-10-16 21:15:01
   thread: 6343106
   txn_id: 0
 txn_time: 0
     user: xxx
 hostname:
       ip: xxx.xxx.xxx.xxx
       db: reportform
      tbl: report_detail
      idx: idx_multi
lock_type: RECORD
lock_mode: X
wait_hold: w
   victim: 1
    query: insert into report_detail(accessKey,insertTime,readTime,fieldName,fieldType,balance, total,successNum,fail,successRatio,failRatio,cost,ratio,value,onlyDate,array,srcChannel,updateTime,DataRange) values ('MWUxMzcwMzFmZWFlNTFh', now(), '2017-10-09 20:00:00','?????????????????????', 'JK_PASS_HOUR_LOAN_SUC_MONEY_HKYH',null,null,null, null,null,null,null, null,'0',DATE_FORMAT('2017-10-09 20:00:00', '%Y-%m-%d %H:00:00'), null,'swqian' , now(), '2017-10-09 20:59:59') ON DUPLICATE KEY UPDATE readTime=VALUES(readTime),balance=VALUES(balance),total=VALUES(total), successNum=VALUES(successNum),fail=VALUES(fail),successRatio=VALUES(successRatio),failRatio=VALUES(failRatio), cost=VALUES(cost),ratio=VALUES(ratio),value=VALUES(value),array=VALUES(array),updateTime=NOW()
    • 分析发现SQL使用到了新建的idx_multi索引，会不会是这个索引造成的？
    • 删除为解决原始死锁而新建的idx_multi索引,该类死锁消失，原始死锁再次出现

    原始死锁第二次解决

    • 分析：
      第一次解决死锁是增加的二级索引，二级索引扫描的行数要比较多，可能是造成新死锁的原因，分析update死锁SQL发现where后面的条件满足唯一索引，如果将二级索引改成唯一索引是否会避免出现新的死锁

    查看后三列是否能确定唯一值
    1）开发人员看代码（代码作者已离职）
    2）DBA查询表中数据

    MariaDB [test]> select count(*) as c from report_detail group by fieldType,readTime,srcChannel having c>1 ;
Empty set (0.21 sec)

    增加多列唯一索引

    alter table report_detail add UNIQUE KEY uk_multi(`fieldType`,`readTime`,`srcChannel`);

    explain transaction1 sql

    MariaDB [test]> explain update report_detail set `value` = '0', onlyDate = '2017-10-12 00:00:00',array = null,updateTime= now(),DataRange= '2017-10-12 05:07:00'  where srcChannel = 'btdai' and readTime = '2017-10-12 00:00:00' and fieldType = 'JK_LC_TODAY_DAOQI_TODAY_HUANKAUN_MONEY';
+------+-------------+---------------+-------+---------------------------------------------------+----------+---------+------+------+-------------+
| id   | select_type | table         | type  | possible_keys                                     | key      | key_len | ref  | rows | Extra       |
+------+-------------+---------------+-------+---------------------------------------------------+----------+---------+------+------+-------------+
|    1 | SIMPLE      | report_detail | range | uk_report_detail_onlydate_fieldtype_srcf,uk_multi | uk_multi | 373     | NULL |    1 | Using where |
+------+-------------+---------------+-------+---------------------------------------------------+----------+---------+------+------+-------------+

    explain transaction2 sql

    MariaDB [test]> explain update report_detail set `value` = '0', onlyDate = DATE_FORMAT('2017-10-12 00:00:00','%Y-%m-%d'),array = null,updateTime= now(),DataRange= '2017-10-12 05:07:00'  where srcChannel = 'btdai' and readTime = '2017-10-12 00:00:00' and fieldType = 'JK_LC_TODAY_DAOQI_TODAY_HUANKAUN_MONEY';
+------+-------------+---------------+-------+---------------------------------------------------+----------+---------+------+------+-------------+
| id   | select_type | table         | type  | possible_keys                                     | key      | key_len | ref  | rows | Extra       |
+------+-------------+---------------+-------+---------------------------------------------------+----------+---------+------+------+-------------+
|    1 | SIMPLE      | report_detail | range | uk_report_detail_onlydate_fieldtype_srcf,uk_multi | uk_multi | 373     | NULL |    1 | Using where |
+------+-------------+---------------+-------+---------------------------------------------------+----------+---------+------+------+-------------+

    观察死锁效果

    相关文章

      网友评论

          本文标题：mysql生产环境死锁案例1

          本文链接：https://www.haomeiwen.com/subject/kayruxtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|mysql生产环境死锁案例1|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！