[packages/mysql.git] / innodb_kill_idle_transaction.patch

# name       : innodb_kill_idle_transaction.patch
# introduced : 5.5.15
# maintainer : Yasufumi
#
#!!! notice !!!
# Any small change to this file in the main branch
# should be done or reviewed by the maintainer!
--- a/include/mysql/plugin.h
+++ b/include/mysql/plugin.h
@@ -634,6 +634,12 @@
 */
 void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton,
                      const void *ha_data);
+
+int thd_command(const MYSQL_THD thd);
+long long thd_start_time(const MYSQL_THD thd);
+void thd_kill(unsigned long id);
+#define EXTENDED_FOR_KILLIDLE
+
 #ifdef __cplusplus
 }
 #endif
--- a/include/mysql/plugin_audit.h.pp
+++ b/include/mysql/plugin_audit.h.pp
@@ -196,6 +196,9 @@
 void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
 void thd_set_ha_data(void* thd, const struct handlerton *hton,
                      const void *ha_data);
+int thd_command(const void* thd);
+long long thd_start_time(const void* thd);
+void thd_kill(unsigned long id);
 struct mysql_event_general
 {
   unsigned int event_subclass;
--- a/include/mysql/plugin_auth.h.pp
+++ b/include/mysql/plugin_auth.h.pp
@@ -196,6 +196,9 @@
 void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
 void thd_set_ha_data(void* thd, const struct handlerton *hton,
                      const void *ha_data);
+int thd_command(const void* thd);
+long long thd_start_time(const void* thd);
+void thd_kill(unsigned long id);
 #include <mysql/plugin_auth_common.h>
 typedef struct st_plugin_vio_info
 {
--- a/include/mysql/plugin_ftparser.h.pp
+++ b/include/mysql/plugin_ftparser.h.pp
@@ -149,6 +149,9 @@
 void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
 void thd_set_ha_data(void* thd, const struct handlerton *hton,
                      const void *ha_data);
+int thd_command(const void* thd);
+long long thd_start_time(const void* thd);
+void thd_kill(unsigned long id);
 enum enum_ftparser_mode
 {
   MYSQL_FTPARSER_SIMPLE_MODE= 0,
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -713,6 +713,42 @@
   return buffer;
 }
 
+/* extend for kill session of idle transaction from engine */
+extern "C"
+int thd_command(const THD* thd)
+{
+  return (int) thd->command;
+}
+
+extern "C"
+long long thd_start_time(const THD* thd)
+{
+  return (long long) thd->start_time;
+}
+
+extern "C"
+void thd_kill(ulong id)
+{
+  THD *tmp;
+  mysql_mutex_lock(&LOCK_thread_count);
+  I_List_iterator<THD> it(threads);
+  while ((tmp=it++))
+  {
+    if (tmp->command == COM_DAEMON)
+      continue;
+    if (tmp->thread_id == id)
+    {
+      mysql_mutex_lock(&tmp->LOCK_thd_data);
+      break;
+    }
+  }
+  mysql_mutex_unlock(&LOCK_thread_count);
+  if (tmp)
+  {
+    tmp->awake(THD::KILL_CONNECTION);
+    mysql_mutex_unlock(&tmp->LOCK_thd_data);
+  }
+}
 
 /**
   Implementation of Drop_table_error_handler::handle_condition().
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -2860,6 +2860,10 @@
 
 	innobase_commit_concurrency_init_default();
 
+#ifndef EXTENDED_FOR_KILLIDLE
+	srv_kill_idle_transaction = 0;
+#endif
+
 #ifdef HAVE_PSI_INTERFACE
 	/* Register keys with MySQL performance schema */
 	if (PSI_server) {
@@ -11694,6 +11698,57 @@
 	return(false);
 }
 
+/***********************************************************************
+functions for kill session of idle transaction */
+extern "C"
+ibool
+innobase_thd_is_idle(
+/*=================*/
+	const void*	thd)	/*!< in: thread handle (THD*) */
+{
+#ifdef EXTENDED_FOR_KILLIDLE
+	return(thd_command((const THD*) thd) == COM_SLEEP);
+#else
+	return(FALSE);
+#endif
+}
+
+extern "C"
+ib_int64_t
+innobase_thd_get_start_time(
+/*========================*/
+	const void*	thd)	/*!< in: thread handle (THD*) */
+{
+#ifdef EXTENDED_FOR_KILLIDLE
+	return((ib_int64_t)thd_start_time((const THD*) thd));
+#else
+	return(0); /*dummy value*/
+#endif
+}
+
+extern "C"
+void
+innobase_thd_kill(
+/*==============*/
+	ulong	thd_id)
+{
+#ifdef EXTENDED_FOR_KILLIDLE
+	thd_kill(thd_id);
+#else
+	return;
+#endif
+}
+
+extern "C"
+ulong
+innobase_thd_get_thread_id(
+/*=======================*/
+	const void*	thd)
+{
+	return(thd_get_thread_id((const THD*) thd));
+}
+
+
 static SHOW_VAR innodb_status_variables_export[]= {
   {"Innodb",                   (char*) &show_innodb_vars, SHOW_FUNC},
   {NullS, NullS, SHOW_LONG}
@@ -11985,6 +12040,15 @@
   "Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket",
   NULL, NULL, 500L, 1L, ~0L, 0);
 
+static MYSQL_SYSVAR_LONG(kill_idle_transaction, srv_kill_idle_transaction,
+  PLUGIN_VAR_RQCMDARG,
+#ifdef EXTENDED_FOR_KILLIDLE
+  "If non-zero value, the idle session with transaction which is idle over the value in seconds is killed by InnoDB.",
+#else
+  "No effect for this build.",
+#endif
+  NULL, NULL, 0, 0, LONG_MAX, 0);
+
 static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY | PLUGIN_VAR_NOSYSVAR,
   "Number of file I/O threads in InnoDB.",
@@ -12327,6 +12391,7 @@
   MYSQL_SYSVAR(fast_checksum),
   MYSQL_SYSVAR(commit_concurrency),
   MYSQL_SYSVAR(concurrency_tickets),
+  MYSQL_SYSVAR(kill_idle_transaction),
   MYSQL_SYSVAR(data_file_path),
   MYSQL_SYSVAR(doublewrite_file),
   MYSQL_SYSVAR(data_home_dir),
--- a/storage/innobase/include/srv0srv.h
+++ b/storage/innobase/include/srv0srv.h
@@ -299,6 +299,7 @@
 extern ulint	srv_activity_count;
 extern ulint	srv_fatal_semaphore_wait_threshold;
 extern ulint	srv_dml_needed_delay;
+extern lint	srv_kill_idle_transaction;
 
 extern mutex_t*	kernel_mutex_temp;/* mutex protecting the server, trx structs,
 				query threads, and lock table: we allocate
--- a/storage/innobase/include/trx0trx.h
+++ b/storage/innobase/include/trx0trx.h
@@ -594,6 +594,8 @@
 					replication has processed */
 	const char*	mysql_relay_log_file_name;
 	ib_int64_t	mysql_relay_log_pos;
+	time_t		idle_start;
+	ib_int64_t	last_stmt_start;
 	/*------------------------------*/
 	ulint		n_mysql_tables_in_use; /* number of Innobase tables
 					used in the processing of the current
--- a/storage/innobase/srv/srv0srv.c
+++ b/storage/innobase/srv/srv0srv.c
@@ -87,6 +87,12 @@
 #include "mysql/plugin.h"
 #include "mysql/service_thd_wait.h"
 
+/* prototypes of new functions added to ha_innodb.cc for kill_idle_transaction */
+ibool		innobase_thd_is_idle(const void* thd);
+ib_int64_t	innobase_thd_get_start_time(const void* thd);
+void		innobase_thd_kill(ulong thd_id);
+ulong		innobase_thd_get_thread_id(const void* thd);
+
 /* prototypes for new functions added to ha_innodb.cc */
 ibool	innobase_get_slow_log();
 
@@ -97,6 +103,9 @@
 /* The following is the maximum allowed duration of a lock wait. */
 UNIV_INTERN ulint	srv_fatal_semaphore_wait_threshold = 600;
 
+/**/
+UNIV_INTERN lint	srv_kill_idle_transaction = 0;
+
 /* How much data manipulation language (DML) statements need to be delayed,
 in microseconds, in order to reduce the lagging of the purge thread. */
 UNIV_INTERN ulint	srv_dml_needed_delay = 0;
@@ -2837,6 +2846,36 @@
 		old_sema = sema;
 	}
 
+	if (srv_kill_idle_transaction && trx_sys) {
+		trx_t*	trx;
+		time_t	now;
+rescan_idle:
+		now = time(NULL);
+		mutex_enter(&kernel_mutex);
+		trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list);
+		while (trx) {
+			if (trx->conc_state == TRX_ACTIVE
+			    && trx->mysql_thd
+			    && innobase_thd_is_idle(trx->mysql_thd)) {
+				ib_int64_t	start_time = innobase_thd_get_start_time(trx->mysql_thd);
+				ulong		thd_id = innobase_thd_get_thread_id(trx->mysql_thd);
+
+				if (trx->last_stmt_start != start_time) {
+					trx->idle_start = now;
+					trx->last_stmt_start = start_time;
+				} else if (difftime(now, trx->idle_start)
+					   > srv_kill_idle_transaction) {
+					/* kill the session */
+					mutex_exit(&kernel_mutex);
+					innobase_thd_kill(thd_id);
+					goto rescan_idle;
+				}
+			}
+			trx = UT_LIST_GET_NEXT(mysql_trx_list, trx);
+		}
+		mutex_exit(&kernel_mutex);
+	}
+
 	/* Flush stderr so that a database user gets the output
 	to possible MySQL error file */
 
