← Back to team overview

maria-developers team mailing list archive

Re: [svoj@xxxxxxxxxxx: [Commits] 02d155a: MDEV-11418 - AliSQL: [Feature] Issue#1 KILL IDLE TRANSACTIONS]

 

Hi!



On Tue, Feb 21, 2017 at 2:37 PM, Sergey Vojtovich <svoj@xxxxxxxxxxx> wrote:
> Hi Monty,
>
> Please review this patch.
>
> Thanks,
> Sergey
>
> ----- Forwarded message from Sergey Vojtovich <svoj@xxxxxxxxxxx> -----
>
> Date: Tue, 21 Feb 2017 16:29:36 +0400
> From: Sergey Vojtovich <svoj@xxxxxxxxxxx>
> To: commits@xxxxxxxxxxx
> 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