Bug #95079 False duplicate key check
Submitted: 23 Apr 2019 2:32 Modified: 23 Apr 2019 13:00
Reporter: Song Libing Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7, 5.7.25 OS:Any
Assigned to: CPU Architecture:Any

[23 Apr 2019 2:32] Song Libing
Description:
In the case that a key doesn't exist in the leaf page but exist in the internal page of an index. INSERT thinks there is an existing record, so it will do duplicate check. That causes applier deadlock on slave. E.g.
XA BEGIN "xid1";
INSERT INTO t1 values(1,b);
XA END;
XA PREPAIR;

XA BEGIN "xid2";
// a is in the  internal pageļ¼Œ duplicate key 
// check will require a S lock on 'b'
INSERT INTO t1 values(1,a);
XA END;
XA PREPAIR;

XA COMMIT "xid1"; // it cannot be applied parallel with xid2.
XA COMMIT "xid2";

How to repeat:
run the test with --innodb-page-size=4k
====================================================
CREATE TABLE t1(c1 varchar(20) NOT NULL PRIMARY KEY,
                c2 varchar(20) not null,
                c3 varchar(100) not null,
                unique key(c2, c3)) ENGINE=InnoDB;

--let $i= 1000
while ($i < 1155)
{
  eval INSERT INTO t1 values("c1:$i", "c2:$i", repeat('b', 100));
  --inc $i
  --inc $i
  --inc $i
}

# c2:1048 is on the internal page of the unique index
DELETE FROM t1 WHERE c1 = "c1:1048";
DELETE FROM t1 WHERE c1 = "c1:1051";
INSERT INTO t1 VALUES("c1:1053", "c2:1053", repeat('b', 100));
INSERT INTO t1 VALUES("c1:1052", "c2:1052", repeat('b', 100));
INSERT INTO t1 VALUES("c1:1055", "c2:1055", repeat('b', 100));
FLUSH TABLES;

BEGIN;
INSERT INTO t1 VALUES("c1:1049", "c2:1049", repeat('b', 100));

--connect(con1,localhost,root)
--send INSERT INTO t1 VALUES("c1:1048", "c2:1048", repeat('b', 100))

--connect(con2,localhost,root)
sleep 1;
SELECT * FROM information_schema.innodb_locks;

--connection con1
--reap

--sleep 10000
[23 Apr 2019 13:00] Umesh Shastry
Hello Libing,

Thank you for the report and test case.

regards,
Umesh
[25 Apr 2019 9:21] Fungo Wang
Looks like 5.6 and 8.0 are also affected by this bug, at least for the versions I tried with(5.6.42 and 8.0.15).

BTW, I think the testcase is awesome :)