advice on pt-online-schema-change--cause dead lock
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
New
|
Undecided
|
Unassigned |
Bug Description
I am a DBA @China,
in online ddl,for update,the trigger is
my $update_trigger
= "CREATE TRIGGER `${prefix}_upd` AFTER UPDATE ON $orig_tbl->{name} "
. "FOR EACH ROW "
. "BEGIN "
. "DELETE IGNORE FROM $new_tbl->{name} WHERE !($upd_index_cols) AND $del_index_cols;"
. "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals);"
. "END ";
if there is a unique key at second index,sush as
CREATE TABLE `myitem` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
`mer_item_no` bigint(20) unsigned NOT NULL DEFAULT '0' , PRIMARY KEY (`id`),
UNIQUE KEY `uni_merchandis
it can cause gap lock,why not change to just
insert into on duplicate key update
no delete from,
then no gap lock ,since gap lock can cause many deadlock
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/ /jira.percona. com/browse/ PT-1427