主动终止空闲事务

功能介绍

当一个事务长时间空闲且不提交，执行回滚操作时，会对数据库的资源和性能造成损耗。如果有大量的空闲事务长期不执行也不提交、回滚，尤其是在业务高峰期的时候，性能损耗会较为严重。

功能特点

华为云RDS for MySQL针对空闲事务有超时回滚断开连接机制，具有以下特点：

• 针对不同类型的事务有不同的参数进行控制 。
• 当空闲事务超时后会被自动的回滚并断开连接。

参数介绍

通过show variables查询相关参数。

show variables like '%idle%';
+------------------------------------+------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| idle_readonly_transaction_timeout | 0     |
| idle_transaction_timeout          | 0     |
| idle_write_transaction_timeout    | 0     |
+-----------------------------------+-------+

应用场景

show variables like '%idle%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| idle_readonly_transaction_timeout | 5     |
| idle_transaction_timeout          | 10    |
| idle_write_transaction_timeout    | 15    |  
+-----------------------------------+-------+

• 设置idle_readonly_transaction_timeout

  设置参数idle_readonly_transaction_timeout=5。

  1. 使用begin开启事务，执行查询语句，查询结果如下：

     begin;
     Query OK, 0 rows affected (0.00 sec)

     select * from t1;
     +-----+-----+-----+-----+
     | a   |  b  |  c  | d   | 
     +-----+-----+-----+-----+
     | 1   |  b  | 303 | d   |
     +-----+-----+-----+-----+
     1 row in set (0.00 sec)

  2. 在idle_readonly_transaction_timeout设置的5s范围以外执行一次查询操作，结果如下：

     select * from t1;
     +-----+-----+-----+-----+
     | a   |  b  |  c  | d   | 
     +-----+-----+-----+-----+
     | 1   |  b  | 303 | d   |
     +-----+-----+-----+-----+
     1 row in set (0.00 sec)

     select * from t1;
     ERROR 2006(HY000): MySQL server has gone away

• 设置idle_transaction_timeout，idle_readonly_transaction_timeout，idle_write_transaction_timeout
  设置参数idle_transaction_timeout=10，idle_readonly_transaction_timeout=0，idle_write_transaction_timeout=0。

  show variables like '%idle%';
  +-----------------------------------+-------+
  | Variable_name                     | Value |
  +-----------------------------------+-------+
  | idle_readonly_transaction_timeout | 0     |
  | idle_transaction_timeout          | 10    |
  | idle_write_transaction_timeout    | 0     |
  +-----------------------------------+-------+
  3 rows in set (0.01 sec)

  • 只读事务

    当idle_readonly_transaction_timeout=0时，idle_transaction_timeout参数生效。

    1. 使用begin开启事务，查询表数据，结果如下：

       begin;
       Query OK, 0 rows affected (0.00 sec)

       select * from t1;
       +-----+-----+-----+-----+
       | a   |  b  |  c  | d   | 
       +-----+-----+-----+-----+
       | 1   |  b  |  43 | d   |
       +-----+-----+-----+-----+
       1 row in set (0.00 sec)

    2. 10s之后重复执行上述查询操作，结果如下：

       select * from t1;
       ERROR 2006(HY000): MySQL server has gone away

  • 读写事务

    当idle_write_transaction_timeout=0时，，idle_transaction_timeout参数生效。

    1. 使用begin开启事务后，插入数据，在10s以内执行一条查询语句，结果如下：

       begin;
       Query OK, 0 rows affected (0.00 sec)

       INSERT INTO t1(a,b,c,d) VALUES (1,'b',FLOOR( 1 + (RAND()*1000)) ,'d');
       Query OK, 1 rows affected (0.00 sec)

       select * from t1;
       +-----+-----+-----+-----+
       | a   |  b  |  c  | d   | 
       +-----+-----+-----+-----+
       | 1   |  b  |  425 | d   |
       +-----+-----+-----+-----+
       1 row in set (0.00 sec)

    2. 10s后执行一条查询语句，结果如下：

       select * from t1;
       ERROR 2006(HY000): MySQL server has gone away

    3. 之后单独执行一条查表语句，结果如下，表示此时事务已经回滚。

       select * from t1;
       Empty set (0.00 sec)

• 设置idle_write_transaction_timeout

  设置参数idle_write_transaction_timeout=15。

  1. 首先由begin开启事务并插入一条数据，结果如下：

     begin;
     Query OK, 0 rows affected (0.00 sec)

     INSERT INTO t1(a,b,c,d) VALUES (1,'b',FLOOR( 1 + (RAND()*1000)) ,'d');
     Query OK, 1 rows affected (0.00 sec)

  2. 在idle_write_transaction_timeout设置的15s范围以内执行一次查询操作，结果如下：

     select * from t1;
     +-----+-----+-----+-----+
     | a   |  b  |  c  | d   | 
     +-----+-----+-----+-----+
     | 1   |  b  | 987 | d   |
     +-----+-----+-----+-----+
     1 row in set (0.00 sec)

  3. 15s后再次执行查询，结果如下：

     select * from t1;
     ERROR 2006(HY000): MySQL server has gone away

  4. 重新连接数据库，执行一条查询语句，结果如下，表示此时事务已经回滚。

     select * from t1;
     Empty set (0.00 sec)