Hi! On Tue, Feb 21, 2017 at 2:37 PM, Sergey Vojtovich <svoj@mariadb.org> wrote:

Hi Monty,

Please review this patch.

Thanks, Sergey

----- Forwarded message from Sergey Vojtovich <svoj@mariadb.org> -----

Date: Tue, 21 Feb 2017 16:29:36 +0400 From: Sergey Vojtovich <svoj@mariadb.org> To: commits@mariadb.org Subject: [Commits] 02d155a: MDEV-11418 - AliSQL: [Feature] Issue#1 KILL IDLE TRANSACTIONS

revision-id: 02d155aeec87aac8a827a29328d68ecb156094cc (mariadb-10.2.2-162-g02d155a) parent(s): 1563354570c5634cb2b448b47a138b4ceaff1a72 committer: Sergey Vojtovich timestamp: 2017-02-21 16:28:42 +0400 message:

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

<cut>

+BEGIN; +INSERT INTO t1 VALUES (4),(5),(6); +SELECT * FROM t1; +ERROR HY000: MySQL server has gone away

It's not optimal to get this kind of error for a timeout as it's make it's very hard for the application to know what is going on. An alternative approach would be to instead of closing the connection do a rollback and for next query send an error "transactions aborted and rolled back because of transaction timeout" What do you think about this approach? To get this done, we should remember in 'net' that we are using a transaction timeout (not default) and when we get the timeout we should do: - Do part of the rollback code + unlock tables in THD::cleanup() (Probably move to a sub function) - Remember that we did a timeout rollback. - When we got next statement and timout_rollback was done, give an error, clear flag and wait for next statement.

+++ b/sql/sql_parse.cc @@ -1209,7 +1209,23 @@ bool do_command(THD *thd) number of seconds has passed. */ if(!thd->skip_wait_timeout) - my_net_set_read_timeout(net, thd->variables.net_wait_timeout); + { + if (thd->in_active_multi_stmt_transaction()) + { + bool is_trx_rw= thd->transaction.all.is_trx_read_write(); + + if (thd->variables.trx_idle_timeout > 0) + my_net_set_read_timeout(net, thd->variables.trx_idle_timeout); + else if (thd->variables.trx_readonly_idle_timeout > 0 && !is_trx_rw) + my_net_set_read_timeout(net, thd->variables.trx_readonly_idle_timeout); + else if (thd->variables.trx_changes_idle_timeout > 0 && is_trx_rw) + my_net_set_read_timeout(net, thd->variables.trx_changes_idle_timeout); + else + my_net_set_read_timeout(net, thd->variables.net_wait_timeout); + } + else + my_net_set_read_timeout(net, thd->variables.net_wait_timeout); + }

Please do it this way instead (less code): if (!thd->skip_wait_timeout) { ulong timeout= thd->variables.net_wait_timeout; if (thd->in_active_multi_stmt_transaction()) { bool is_trx_rw= thd->transaction.all.is_trx_read_write(); if (thd->variables.trx_idle_timeout > 0) timeout= thd->variables.trx_idle_timeout; else if (thd->variables.trx_readonly_idle_timeout > 0 && !is_trx_rw) timeout= thd->variables.trx_readonly_idle_timeout; else if (thd->variables.trx_changes_idle_timeout > 0 && is_trx_rw) timeout= thd->variables.trx_changes_idle_timeout; } my_net_set_read_timeout(net, timeout); } Regards, Monty