背景：线上的一个3000W的表需要增加一个，表有20G大小。
晚上11点开始用pt-online-schema-change增加字段
pt-online-schema-change -h $IP -u $user -p $password -P $PORT --alter="$STATEMENT" --charset=utf8 --no-check-replication-filters --execute D=$DATABASE,t=$TABLE
到凌晨12点半左右增加完毕，但是报了一个死锁信息。
LATEST DETECTED DEADLOCK
------------------------
180125 0:08:28
*** (1) TRANSACTION:
TRANSACTION C591765D9, ACTIVE 0 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 35165134485, query id 636648490142 10.47.132.26 zhs_writer update
REPLACE INTO `DB`.`_Table_new` (`user_id`, `username`, `password`, `mobile`, `kid`, `maintype`, `subtype`, `user_type`, `enabled`, `device_uuid`, `register_time`, `first_login_time`, `last_login_time`, `sandbox`, `origin`, `is_biz`, `uid`, `is_del`) VALUES (NEW.`user_id`, NEW.`username`, NEW.`password`, NEW.`mobile`, NEW.`kid`, NEW.`maintype`, NEW.`subtype`, NEW.`user_type`, NEW.`enabled`, NEW.`device_uuid`, NEW.`register_time`, NEW.`first_login_time`, NEW.`last_login_time`, NEW.`sandbox`, NEW.`origin`, NEW.`is_biz`, NEW.`uid`, NEW.`is_del`)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `DB`.`_Table_new` trx id C591765D9 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION C591765D3, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 499
mysql tables in use 2, locked 2
46 lock struct(s), heap size 6960, 2483 row lock(s), undo log entries 2418
MySQL thread id 35165133950, query id 636648489679 10.47.109.162 dba_account Sending data
INSERT LOW_PRIORITY IGNORE INTO `DB`.`_Table_new` (`user_id`, `username`, `password`, `mobile`, `kid`, `maintype`, `subtype`, `user_type`, `enabled`, `device_uuid`, `register_time`, `first_login_time`, `last_login_time`, `sandbox`, `origin`, `is_biz`, `uid`, `is_del`) SELECT `user_id`, `username`, `password`, `mobile`, `kid`, `maintype`, `subtype`, `user_type`, `enabled`, `device_uuid`, `register_time`, `first_login_time`, `last_login_time`, `sandbox`, `origin`, `is_biz`, `uid`, `is_del` FROM `DB`.`Table` FORCE INDEX(`PRIMARY`) WHERE ((`user_id` >= '224531025')) AND ((`user_id` <= '224534530')) LOCK IN SHARE MODE /*pt-online-schema-change 15213 copy nibble*/
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `DB`.`_Table_new` trx id C591765D3 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2689 page no 723971 n bits 144 index `PRIMARY` of table `DB`.`Table` trx id C591765D3 lock mode S waiting
Record lock, heap no 47 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
0: len 4; hex 0d621bd6; asc b ;;
1: len 6; hex 000c591765d9; asc Y e ;;
2: len 7; hex 16000f00160bae; asc ;;
3: len 11; hex 3133363530323230393635; asc 13650220965;;
4: len 30; hex 663762363936323535643939653338306338623733623231303336643838; asc f7b696255d99e380c8b73b21036d88; (total 32 bytes);
5: len 11; hex 3133363530323230393635; asc 13650220965;;
6: len 0; hex ; asc ;;
7: len 1; hex 01; asc ;;
8: len 1; hex 80; asc ;;
9: len 1; hex 82; asc ;;
10: len 1; hex 01; asc ;;
11: len 16; hex 32363838313062346264633836303639; asc 268810b4bdc86069;;
12: len 8; hex 800012586de60fae; asc Xm ;;
13: len 8; hex 800012586de62386; asc Xm # ;;
14: len 8; hex 8000125a8d2ea47c; asc Z . |;;
15: len 1; hex 80; asc ;;
16: len 8; hex 77656978696e7971; asc weixinyq;;
17: len 1; hex 80; asc ;;
18: len 8; hex 8000000000000000; asc ;;
19: len 1; hex 80; asc ;;
*** WE ROLL BACK TRANSACTION (1)
当pt进行加字段的时候创建了三个触发器 而插入新表数据的时候是对原表加了S锁 当程序需要更新这个数据时候需要加一个X锁 就产生了死锁
解决办法：减小从老表到新表数据的颗粒度（待续）