--- a/storage/innobase/trx/trx0trx.c
+++ b/storage/innobase/trx/trx0trx.c
@@ -143,6 +143,9 @@
 	trx->mysql_relay_log_file_name = "";
 	trx->mysql_relay_log_pos = 0;
 
+	trx->idle_start = 0;
+	trx->last_stmt_start = 0;
+
 	mutex_create(trx_undo_mutex_key, &trx->undo_mutex, SYNC_TRX_UNDO);
 
 	trx->rseg = NULL;
--- /dev/null
+++ b/mysql-test/r/percona_innodb_kill_idle_trx.result
@@ -0,0 +1,41 @@
+DROP TABLE IF EXISTS t1;
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
+Variable_name	Value
+innodb_kill_idle_transaction	0
+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_KILL_IDLE_TRANSACTION	0
+SET GLOBAL innodb_kill_idle_transaction=1;
+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
+Variable_name	Value
+innodb_kill_idle_transaction	1
+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_KILL_IDLE_TRANSACTION	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;
+ERROR HY000: MySQL server has gone away
+SELECT * FROM t1;
+a
+1
+2
+3
+DROP TABLE t1;
+SET GLOBAL innodb_kill_idle_transaction=0;
+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
+Variable_name	Value
+innodb_kill_idle_transaction	0
+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_KILL_IDLE_TRANSACTION	0
--- /dev/null
+++ b/mysql-test/r/percona_innodb_kill_idle_trx_locks.result
@@ -0,0 +1,45 @@
+DROP TABLE IF EXISTS t1;
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
+Variable_name	Value
+innodb_kill_idle_transaction	0
+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_KILL_IDLE_TRANSACTION	0
+SET GLOBAL innodb_kill_idle_transaction=5;
+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
+Variable_name	Value
+innodb_kill_idle_transaction	5
+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_KILL_IDLE_TRANSACTION	5
+BEGIN;
+INSERT INTO t1 VALUES (1),(2),(3);
+COMMIT;
+SELECT * FROM t1;
+a
+1
+2
+3
+### Locking rows. Lock should be released when idle trx is killed.
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
+a
+1
+2
+3
+UPDATE t1 set a=4;
+SELECT * FROM t1;
+a
+4
+4
+4
+DROP TABLE t1;
+SET GLOBAL innodb_kill_idle_transaction=0;
+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
+Variable_name	Value
+innodb_kill_idle_transaction	0
+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_KILL_IDLE_TRANSACTION	0
--- /dev/null
+++ b/mysql-test/t/percona_innodb_kill_idle_trx.test
@@ -0,0 +1,28 @@
+--source include/have_innodb.inc
+--disable_warnings
+DROP TABLE IF EXISTS t1; 
+--enable_warnings
+
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+
+--source include/percona_innodb_kill_idle_trx_show.inc
+SET GLOBAL innodb_kill_idle_transaction=1;
+--source include/percona_innodb_kill_idle_trx_show.inc
+
+BEGIN;
+INSERT INTO t1 VALUES (1),(2),(3);
+COMMIT;
+SELECT * FROM t1;
+
+BEGIN;
+INSERT INTO t1 VALUES (4),(5),(6);
+sleep 3;
+
+--enable_reconnect
+--error 2006 --error CR_SERVER_GONE_ERROR
+SELECT * FROM t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+SET GLOBAL innodb_kill_idle_transaction=0;
+--source include/percona_innodb_kill_idle_trx_show.inc
--- /dev/null
+++ b/mysql-test/t/percona_innodb_kill_idle_trx_locks.test
@@ -0,0 +1,31 @@
+--source include/have_innodb.inc
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+SET autocommit=0;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+
+--source include/percona_innodb_kill_idle_trx_show.inc
+SET GLOBAL innodb_kill_idle_transaction=5;
+--source include/percona_innodb_kill_idle_trx_show.inc
+
+connect (conn1,localhost,root,,);
+connection conn1;
+
+BEGIN;
+INSERT INTO t1 VALUES (1),(2),(3);
+COMMIT;
+SELECT * FROM t1;
+
+--echo ### Locking rows. Lock should be released when idle trx is killed.
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
+
+connection default;
+UPDATE t1 set a=4;
+
+SELECT * FROM t1;
+DROP TABLE t1;
+SET GLOBAL innodb_kill_idle_transaction=0;
+--source include/percona_innodb_kill_idle_trx_show.inc
--- /dev/null
+++ b/mysql-test/include/percona_innodb_kill_idle_trx_show.inc
@@ -0,0 +1,2 @@
+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
Commit	Line	Data
734d6226 AM	1	# name : innodb_kill_idle_transaction.patch
	2	# introduced : 5.5.15
	3	# maintainer : Yasufumi
	4	#
	5	#!!! notice !!!
	6	# Any small change to this file in the main branch
	7	# should be done or reviewed by the maintainer!
	8	--- a/include/mysql/plugin.h
	9	+++ b/include/mysql/plugin.h
	10	@@ -634,6 +634,12 @@
	11	*/
	12	void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton,
	13	const void *ha_data);
	14	+
	15	+int thd_command(const MYSQL_THD thd);
	16	+long long thd_start_time(const MYSQL_THD thd);
	17	+void thd_kill(unsigned long id);
	18	+#define EXTENDED_FOR_KILLIDLE
	19	+
	20	#ifdef __cplusplus
	21	}
	22	#endif
	23	--- a/include/mysql/plugin_audit.h.pp
	24	+++ b/include/mysql/plugin_audit.h.pp
	25	@@ -196,6 +196,9 @@
	26	void thd_get_ha_data(const void thd, const struct handlerton *hton);
	27	void thd_set_ha_data(void* thd, const struct handlerton *hton,
	28	const void *ha_data);
	29	+int thd_command(const void* thd);
	30	+long long thd_start_time(const void* thd);
	31	+void thd_kill(unsigned long id);
	32	struct mysql_event_general
	33	{
	34	unsigned int event_subclass;
	35	--- a/include/mysql/plugin_auth.h.pp
	36	+++ b/include/mysql/plugin_auth.h.pp
	37	@@ -196,6 +196,9 @@
	38	void thd_get_ha_data(const void thd, const struct handlerton *hton);
	39	void thd_set_ha_data(void* thd, const struct handlerton *hton,
	40	const void *ha_data);
	41	+int thd_command(const void* thd);
	42	+long long thd_start_time(const void* thd);
	43	+void thd_kill(unsigned long id);
	44	#include <mysql/plugin_auth_common.h>
	45	typedef struct st_plugin_vio_info
	46	{
	47	--- a/include/mysql/plugin_ftparser.h.pp
	48	+++ b/include/mysql/plugin_ftparser.h.pp
	49	@@ -149,6 +149,9 @@
	50	void thd_get_ha_data(const void thd, const struct handlerton *hton);
	51	void thd_set_ha_data(void* thd, const struct handlerton *hton,
	52	const void *ha_data);
	53	+int thd_command(const void* thd);
	54	+long long thd_start_time(const void* thd);
	55	+void thd_kill(unsigned long id);
	56	enum enum_ftparser_mode
	57	{
	58	MYSQL_FTPARSER_SIMPLE_MODE= 0,
	59	--- a/sql/sql_class.cc
	60	+++ b/sql/sql_class.cc
	61	@@ -713,6 +713,42 @@
	62	return buffer;
	63	}
	64
65	+/* extend for kill session of idle transaction from engine */
66	+extern "C"
67	+int thd_command(const THD* thd)
68	+{
69	+ return (int) thd->command;
70	+}
71	+
72	+extern "C"
73	+long long thd_start_time(const THD* thd)
74	+{
75	+ return (long long) thd->start_time;
76	+}
77	+
78	+extern "C"
79	+void thd_kill(ulong id)
80	+{
81	+ THD *tmp;
82	+ mysql_mutex_lock(&LOCK_thread_count);
83	+ I_List_iterator<THD> it(threads);
84	+ while ((tmp=it++))
85	+ {
86	+ if (tmp->command == COM_DAEMON)
87	+ continue;
88	+ if (tmp->thread_id == id)
89	+ {
90	+ mysql_mutex_lock(&tmp->LOCK_thd_data);
91	+ break;
92	+ }
93	+ }
94	+ mysql_mutex_unlock(&LOCK_thread_count);
95	+ if (tmp)
96	+ {
97	+ tmp->awake(THD::KILL_CONNECTION);
98	+ mysql_mutex_unlock(&tmp->LOCK_thd_data);
99	+ }
100	+}
101
102	/**
103	Implementation of Drop_table_error_handler::handle_condition().
104	--- a/storage/innobase/handler/ha_innodb.cc
105	+++ b/storage/innobase/handler/ha_innodb.cc
106	@@ -2860,6 +2860,10 @@
107
108	innobase_commit_concurrency_init_default();
109
110	+#ifndef EXTENDED_FOR_KILLIDLE
111	+ srv_kill_idle_transaction = 0;
112	+#endif
113	+
114	#ifdef HAVE_PSI_INTERFACE
115	/* Register keys with MySQL performance schema */
116	if (PSI_server) {
29ffd636	117	@@ -11694,6 +11698,57 @@
734d6226 AM	118	return(false);
	119	}
	120
	121	+/***********************************************************************
	122	+functions for kill session of idle transaction */
	123	+extern "C"
	124	+ibool
	125	+innobase_thd_is_idle(
	126	+/=================/
	127	+ const void* thd) /!< in: thread handle (THD) */
	128	+{
	129	+#ifdef EXTENDED_FOR_KILLIDLE
	130	+ return(thd_command((const THD*) thd) == COM_SLEEP);
	131	+#else
	132	+ return(FALSE);
	133	+#endif
	134	+}
	135	+
	136	+extern "C"
	137	+ib_int64_t
	138	+innobase_thd_get_start_time(
	139	+/========================/
	140	+ const void* thd) /!< in: thread handle (THD) */
	141	+{
	142	+#ifdef EXTENDED_FOR_KILLIDLE
	143	+ return((ib_int64_t)thd_start_time((const THD*) thd));
	144	+#else
	145	+ return(0); /dummy value/
	146	+#endif
	147	+}
	148	+
	149	+extern "C"
	150	+void
	151	+innobase_thd_kill(
	152	+/==============/
	153	+ ulong thd_id)
	154	+{
	155	+#ifdef EXTENDED_FOR_KILLIDLE
	156	+ thd_kill(thd_id);
	157	+#else
	158	+ return;
	159	+#endif
	160	+}
	161	+
	162	+extern "C"
	163	+ulong
	164	+innobase_thd_get_thread_id(
	165	+/=======================/
	166	+ const void* thd)
	167	+{
	168	+ return(thd_get_thread_id((const THD*) thd));
	169	+}
	170	+
	171	+
	172	static SHOW_VAR innodb_status_variables_export[]= {
	173	{"Innodb", (char*) &show_innodb_vars, SHOW_FUNC},
	174	{NullS, NullS, SHOW_LONG}
29ffd636	175	@@ -11985,6 +12040,15 @@
734d6226 AM	176	"Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket",
	177	NULL, NULL, 500L, 1L, ~0L, 0);
	178
	179	+static MYSQL_SYSVAR_LONG(kill_idle_transaction, srv_kill_idle_transaction,
	180	+ PLUGIN_VAR_RQCMDARG,
	181	+#ifdef EXTENDED_FOR_KILLIDLE
	182	+ "If non-zero value, the idle session with transaction which is idle over the value in seconds is killed by InnoDB.",
	183	+#else
	184	+ "No effect for this build.",
	185	+#endif
	186	+ NULL, NULL, 0, 0, LONG_MAX, 0);
	187	+
	188	static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads,
	189	PLUGIN_VAR_RQCMDARG \| PLUGIN_VAR_READONLY \| PLUGIN_VAR_NOSYSVAR,
	190	"Number of file I/O threads in InnoDB.",
29ffd636	191	@@ -12327,6 +12391,7 @@
734d6226 AM	192	MYSQL_SYSVAR(fast_checksum),
	193	MYSQL_SYSVAR(commit_concurrency),
	194	MYSQL_SYSVAR(concurrency_tickets),
	195	+ MYSQL_SYSVAR(kill_idle_transaction),
	196	MYSQL_SYSVAR(data_file_path),
	197	MYSQL_SYSVAR(doublewrite_file),
	198	MYSQL_SYSVAR(data_home_dir),
	199	--- a/storage/innobase/include/srv0srv.h
	200	+++ b/storage/innobase/include/srv0srv.h
29ffd636	201	@@ -299,6 +299,7 @@
734d6226 AM	202	extern ulint srv_activity_count;
	203	extern ulint srv_fatal_semaphore_wait_threshold;
	204	extern ulint srv_dml_needed_delay;
	205	+extern lint srv_kill_idle_transaction;
	206
	207	extern mutex_t* kernel_mutex_temp;/* mutex protecting the server, trx structs,
	208	query threads, and lock table: we allocate
	209	--- a/storage/innobase/include/trx0trx.h
	210	+++ b/storage/innobase/include/trx0trx.h
	211	@@ -594,6 +594,8 @@
	212	replication has processed */
	213	const char* mysql_relay_log_file_name;
	214	ib_int64_t mysql_relay_log_pos;
	215	+ time_t idle_start;
	216	+ ib_int64_t last_stmt_start;
	217	/------------------------------/
	218	ulint n_mysql_tables_in_use; /* number of Innobase tables
	219	used in the processing of the current
	220	--- a/storage/innobase/srv/srv0srv.c
	221	+++ b/storage/innobase/srv/srv0srv.c
	222	@@ -87,6 +87,12 @@
	223	#include "mysql/plugin.h"
	224	#include "mysql/service_thd_wait.h"
	225
	226	+/* prototypes of new functions added to ha_innodb.cc for kill_idle_transaction */
	227	+ibool innobase_thd_is_idle(const void* thd);
	228	+ib_int64_t innobase_thd_get_start_time(const void* thd);
	229	+void innobase_thd_kill(ulong thd_id);
	230	+ulong innobase_thd_get_thread_id(const void* thd);
	231	+
	232	/* prototypes for new functions added to ha_innodb.cc */
	233	ibool innobase_get_slow_log();
	234
	235	@@ -97,6 +103,9 @@
	236	/* The following is the maximum allowed duration of a lock wait. */
	237	UNIV_INTERN ulint srv_fatal_semaphore_wait_threshold = 600;
	238
	239	+/**/
	240	+UNIV_INTERN lint srv_kill_idle_transaction = 0;
	241	+
	242	/* How much data manipulation language (DML) statements need to be delayed,
	243	in microseconds, in order to reduce the lagging of the purge thread. */
	244	UNIV_INTERN ulint srv_dml_needed_delay = 0;
29ffd636	245	@@ -2837,6 +2846,36 @@
734d6226 AM	246	old_sema = sema;
	247	}
	248
	249	+ if (srv_kill_idle_transaction && trx_sys) {
	250	+ trx_t* trx;
	251	+ time_t now;
	252	+rescan_idle:
	253	+ now = time(NULL);
	254	+ mutex_enter(&kernel_mutex);
	255	+ trx = UT_LIST_GET_FIRST(trx_sys->mysql_trx_list);
	256	+ while (trx) {
	257	+ if (trx->conc_state == TRX_ACTIVE
	258	+ && trx->mysql_thd
	259	+ && innobase_thd_is_idle(trx->mysql_thd)) {
	260	+ ib_int64_t start_time = innobase_thd_get_start_time(trx->mysql_thd);
	261	+ ulong thd_id = innobase_thd_get_thread_id(trx->mysql_thd);
	262	+
	263	+ if (trx->last_stmt_start != start_time) {
	264	+ trx->idle_start = now;
	265	+ trx->last_stmt_start = start_time;
	266	+ } else if (difftime(now, trx->idle_start)
	267	+ > srv_kill_idle_transaction) {
	268	+ /* kill the session */
	269	+ mutex_exit(&kernel_mutex);
	270	+ innobase_thd_kill(thd_id);
	271	+ goto rescan_idle;
	272	+ }
	273	+ }
	274	+ trx = UT_LIST_GET_NEXT(mysql_trx_list, trx);
	275	+ }
	276	+ mutex_exit(&kernel_mutex);
	277	+ }
	278	+
	279	/* Flush stderr so that a database user gets the output
	280	to possible MySQL error file */
	281
	282	--- a/storage/innobase/trx/trx0trx.c
	283	+++ b/storage/innobase/trx/trx0trx.c
	284	@@ -143,6 +143,9 @@
	285	trx->mysql_relay_log_file_name = "";
	286	trx->mysql_relay_log_pos = 0;
	287
	288	+ trx->idle_start = 0;
	289	+ trx->last_stmt_start = 0;
	290	+
	291	mutex_create(trx_undo_mutex_key, &trx->undo_mutex, SYNC_TRX_UNDO);
	292
	293	trx->rseg = NULL;
	294	--- /dev/null
	295	+++ b/mysql-test/r/percona_innodb_kill_idle_trx.result
	296	@@ -0,0 +1,41 @@
	297	+DROP TABLE IF EXISTS t1;
	298	+SET autocommit=0;
	299	+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
	300	+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
	301	+Variable_name Value
	302	+innodb_kill_idle_transaction 0
	303	+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
	304	+VARIABLE_NAME VARIABLE_VALUE
	305	+INNODB_KILL_IDLE_TRANSACTION 0
	306	+SET GLOBAL innodb_kill_idle_transaction=1;
	307	+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
	308	+Variable_name Value
	309	+innodb_kill_idle_transaction 1
310	+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
311	+VARIABLE_NAME VARIABLE_VALUE
312	+INNODB_KILL_IDLE_TRANSACTION 1
313	+BEGIN;
314	+INSERT INTO t1 VALUES (1),(2),(3);
315	+COMMIT;
316	+SELECT * FROM t1;
317	+a
318	+1
319	+2
320	+3
321	+BEGIN;
322	+INSERT INTO t1 VALUES (4),(5),(6);
323	+SELECT * FROM t1;
324	+ERROR HY000: MySQL server has gone away
325	+SELECT * FROM t1;
326	+a
327	+1
328	+2
329	+3
330	+DROP TABLE t1;
331	+SET GLOBAL innodb_kill_idle_transaction=0;
332	+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
333	+Variable_name Value
334	+innodb_kill_idle_transaction 0
335	+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
336	+VARIABLE_NAME VARIABLE_VALUE
337	+INNODB_KILL_IDLE_TRANSACTION 0
338	--- /dev/null
339	+++ b/mysql-test/r/percona_innodb_kill_idle_trx_locks.result
340	@@ -0,0 +1,45 @@
341	+DROP TABLE IF EXISTS t1;
342	+SET autocommit=0;
343	+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
344	+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
345	+Variable_name Value
346	+innodb_kill_idle_transaction 0
347	+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
348	+VARIABLE_NAME VARIABLE_VALUE
349	+INNODB_KILL_IDLE_TRANSACTION 0
350	+SET GLOBAL innodb_kill_idle_transaction=5;
351	+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
352	+Variable_name Value
353	+innodb_kill_idle_transaction 5
354	+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
355	+VARIABLE_NAME VARIABLE_VALUE
356	+INNODB_KILL_IDLE_TRANSACTION 5
357	+BEGIN;
358	+INSERT INTO t1 VALUES (1),(2),(3);
359	+COMMIT;
360	+SELECT * FROM t1;
361	+a
362	+1
363	+2
364	+3
365	+### Locking rows. Lock should be released when idle trx is killed.
366	+BEGIN;
367	+SELECT * FROM t1 FOR UPDATE;
368	+a
369	+1
370	+2
371	+3
372	+UPDATE t1 set a=4;
373	+SELECT * FROM t1;
374	+a
375	+4
376	+4
377	+4
378	+DROP TABLE t1;
379	+SET GLOBAL innodb_kill_idle_transaction=0;
380	+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
381	+Variable_name Value
382	+innodb_kill_idle_transaction 0
383	+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
384	+VARIABLE_NAME VARIABLE_VALUE
385	+INNODB_KILL_IDLE_TRANSACTION 0
386	--- /dev/null
387	+++ b/mysql-test/t/percona_innodb_kill_idle_trx.test
388	@@ -0,0 +1,28 @@
389	+--source include/have_innodb.inc
390	+--disable_warnings
391	+DROP TABLE IF EXISTS t1;
392	+--enable_warnings
393	+
394	+SET autocommit=0;
395	+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
396	+
397	+--source include/percona_innodb_kill_idle_trx_show.inc
398	+SET GLOBAL innodb_kill_idle_transaction=1;
399	+--source include/percona_innodb_kill_idle_trx_show.inc
400	+
401	+BEGIN;
402	+INSERT INTO t1 VALUES (1),(2),(3);
403	+COMMIT;
404	+SELECT * FROM t1;
405	+
406	+BEGIN;
407	+INSERT INTO t1 VALUES (4),(5),(6);
408	+sleep 3;
409	+
410	+--enable_reconnect
411	+--error 2006 --error CR_SERVER_GONE_ERROR
412	+SELECT * FROM t1;
413	+SELECT * FROM t1;
414	+DROP TABLE t1;
415	+SET GLOBAL innodb_kill_idle_transaction=0;
416	+--source include/percona_innodb_kill_idle_trx_show.inc
417	--- /dev/null
418	+++ b/mysql-test/t/percona_innodb_kill_idle_trx_locks.test
419	@@ -0,0 +1,31 @@
420	+--source include/have_innodb.inc
421	+--disable_warnings
422	+DROP TABLE IF EXISTS t1;
423	+--enable_warnings
424	+
425	+SET autocommit=0;
426	+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
427	+
428	+--source include/percona_innodb_kill_idle_trx_show.inc
429	+SET GLOBAL innodb_kill_idle_transaction=5;
430	+--source include/percona_innodb_kill_idle_trx_show.inc
431	+
432	+connect (conn1,localhost,root,,);
433	+connection conn1;
434	+
435	+BEGIN;
436	+INSERT INTO t1 VALUES (1),(2),(3);
437	+COMMIT;
438	+SELECT * FROM t1;
439	+
440	+--echo ### Locking rows. Lock should be released when idle trx is killed.
441	+BEGIN;
442	+SELECT * FROM t1 FOR UPDATE;
443	+
444	+connection default;
445	+UPDATE t1 set a=4;
446	+
447	+SELECT * FROM t1;
448	+DROP TABLE t1;
449	+SET GLOBAL innodb_kill_idle_transaction=0;
450	+--source include/percona_innodb_kill_idle_trx_show.inc
451	--- /dev/null
452	+++ b/mysql-test/include/percona_innodb_kill_idle_trx_show.inc
453	@@ -0,0 +1,2 @@
454	+SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
455	+SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';