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
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