From 2388d49eb4e1c177ec7c65bbe4e68e82b012604b Mon Sep 17 00:00:00 2001 From: Venkatesh Prasad Venugopal Date: Tue, 29 Sep 2020 10:41:09 +0530 Subject: [PATCH] MTS deadlock with slave_preserve_commit_order - 8.0 This fixes the below mentioned bugs. https://bugs.mysql.com/bug.php?id=87796 https://bugs.mysql.com/bug.php?id=89247 https://bugs.mysql.com/bug.php?id=95249 https://bugs.mysql.com/bug.php?id=99440 Background ---------- In general, if a replication applier thread fails to execute a transaction because of an InnoDB deadlock or because the transaction's execution time exceeded InnoDB's innodb_lock_wait_timeout, it automatically retries slave_transaction_retries times before stopping with an error. And when --slave_preserve_commit_order is enabled, the replica server ensures that transactions are externalized on the replica in the same order as they appear in the replica's relay log, and prevents gaps in the sequence of transactions that have been executed from the relay log. If a thread's execution is completed before its preceding thread, then the executing thread waits until all previous transactions are committed before committing. Problem ------- When --slave_preserve_commit_order is enabled on slave and if the waiting thread has locked the rows which are needed by the thread executing the previous transaction(as per their order in the relay log), then the innodb deadlock detection algorithm detects the deadlock between workers and will ask the waiting thread to rollback (only if its sequence number is lesser than that of the waiting thread). When this happens, the waiting thread wakes up from the cond_wait(SPCO) and it gets to know that it was asked to rollback by its preceding transaction as it was holding a lock that is needed by the other transaction to progress. It then rolls back its transaction so that the the preceding transaction can be committed and retries the transaction. The above logic sometimes caused the worker thread to miss the signals resulting in the replica server to hang. One of such hang is mentioned below. Consider a replica server which is configured with slave_parallel_workers=3, slave_parallel_type=LOGICAL_CLOCK, slave_preserve_commit_order=1 and slave_transaction_retries=0. When MTS is enabled, it is quite possible that workers execute out of order causing the below state. Worker 1 - Processing the events of Transaction T1 Worker 2 - Executed Transaction T2 and is waiting for T1 to commit. Worker 3 - Processing the events of Transaction T3 1. If T1 and T2 are modifying same rows in InnodB, then the worker 1 detects deadlock and asks worker 2 to rollback by signalling. 2. Worker 2 wakes up from the cond_wait. It gets to know that it was asked to roll back by the other transaction and returns with an error. 3. Worker 2 rolls back the transaction and comes to the retry part of the code and checks the value of slave_transaction_retries. Since it is 0, it returns from the handle_slave_worker loop and enters the error handling part. 4. As part of error handling, Worker 2 notifies the co-ordinator that it is exiting, and then calls report_rollback() function to unregister itself from the SPCO queue. 5. While executing report_rollback(), Worker 2 will again enter wait_for_its_turn(). But before entering the wait, it checks the commit_order_deadlock flag. Since the flag is already set, Worker2 immediately returns from the function with error. 6. Co-ordinator thread gets this information and sets the rli->abort_slave=1 to stop replication and waits till all workers exit. 7. Worker 2 exits. There is no worker 2 from here onwards. Now the status is, Worker 1 - Processing the events of Transaction T1 Worker 2 - Not running. Worker 3 - Processing the events of Transaction T3 8. Now the worker 1 proceeds and executes the transaction and enters the Commit_order_manager::wait_for_its_turn. 9. Worker 1 finds out that the previous worker(Worker 2) failed because of an error. 10. Worker 1 signals next transaction/worker to proceed. 11. Worker 3 executes the transaction and enters the Commit_order_manager::wait_for_its_turn. 12. Worker 1 rolls back the transaction and eventually exits. 13. There will be no one to signal Worker 3 and thus waits forever. This resulted in a system hang as the co-ordinator thread will be waiting for the worker thread to finish and the worker thread will be waiting for the signal to proceed with the commit. mysql> show processlist; +----+-------------+-----------------+------+---------+------+-----------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+-------------+-----------------+------+---------+------+-----------------------------------------+ | 2 | root | localhost:55708 | test | Query | 0 | starting | show processlist | 0 | 0 | | 3 | system user | | NULL | Connect | 107 | Waiting for master to send event | NULL | 0 | 0 | | 4 | system user | | NULL | Connect | 77 | Waiting for workers to exit | NULL | 0 | 0 | | 7 | system user | | NULL | Connect | 84 | Waiting for preceding transaction to commit | NULL | 0 | 0 | +----+-------------+-----------------+------+---------+------+-----------------------------------------+ Analysis -------- Considering the above flow, the root cause for the deadlock seems to be in the step 5 since Worker 2 which is in the middle of the commit order queue exits abruptly without signaling Worker 3 causing the Worker 3 to wait forever on the signal. If worker 2, had waited for its turn during report_rollback(), instead of returning immediately from Commit_order_manager::wait_for_its_turn() after checking the m_order_commit_deadlock flag, then Worker 3 would have received signal from Worker 2 when Worker 2 proceeded (after getting woken up when Worker 1 signals) and there would have been no cases of missing signals. Fix --- When then above situation arises, we now make Worker 2 to wait for its turn while it is calling report_rollback() if the m_order_commit_deadlock flag is set. This ensures that any worker which is in the middle of the queue won't exit abruptly and all other workers are signalled in a synchronised manner. As part of the fix, the `slave_worker_ends_group()` has been modified to rollback any pending transaction and clear the Slave_worker::m_order_commit_deadlock before calling the `report_rollback()`. --- ...pco_deadlock_hang_on_non_temp_error.result | 145 ++++++++++ ...o_deadlock_slave_trans_retries_hang.result | 122 ++++++++ ..._deadlock_hang_on_non_temp_error-slave.opt | 1 + ..._spco_deadlock_hang_on_non_temp_error.test | 230 +++++++++++++++ ...pco_deadlock_slave_trans_retries_hang.test | 267 ++++++++++++++++++ sql/rpl_rli_pdb.cc | 17 ++ 6 files changed, 782 insertions(+) create mode 100644 mysql-test/suite/rpl/r/rpl_mts_spco_deadlock_hang_on_non_temp_error.result create mode 100644 mysql-test/suite/rpl/r/rpl_mts_spco_deadlock_slave_trans_retries_hang.result create mode 100644 mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_hang_on_non_temp_error-slave.opt create mode 100644 mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_hang_on_non_temp_error.test create mode 100644 mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_slave_trans_retries_hang.test diff --git a/mysql-test/suite/rpl/r/rpl_mts_spco_deadlock_hang_on_non_temp_error.result b/mysql-test/suite/rpl/r/rpl_mts_spco_deadlock_hang_on_non_temp_error.result new file mode 100644 index 00000000000..b2474729eca --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mts_spco_deadlock_hang_on_non_temp_error.result @@ -0,0 +1,145 @@ +include/master-slave.inc +Warnings: +Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. +Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. +[connection master] +# +# 1. Initial data +# +CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB; +CREATE TABLE t2(c1 INT PRIMARY KEY) ENGINE = InnoDB; +include/sync_slave_sql_with_master.inc +# +# 2. Initial setup on Slave. +# +# 2.1. Enable slave_preserve_commit_order on slave for Commit order +# deadlock to occur. +# +include/stop_slave_sql.inc +SET @saved_slave_preserve_commit_order = @@GLOBAL.slave_preserve_commit_order; +SET GLOBAL slave_preserve_commit_order = ON; +# +# 2.2 Set innodb_lock_wait_timeout to 300 to make test work without issues +# on slow platform runs. +# +SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout; +SET GLOBAL innodb_lock_wait_timeout = 300; +# +# 2.3 Adding 'rpl_fake_cod_deadlock' debug point to execute special code required +# for the test case. +# +# Adding debug point 'rpl_fake_cod_deadlock' to @@GLOBAL.debug +# +# 2.4 Add error suppressions. +# +call mtr.add_suppression("Worker .* failed executing transaction"); +call mtr.add_suppression("The slave coordinator and worker threads are stopped"); +# +# 2.5 An extra connection needed on Slave. +# +# +# 3. Actual testing starts from here. +# +# 3.1 Insert basic data on Master +# +[connection master] +INSERT INTO t1 VALUES(1, 1),(2, 2); +# +# 3.2 Enabling set_commit_parent_100 debug point +# on Master to make two conflicting transactions +# run parallely by two worker threads on Slave. +# +# Adding debug point 'set_commit_parent_100' to @@SESSION.debug +# +# 3.3 Execute Transaction-1 +# +INSERT INTO t1 VALUES(3, 1); +# +# 3.4 Execute Transaction-2 +# +BEGIN; +DELETE FROM t1 WHERE c2 <= 1; +INSERT INTO t2 values(1); +COMMIT; +# +# 3.5 Execute Transaction-3 +# +INSERT INTO t1 values(10,10); +# +# 3.6 On Slave, begin a transaction (Transacation-4) +# which will acquire MDL lock on t1 +# (that blocks 'Transacation-1'). +# +[connection slave1] +BEGIN; +INSERT INTO t1 VALUES(3, 3); +# +# 3.7 On Slave, begin a transaction (Transacation-5) +# that can block INSERT in 'Transacation-2'. +# +[connection slave] +BEGIN; +INSERT INTO t2 VALUES(1); +# +# 3.8 Start SQL thread, let Transaction-1 & 2 reach SQL workers +# +include/start_slave_sql.inc +# +# 3.9 Wait till the worker threads pick up those transacations +# (Transaction 1 and Transaction 2). They will wait for locks due +# local transacations (4 & 5). +# +# 3.10 Rollback Transaction-4 which will release MDL lock required for +# Transaction-1. +# +[connection slave1] +ROLLBACK; +# +# 3.11 Wait till the worker thread detects commit order deadlock +# between two workers +# (Worker1 : Transaction-1 & Worker-2: Transaction-2). +# +SET debug_sync = "now WAIT_FOR reported_deadlock"; +# +# 3.12 Commit Transaction-5 which will release MDL lock required for +# Transaction-2. +# +[connection slave] +COMMIT; +# +# 3.13 Step 3.11 will cause worker 2 ("Transacation-2") +# to fail with 'ER_DUP_ENTRY' error (after the worker2 +# detected the 'commit order deadlock'). +# +include/wait_for_slave_sql_error.inc [errno=1062] +# +# 3.13 Check that worker 2 did not *retry* transaction-2 +# before throwing ER_DUP_ENTRY error (non-temporary error). +# +include/assert_grep.inc [Found 'Duplicate entry' error only once time which proves that transaction is not retried after getting 'non-temporary error'.] +# +# 4. Cleanup +# 4.1 Clear debug point. +# +# Removing debug point 'rpl_fake_cod_deadlock' from @@GLOBAL.debug +# +# 4.2 Clean the data on Slave. +# +include/stop_slave_io.inc +RESET SLAVE; +DROP TABLE t1, t2; +# +# 4.3 Clear system variables. +# +SET GLOBAL slave_preserve_commit_order = @saved_slave_preserve_commit_order; +SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout; +# +# 4.4 Clear debug point. +# +[connection master] +# Removing debug point 'set_commit_parent_100' from @@SESSION.debug +# +# 4.5 Clean the data on Master. +# +DROP TABLE t1, t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_mts_spco_deadlock_slave_trans_retries_hang.result b/mysql-test/suite/rpl/r/rpl_mts_spco_deadlock_slave_trans_retries_hang.result new file mode 100644 index 00000000000..c82b9a63308 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mts_spco_deadlock_slave_trans_retries_hang.result @@ -0,0 +1,122 @@ +# +# 1. Create source-replica topolgy. +include/master-slave.inc +Warnings: +Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. +Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. +[connection master] +# +# 2. Setup testing environment. +# 2.1. Create table on source and insert few rows. +# 2.2. Setup necessary variables on replica server. +CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB; +INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); +include/sync_slave_sql_with_master.inc +include/stop_slave_sql.inc +SET @saved_slave_parallel_type = @@GLOBAL.slave_parallel_type; +SET @saved_slave_parallel_workers = @@GLOBAL.slave_parallel_workers; +SET @saved_slave_preserve_commit_order = @@GLOBAL.slave_preserve_commit_order; +SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout; +SET @saved_slave_transaction_retries = @@GLOBAL.slave_transaction_retries; +SET GLOBAL slave_transaction_retries = 0; +SET GLOBAL slave_parallel_type = "LOGICAL_CLOCK"; +SET GLOBAL slave_parallel_workers = 8; +SET GLOBAL slave_preserve_commit_order = ON; +SET GLOBAL innodb_lock_wait_timeout = 1000; +# +# Case 1 +# ====== +# +# Verify replica server can find the deadlock when the victim thread +# (DELETE) is waiting for its turn to commit and is handled properly. +# +# 3.1 Execute transactions on source server that would generate deadlock on the +# replica server. +[connection master] +INSERT INTO t1 VALUES(10, 10); +# Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug +INSERT INTO t1 VALUES(11, NULL); +DELETE FROM t1 WHERE c2 <= 3; +INSERT INTO t1 VALUES(21, 21); +INSERT INTO t1 VALUES(22, 22); +INSERT INTO t1 VALUES(23, 23); +# Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug +[connection slave] +BEGIN; +INSERT INTO t1 VALUES(11, 11); +[connection slave1] +include/start_slave_sql.inc +[connection slave] +ROLLBACK; +# +# 3.2. Wait till the co-ordinator thread to error out with ER_MTS_INCONSISTENT_DATA. +include/wait_for_slave_sql_error.inc [errno=1756] +# +# 3.3. Restart replication threads, sync the replica and verify that table +# data is consistent. +include/start_slave.inc +include/rpl_diff.inc +# +# Case 2 +# ====== +# +# Verify that replica server can find the deadlock when the victim +# thread (DELETE) is in the middle of transaction and is handled +# properly. +# +# 3.4 Execute transactions on source server that would generate deadlock on the +# replica server. +[connection master] +TRUNCATE t1; +INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); +include/sync_slave_sql_with_master.inc +include/stop_slave_sql.inc +[connection master] +INSERT INTO t1 VALUES(20, NULL); +# Adding debug point 'set_commit_parent_100' to @@GLOBAL.debug +INSERT INTO t1 VALUES(21, NULL); +BEGIN; +INSERT INTO t1 VALUES(22, 22); +DELETE FROM t1 WHERE c2 <= 3; +INSERT INTO t1 VALUES(23, 23); +INSERT INTO t1 VALUES(24, NULL); +INSERT INTO t1 VALUES(25, 25); +COMMIT; +INSERT INTO t1 VALUES(26, 26); +INSERT INTO t1 VALUES(27, 27); +INSERT INTO t1 VALUES(28, 28); +# Removing debug point 'set_commit_parent_100' from @@GLOBAL.debug +[connection slave] +BEGIN; +INSERT INTO t1 VALUES(21, 21); +[connection server_2_1] +BEGIN; +INSERT INTO t1 VALUES(23, 23); +[connection slave1] +include/start_slave_sql.inc +[connection slave] +ROLLBACK; +[connection server_2_1] +ROLLBACK; +# +# 3.5. Wait till the co-ordinator thread to error out with ER_MTS_INCONSISTENT_DATA. +include/wait_for_slave_sql_error.inc [errno=1756] +# +# 3.6. Restart replication threads, sync the replica and verify that table +# data is consistent. +include/start_slave.inc +include/rpl_diff.inc +# +# 4. Cleanup. +# +[connection master] +DROP TABLE t1; +include/sync_slave_sql_with_master.inc +CALL mtr.add_suppression("The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state."); +include/stop_slave.inc +SET GLOBAL slave_transaction_retries = @saved_slave_transaction_retries; +SET GLOBAL slave_parallel_type = @saved_slave_parallel_type; +SET GLOBAL slave_parallel_workers = @saved_slave_parallel_workers; +SET GLOBAL slave_preserve_commit_order = @saved_slave_preserve_commit_order; +SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_hang_on_non_temp_error-slave.opt b/mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_hang_on_non_temp_error-slave.opt new file mode 100644 index 00000000000..001037b475b --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_hang_on_non_temp_error-slave.opt @@ -0,0 +1 @@ +--slave-parallel-workers=3 --slave-parallel-type=LOGICAL_CLOCK diff --git a/mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_hang_on_non_temp_error.test b/mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_hang_on_non_temp_error.test new file mode 100644 index 00000000000..2e71eff8698 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_hang_on_non_temp_error.test @@ -0,0 +1,230 @@ +# ==== Purpose ==== +# +# This test verifies that when worker threads enter into commit order deadlock, +# retry logic will not be triggered if there is non-temporary error in the +# transaction and such cases are handled properly by signalling other workers. +# +# This test is based on +# mysql-test/suite/rpl/t/rpl_mts_slave_preserve_commit_order_deadlock_error.test +# and differs from it by having an extra transaction in the waiting state. +# +# For more information about "commit order deadlock and +# retry logic, please see header section in +# mysql-test/suite/rpl/t/rpl_mts_slave_preserve_commit_order_deadlock.test. +# +# ==== Implementation ==== +# +# 1) Create a situation that can cause commit order deadlock between two +# worker threads on slave. +# 2) After the worker thread detects commit order deadlock, make transaction +# fail due to some other error (non-temp error). +# 3) Make sure that in this non-temp error case, the worker thread stopped +# immediately with the error instead of retrying it again. +# +# ==== References ==== +# +# Bug #87796 Commit_order_manager can't terminate MTS worker properly when deadlock happens +# Bug #89247 Deadlock with MTS when slave_preserve_commit_order = ON. +# Bug #95249 stop slave permanently blocked +# Bug #99440 Threads of MTS Slave randomly stuck + +--source include/have_debug.inc +--source include/have_binlog_format_statement.inc +--source include/master-slave.inc + +--echo # +--echo # 1. Initial data +--echo # +CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB; +CREATE TABLE t2(c1 INT PRIMARY KEY) ENGINE = InnoDB; +--source include/sync_slave_sql_with_master.inc + +# switched to slave connection +--source include/only_mts_slave_parallel_type_logical_clock.inc +--source include/only_mts_slave_parallel_workers.inc + +--echo # +--echo # 2. Initial setup on Slave. +--echo # +--echo # 2.1. Enable slave_preserve_commit_order on slave for Commit order +--echo # deadlock to occur. +--echo # +--source include/stop_slave_sql.inc +SET @saved_slave_preserve_commit_order = @@GLOBAL.slave_preserve_commit_order; +SET GLOBAL slave_preserve_commit_order = ON; + +--echo # +--echo # 2.2 Set innodb_lock_wait_timeout to 300 to make test work without issues +--echo # on slow platform runs. +--echo # +SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout; +SET GLOBAL innodb_lock_wait_timeout = 300; + +--echo # +--echo # 2.3 Adding 'rpl_fake_cod_deadlock' debug point to execute special code required +--echo # for the test case. +--echo # +--let $debug_point= rpl_fake_cod_deadlock +--source include/add_debug_point.inc + +--echo # +--echo # 2.4 Add error suppressions. +--echo # +call mtr.add_suppression("Worker .* failed executing transaction"); +call mtr.add_suppression("The slave coordinator and worker threads are stopped"); + +--echo # +--echo # 2.5 An extra connection needed on Slave. +--echo # +connect(slave2,127.0.0.1,root,,test,$SLAVE_MYPORT,); + +--echo # +--echo # 3. Actual testing starts from here. +--echo # +--echo # 3.1 Insert basic data on Master +--echo # +--source include/rpl_connection_master.inc +INSERT INTO t1 VALUES(1, 1),(2, 2); + +--echo # +--echo # 3.2 Enabling set_commit_parent_100 debug point +--echo # on Master to make two conflicting transactions +--echo # run parallely by two worker threads on Slave. +--echo # +--let $debug_type= SESSION +--let $debug_point= set_commit_parent_100 +--source include/add_debug_point.inc + +--echo # +--echo # 3.3 Execute Transaction-1 +--echo # +INSERT INTO t1 VALUES(3, 1); + +--echo # +--echo # 3.4 Execute Transaction-2 +--echo # +BEGIN; +DELETE FROM t1 WHERE c2 <= 1; +INSERT INTO t2 values(1); +COMMIT; + +--echo # +--echo # 3.5 Execute Transaction-3 +--echo # +INSERT INTO t1 values(10,10); + +--echo # +--echo # 3.6 On Slave, begin a transaction (Transacation-4) +--echo # which will acquire MDL lock on t1 +--echo # (that blocks 'Transacation-1'). +--echo # +--source include/rpl_connection_slave1.inc +BEGIN; +INSERT INTO t1 VALUES(3, 3); + +--echo # +--echo # 3.7 On Slave, begin a transaction (Transacation-5) +--echo # that can block INSERT in 'Transacation-2'. +--echo # +--source include/rpl_connection_slave.inc +BEGIN; +INSERT INTO t2 VALUES(1); + +--echo # +--echo # 3.8 Start SQL thread, let Transaction-1 & 2 reach SQL workers +--echo # +--connection slave2 +--source include/start_slave_sql.inc + +--echo # +--echo # 3.9 Wait till the worker threads pick up those transacations +--echo # (Transaction 1 and Transaction 2). They will wait for locks due +--echo # local transacations (4 & 5). +# +--let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE PROCESSLIST_INFO= 'INSERT INTO t1 VALUES(3, 1)' AND NAME LIKE '%slave_worker%' +--source include/wait_condition.inc +--let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.threads WHERE PROCESSLIST_INFO= 'INSERT INTO t2 values(1)' AND NAME LIKE '%slave_worker%' +--source include/wait_condition.inc + +--echo # +--echo # 3.10 Rollback Transaction-4 which will release MDL lock required for +--echo # Transaction-1. +--echo # +--source include/rpl_connection_slave1.inc +ROLLBACK; + +--echo # +--echo # 3.11 Wait till the worker thread detects commit order deadlock +--echo # between two workers +--echo # (Worker1 : Transaction-1 & Worker-2: Transaction-2). +--echo # +SET debug_sync = "now WAIT_FOR reported_deadlock"; + +--echo # +--echo # 3.12 Commit Transaction-5 which will release MDL lock required for +--echo # Transaction-2. +--echo # +--source include/rpl_connection_slave.inc +COMMIT; + +# Without the fix, this would make the worker 2 to exit without removing its +# entry from the SPCO queue thus resulting in other worker threads to wait +# forever on the signal. Meanwhile if co-ordinator was asked to go down by any +# of the previous workers, this would make co-ordinator thread to wait for the +# workers to finish thus leading to server hang. + +--echo # +--echo # 3.13 Step 3.11 will cause worker 2 ("Transacation-2") +--echo # to fail with 'ER_DUP_ENTRY' error (after the worker2 +--echo # detected the 'commit order deadlock'). +--echo # +--let $slave_sql_errno= convert_error(ER_DUP_ENTRY) +--source include/wait_for_slave_sql_error.inc + +--echo # +--echo # 3.13 Check that worker 2 did not *retry* transaction-2 +--echo # before throwing ER_DUP_ENTRY error (non-temporary error). +--echo # +--let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.2.err +--let $assert_only_after = CURRENT_TEST: rpl.rpl_mts_spco_deadlock_hang_on_non_temp_error +--let $assert_count = 1 +--let $assert_select = Error 'Duplicate entry '1' for key 't2.PRIMARY'' on query +--let $assert_text = Found 'Duplicate entry' error only once time which proves that transaction is not retried after getting 'non-temporary error'. +--source include/assert_grep.inc + +--echo # +--echo # 4. Cleanup +--echo # 4.1 Clear debug point. +--echo # +--let $debug_type= GLOBAL +--let $debug_point= rpl_fake_cod_deadlock +--source include/remove_debug_point.inc + +--echo # +--echo # 4.2 Clean the data on Slave. +--echo # +--source include/stop_slave_io.inc +RESET SLAVE; +DROP TABLE t1, t2; + +--echo # +--echo # 4.3 Clear system variables. +--echo # +SET GLOBAL slave_preserve_commit_order = @saved_slave_preserve_commit_order; +SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout; + +--echo # +--echo # 4.4 Clear debug point. +--echo # +--source include/rpl_connection_master.inc +--let $debug_type= SESSION +--let $debug_point= set_commit_parent_100 +--source include/remove_debug_point.inc + +--echo # +--echo # 4.5 Clean the data on Master. +--echo # +DROP TABLE t1, t2; + +--let $rpl_only_running_threads= 1 +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_slave_trans_retries_hang.test b/mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_slave_trans_retries_hang.test new file mode 100644 index 00000000000..a066799e794 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mts_spco_deadlock_slave_trans_retries_hang.test @@ -0,0 +1,267 @@ +# ==== Purpose ==== +# +# This test verifies that deadlock errors arising due to lock conflicts are +# handled properly when --slave-preserve-commit-order is enabled on the replica +# server. +# +# ==== Implementation ==== +# +# 1. Create source-replica topolgy. +# +# 2. Setup testing environment. +# +# 2.1. Create table on source and insert few rows. +# 2.2. Setup necessary variables on replica server. +# +# 3. Execute transactions on source server that would generate deadlock on the +# replica server. +# +# The commands used to generate deadlock in this test are: +# +# INSERT INTO t1 (11, NULL); +# DELETE FROM t1 WHERE c2 <= 3; +# +# With the help of debug points on the source server, simulate that they are +# executed parallely on master and make sure INSERT is binlogged before +# DELETE. +# +# On replica server, they are applied parallel by w1 and w2. And the deadlock is: +# w1 w2 +# ------------------------- -------------------------- +# Applying INSERT Applying DELETE +# Gets the row locks first. +# Waits for w2 to release +# the row lock. +# Waits for w1 to commit. +# +# For these two special statements, INSERT doesn't block DELETE, but DELETE +# does block the INSERT because of the gap locks. +# +# CASE 1: +# 3.1. Verify that replica server can find the deadlock when the victim +# thread (DELETE) is waiting for its turn to commit and is handled +# properly. +# 3.2. Wait till server to handle the deadlock and till the co-ordinator +# thread to error out with ER_MTS_INCONSISTENT_DATA. +# 3.3. Restart the replication threads, sync the replica and verify that +# table data is consistent. +# +# CASE 2: +# 3.4. Verify that replica server can find the deadlock when the victim +# thread (DELETE) is in the middle of transaction and is handled +# properly. +# 3.5. Wait till server to handle the deadlock and till the co-ordinator +# thread to error out with ER_MTS_INCONSISTENT_DATA. +# 3.6. Restart the replication threads, sync the replica and verify that +# table data is consistent. +# +# 4. Cleanup. +# +# ==== References ==== +# +# Bug #87796 Commit_order_manager can't terminate MTS worker properly when deadlock happens +# Bug #89247 Deadlock with MTS when slave_preserve_commit_order = ON. +# Bug #95249 stop slave permanently blocked +# Bug #99440 Threads of MTS Slave randomly stuck + + +# The test needs debug binaries + +--source include/have_debug.inc +--source include/have_binlog_format_statement.inc + +--echo # +--echo # 1. Create source-replica topolgy. +--source include/master-slave.inc + +--echo # +--echo # 2. Setup testing environment. +--echo # 2.1. Create table on source and insert few rows. +--echo # 2.2. Setup necessary variables on replica server. +CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB; +INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); + +--source include/sync_slave_sql_with_master.inc +--source include/stop_slave_sql.inc +SET @saved_slave_parallel_type = @@GLOBAL.slave_parallel_type; +SET @saved_slave_parallel_workers = @@GLOBAL.slave_parallel_workers; +SET @saved_slave_preserve_commit_order = @@GLOBAL.slave_preserve_commit_order; +SET @saved_innodb_lock_wait_timeout = @@GLOBAL.innodb_lock_wait_timeout; +SET @saved_slave_transaction_retries = @@GLOBAL.slave_transaction_retries; + +SET GLOBAL slave_transaction_retries = 0; +SET GLOBAL slave_parallel_type = "LOGICAL_CLOCK"; +SET GLOBAL slave_parallel_workers = 8; +SET GLOBAL slave_preserve_commit_order = ON; +# Set it to a long time to guarantee it doesn't report timeout error. +SET GLOBAL innodb_lock_wait_timeout = 1000; + +--echo # +--echo # Case 1 +--echo # ====== +--echo # +--echo # Verify replica server can find the deadlock when the victim thread +--echo # (DELETE) is waiting for its turn to commit and is handled properly. +--echo # +--echo # 3.1 Execute transactions on source server that would generate deadlock on the +--echo # replica server. +--source include/rpl_connection_master.inc +# There is a bug that the first two transactions cannot be applied parallel. +# So we need to an extra transaction here. +INSERT INTO t1 VALUES(10, 10); + +--let $debug_point = set_commit_parent_100 +--source include/add_debug_point.inc +INSERT INTO t1 VALUES(11, NULL); # Transaction 1 +DELETE FROM t1 WHERE c2 <= 3; # Transaction 2 +INSERT INTO t1 VALUES(21, 21); # Transaction 3 +INSERT INTO t1 VALUES(22, 22); # Transaction 4 +INSERT INTO t1 VALUES(23, 23); # Transaction 5 +--source include/remove_debug_point.inc + +# Start a local transaction on replica to block the INSERT statement coming +# through the replication channel. This blocks the worker thread executing +# "INSERT INTO t1 VALUES(11, NULL)" +--source include/rpl_connection_slave.inc +BEGIN; +INSERT INTO t1 VALUES(11, 11); + +# Wait till DELETE statement(w2) and other workers wait for the the preceding transaction(w1) to commit. +--source include/rpl_connection_slave1.inc +--source include/start_slave_sql.inc +--let $wait_condition = SELECT count(*) = 4 FROM information_schema.processlist WHERE STATE = "Waiting for preceding transaction to commit" +--source include/wait_condition.inc + +# Resume the INSERT statement and trigger the deadlock +--source include/rpl_connection_slave.inc +ROLLBACK; + +# When the INSERT statement proceeds, the w1 finds deadlock with w2, and it +# will ask w2 to rollback. When w2 rolls back the transaction, it would check +# the value of slave_trans_retries before retrying the transaction. Since, we +# have set it to 0 throughout the test, the w2 shall not retry the transaction +# and would call report_rollback. +# +# Without the fix, calling report_rollback() would make the w2 to return +# without removing its entry from the SPCO queue thus resulting in other worker +# threads to wait forever on the signal. Meanwhile if co-ordinator was asked to +# go down by any of the previous workers, this would make co-ordinator thread +# to wait for the workers to finish thus leading to server hang. + +--echo # +--echo # 3.2. Wait till the co-ordinator thread to error out with ER_MTS_INCONSISTENT_DATA. +--let $slave_sql_errno = convert_error(ER_MTS_INCONSISTENT_DATA) +--source include/wait_for_slave_sql_error.inc + +--echo # +--echo # 3.3. Restart replication threads, sync the replica and verify that table +--echo # data is consistent. +--source include/start_slave.inc +--let $rpl_diff_statement = SELECT * FROM t1; +--source include/rpl_diff.inc + +--echo # +--echo # Case 2 +--echo # ====== +--echo # +--echo # Verify that replica server can find the deadlock when the victim +--echo # thread (DELETE) is in the middle of transaction and is handled +--echo # properly. +--echo # +--echo # 3.4 Execute transactions on source server that would generate deadlock on the +--echo # replica server. +--source include/rpl_connection_master.inc +TRUNCATE t1; +INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6); +--source include/sync_slave_sql_with_master.inc +--source include/stop_slave_sql.inc + +--source include/rpl_connection_master.inc +INSERT INTO t1 VALUES(20, NULL); + +--let $debug_point = set_commit_parent_100 +--source include/add_debug_point.inc +INSERT INTO t1 VALUES(21, NULL); # Transaction 1 + +BEGIN; # Transaction 2 +INSERT INTO t1 VALUES(22, 22); +DELETE FROM t1 WHERE c2 <= 3; +INSERT INTO t1 VALUES(23, 23); +INSERT INTO t1 VALUES(24, NULL); +INSERT INTO t1 VALUES(25, 25); +COMMIT; + +INSERT INTO t1 VALUES(26, 26); # Transaction 3 +INSERT INTO t1 VALUES(27, 27); # Transaction 4 +INSERT INTO t1 VALUES(28, 28); # Transaction 5 +--source include/remove_debug_point.inc + +--source include/rpl_connection_slave.inc +# Start a local transaction on replica to block the INSERT statement coming +# through the replication channel. This blocks the worker thread executing +# "INSERT INTO t1 VALUES(21, NULL)" +BEGIN; +INSERT INTO t1 VALUES(21, 21); + +--let $rpl_connection_name = server_2_1 +--source include/rpl_connection.inc +# Use it to block the worker applying 'INSERT INTO t1 VALUES(23, 23)'; +BEGIN; +INSERT INTO t1 VALUES(23, 23); + +# Wait till DELETE statement(w2) and other workers wait for the the preceding transaction(w1) to commit. +--source include/rpl_connection_slave1.inc +--source include/start_slave_sql.inc + +# It guarantees 'DELETE FROM t1 WHERE c2 <= 3;' is applied. +--let $wait_condition = SELECT count(*) = 1 FROM information_schema.processlist WHERE info = "INSERT INTO t1 VALUES(23, 23)" +--source include/wait_condition.inc + +--let $wait_condition = SELECT count(*) = 3 FROM information_schema.processlist WHERE STATE = "Waiting for preceding transaction to commit" +--source include/wait_condition.inc + +# It guarantees that both workers are waiting for locks held by above two transactions. +--let $status_var = Innodb_row_lock_current_waits +--let $status_var_value = 2 +--source include/wait_for_status_var.inc + +# Resume "INSERT INTO t1 VALUES(21, 21);" to trigger the lock conflict. +--source include/rpl_connection_slave.inc +ROLLBACK; + +# Resume "INSERT INTO t1 VALUES(23, 23);". It will be rolled back. +--let $rpl_connection_name = server_2_1 +--source include/rpl_connection.inc +ROLLBACK; + +--echo # +--echo # 3.5. Wait till the co-ordinator thread to error out with ER_MTS_INCONSISTENT_DATA. +--let $slave_sql_errno = convert_error(ER_MTS_INCONSISTENT_DATA) +--source include/wait_for_slave_sql_error.inc + +--echo # +--echo # 3.6. Restart replication threads, sync the replica and verify that table +--echo # data is consistent. +--source include/start_slave.inc +--let $rpl_diff_statement = SELECT * FROM t1; +--source include/rpl_diff.inc + +--echo # +--echo # 4. Cleanup. +--echo # +--source include/rpl_connection_master.inc +DROP TABLE t1; +--source include/sync_slave_sql_with_master.inc + +# Add Test suppressions +CALL mtr.add_suppression("The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state."); + +--source include/stop_slave.inc +SET GLOBAL slave_transaction_retries = @saved_slave_transaction_retries; +SET GLOBAL slave_parallel_type = @saved_slave_parallel_type; +SET GLOBAL slave_parallel_workers = @saved_slave_parallel_workers; +SET GLOBAL slave_preserve_commit_order = @saved_slave_preserve_commit_order; +SET GLOBAL innodb_lock_wait_timeout = @saved_innodb_lock_wait_timeout; + +--let $rpl_only_running_threads = 1 +--source include/rpl_end.inc diff --git a/sql/rpl_rli_pdb.cc b/sql/rpl_rli_pdb.cc index 8752a2ffbb9..9a997112121 100644 --- a/sql/rpl_rli_pdb.cc +++ b/sql/rpl_rli_pdb.cc @@ -1164,6 +1164,23 @@ void Slave_worker::slave_worker_ends_group(Log_event *ev, int error) { mysql_mutex_unlock(&jobs_lock); // Fatal error happens, it notifies the following transaction to rollback + if (get_commit_order_manager()) { + /* + If we still have the deadlock flag set, it means that the current + thread was involved in a deadlock in its last retry (or all retries + have been exhausted). In such a case, we must release all transaction + locks by rolling back the transaction and clear the deadlock flag + before we wait for this worker's turn in report_rollback(). + */ + if (found_commit_order_deadlock()) { + /* + We call cleanup_context() because it is even capable of rolling + back XA transactions. + */ + cleanup_context(info_thd, true); + reset_commit_order_deadlock(); + } + } Commit_order_manager::wait_and_finish(info_thd, true); // Killing Coordinator to indicate eventual consistency error -- 2.25.1