pt-osc又又出现死锁了

今天使用pt-osc修改mysql表结构，又出现死锁了，老大让尽量解决这个问题，我们先分析一下pt-osc容易出现死锁的原因，再来解决这个问题。

 

根据pt-osc打印的日志，可以看到pt-osc执行原理:
1. 创建一个跟原表表结构一样的新表；注意：这里的新表是一个临时表 _table_new
2. 修改新表的表结构；
3. 在原表中创建insert、update、delete这3个类型的触发器，用于增量数据的迁移；注意：触发器对新表的修改操作和原SQL语句在同一个事务中，insert\update对新表的操作采用replace into 操作
4. 会以一定块大小(chunk-size)从原表拷贝数据到新表中；注意：这里采用 insert low_priority ignore into _table_new select lock in share mode；的方式
5. 数据拷贝完成之后，rename表，整个过程为原子操作：原表重命名为old表，新表重命名为原表；
6. 删除old表(默认);
7. pt-osc操作完成。

 事务的执行过程及锁情况如下所示：

 

 

 

 

 如上，死锁出现。

 

解决办法：

1. 设置pt-osc的chunk-size为更小的值，将 Transaction2 中id >= and id <= 的区块变小，减少死锁发生概率。

2. 将 innodb_autoinc_lock_mode 设置为 2， 该参数为 0 时, 对于所有类型的操作，auto_inc 获取表级锁, 在语句执行完成后释放；该参数为1时，针对 insert into select ...., auto_inc 锁为特殊的表级锁，在语句执行完成后释放, 对于在插入时知道插入条数的简单插入操作，auto_inc 锁在申请完成后立即释放；该参数为2时，auto_inc锁在申请完成后就释放，不会等待语句执行完成之后, 不过该参数修改需要重启mysql服务。

3. 使用gh-ost替代pt-osc工具

 

使用 gh-ost相比pt-osc好处:

1. 使用pt-osc在针对表创建触发器时需要获取MDL锁，同时触发器不可中断，另外触发器生成语句和原语句在同一个事务中，容易出现死锁问题。

2. 当pt-osc执行中断，临时表会被立即删除，但是触发器并不会被删除，这时会造成表阻塞。