Ending Idle Transactions

Introduction

If an idle transaction is not committed for a long time, its rollback will consume database resources and performance. If a large number of idle transactions are not committed and not rolled back for a long time, the performance loss to a database is severe especially during peak hours.

Characteristics

Huawei Cloud RDS for MySQL supports idle transaction disconnection when a rollback timed out. This function has the following characteristics:

Different parameters are used to control different types of transactions.
When idle transactions timed out, they are automatically rolled back and disconnected.

Description

Run show variables to query related parameters.

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

**Table 1** Parameter description
Parameter	Type	Value Range	Dynamic Validation	Description
idle_readonly_transaction_timeout	integer	Positive integer	Yes	Time in seconds that the server waits for idle read-only transactions before killing the connection. If this parameter is set to 0, there is not timeout threshold for idle read-only transactions.
idle_transaction_timeout	integer	Positive integer	Yes	Time in seconds that the server waits for common idle transactions before killing the connection. If this parameter is set to 0, there is not timeout threshold for common idle transactions. NOTE: The parameters idle_readonly_transaction_timeout and idle_write_transaction_timeout have higher priorities than the parameter idle_transaction_timeout. If you set a value for idle_readonly_transaction_timeout or idle_write_transaction_timeout and validate the value, idle_transaction_timeout becomes invalid. If only the parameter idle_transaction_timeout has been set and validated, the value of this parameter is used as the timeout interval for read and write operations on transactions.
idle_write_transaction_timeout	integer	Positive integer	Yes	Time in seconds that the server waits for idle read/write transactions before killing the connection. If this parameter is set to 0, there is not timeout threshold for idle read/write transactions.

Application Scenarios

The parameters are set as follows.

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

Setting idle_readonly_transaction_timeout

Set idle_readonly_transaction_timeout to 5.

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

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)

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

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

Setting idle_transaction_timeout, idle_readonly_transaction_timeout, and idle_write_transaction_timeout

Set idle_transaction_timeout to 10, idle_readonly_transaction_timeout to 0, and idle_write_transaction_timeout to 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)

Read-only transactions
When idle_readonly_transaction_timeout is set to 0, the idle_transaction_timeout parameter takes effect.
1. Run the begin statement to start a transaction and run a statement to query the table data. The following information is displayed.
```
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. Wait for 10 seconds and run a query statement again. The following information is displayed.
```
select * from t1;
ERROR 2006(HY000): MySQL server has gone away
```

Read/write transactions
When idle_write_transaction_timeout is set to 0, the idle_transaction_timeout parameter takes effect.
1. Run the begin statement to start a transaction, insert data, and run a query statement within 10 seconds. The following information is displayed.
```
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. Wait for 10 seconds and run a query statement again. The following information is displayed.
```
select * from t1;
ERROR 2006(HY000): MySQL server has gone away
```
3. Independently run a statement to query the table. If the following information is displayed, the transaction has been rolled back.
```
select * from t1;
Empty set (0.00 sec)
```

Setting idle_write_transaction_timeout
Set idle_write_transaction_timeout to 15.
1. Run the begin statement to start a transaction and then insert a data record. The following information is displayed.
```
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. Run a query statement within 15 seconds of the time range specified by idle_write_transaction_timeout. The following information is displayed.
```
select * from t1;
+-----+-----+-----+-----+
| a   |  b  |  c  | d   | 
+-----+-----+-----+-----+
| 1   |  b  | 987 | d   |
+-----+-----+-----+-----+
1 row in set (0.00 sec)
```
3. Wait for 15 seconds and run a query statement again. The following information is displayed.
```
select * from t1;
ERROR 2006(HY000): MySQL server has gone away
```
4. Reconnect the transaction to the database and run a query statement. If the following information is displayed, the transaction has been rolled back.
```
select * from t1;
Empty set (0.00 sec)
```