背景
今天看到叶老师公众号推送文章《pt-osc在线重建表导致死锁的分析及对应的优化方案》，让我想起了前段时间同样遇到了pt-osc改表导致的死锁。故此记录一下。
pt-osc
pt-online-schema-change：PERCONA提供的在线改表工具，避免MySQL在执行改表操作时造成的锁表和主从延迟情况发生。
工作原理
1、创建一个跟原表表结构一样的新表。取名为_oldTableNmae_new
2、修改新表结构
3、在原表中创建insert、update、delete三个类型的触发器，用于做增量数据迁移。原表SQL和触发器触发SQL在同一事务当中。
4、以一定块大小(chunk-size)从原表拷贝数据到新表
5、数据拷贝完后，修改表名：rename table to table_old; rename _table_new to table
6、删除old表，删除三个触发器
版本变化
3.0.2之前的update触发器：1
REPLACE INTO `lc`.`_hb_new` (`id`, `ts`, `ts2`, `c1`) VALUES (NEW.`id`, NEW.`ts`, NEW.`ts2`, NEW.`c1`)
3.0.2之后的update触发器:1
2
3
4BEGIN
DELETE IGNORE FROM `lc`.`_hb_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `lc`.`_hb_new`.`id` <=> OLD.`id`;
REPLACE INTO `lc`.`_hb_new` (`id`, `ts`, `ts2`) VALUES (NEW.`id`, NEW.`ts`, NEW.`ts2`);
END
死锁案例一
pt-osc改表操作与业务insert语句形成死锁，业务insert操作回滚。
环境
MySQL5.7.26、RC隔离级别、innodb_autoinc_lock_mode=1
表结构：1
2
3
4
5
6
7CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`c1` int(11) NOT NULL DEFAULT '0' COMMENT 'c1',
`c2` int(11) NOT NULL DEFAULT '0' COMMENT 'c2'
`c3` int(11) NOT NULL DEFAULT '0' COMMENT 'c3'
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='';
死锁日志
1 | 2020-04-26T06:24:05.340343+08:00 733947 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. |
死锁分析
事务：9187734821
2
3
4
5
6
7
8
9
10
11
12
13
14
15当前的SQL语句:
INSERT LOW_PRIORITY IGNORE INTO `test_db`.`_t_new` (`id`, `c1`, `c2`, `c3`) SELECT `id`, `c1`, `c2`, `c3` FROM
`test_db`.`t` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '95439963')) AND ((`id` <= '95448404')) LOCK IN SHARE MODE;
持有的锁信息：
TABLE LOCK table `test_db`.`_t_new` ... lock mode AUTO-INC --表示持有表_t_new上的自增长锁;
在等待的锁信息:
index PRIMARY of table `test_db`.`t` --表示在等的是表t的主键索引上面的锁;
lock_mode S locks rec but not gap waiting --表示需要加一个共享锁(读锁)，当前的状态是等待中;
0: len 4; hex 85b06d55; asc mU;; --主键字段, 5b06d55的16进制为转换为10进制得到值为: 95448405;
通过分析得知：
TRANSACTION 918773482持有的锁: 表_t_new的自增长锁;
TRANSACTION 918773482在等待TRANSACTION 918773485的锁: 表t的主键索引primary: record lock: id=95448405。
事务：9187734851
2
3
4
5
6
7
8
9
10
11
12当前的SQL语句:
REPLACE INTO `test_db`.`_t_new` (`id`, `c1`, `c2`, `c3`) VALUES (NEW.`id`, NEW.`c1`, NEW.`c2`, NEW.`c3`);
持有的锁信息:
根据TRANSACTION 918773482在等待TRANSACTION 918773485的锁为 primary: record lock: id=95448405 的行锁，所以推导出TRANSACTION 918773485持有表t主键索引 id=95448405 的行锁;
在等待的锁信息:
TABLE LOCK table `test_db`.`_t_new` ... lock mode AUTO-INC waiting --表示在等表_t_new上的自增长锁;
通过分析得知：
TRANSACTION 918773485持有的锁：持有表t主键索引primary id=95448405 的行锁；
TRANSACTION 918773485在等待TRANSACTION 918773482的锁: 表_t_new上的自增长锁。
| 时间点 | 918773482 pt-osc改表批量导入语句 |
918773485918773485 业务正常插入操作 |
|---|---|---|
| begin | begin | |
| INSERT LOW_PRIORITY IGNORE INTO _t_new (id, c1, c2, c3) SELECT id, c1, c2, c3 FROM test_db.t FORCE INDEX(PRIMARY) WHERE ((id >= ‘95439963’)) AND ((id <= ‘95448404’)) LOCK IN SHARE MODE; | ||
| T1 | 持有的锁: 表_t_new: AUTO-INC | |
| T2 | INSERT INTO t (c1, c2, c3) VALUES (0, 0, 0); 持有表t主键索引 id=95448405 的行锁(排他X锁) |
|
| T3 | REPLACE INTO _t_new (id, c1, c2, c3) VALUES (NEW.id, NEW.c1, NEW.c2, NEW.c3); 等待的锁: 表_t_new: AUTO-INC |
|
| T4 | 等待的锁: 表t: primary: record lock: id=95448405 |
T3被T1阻塞，T4被T2阻塞，因此锁资源请求形成了环路，进而触发死锁检测，MySQL会把执行代价最小的事务回滚掉，让其它事务得以继续进行;
思考
- 为什么pt-osc会去申请原表t的主键锁呢？
因为 id <= 95448404 是范围等值查询并且id=95448404是当前主键索引的最大值 ， 锁的过程实际上是id<=95448404的下一条记录也就是这个索引页的最大记录supremum（如果这个在RC隔离级别下没有被锁，则会立即释放），需要访问到 id=95448405 才会停止下来，所以需要申请 持有 id=95448405 的行锁 ，因此被 T2时刻 的SQL语句阻塞。
TRANSACTION 918773482的事务语句是pt-osc拷贝的最后一个chunk-size，并且期间其它事务有对原表做insert操作， 所以才会发生死锁。
死锁案例二
pt-osc改表操作与业务update语句形成死锁，业务update操作回滚。
环境
阿里云MySQL5.6、RC隔离级别
表结构：1
2
3
4
5
6
7
8CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`c1` int(11) NOT NULL DEFAULT '0' COMMENT 'c1',
`c2` int(11) NOT NULL DEFAULT '0' COMMENT 'c2'
`c3` int(11) NOT NULL DEFAULT '0' COMMENT 'c3'
PRIMARY KEY (`id`),
UNIQUE KEY `idx_c1_c2(`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='';
死锁日志
1 | 2020-05-11 15:04:22 7f728ebff700 |
死锁分析
事务：5825274161
2
3
4
5
6
7
8
9
10
11
12
13原业务语句：
UPDATE t SET c3 = 'xx' WHERE c1 = 'xx' AND c2 = 'xx' LIMIT 1
经过触发器改写后语句：
begin
UPDATE t SET c3 = 'xx' WHERE c1 = 'xx' AND c2 = 'xx' LIMIT 1
DELETE IGNORE FROM `test`.`_t_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_t_new`.`id` <=> OLD.`id`
REPLACE INTO `test`.`_t_new` (`id`, `c1`, `c2`, `c3`) VALUES (NEW.`id`, NEW.`c1`, NEW.`c2`, NEW.`c3`);
end
锁分析：
当前持有 lock mode AUTO-INC 锁（表级别）
由于存在唯一索引，所以在插入的时候会进行唯一性检测，此时需要获取 next-key lock
事务：TRANSACTION 5825274181
2
3
4
5
6
7
8
9
10
11
12原业务语句：
UPDATE t SET c3 = 'xx' WHERE c1 = 'xx' AND c2 = 'xx' LIMIT 1
经过触发器改写后语句：
begin
UPDATE t SET c3 = 'xx' WHERE c1 = 'xx' AND c2 = 'xx' LIMIT 1
DELETE IGNORE FROM `test`.`_t_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test`.`_t_new`.`id` <=> OLD.`id`
REPLACE INTO `test`.`_t_new` (`id`, `c1`, `c2`, `c3`) VALUES (NEW.`id`, NEW.`c1`, NEW.`c2`, NEW.`c3`);
end
锁分析：
持有记录的x锁和插入意向锁 等待_t_new表级别的auto-inc 锁
| 时间点 | sesson 1 582527416 |
session 2 582527418 |
|---|---|---|
| begin | begin | |
| T1 | REPLACE INTO test._t_new (id, c1, c2, c3) VALUES (NEW.id, NEW.c1, NEW.c2, NEW.c3); 插入前检查，唯一键上的插入意向锁 |
|
| T2 | REPLACE INTO test._t_new (id, c1, c2, c3) VALUES (NEW.id, NEW.c1, NEW.c2, NEW.c3);插入前检查，唯一键上的插入意向锁 |
|
| T3 | 执行插入，拥有_t_new的auto-inc锁，等待唯一键插入意向锁释放 | |
| T4 | 执行插入，等待_t_new的auto-inc锁 |
思考
- 为什么平时业务同样的update，但是只有在改表时才会触发死锁呢？
因为改表过程中，是增量将数据导入，此时还未导入到update所操作的id上。业务update执行时，触发器转换后的语句在执行时，会申请_t_new表的最大值之间的auto-inc锁。当业务并发大时可能就会造成业务update语句之间的死锁情况。
如何避免
- 设置pt-osc的chunk-size为更小的值，可以减少死锁的发生，但是不可能避免死锁的发生。
- 如果参数innodb_autoinc_lock_mode的值为2，大大降低死锁发生的概率，原因如下：
造成本案例死锁的原因之一就是在参数innodb_autoinc_lock_mode=1的环境下，持有的自增锁直到SQL语句结束后才释放；
如果参数innodb_autoinc_lock_mode=2，自增锁在申请后就释放，不需要等语句结束，大大缩短了持有自增锁的时间，从而降低了死锁发生的概率。 - 数据库版本为MySQL 8.0.18或者以上， 事务隔离为RR可重复读则不会出现本案例的死锁，原因如下：
8.0.18或者以上的版本中，对加锁规则有一个优化：在RR可重复读级别下，唯一索引上的范围查询，不再需要访问到不满足条件的第一个值为止（即不再需要对不必要的数据上锁）。在叶老师的这篇文章中有说明：https://mp.weixin.qq.com/s/xDKKuIvVgFNiKp5kt2NIgA InnoDB这个将近20年的”bug”修复了；
https://mp.weixin.qq.com/s/mJ6a-sV2C2ru5pA5QNAAZQ
https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html