背景：线上的一个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 lockmysql tables in use 2, locked 2LOCK WAIT 4 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 2MySQL thread id 35165134485, query id 636648490142 10.47.132.26 zhs_writer updateREPLACE 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 499mysql tables in use 2, locked 246 lock struct(s), heap size 6960, 2483 row lock(s), undo log entries 2418MySQL thread id 35165133950, query id 636648489679 10.47.109.162 dba_account Sending dataINSERT 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 waitingRecord 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锁 就产生了死锁
解决办法：减小从老表到新表数据的颗粒度（待续）
转载于:https://www.cnblogs.com/DBA-tomzhao/p/8351163.html
