MDEV-11418 - AliSQL: [Feature] Issue#1 KILL IDLE TRANSACTIONS

Terminate idle transactions safely in server layer by setting up socket timeout
parameter. Percona provides another patch to resolve similar problem, but it
calls server layer's callback in InnoDB plugin to close THD, which crashes in
some testcases. See https://bugs.launchpad.net/percona-server/+bug/901060 for
more detailed information.

  1. export parameter trx_idle_timeout to handle all kinds of transactions, the priority is highest
  2. export parameter trx_readonly_idle_timeout to handle read-only transactions
  3. export parameter trx_changes_idle_timeout to handle read-write transactions

11 files changed, 354 insertions, 5 deletions

diff --git a/include/thread_pool_priv.h b/include/thread_pool_priv.h
index afa2848ae88..f5fdbfbdf47 100644
--- a/include/thread_pool_priv.h
+++ b/include/thread_pool_priv.h
@@ -58,7 +58,6 @@ int thd_connection_has_data(THD *thd);
 void thd_set_net_read_write(THD *thd, uint val);
 uint thd_get_net_read_write(THD *thd);
 void thd_set_mysys_var(THD *thd, st_my_thread_var *mysys_var);
-ulong  thd_get_net_wait_timeout(THD *thd);
 my_socket thd_get_fd(THD *thd);
 int thd_store_globals(THD* thd);
 
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index 5938da489f7..9644065f887 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -270,6 +270,15 @@ The following options may be given as the first argument:
  height-balanced.
  --host-cache-size=# How many host names should be cached to avoid resolving.
  (Automatically configured unless set explicitly)
+ --idle-readonly-transaction-timeout=# 
+ The number of seconds the server waits for read-only idle
+ transaction
+ --idle-readwrite-transaction-timeout=# 
+ The number of seconds the server waits for read-write
+ idle transaction
+ --idle-transaction-timeout=# 
+ The number of seconds the server waits for idle
+ transaction
  --ignore-builtin-innodb 
  Disable initialization of builtin InnoDB plugin
  --ignore-db-dirs=name 
@@ -1255,6 +1264,9 @@ help TRUE
 histogram-size 0
 histogram-type SINGLE_PREC_HB
 host-cache-size 279
+idle-readonly-transaction-timeout 0
+idle-readwrite-transaction-timeout 0
+idle-transaction-timeout 0
 ignore-builtin-innodb FALSE
 ignore-db-dirs 
 init-connect 
