MySQL Auto Increment -- 自增锁模式与pt-osc

死锁场景

MySQL版本： 5.6.28-76.1-log Percona Server (GPL)

自增原子锁参数： innodb_autoinc_lock_mode = 1

使用pt-osc修改表，运行中频繁死锁导致pt-osc异常退出。

死锁信息

*** TRANSACTION:
TRANSACTION 482192229889, ACTIVE 0 sec setting auto-inc lock, thread declared inside InnoDB 5000
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 2
MySQL thread id 315469000, OS thread handle 0x7f55f6b1d700, query id 86743214374 10.68.85.219 wap_mobstat_w update
REPLACE INTO `mobiledcs_ad`.`_advertise_idfa_202208_new` (`id`, `ad_name`, `date`, `active_time`, `push_time`, `app_id`, `idfa`, `caid`, `ip`, `ua`, `model`, `mac`, `uid`, `status`, `extend`, `callback_url`, `callback_content`, `ad_priority`, `other_info`, `update_time`) VALUES (NEW.`id`, NEW.`ad_name`, NEW.`date`, NEW.`active_time`, NEW.`push_time`, NEW.`app_id`, NEW.`idfa`, NEW.`caid`, NEW.`ip`, NEW.`ua`, NEW.`model`, NEW.`mac`, NEW.`uid`, NEW.`status`, NEW.`extend`, NEW.`callback_url`, NEW.`callback_content`, NEW.`ad_priority`, NEW.`other_info`, NEW.`update_time`)
*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `mobiledcs_ad`.`_advertise_idfa_202208_new` trx id 482192229889 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
2022-08-17 18:03:07 7f54722dd700InnoDB: transactions deadlock detected, dumping detailed information.
2022-08-17 18:03:07 7f54722dd700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION

*** TRANSACTION:
TRANSACTION 482192229472, ACTIVE 3 sec setting auto-inc lock, thread declared inside InnoDB 5000
mysql tables in use 2, locked 2
3 lock struct(s), heap size 360, 0 row lock(s), undo log entries 2
MySQL thread id 315469131, OS thread handle 0x7f54722dd700, query id 86743213480 10.66.71.69 wap_mobstat_w update
REPLACE INTO `mobiledcs_ad`.`_advertise_idfa_202208_new` (`id`, `ad_name`, `date`, `active_time`, `push_time`, `app_id`, `idfa`, `caid`, `ip`, `ua`, `model`, `mac`, `uid`, `status`, `extend`, `callback_url`, `callback_content`, `ad_priority`, `other_info`, `update_time`) VALUES (NEW.`id`, NEW.`ad_name`, NEW.`date`, NEW.`active_time`, NEW.`push_time`, NEW.`app_id`, NEW.`idfa`, NEW.`caid`, NEW.`ip`, NEW.`ua`, NEW.`model`, NEW.`mac`, NEW.`uid`, NEW.`status`, NEW.`extend`, NEW.`callback_url`, NEW.`callback_content`, NEW.`ad_priority`, NEW.`other_info`, NEW.`update_time`)
*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `mobiledcs_ad`.`_advertise_idfa_202208_new` trx id 482192229472 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
2022-08-17 18:03:07 7f55f6b5f700InnoDB: transactions deadlock detected, dumping detailed information.
2022-08-17 18:03:07 7f55f6b5f700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION