pt-osc在线修改表发生死锁问题分析及思考
2017-06-22 10:37 Kevin.hhl 阅读(3363) 评论(0) 收藏 举报前提背景描述：
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 lockmysql tables in use 2, locked 2LOCK WAIT 6 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1LOCK BLOCKING MySQL thread id: 14850 block 27862MySQL thread id 27862, OS thread handle 0x7f21b37ff700, query id 16149004 10.25.129.0 xx_rw updateREPLACE 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 rowsmysql tables in use 2, locked 2150 lock struct(s), heap size 13864, 5114 row lock(s), undo log entries 2016MySQL thread id 14850, OS thread handle 0x7f21283be700, query id 16148991 10.46.161.106 xx_rw Sending dataINSERT 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 locks rec but not gap waitingRecord 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 = 4041COMMIT/*!*/; |
浙公网安备 33010602011771号