diff --git a/mysql-test/r/transaction_timeout.result b/mysql-test/r/transaction_timeout.result
new file mode 100644
index 00000000000..46ac6200a7f
--- /dev/null
+++ b/mysql-test/r/transaction_timeout.result
@@ -0,0 +1,118 @@
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+Variable_name	Value
+idle_readonly_transaction_timeout	0
+idle_readwrite_transaction_timeout	0
+idle_transaction_timeout	0
+SET idle_transaction_timeout=1;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+Variable_name	Value
+idle_readonly_transaction_timeout	0
+idle_readwrite_transaction_timeout	0
+idle_transaction_timeout	1
+BEGIN;
+INSERT INTO t1 VALUES (1),(2),(3);
+COMMIT;
+SELECT * FROM t1;
+a
+1
+2
+3
+BEGIN;
+INSERT INTO t1 VALUES (4),(5),(6);
+SELECT * FROM t1;
+Got one of the listed errors
+SELECT * FROM t1;
+a
+1
+2
+3
+DROP TABLE t1;
+SET idle_transaction_timeout=0;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+Variable_name	Value
+idle_readonly_transaction_timeout	0
+idle_readwrite_transaction_timeout	0
+idle_transaction_timeout	0
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+Variable_name	Value
+idle_readonly_transaction_timeout	0
+idle_readwrite_transaction_timeout	0
+idle_transaction_timeout	0
+SET idle_readonly_transaction_timeout=1;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+Variable_name	Value
+idle_readonly_transaction_timeout	1
+idle_readwrite_transaction_timeout	0
+idle_transaction_timeout	0
+BEGIN;
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT * FROM t1;
+a
+1
+2
+3
+COMMIT;
+SELECT * FROM t1;
+a
+1
+2
+3
+SELECT * FROM t1;
+Got one of the listed errors
+SELECT * FROM t1;
+a
+1
+2
+3
+DROP TABLE t1;
+SET idle_readonly_transaction_timeout=0;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+Variable_name	Value
+idle_readonly_transaction_timeout	0
+idle_readwrite_transaction_timeout	0
+idle_transaction_timeout	0
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+Variable_name	Value
+idle_readonly_transaction_timeout	0
+idle_readwrite_transaction_timeout	0
+idle_transaction_timeout	0
+SET idle_readwrite_transaction_timeout=1;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+Variable_name	Value
+idle_readonly_transaction_timeout	0
+idle_readwrite_transaction_timeout	1
+idle_transaction_timeout	0
+BEGIN;
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT * FROM t1;
+a
+1
+2
+3
+COMMIT;
+SELECT * FROM t1;
+a
+1
+2
+3
+SELECT * FROM t1;
+a
+1
+2
+3
+INSERT INTO t1 VALUES (4),(5),(6);
+SELECT * FROM t1;
+Got one of the listed errors
+DROP TABLE t1;
+SET idle_readwrite_transaction_timeout=0;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+Variable_name	Value
+idle_readonly_transaction_timeout	0
+idle_readwrite_transaction_timeout	0
+idle_transaction_timeout	0
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 8fbb54ba0d4..97779a09b23 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -1213,6 +1213,48 @@ NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	NULL
+VARIABLE_NAME	IDLE_READONLY_TRANSACTION_TIMEOUT
+SESSION_VALUE	0
+GLOBAL_VALUE	0
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	0
+VARIABLE_SCOPE	SESSION
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	The number of seconds the server waits for read-only idle transaction
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	31536000
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	IDLE_READWRITE_TRANSACTION_TIMEOUT
+SESSION_VALUE	0
+GLOBAL_VALUE	0
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	0
+VARIABLE_SCOPE	SESSION
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	The number of seconds the server waits for read-write idle transaction
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	31536000
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	IDLE_TRANSACTION_TIMEOUT
+SESSION_VALUE	0
+GLOBAL_VALUE	0
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	0
+VARIABLE_SCOPE	SESSION
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	The number of seconds the server waits for idle transaction
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	31536000
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	IGNORE_BUILTIN_INNODB
 SESSION_VALUE	NULL
 GLOBAL_VALUE	OFF
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index e9cd0cf49b8..605d39ac14c 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -1325,6 +1325,48 @@ NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	NULL
+VARIABLE_NAME	IDLE_READONLY_TRANSACTION_TIMEOUT
+SESSION_VALUE	0
+GLOBAL_VALUE	0
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	0
+VARIABLE_SCOPE	SESSION
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	The number of seconds the server waits for read-only idle transaction
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	31536000
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	IDLE_READWRITE_TRANSACTION_TIMEOUT
+SESSION_VALUE	0
+GLOBAL_VALUE	0
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	0
+VARIABLE_SCOPE	SESSION
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	The number of seconds the server waits for read-write idle transaction
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	31536000
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	IDLE_TRANSACTION_TIMEOUT
+SESSION_VALUE	0
+GLOBAL_VALUE	0
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	0
+VARIABLE_SCOPE	SESSION
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	The number of seconds the server waits for idle transaction
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	31536000
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	IGNORE_BUILTIN_INNODB
 SESSION_VALUE	NULL
 GLOBAL_VALUE	OFF
diff --git a/mysql-test/t/transaction_timeout.test b/mysql-test/t/transaction_timeout.test
new file mode 100644
index 00000000000..e33341f1710
--- /dev/null
+++ b/mysql-test/t/transaction_timeout.test
@@ -0,0 +1,78 @@
+# Test Case For Kill All Transactions
+--source include/no_protocol.inc
+--source include/have_innodb.inc
+--source include/not_embedded.inc
+
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+SET idle_transaction_timeout=1;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+
+BEGIN;
+INSERT INTO t1 VALUES (1),(2),(3);
+COMMIT;
+SELECT * FROM t1;
+
+BEGIN;
+INSERT INTO t1 VALUES (4),(5),(6);
+sleep 2;
+
+--enable_reconnect
+--error 2006,2013
+SELECT * FROM t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+SET idle_transaction_timeout=0;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+
+# Test Case For Kill Read-Only Transaction
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+SET idle_readonly_transaction_timeout=1;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+
+BEGIN;
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT * FROM t1;
+COMMIT;
+SELECT * FROM t1;
+
+sleep 2;
+
+--enable_reconnect
+--error 2006,2013
+SELECT * FROM t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+SET idle_readonly_transaction_timeout=0;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+
+# Test Case For Kill Changes Transaction
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+SET idle_readwrite_transaction_timeout=1;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
+
+BEGIN;
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT * FROM t1;
+COMMIT;
+SELECT * FROM t1;
+
+sleep 2;
+
+SELECT * FROM t1;
+INSERT INTO t1 VALUES (4),(5),(6);
+sleep 2;
+--enable_reconnect
+--error 2006,2013
+SELECT * FROM t1;
+DROP TABLE t1;
+SET idle_readwrite_transaction_timeout=0;
+SHOW VARIABLES LIKE 'idle_%transaction_timeout';
diff --git a/sql/handler.h b/sql/handler.h
index 8b00ec8503b..fbe63e074ef 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -1476,7 +1476,7 @@ struct THD_TRANS
   bool trans_did_wait() const {
     return (m_unsafe_rollback_flags & DID_WAIT) != 0;
   }
