pt-osc改表导致数据不一致案例分析

 

2016-06-10 李丹 dba流浪猫

我们平时除了解决自己问题外，有时候也会协助圈内人士，进行一些故障排查，此案例就是帮某公司DBA进行的故障分析，因为比较典型，特分享一下，但仅仅是分享发生的过程，不对该案例的产生以及如何避免做过多评价！

 

pt-online-schema-change：是对大表进行在线alter操作，并尽量避免影响线上业务，这是最优秀的mysql管理工作之一，在平时的工作中，帮助我们胜多。

环境说明

 

             pt-osc 版本：percona-toolkit-2.2.14

              mysql版本： percona-server-5.5

              数据库架构：双主复制（本次pt-osc改表是在未在线主库上执行的）

问题描述

某天接到圈内朋友求助，反馈使用pt-online-schema-change 添加字段却意外产生了死锁的情况，并且数据可能有问题了，哥们百思不得骑姐希望我能帮忙分析分析。不过因线上环境没法测试复现，因此只给了死锁发生时的引擎日志（执行 SHOW ENGINE  innodb STATUS 查看）。      

我们来看看当时存储引擎的日志情况，这里为了方便只截取了事务相关日志，其他日志信息略过，具体日志如下：

TRANSACTION1

 

*** (1) TRANSACTION: 

TRANSACTION 107BF2CDD, ACTIVE 1 sec setting auto-inc lock

mysql tables in use 2, locked 2

LOCK WAIT 4 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 2

MySQL thread id 6, OS thread handle 0x7fd210190700, query id 1080843123 Reading event from the relay log 

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 

TABLE LOCK table `redcliff`.`_rider_new` trx id 107BF2CDD lock mode AUTO-INC waiting 

这里我们能读懂两个信息：

1：事务是从relaylog 读取日志                                                      2：事务1(事务id为107BF2CDD）正在等待_rider_new表AUTO-INC锁

TRANSACTION2

 

*** (2) TRANSACTION:

TRANSACTION 107BF2CDC, ACTIVE 1 sec fetching rows

mysql tables in use 2, locked 2

253 lock struct(s), heap size 31160, 10864 row lock(s), undo log entries 10616

MySQL thread id 22433333, OS thread handle 0x7fc781b16700, query id 1080843120 127.0.0.1 dwbdba_mgr Sending data

INSERT LOW_PRIORITY IGNORE INTO `redcliff`.`_rider_new` 

************************************（省略中）

    `frozen_provision`, `bloc…. LOCK IN SHARE MODE  /*pt-online-schema-change 18153 copy nibble*/

*** (2) HOLDS THE LOCK(S): 

TABLE LOCK table `redcliff`.`_rider_new` trx id 107BF2CDC lock mode AUTO-INC 

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 

RECORD LOCKS space id 636 page no 4599 n bits 112 index `PRIMARY` of table 

`redcliff`.`rider` trx id 107BF2CDC lock mode S waiting 

*** WE ROLL BACK TRANSACTION (1) 

我们能读懂如下信息：

1、事务2（事务id 为107BF2CDC）持有表_rider_new 的auto-inc 自增锁 

2、事务2等待rider表S锁                                             

3、pt-osc工具通过LOCK IN SHARE MODE来实现读取当前读取之后还需要保证其他并发事务不能修改当前读取的记录，确保数据的新老数据的100%一致，故要对读取记录加S锁

通过上面读懂的信息我们分析如下：

 

事务1

1、Reading event from the relay log 要执行对rider表的修改

（这里事后通过解析relaylog确认是对rider表进行的修改）

     故持有rider表记录上的x锁

 2、等待_rider_new 表上的auto-inc lock

（注：pt-osc工具对表进行修改会在表上创建增删改三个触发器。故rider表上有已有三个触发器，并且对rider表的update,insert操作触发器触发后会转换为_rider_new表上的replace 操作，在有自增id的表上replace操作会产生新的自增id值）

事务2

1、INSERT LOW_PRIORITY IGNORE INTO `redcliff`.`_rider_new` (`id`, `city_id`,

该语句需要往_rider_new表批量写入数据，这里已经持有 _rider_new 表上的auto-inc lock， 从上面的分析可以看到事务需要等待rider 表上的共享读锁!

 

 

删繁就简

 

 

事务一：

            持有：rider表记录上的x锁

            等待：rider_new 表上的auto-inc lock

事务二：

            持有：_rider_new 表上的auto-inc lock

            等待：rider 表上的S锁

完美的死锁

 

 

最后回滚事务1（即复制更新操作被回滚，主从数据不一致）

 

我的观点

在以上的分析中，我们得出，pt-osc工具在某些情况下，可能会因为死锁回滚而导致数据的不一致，根据原理，我们无法避免，只能尽量缓解（例如： --chunk-size参数设置的更小，或者在TPS极大的线上不使用pt-osc），在mysql online ddl 发展尚不完善的情况下，相信当前mysql DBA 线上使用的改表工具主流还是pt-online-schema-change ，所以希望通过本次的分享让大家少踩点坑，早回家睡会好觉。