If a slave replicating an event has waited for more than
@@slave_abort_blocking_timeout for a conflicting metadata lock held by a
non-replication thread, the blocking query is killed to allow replication to
proceed and not be blocked indefinitely by a user query.
Signed-off-by: Kristian Nielsen
---
.../rpl/r/slave_abort_blocking_timeout.result | 74 ++++++++++++++++
.../rpl/t/slave_abort_blocking_timeout.test | 85 +++++++++++++++++++
sql/mdl.cc | 43 ++++++++--
sql/mdl.h | 3 +-
sql/mysqld.cc | 1 +
sql/mysqld.h | 1 +
sql/privilege.h | 2 +
sql/sql_base.cc | 2 +-
sql/sql_class.cc | 17 ++--
sql/sql_class.h | 3 +-
sql/sys_vars.cc | 11 +++
11 files changed, 225 insertions(+), 17 deletions(-)
create mode 100644 mysql-test/suite/rpl/r/slave_abort_blocking_timeout.result
create mode 100644 mysql-test/suite/rpl/t/slave_abort_blocking_timeout.test
diff --git a/mysql-test/suite/rpl/r/slave_abort_blocking_timeout.result b/mysql-test/suite/rpl/r/slave_abort_blocking_timeout.result
new file mode 100644
index 00000000000..911ea4b070e
--- /dev/null
+++ b/mysql-test/suite/rpl/r/slave_abort_blocking_timeout.result
@@ -0,0 +1,74 @@
+include/master-slave.inc
+[connection master]
+*** Testcase to show how a long-running SELECT can block replication from proceeding
+*** past a DDL. Intention to implement a timeout after which such SELECT can be
+*** killed.
+connection master;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t1 SELECT seq, 100+seq FROM seq_1_to_20;
+connection slave;
+include/stop_slave.inc
+SELECT @@GLOBAL.slave_abort_blocking_timeout;
+@@GLOBAL.slave_abort_blocking_timeout
+31536000.000000
+SET @old_abort_timeout= @@slave_abort_blocking_timeout;
+SET GLOBAL slave_abort_blocking_timeout= -1;
+Warnings:
+Warning 1292 Truncated incorrect slave_abort_blocking_timeout value: '-1'
+SELECT @@GLOBAL.slave_abort_blocking_timeout;
+@@GLOBAL.slave_abort_blocking_timeout
+0.000000
+SET GLOBAL slave_abort_blocking_timeout= 1.0;
+SELECT @@GLOBAL.slave_abort_blocking_timeout;
+@@GLOBAL.slave_abort_blocking_timeout
+1.000000
+connection server_2;
+SELECT X.a, SLEEP(IF((X.b MOD 2)=0, 0.4, 0.6)) FROM t1 X CROSS JOIN t1 Y;
+connection slave;
+connection master;
+UPDATE t1 SET b=b+1000 WHERE a=1;
+ALTER TABLE t1 ADD INDEX b_idx(b);
+UPDATE t1 SET b=b+1000 WHERE a=20;
+connection slave;
+include/start_slave.inc
+connection server_2;
+ERROR 70100: Query execution was interrupted
+connection slave;
+SHOW CREATE TABLE t1;
+Table t1
+Create Table CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ KEY `b_idx` (`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
+include/stop_slave.inc
+SET GLOBAL slave_abort_blocking_timeout= 0;
+SELECT @@GLOBAL.slave_abort_blocking_timeout;
+@@GLOBAL.slave_abort_blocking_timeout
+0.000000
+connection server_2;
+SELECT X.a, SLEEP(IF((X.b MOD 2)=0, 0.4, 0.6)) FROM t1 X CROSS JOIN t1 Y;
+connection slave;
+connection master;
+UPDATE t1 SET b=b+1000 WHERE a=1;
+ALTER TABLE t1 DROP INDEX b_idx;
+UPDATE t1 SET b=b+1000 WHERE a=20;
+connection slave;
+include/start_slave.inc
+connection server_2;
+ERROR 70100: Query execution was interrupted
+connection slave;
+SHOW CREATE TABLE t1;
+Table t1
+Create Table CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
+include/stop_slave.inc
+SET GLOBAL slave_abort_blocking_timeout= @old_abort_timeout;
+include/start_slave.inc
+connection master;
+DROP TABLE t1;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/slave_abort_blocking_timeout.test b/mysql-test/suite/rpl/t/slave_abort_blocking_timeout.test
new file mode 100644
index 00000000000..04f24ff5df8
--- /dev/null
+++ b/mysql-test/suite/rpl/t/slave_abort_blocking_timeout.test
@@ -0,0 +1,85 @@
+--source include/have_innodb.inc
+--source include/have_sequence.inc
+--source include/have_binlog_format_mixed.inc
+--source include/master-slave.inc
+
+--echo *** Testcase to show how a long-running SELECT can block replication from proceeding
+--echo *** past a DDL. Intention to implement a timeout after which such SELECT can be
+--echo *** killed.
+
+--connection master
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t1 SELECT seq, 100+seq FROM seq_1_to_20;
+
+--sync_slave_with_master
+
+--source include/stop_slave.inc
+SELECT @@GLOBAL.slave_abort_blocking_timeout;
+SET @old_abort_timeout= @@slave_abort_blocking_timeout;
+SET GLOBAL slave_abort_blocking_timeout= -1;
+SELECT @@GLOBAL.slave_abort_blocking_timeout;
+SET GLOBAL slave_abort_blocking_timeout= 1.0;
+SELECT @@GLOBAL.slave_abort_blocking_timeout;
+--connection server_2
+# Start a SELECT that will run for long.
+send SELECT X.a, SLEEP(IF((X.b MOD 2)=0, 0.4, 0.6)) FROM t1 X CROSS JOIN t1 Y;
+
+--connection slave
+# Wait for the SELECT to have started so it will block the coming DDL
+# from replicating.
+--let $wait_condition= SELECT COUNT(*)=1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE state = 'User sleep'
+--source include/wait_condition.inc
+
+--connection master
+UPDATE t1 SET b=b+1000 WHERE a=1;
+ALTER TABLE t1 ADD INDEX b_idx(b);
+UPDATE t1 SET b=b+1000 WHERE a=20;
+
+--save_master_pos
+--connection slave
+--source include/start_slave.inc
+--sync_with_master
+
+--connection server_2
+--error ER_QUERY_INTERRUPTED
+reap;
+
+--connection slave
+query_vertical SHOW CREATE TABLE t1;
+
+# Do it again to test that a timeout of 0 also works to abort user queries.
+--source include/stop_slave.inc
+SET GLOBAL slave_abort_blocking_timeout= 0;
+SELECT @@GLOBAL.slave_abort_blocking_timeout;
+--connection server_2
+send SELECT X.a, SLEEP(IF((X.b MOD 2)=0, 0.4, 0.6)) FROM t1 X CROSS JOIN t1 Y;
+
+--connection slave
+--let $wait_condition= SELECT COUNT(*)=1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE state = 'User sleep'
+--source include/wait_condition.inc
+
+--connection master
+UPDATE t1 SET b=b+1000 WHERE a=1;
+ALTER TABLE t1 DROP INDEX b_idx;
+UPDATE t1 SET b=b+1000 WHERE a=20;
+
+--save_master_pos
+--connection slave
+--source include/start_slave.inc
+--sync_with_master
+
+--connection server_2
+--error ER_QUERY_INTERRUPTED
+reap;
+
+--connection slave
+query_vertical SHOW CREATE TABLE t1;
+
+
+--source include/stop_slave.inc
+SET GLOBAL slave_abort_blocking_timeout= @old_abort_timeout;
+--source include/start_slave.inc
+
+--connection master
+DROP TABLE t1;
+--source include/rpl_end.inc
diff --git a/sql/mdl.cc b/sql/mdl.cc
index faccd1c9476..9845718e165 100644
--- a/sql/mdl.cc
+++ b/sql/mdl.cc
@@ -613,7 +613,7 @@ class MDL_lock
bool needs_notification(const MDL_ticket *ticket) const
{ return m_strategy->needs_notification(ticket); }
- void notify_conflicting_locks(MDL_context *ctx)
+ void notify_conflicting_locks(MDL_context *ctx, bool abort_blocking)
{
for (const auto &conflicting_ticket : m_granted)
{
@@ -624,7 +624,8 @@ class MDL_lock
ctx->get_owner()->
notify_shared_lock(conflicting_ctx->get_owner(),
- conflicting_ctx->get_needs_thr_lock_abort());
+ conflicting_ctx->get_needs_thr_lock_abort(),
+ abort_blocking);
}
}
}
@@ -2361,10 +2362,10 @@ MDL_context::acquire_lock(MDL_request *mdl_request, double lock_wait_timeout)
/*
Don't break conflicting locks if timeout is 0 as 0 is used
- To check if there is any conflicting locks...
+ to check if there is any conflicting locks...
*/
if (lock->needs_notification(ticket) && lock_wait_timeout)
- lock->notify_conflicting_locks(this);
+ lock->notify_conflicting_locks(this, false);
/*
Ensure that if we are trying to get an exclusive lock for a slave
@@ -2397,14 +2398,39 @@ MDL_context::acquire_lock(MDL_request *mdl_request, double lock_wait_timeout)
find_deadlock();
- struct timespec abs_timeout, abs_shortwait;
+ struct timespec abs_timeout, abs_shortwait, abs_abort_blocking_timeout;
+ bool abort_blocking_enabled= false;
+ double abort_blocking_timeout= slave_abort_blocking_timeout;
+ if (abort_blocking_timeout < lock_wait_timeout &&
+ m_owner->get_thd()->rgi_slave)
+ {
+ set_timespec_nsec(abs_abort_blocking_timeout,
+ (ulonglong)(abort_blocking_timeout * 1000000000ULL));
+ abort_blocking_enabled= true;
+ }
set_timespec_nsec(abs_timeout,
(ulonglong)(lock_wait_timeout * 1000000000ULL));
- set_timespec(abs_shortwait, 1);
wait_status= MDL_wait::EMPTY;
- while (cmp_timespec(abs_shortwait, abs_timeout) <= 0)
+ for (;;)
{
+ bool abort_blocking= false;
+ set_timespec(abs_shortwait, 1);
+ if (abort_blocking_enabled &&
+ cmp_timespec(abs_shortwait, abs_abort_blocking_timeout) >= 0)
+ {
+ /*
+ If a slave DDL has waited for --slave-abort-select-timeout, then notify
+ any blocking SELECT once before continuing to wait until the full
+ timeout.
+ */
+ abs_shortwait= abs_abort_blocking_timeout;
+ abort_blocking= true;
+ abort_blocking_enabled= false;
+ }
+ if (cmp_timespec(abs_shortwait, abs_timeout) > 0)
+ break;
+
/* abs_timeout is far away. Wait a short while and notify locks. */
wait_status= m_wait.timed_wait(m_owner, &abs_shortwait, FALSE,
mdl_request->key.get_wait_state_name());
@@ -2425,9 +2451,8 @@ MDL_context::acquire_lock(MDL_request *mdl_request, double lock_wait_timeout)
mysql_prlock_wrlock(&lock->m_rwlock);
if (lock->needs_notification(ticket))
- lock->notify_conflicting_locks(this);
+ lock->notify_conflicting_locks(this, abort_blocking);
mysql_prlock_unlock(&lock->m_rwlock);
- set_timespec(abs_shortwait, 1);
}
if (wait_status == MDL_wait::EMPTY)
wait_status= m_wait.timed_wait(m_owner, &abs_timeout, TRUE,
diff --git a/sql/mdl.h b/sql/mdl.h
index 68cf5d2e811..aa271cb6d09 100644
--- a/sql/mdl.h
+++ b/sql/mdl.h
@@ -110,7 +110,8 @@ class MDL_context_owner
@see THD::notify_shared_lock()
*/
virtual bool notify_shared_lock(MDL_context_owner *in_use,
- bool needs_thr_lock_abort) = 0;
+ bool needs_thr_lock_abort,
+ bool needs_non_slave_abort) = 0;
};
/**
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index e938e8f6cfa..7e0a7f339c0 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -494,6 +494,7 @@ uint internal_slave_connections_needed_for_purge;
ulong slave_max_allowed_packet= 0;
double slave_max_statement_time_double;
ulonglong slave_max_statement_time;
+double slave_abort_blocking_timeout;
ulonglong binlog_stmt_cache_size=0;
ulonglong max_binlog_stmt_cache_size=0;
ulonglong test_flags;
diff --git a/sql/mysqld.h b/sql/mysqld.h
index 7cc88be0ad1..20644f0c404 100644
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -242,6 +242,7 @@ extern ulong max_binlog_size;
extern ulong slave_max_allowed_packet;
extern ulonglong slave_max_statement_time;
extern double slave_max_statement_time_double;
+extern double slave_abort_blocking_timeout;
extern ulong opt_binlog_rows_event_max_size;
extern ulong binlog_row_metadata;
extern my_bool opt_binlog_gtid_index;
diff --git a/sql/privilege.h b/sql/privilege.h
index 84efc010d1e..eec0eb49df1 100644
--- a/sql/privilege.h
+++ b/sql/privilege.h
@@ -598,6 +598,8 @@ constexpr privilege_t PRIV_SET_SYSTEM_GLOBAL_VAR_SLAVE_MAX_ALLOWED_PACKET=
REPL_SLAVE_ADMIN_ACL;
constexpr privilege_t PRIV_SET_SYSTEM_GLOBAL_VAR_SLAVE_MAX_STATEMENT_TIME=
REPL_SLAVE_ADMIN_ACL;
+constexpr privilege_t PRIV_SET_SYSTEM_GLOBAL_VAR_SLAVE_ABORT_BLOCKING_TIMEOUT=
+ REPL_SLAVE_ADMIN_ACL;
constexpr privilege_t PRIV_SET_SYSTEM_GLOBAL_VAR_SLAVE_NET_TIMEOUT=
REPL_SLAVE_ADMIN_ACL;
constexpr privilege_t PRIV_SET_SYSTEM_GLOBAL_VAR_SLAVE_PARALLEL_MAX_QUEUED=
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index b045bf14cd9..a96082e5903 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1025,7 +1025,7 @@ void close_thread_table(THD *thd, TABLE **table_ptr)
thd->handler_stats.add(file->handler_stats);
}
/*
- This look is needed to allow THD::notify_shared_lock() to
+ This lock is needed to allow THD::notify_shared_lock() to
traverse the thd->open_tables list without having to worry that
some of the tables are removed from under it
*/
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 13818813574..595ffd681a5 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -2158,21 +2158,28 @@ void THD::disconnect()
bool THD::notify_shared_lock(MDL_context_owner *ctx_in_use,
- bool needs_thr_lock_abort)
+ bool needs_thr_lock_abort,
+ bool needs_non_slave_abort)
{
THD *in_use= ctx_in_use->get_thd();
bool signalled= FALSE;
DBUG_ENTER("THD::notify_shared_lock");
DBUG_PRINT("enter",("needs_thr_lock_abort: %d", needs_thr_lock_abort));
- if ((in_use->system_thread & SYSTEM_THREAD_DELAYED_INSERT) &&
- !in_use->killed)
+ enum killed_state kill_signal;
+ if (in_use->system_thread & SYSTEM_THREAD_DELAYED_INSERT)
+ kill_signal= KILL_CONNECTION;
+ else if (needs_non_slave_abort && !in_use->slave_thread)
+ kill_signal= KILL_QUERY;
+ else
+ kill_signal= NOT_KILLED;
+ if (kill_signal != NOT_KILLED && !in_use->killed)
{
/* This code is similar to kill_delayed_threads() */
DBUG_PRINT("info", ("kill delayed thread"));
mysql_mutex_lock(&in_use->LOCK_thd_kill);
- if (in_use->killed < KILL_CONNECTION)
- in_use->set_killed_no_mutex(KILL_CONNECTION);
+ if (in_use->killed < kill_signal)
+ in_use->set_killed_no_mutex(kill_signal);
in_use->abort_current_cond_wait(true);
mysql_mutex_unlock(&in_use->LOCK_thd_kill);
signalled= TRUE;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index f79d99c902e..410dddd9d3a 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -4288,7 +4288,8 @@ class THD: public THD_count, /* this must be first */
@retval FALSE otherwise.
*/
bool notify_shared_lock(MDL_context_owner *ctx_in_use,
- bool needs_thr_lock_abort) override;
+ bool needs_thr_lock_abort,
+ bool needs_non_slave_abort) override;
// End implementation of MDL_context_owner interface.
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index d4997793428..25be6ffeda6 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2591,6 +2591,17 @@ static Sys_var_on_access_global<
GLOBAL_VAR(slave_max_statement_time_double), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(0, LONG_TIMEOUT), DEFAULT(0), NO_MUTEX_GUARD,
NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(update_slave_max_statement_time));
+
+static Sys_var_on_access_global<
+ Sys_var_double, PRIV_SET_SYSTEM_GLOBAL_VAR_SLAVE_ABORT_BLOCKING_TIMEOUT>
+ Sys_slave_abort_blocking_timeout(
+ "slave_abort_blocking_timeout",
+ "Maximum time a slave DDL will wait for a blocking SELECT or other "
+ "user query until that query will be aborted. The argument will be "
+ "treated as a decimal value with microsecond precision",
+ GLOBAL_VAR(slave_abort_blocking_timeout), CMD_LINE(REQUIRED_ARG),
+ VALID_RANGE(0, LONG_TIMEOUT), DEFAULT(LONG_TIMEOUT), NO_MUTEX_GUARD,
+ NOT_IN_BINLOG);
#endif
--
2.39.2