-
+  bool is_trx_read_write() const;
 };
 
 
@@ -1576,6 +1576,16 @@ private:
 };
 
 
+inline bool THD_TRANS::is_trx_read_write() const
+{
+  Ha_trx_info *ha_info;
+  for (ha_info= ha_list; ha_info; ha_info= ha_info->next())
+    if (ha_info->is_trx_read_write())
+      return TRUE;
+  return FALSE;
+}
+
+
 enum enum_tx_isolation { ISO_READ_UNCOMMITTED, ISO_READ_COMMITTED,
 			 ISO_REPEATABLE_READ, ISO_SERIALIZABLE};
 
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 295474d0d62..e9bc199f36d 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -695,6 +695,10 @@ typedef struct system_variables
   my_bool session_track_state_change;
 
   ulong threadpool_priority;
+
+  uint idle_transaction_timeout;
+  uint idle_readonly_transaction_timeout;
+  uint idle_readwrite_transaction_timeout;
 } SV;
 
 /**
@@ -4303,6 +4307,29 @@ public:
     current_linfo= 0;
     mysql_mutex_unlock(&LOCK_thread_count);
   }
+
+
+  uint get_net_wait_timeout()
+  {
+    if (in_active_multi_stmt_transaction())
+    {
+      if (transaction.all.is_trx_read_write())
+      {
+        if (variables.idle_readwrite_transaction_timeout > 0)
+          return variables.idle_readwrite_transaction_timeout;
+      }
+      else
+      {
+        if (variables.idle_readonly_transaction_timeout > 0)
+          return variables.idle_readonly_transaction_timeout;
+      }
+
+      if (variables.idle_transaction_timeout > 0)
+        return variables.idle_transaction_timeout;
+    }
+
+    return variables.net_wait_timeout;
+  }
 };
 
 inline void add_to_active_threads(THD *thd)
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 53482454491..63240a9a96a 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1208,8 +1208,8 @@ bool do_command(THD *thd)
     the client, the connection is closed or "net_wait_timeout"
     number of seconds has passed.
   */
-  if(!thd->skip_wait_timeout)
-    my_net_set_read_timeout(net, thd->variables.net_wait_timeout);
+  if (!thd->skip_wait_timeout)
+    my_net_set_read_timeout(net, thd->get_net_wait_timeout());
 
   /*
     XXX: this code is here only to clear possible errors of init_connect. 
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 29187d70902..72786576bd4 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -3516,6 +3516,27 @@ static Sys_var_ulong Sys_net_wait_timeout(
        VALID_RANGE(1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)),
        DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1));
 
+static Sys_var_uint Sys_idle_transaction_timeout(
+       "idle_transaction_timeout",
+       "The number of seconds the server waits for idle transaction",
+       SESSION_VAR(idle_transaction_timeout), CMD_LINE(REQUIRED_ARG),
+       VALID_RANGE(0, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)),
+       DEFAULT(0), BLOCK_SIZE(1));
+
+static Sys_var_uint Sys_idle_readonly_transaction_timeout(
+       "idle_readonly_transaction_timeout",
+       "The number of seconds the server waits for read-only idle transaction",
+       SESSION_VAR(idle_readonly_transaction_timeout), CMD_LINE(REQUIRED_ARG),
+       VALID_RANGE(0, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)),
+       DEFAULT(0), BLOCK_SIZE(1));
+
+static Sys_var_uint Sys_idle_readwrite_transaction_timeout(
+       "idle_readwrite_transaction_timeout",
+       "The number of seconds the server waits for read-write idle transaction",
+       SESSION_VAR(idle_readwrite_transaction_timeout), CMD_LINE(REQUIRED_ARG),
+       VALID_RANGE(0, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)),
+       DEFAULT(0), BLOCK_SIZE(1));
+
 static Sys_var_plugin Sys_default_storage_engine(
        "default_storage_engine", "The default storage engine for new tables",
        SESSION_VAR(table_plugin), NO_CMD_LINE,
diff --git a/sql/threadpool_common.cc b/sql/threadpool_common.cc
index 643ff80de2a..44e483dfccf 100644
--- a/sql/threadpool_common.cc
+++ b/sql/threadpool_common.cc
@@ -199,7 +199,7 @@ void tp_callback(TP_connection *c)
   c->priority= get_priority(c);
 
   /* Read next command from client. */
-  c->set_io_timeout(thd->variables.net_wait_timeout);
+  c->set_io_timeout(thd->get_net_wait_timeout());
   c->state= TP_STATE_IDLE;
   if (c->start_io())
     goto error;