更新时间:2024-07-02 GMT+08:00

主动终止空闲事务

功能介绍

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

功能特点

华为云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     |
+-----------------------------------+-------+
表1 参数总览

参数名

参数类型

取值范围

是否动态生效

参数描述

idle_readonly_transaction_timeout

integer

正整数

控制只读事务连接的超时时间(单位:秒),参数设置为0时不生效,即表示只读事务连接的超时时间没有限制。

idle_transaction_timeout

integer

正整数

控制一般空闲事务连接的超时时间(单位:秒),参数设置为0时不生效,即表示一般空闲事务的连接超时时间没有限制。

说明:

“idle_readonly_transaction_timeout”和“idle_write_transaction_timeout”参数的优先级高于“idle_transaction_timeout”参数。

  • 如果设置“idle_readonly_transaction_timeout”或“idle_write_transaction_timeout”参数值并使之生效,那么“idle_transaction_timeout”参数将会失效。
  • 如果仅设置“idle_transaction_timeout”参数值并使之生效,那么在进行事务的读写操作时,超时时间均以“idle_transaction_timeout”设置的参数值为准。

idle_write_transaction_timeout

integer

正整数

控制读写事务连接的超时时间(单位:秒),参数设置为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)