I was looking into the source code for pt-online-schema-change here: https://github.com/percona/percona-toolkit/blob/3.x/bin/pt-online-schema-change#L11417). I noticed that the After Update trigger is doing a REPLACE INTO when there are updates on the source table that need to copy into the new table.
Was there any particular reason to use REPLACE INTO vs other options like Insert On Duplicate Key Update? Reason I ask is I have a table with a primary + unique key, and I’ve noticed that when there’s a match on the primary + unique key, the REPLACE acquires a gap lock on the unique key index record. This in turn leads to some lock contention/deadlocks. (similar to the issue here:
While executing pt-osc i can see lot of deadlock errors)
When I switched to using Insert On Duplicate Key update, it seems to avoid the gap lock as long as it finds a row to update with a matching primary key. For example, I have the following test table:
CREATE TABLE `locktest_copy` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` int(11) DEFAULT NULL, `string` varchar(15) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `value_UNIQUE` (`value`) ) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8 COLLATE=utf8_bin mysql> select id, value from locktest_copy; +----+-------+ | id | value | +----+-------+ | 1 | 1 | | 2 | 3 | | 3 | 5 | | 23 | 10 | +----+-------+ 7 rows in set (0.00 sec)
When I issue the following statement in a transaction (using the Read committed isolation level), I don’t see any gap locks because I’m able to insert into the gap before and after the record
Insert into locktest_copy (id, value, string) values (3,5, ‘update’) on duplicate key update id = values(id), value = values(value), string = values(string);
Is there a way to override the AFTER UPDATE trigger to use something other than Replace INTO? If so, would it be advisable? Or is there some scenario I’m not thinking of in which the Replace INTO is actually necessary.