retry TX on a deadlock

[ghost]

1992dd6c

[ghost]

Changes in MySQL 5.7.26

InnoDB: Two sessions concurrently executing an INSERT ... ON DUPLICATE KEY UPDATE operation generated a deadlock. During partial rollback of a tuple, another session could update it. The fix for this bug reverts fixes for Bug #11758237, Bug #17604730, and Bug #20040791. (Bug #25966845)

[ghost]

create schema if not exists deadlock2;
create table deadlock2.t (
  id int primary key auto_increment,
  val1 int,
  val2 int,
  unique key `uk_val` (val1, val2)
);
-- 注: unique key含null值时, 允许重复.

-- session 1.1
begin;
replace into deadlock2.t values (0, NULL, 1);

-- session 2
begin;
replace into deadlock2.t values (0, NULL, 1);

-- session 1.2
replace into deadlock2.t values (0, NULL, 1);
-- 此时session 2发生deadlock

[asiroliu]

使用 DTLE 9.9.9.9-master-1103402 ， MySQL 5.7.25， MTS 32, job 使用默认值"retry_tx_limit": 3

CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, val1 INT, val2 INT, UNIQUE KEY uk_val (val1, val2));

源端连续执行1000个TX， 其中60% 会触发死锁的SQL： begin; replace into t1 values (0, NULL, 1); replace into t1 values (0, NULL, 1); commit;

另外40%的SQL语句为： begin; replace into t1 values (0, NULL, 1); commit; 此场景可以保证数据一致性。

当触发死锁的SQL比例高于60%时，会到时job失败，数据不一致。