Description:
In a master-slave replication scenario, when master executes XA transactions with replace into statements, replication may fail with lock wait timeout.
The following conditions are required to cause the replication failure:
(1) A table with unique key.
(2) XA transactions.
(3) Replace into statements in XA transactions cause the replacement of existing unique key records.
(4) The records replaced by replace into statements have not been purged due to a high workload or the need to read the replaced records by other readviews.
When a replace into statement encounters an existing duplicate unique key record, it locks the next record of this unique key record even the next record is delete-marked and has not been purged yet.
Assume a, b, c are the consecutive unique key records of a table. Replication error happens when:
(1) Session1 deletes unique key 'b' and commits the transaction;
(2) Unique key 'b' is not purged on master for some reason(noted above);
(3) The delete statement is replicated to slave and executed by slave;
(4) Unique key 'b' is purged by slave;
(5) Session1 executes an XA transaction(called XA1) and replaces unique key 'a', causing the lock of the delete-marked unique key 'b'. Then session1 prepares the XA transaction. Note that unique key 'c' is not locked by the transaction because it is not the next record of 'a';
(6) Session2 executes an XA transaction(called XA2) and replaces unique key 'c'.
(7) The two XA transactions are replicated to slave. Since 'b' is purged on slave, 'c' is the next record of 'a'. So XA1 locks unique key 'c', XA2 will never get the lock of 'c'. Replication fails.
How to repeat:
Testcase:
(1) master opt file:
issue_rpl_lock_wait_timeout_when_replace_into_and_xa_transactions-master.opt
--gtid_mode=ON
--enforce_gtid_consistency=ON
--binlog_format=row
--binlog_transaction_dependency_tracking=COMMIT_ORDER
(2) slave opt file:
issue_rpl_lock_wait_timeout_when_replace_into_and_xa_transactions-slave.opt
--gtid_mode=ON
--enforce_gtid_consistency=ON
--binlog_format=row
--binlog_transaction_dependency_tracking=COMMIT_ORDER
--innodb_lock_wait_timeout=3
--slave_transaction_retries=0
--slave_parallel_workers=0
--innodb_status_output_locks=ON
(3) testcase file
issue_rpl_lock_wait_timeout_when_replace_into_and_xa_transactions.test
# Replication fails with lock wait timeout in the following case.
-- source include/master-slave.inc
--echo # 1. Prepare
--let $rpl_connection_name= master
--source include/rpl_connection.inc
select @@global.binlog_transaction_dependency_tracking;
CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT,
k VARCHAR(20),
c VARCHAR(20),
PRIMARY KEY(id),
UNIQUE KEY uk(k)
) ENGINE=InnoDB;
INSERT INTO t VALUES(1, 'aaa', 'aaaaa');
INSERT INTO t VALUES(2, 'bbb', 'bbbbb');
INSERT INTO t VALUES(3, 'ccc', 'ccccc');
SELECT * FROM t;
CREATE USER user1;
GRANT SELECT, UPDATE, DELETE, INSERT ON test.* to user1;
--connect(conn1,localhost,user1)
--connect(conn2,localhost,user1)
--connect(conn3,localhost,user1)
--echo
--echo # 2. Start a tansaction and don't end it. This will prevent the purge thread from running.
--connection conn1
BEGIN;
SET SESSION TRANSACTION_ISOLATION = 'REPEATABLE-READ';
SELECT * FROM t;
--echo
--echo # 3. Delete the record of k = 'bbb'. It won't be purged by master but it will be replicated to slave and will be purged on slave.
--connection conn2
DELETE FROM t WHERE k = 'bbb';
--source include/rpl_sync.inc
--echo sleep 5 seconds to wait for slave to purge the deleted record.
--sleep 5
--echo
--echo # 4. Execute XA transactions and replace the records of k = 'aaa' and 'ccc'
--connection conn2
XA START 'XA1';
REPLACE INTO t values(1, 'aaa', 'xxxxx');
XA END 'XA1';
XA PREPARE 'XA1';
--connection conn3
XA START 'XA2';
REPLACE INTO t values(3, 'ccc', 'yyyyy');
XA END 'XA2';
XA PREPARE 'XA2';
--echo
--echo # 5. Replication error occurs.
--let $rpl_connection_name= master
--source include/rpl_connection.inc
--sleep 1
--let $rpl_connection_name= slave
--source include/rpl_connection.inc
select @@global.innodb_lock_wait_timeout;
--echo
--echo There is a lock wait in SHOW ENGINE INNODB STATUS.
query_vertical SHOW ENGINE INNODB STATUS;
--echo sleep 5 seconds to wait for lock wait timeout.
--sleep 5
--echo
--echo SHOW SLAVE STATUS shows lock wait timeout.
query_vertical SHOW SLAVE STATUS;
=======
Normally, the test result will show the replication error. If it doesn't, please run the testcase a couple of times, there is a possibility that the slave hasn't purged the deleted record in 5 seconds.
Suggested fix:
I think a replace into statement should release the lock of the next unique record of the replaced record. Maybe we should release the lock when at the end of XA PREPARE;
