Updated on 2024-09-06 GMT+08:00

Example

  1. Set idle_transaction_timeout to 10, idle_readonly_transaction_timeout to 0, and idle_write_transaction_timeout to 0.
    • Read-only transactions
      mysql> begin;
      Query OK, 0 rows affected (0.00 sec)

      Wait for 10 seconds and run a query statement again. The following information is displayed.

      mysql> select * from t1;
      ERROR 2013 (HY000): Lost connection to MySQL server during query
    • Read/Write Transaction

      Run the begin statement to start a transaction and run a query statement. The following information is displayed.

      mysql> select * from t1;
      +---------+
      | col_int |
      +---------+
      |       1 |
      +---------+
      1 row in set (0.00 sec)
      mysql> begin;
      Query OK, 0 rows affected (0.00 sec)
      ​
      mysql> insert into t1 values(2);
      Query OK, 1 row affected (0.00 sec)

      Wait for 10 seconds and run a query statement again. The following information is displayed.

      mysql> select * from t1;
      ERROR 2013 (HY000): Lost connection to MySQL server during query

      Reconnect the transaction to the database and run a query statement. If the following information is displayed, the transaction has been rolled back.

      mysql> select * from t1;
      +---------+
      | col_int |
      +---------+
      |       1 |
      +---------+
      1 row in set (0.00 sec)
  2. Set idle_write_transaction_timeout to 15.
    • Read/Write transactions

      Run the begin statement to start a transaction and run a query statement. The following information is displayed.

      mysql> select * from t1;
      +---------+
      | col_int |
      +---------+
      |       2 |
      +---------+
      1 row in set (0.00 sec)
      ​
      mysql> begin;
      Query OK, 0 rows affected (0.00 sec)
      ​
      mysql> insert into t1 values(3);
      Query OK, 1 row affected (0.00 sec)

      Wait for 15 seconds and run a query statement again. The following information is displayed.

      mysql> select * from t1;
      ERROR 2013 (HY000): Lost connection to MySQL server during query

      Reconnect the transaction to the database and run a query statement. If the following information is displayed, the transaction has been rolled back.

      mysql> select * from t1;
      +---------+
      | col_int |
      +---------+
      |       2 |
      +---------+
      1 row in set (0.01 sec)
  3. Set idle_readonly_transaction_timeout to 15.
    • Read-only transactions
      mysql> begin;
      Query OK, 0 rows affected (0.00 sec)

      Wait for 15 seconds and run a query statement again. The following information is displayed.

      mysql> select * from t1;
      ERROR 2013 (HY000): Lost connection to MySQL server during query