Pt-online-schema-change: Alternatives to "REPLACE INTO"

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hello,

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.

2

Read-Committed does not use gap locks. That’s why you are not seeing any. Gap locks are present on Repeatable-Read isolation level, or R-C with foreign keys.
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Also, INSERT … ON DUPLICATE KEY UPDATE would require more verbose SQL since you would be duplicating every data point, once for the VALUES() and once for the SET=. By using REPLACE, you can use simpler syntax and achieve the same end-result.

Is your entire MySQL server in READ-COMMITTED? If not, you can use --set-vars option on pt-osc to set its connections to R-C.

3

Thanks for the info.

Just to clarify, even though I wouldn’t see any gap locks with Insert On duplicate key update, I would see gap locks with a REPLACE INTO (also in Read committed). For example, given the above table, and the following 2 transactions:

Transaction 1 - Replace into with value = 5

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
start transaction;
replace into instance_1000020.locktest_copy (id, value, string) values (3,5, 'replace'); // succeeds

Transaction 2 - Insert into gap before value = 5

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
start transaction;
insert into instance_1000020.locktest_copy (value, string) values (4, 'insertInGap'); // this blocks

Show engine innodb status:

------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12593 page no 4 n bits 88 index value_UNIQUE of table `instance_1000020`.`locktest_copy` trx id 4479642 lock_mode X locks gap before rec insert intention waiting
------------------
TABLE LOCK table `instance_1000020`.`locktest_copy` trx id 4479642 lock mode IX
RECORD LOCKS space id 12593 page no 4 n bits 88 index value_UNIQUE of table `instance_1000020`.`locktest_copy` trx id 4479642 lock_mode X locks gap before rec insert intention waiting
---TRANSACTION 4479310, ACTIVE 441 sec
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 6573, OS thread handle 140408504698624, query id 1011119 localhost root starting
show engine innodb status
TABLE LOCK table `instance_1000020`.`locktest_copy` trx id 4479310 lock mode IX
RECORD LOCKS space id 12593 page no 3 n bits 104 index PRIMARY of table `instance_1000020`.`locktest_copy` trx id 4479310 lock_mode X locks rec but not gap
RECORD LOCKS space id 12593 page no 4 n bits 88 index value_UNIQUE of table `instance_1000020`.`locktest_copy` trx id 4479310 lock_mode X locks rec but not gap
RECORD LOCKS space id 12593 page no 4 n bits 88 index value_UNIQUE of table `instance_1000020`.`locktest_copy` trx id 4479310 lock_mode X

So it seems like even though the REPLACE INTO is touching an index record of value=5, it still somehow blocks Transaction 2 when the latter tries to put an Insert Intention lock into the gap before the record. When I switch Transaction 1 to use Insert On Duplicate Key Update, Transaction 2 is no longer blocked.

For the Read Committed isolation level, I see this documentation that extends what you were saying: “Gap locking is only used for foreign-key constraint checking and duplicate-key checking.” However, according to this link (https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html), it sounds like Insert On Duplicate Key Update may restrict gap locks even further -

INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. **An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.**

Correct me if I’m wrong, but this could explain why I’m not seeing Gap locks for the Insert On Duplicate Key Update. Since I’m doing an insert where it finds a matching primary key, it forgoes the next-key lock. However, the documentation for REPLACE doesn’t have any qualifier regarding the locks it takes, so it could gap lock even if it finds a matching primary key row to delete/insert.

Other than Replace being less verbose, are there any other benefits to Replace which you know of? If not, it might be good to look into changing the trigger or providing an option for people to override the trigger (at their own risk, of course). Especially for cases like mine where the Insert on duplicate key would reduce lock contention while still ultimately hitting the same result.

4

@matthewb , just wanted to follow up and see if you knew more about the benefits of replace into vs insert on duplicate key update. Googling around, I haven’t been able to find any other than the fact that Replace Into is less verbose.