pt-osc在线修改表发生死锁问题分析及思考

原创黄华亮 ACMUG

ACMUG征集原创技术文章。详情请添加 A_CMUG或者扫描文末二维码关注我们的微信公众号。有奖征稿，请发送稿件至：acmug@acmug.com。

3306现金有奖征稿说明：知识无价，劳动有偿，ACMUG特约撰稿人有奖回报计划（修订版）

黄华亮，曾就职于光宇游戏、京东金融，现快看漫画数据库团队负责人，主要负责快看MySQL、Redis、Codis、MongoDB运维，数据库优化、数据库中间件、数据库自动化平台。

前提背景描述：

MySQL 版本: MySQL community 5.6.16

pt-online-schema-change 2.2.20

问题描述：使用pt-online-schema-change 修改字段varchar(N) N变长。

死锁日志如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-06-19 21:20:18 7f21283be700
*** (1) TRANSACTION:
TRANSACTION 14674152324, ACTIVE 0.162 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
LOCK BLOCKING MySQL thread id: 14850 block 27862
MySQL thread id 27862, OS thread handle 0x7f21b37ff700, query id 16149004 10.25.129.0 xx_rw update
REPLACE INTO `xx`.`_users_profile_new` (`id`, 省略若干字段) VALUES (NEW.`id`,省略若干字段）
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `xx`.`_users_profile_new` trx id 14674152324 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 14674152323, ACTIVE 0.162 sec fetching rows
mysql tables in use 2, locked 2
150 lock struct(s), heap size 13864, 5114 row lock(s), undo log entries 2016
MySQL thread id 14850, OS thread handle 0x7f21283be700, query id 16148991 10.46.161.106 xx_rw Sending data
INSERT LOW_PRIORITY IGNORE INTO `xx`.`_users_profile_new` (`id`, `changed_time`,省略若干字段 ) SELECT `id`, `changed_time`,省略若干字段)
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `xx`.`_users_profile_new` trx id 14674152323 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 519 page no 637613 n bits 112 index `PRIMARY` of table `xx`.`users_profile` trx id 14674152323 lock mode S lo
cks rec but not gap waiting
Record lock, heap no 41 PHYSICAL RECORD: n_fields 37; compact format; info bits 0
0: len 4; hex 80dd6fa7; asc   o ;;
1: len 6; hex 00036aa5cb84; asc   j   ;;
2: len 7; hex 13000100180e20; asc        ;;
3: len 8; hex 999ce6d51205a4d2; asc         ;;
4: len 9; hex e591a8e6b0b8e99fa9; asc          ;;
5: len 18; hex 353131353233313939373038323831373338; asc 511523199708281738;;
6: len 6; hex e59b9be5b79d; asc       ;;
7: len 6; hex e5ae9ce5aebe; asc       ;;
8: len 30; hex e59b9be5b79de79c81e6b19fe5ae89e58ebfe5ba95e893ace5ae89e99587; asc                               ;;
9: len 4; hex 80000006; asc     ;;
10: len 21; hex 32343831343234345f676972666c476c61417a5f61; asc 24814244_girflGlaAz_a;
11: len 21; hex 32343831343234345f37426e7a426c336837585f63; asc 24814244_7BnzBl3h7X_c;;
12: len 19; hex 36323137393936373130303036393437393237; asc 6217996710006947927;;
13: len 24; hex e4b8ade59bbde982aee694bfe582a8e89384e993b6e8a18c; asc                         ;;
14: len 30; hex 5b7b2250686f6e65223a223138393930393331343038222c224e616d6522; asc [{"Phone":"18990931408","Name"; (total 185 bytes);
15: len 6; hex e58cbbe7949f; asc       ;;
16: len 6; hex e58cbbe999a2; asc       ;;
17: len 27; hex e68890e983bde5b882e9be99e6b389e9a9bfe4b8ade58cbbe999a2; asc                            ;;
18: len 6; hex e68890e983bd; asc       ;;
19: len 11; hex 3133353431313536303631; asc 13541156061;;
20: len 23; hex e998b3e58589e59f8ee5b9b8e7a68fe8b7af3237e58fb7; asc                   27   ;
21: len 4; hex 817aa2a4; asc z ;;
22: len 6; hex e59b9be5b79d; asc       ;;
23: len 6; hex e5ae9ce5aebe; asc       ;;
24: len 11; hex 3133353431313536303631; asc 13541156061;;
25: len 6; hex e59b9be5b79d; asc       ;;
26: len 4; hex 80000000; asc     ;;
27: SQL NULL;
28: len 4; hex 80000000; asc     ;;
29: len 0; hex ; asc ;;
30: len 0; hex ; asc ;;
31: len 0; hex ; asc ;;
32: len 0; hex ; asc ;;
33: len 4; hex 80000000; asc     ;;
34: len 9; hex e9be99e6b389e9a9bf; asc          ;;
35: len 21; hex 32343831343234345f69714d793635413578545f62; asc 24814244_iqMy65A5xT_b;;
36: len 6; hex e6b19fe5ae89; asc       ;;
*** WE ROLL BACK TRANSACTION (1)

就此对上面的死锁日志分析2个并发事务如下：

事务1:

   持有原表：users_profile 某行或多行的X锁， 等待新表：_users_profile_new 的auto_inc 锁。

事务2：

   持有新表：_users_profile_new 的auto_inc 锁， 等待原表：users_profile 多行的S锁。

从上面看出两个事务执行到此满足死锁条件，MySQL最终回滚事务1。

下面描述2个并发事务：

事务1:是pt-osc 建立的触发器，原表的更新后触发触发器把数新的更新替换到新表(在同一个事务里面)，可以表示如下：

begin;

update users_profile set ... ;

REPLACE INTO `xx`.`_users_profile_new`

死锁发生

rollback;

事务2:是pt-osc 批量从原表查询(select ＊ from 原表 where id>=x1 and id<x2 lock in shared mode )后插入数据到新表，可以表示如下：

insert into _new select * from _old where ... lock in share mode ;

思考问题：

1.使用pt-osc 如何减少死锁的发生？

根据pt-osc 原理，不能完全避免死锁有产生，但是可以减少，在业务低峰使用pt-osc，chunk_size 设置的更小。

2.发生死锁会不会导致数据不一致？

不会造成数据不一致。如果发生死锁会导致原表的更新会丢失，就是事务被回滚了，对应用不友好。

3.会不会导致主从数据不一致？

binlog_format=ROW 不会导致主从数据不一致。

binlog_format=statement 因触发器在slave 也会被触发，如果主上没有死锁，从库上发生了死锁，有可能导致主从数据不一致。

证明原sql和触发器触发的sql在同一个事务里：

结论：从binlog 看触发器触发的sql 和原sql在同一事务里面。

# at 131663
#170621 18:08:07 server id 249623306 end_log_pos 131796 CRC32 0xf7f6676e       Write_rows: table id 119 flags: STMT_END_F

### UPDATE `xx`.`users_profile`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @10='urlagrr' /* VARSTRING(112) meta=112 nullable=0 is_null=0 */

### DELETE FROM `xx`.`_users_profile_new`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
### INSERT INTO `xx`.`_users_profile_new`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2017-04-24 11:07:08.000000' /* DATETIME(6) meta=6 nullable=0 is_null=0 */
###   @3='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @4=NULL /* VARSTRING(80) meta=72 nullable=1 is_null=1 */
###   @5='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @6='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @7='' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
###   @8=0 /* INT meta=0 nullable=0 is_null=0 */
###   @9='' /* VARSTRING(112) meta=112 nullable=0 is_null=0 */
###   @10='urlagrr' /* VARSTRING(112) meta=112 nullable=0 is_null=0 */
###   @11='' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @12='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @13='' /* VARSTRING(800) meta=800 nullable=0 is_null=0 */
###   @14='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @15='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @16='' /* VARSTRING(160) meta=160 nullable=0 is_null=0 */
###   @17='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @18='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @19='' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
###   @20=2 /* INT meta=0 nullable=0 is_null=0 */
###   @21='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @22='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 *
###   @23='special_xx' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @24='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @25=2 /* INT meta=0 nullable=0 is_null=0 */
###   @26='2017-04-24 11:07:08.000000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
###   @27=10 /* INT meta=0 nullable=0 is_null=0 */
###   @28='' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
###   @29='' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @30='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @31='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @32=3 /* INT meta=0 nullable=0 is_null=0 */
###   @33='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
###   @34='' /* VARSTRING(112) meta=112 nullable=0 is_null=0 */
###   @35='' /* VARSTRING(80) meta=80 nullable=0 is_null=0 */
# at 131796

#170621 18:08:12 server id 249623306 end_log_pos 131827 CRC32 0xd6ed36bc       Xid = 4041
COMMIT/*!*/;

注：ACMUG收录技术文章版权属于原作者本人所有。如有疑问，请联系作者。

看完转发，手留余香。关注我们，一起进步。

关注ACMUG公众号，参与社区活动，交流开源技术，分享学习心得，一起共同进步。

继续滑动看下一个