MDEV-26682 Replication timeouts with XA PREPAREbb-10.5-MDEV-26682

The purpose of non-exclusive locks in a transaction is to guarantee
that the records covered by those locks must remain in that way until
the transaction is committed. (The purpose of gap locks is to ensure
that a record that was nonexistent will remain that way.)

Once a transaction has reached the XA PREPARE state, the only allowed
further actions are XA ROLLBACK or XA COMMIT. Therefore, it can be
argued that only the exclusive locks that the XA PREPARE transaction
is holding are essential.

Furthermore, InnoDB never preserved explicit locks across server restart.
For XA PREPARE transations, we will only recover implicit exclusive locks
for records that had been modified.

Because of the fact that XA PREPARE followed by a server restart will
cause some locks to be lost, we might as well always release all
non-exclusive locks during the execution of an XA PREPARE statement.

lock_release_on_prepare(): Release non-exclusive locks on XA PREPARE.

trx_prepare(): Invoke lock_release_on_prepare() unless the
isolation level is SERIALIZABLE or this is an internal distributed
transaction with the binlog (not actual XA PREPARE statement).

This has been discussed with Sergei Golubchik and Andrei Elkin.

Reviewed by: Sergei Golubchik

3 files changed, 201 insertions, 1 deletions

diff --git a/mysql-test/suite/rpl/r/rpl_xa.result b/mysql-test/suite/rpl/r/rpl_xa.result
index a90e6e0b996..061c7b360d0 100644
--- a/mysql-test/suite/rpl/r/rpl_xa.result
+++ b/mysql-test/suite/rpl/r/rpl_xa.result
@@ -219,4 +219,65 @@ include/sync_with_master_gtid.inc
 connection master;
 drop database test_ign;
 drop table t1, t2, t3, tm;
+#
+# MDEV-26682 slave lock timeout with XA and gap locks
+#
+create table t1 (a int primary key, b int unique) engine=innodb;
+insert t1 values (1,1),(3,3),(5,5);
+connection slave;
+set session tx_isolation='repeatable-read';
+start transaction;
+select * from t1;
+a	b
+1	1
+3	3
+5	5
+connect m2, localhost, root;
+delete from t1 where a=3;
+xa start 'x1';
+update t1 set b=3 where a=5;
+xa end 'x1';
+xa prepare 'x1';
+connect m3, localhost, root;
+insert t1 values (2, 2);
+-->slave
+connection slave;
+commit;
+select * from t1;
+a	b
+1	1
+2	2
+5	5
+connection m2;
+xa rollback 'x1';
+disconnect m2;
+disconnect m3;
+connection master;
+drop table t1;
+create table t1 (id int auto_increment primary key, c1 int not null unique)
+engine=innodb;
+create table t2 (id int auto_increment primary key, c1 int not null,
+foreign key(c1) references t1(c1), unique key(c1)) engine=innodb;
+insert t1 values (869,1), (871,3), (873,4), (872,5), (870,6), (877,7);
+insert t2 values (795,6), (800,7);
+xa start '1';
+update t2 set id = 9, c1 = 5 where c1 in (null, null, null, null, null, 7, 3);
+connect con1, localhost,root;
+xa start '2';
+delete from t1 where c1 like '3%';
+xa end '2';
+xa prepare '2';
+connection master;
+xa end '1';
+xa prepare '1';
+->slave
+connection slave;
+connection slave;
+include/sync_with_master_gtid.inc
+connection con1;
+xa commit '2';
+disconnect con1;
+connection master;
+xa commit '1';
+drop table t2, t1;
 include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result b/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result
index ffd0426ab0d..35625cc7026 100644
--- a/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result
+++ b/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result
@@ -228,6 +228,67 @@ include/sync_with_master_gtid.inc
 connection master;
 drop database test_ign;
 drop table t1, t2, t3, tm;
+#
+# MDEV-26682 slave lock timeout with XA and gap locks
+#
+create table t1 (a int primary key, b int unique) engine=innodb;
+insert t1 values (1,1),(3,3),(5,5);
+connection slave;
+set session tx_isolation='repeatable-read';
+start transaction;
+select * from t1;
+a	b
+1	1
+3	3
+5	5
+connect m2, localhost, root;
+delete from t1 where a=3;
+xa start 'x1';
+update t1 set b=3 where a=5;
+xa end 'x1';
+xa prepare 'x1';
+connect m3, localhost, root;
+insert t1 values (2, 2);
+-->slave
+connection slave;
+commit;
+select * from t1;
+a	b
+1	1
+2	2
+5	5
+connection m2;
+xa rollback 'x1';
+disconnect m2;
+disconnect m3;
+connection master;
+drop table t1;
+create table t1 (id int auto_increment primary key, c1 int not null unique)
+engine=innodb;
+create table t2 (id int auto_increment primary key, c1 int not null,
+foreign key(c1) references t1(c1), unique key(c1)) engine=innodb;
+insert t1 values (869,1), (871,3), (873,4), (872,5), (870,6), (877,7);
+insert t2 values (795,6), (800,7);
+xa start '1';
+update t2 set id = 9, c1 = 5 where c1 in (null, null, null, null, null, 7, 3);
+connect con1, localhost,root;
+xa start '2';
+delete from t1 where c1 like '3%';
+xa end '2';
+xa prepare '2';
+connection master;
+xa end '1';
+xa prepare '1';
+->slave
+connection slave;
+connection slave;
+include/sync_with_master_gtid.inc
+connection con1;
+xa commit '2';
+disconnect con1;
+connection master;
+xa commit '1';
+drop table t2, t1;
 connection slave;
 include/stop_slave.inc
 SET @@global.gtid_pos_auto_engines="";
diff --git a/mysql-test/suite/rpl/t/rpl_xa.inc b/mysql-test/suite/rpl/t/rpl_xa.inc
index 38344da5e66..d22d2d2ef3d 100644
--- a/mysql-test/suite/rpl/t/rpl_xa.inc
+++ b/mysql-test/suite/rpl/t/rpl_xa.inc
@@ -1,6 +1,6 @@
 #
 # This "body" file checks general properties of XA transaction replication
-# as of MDEV-7974.
+# as of MDEV-742.
 # Parameters:
 # --let rpl_xa_check= SELECT ...
 #
@@ -353,3 +353,81 @@ source include/sync_with_master_gtid.inc;
 connection master;
 --eval drop database test_ign
 drop table t1, t2, t3, tm;
+
+--echo #
+--echo # MDEV-26682 slave lock timeout with XA and gap locks
+--echo #
+create table t1 (a int primary key, b int unique) engine=innodb;
+insert t1 values (1,1),(3,3),(5,5);
+sync_slave_with_master;
+
+# set a strong isolation level to keep the read view below.
+# alternatively a long-running select can do that too even in read-committed
+set session tx_isolation='repeatable-read';
+start transaction;
+# opens a read view to disable purge on the slave
+select * from t1;
+
+connect m2, localhost, root;
+# now, delete a value, purge it on the master, but not on the slave
+delete from t1 where a=3;
+xa start 'x1';
+# this sets a gap lock on <3>, when it exists (so, on the slave)
+update t1 set b=3 where a=5;
+xa end 'x1';
+xa prepare 'x1';
+
+connect m3, localhost, root;
+# and this tries to insert straight into the locked gap
+insert t1 values (2, 2);
+
+echo -->slave;
+sync_slave_with_master;
+commit;
+select * from t1;
+
+connection m2;
+xa rollback 'x1';
+disconnect m2;
+disconnect m3;
+
+connection master;
+
+drop table t1;
+
+create table t1 (id int auto_increment primary key, c1 int not null unique)
+engine=innodb;
+
+create table t2 (id int auto_increment primary key, c1 int not null,
+foreign key(c1) references t1(c1), unique key(c1)) engine=innodb;
+
+insert t1 values (869,1), (871,3), (873,4), (872,5), (870,6), (877,7);
+insert t2 values (795,6), (800,7);
+
+xa start '1';
+update t2 set id = 9, c1 = 5 where c1 in (null, null, null, null, null, 7, 3);
+
+connect con1, localhost,root;
+xa start '2';
+delete from t1 where c1 like '3%';
+xa end '2';
+xa prepare '2';
+
+connection master;
+xa end '1';
+xa prepare '1';
+
+echo ->slave;
+
+sync_slave_with_master;
+
+connection slave;
+source include/sync_with_master_gtid.inc;
+
+connection con1;
+xa commit '2';
+disconnect con1;
+
+connection master;
+xa commit '1';
+drop table t2, t1;