Help Center/ TaurusDB/ Kernel/ Transaction Optimization/ Proactive Termination of Idle Transactions

Updated on 2026-08-04 GMT+08:00

Proactive Termination of Idle Transactions

Scenarios

If a transaction remains idle for an extended period without being committed, rolling it back will consume database resources and degrade performance. When many idle transactions accumulate and later roll back during peak hours, the performance impact can be significant. TaurusDB supports proactive termination of idle transactions. It provides distinct configuration parameters to control different transaction types (read-only and read/write). Once an idle transaction exceeds its preset timeout threshold, the system automatically rolls it back and disconnects the session.

Read/Write transactions: transactions that execute write operations such as INSERT, UPDATE, and DELETE
Read-only transactions: transactions that only execute SELECT queries, or are explicitly started using START TRANSACTION READ ONLY

What Is Idle?

The lifecycle of a connection alternates between "waiting for a client command" and "executing a client command." An idle state means that the server is waiting for the client to send the next command. As long as a SQL statement is executing, regardless of how long it takes, the connection is not considered idle and will not be disconnected by any idle timeout mechanism.

TaurusDB has a two-tiered idle timeout mechanism: idle connection timeout and idle transaction timeout.

**Table 1** TaurusDB idle timeout mechanisms
Category	Control Parameter	Applicable Scenarios	Description	Behavior Upon Timeout
Idle connection timeout	wait_timeout: idle timeout for interactive connections interactive_timeout: idle timeout for non-interactive connections	No transaction is open, or the connection is outside of any transaction.	It is a global mechanism. Any connection that remains idle longer than this threshold will be disconnected.	Disconnecting the session
Idle transaction timeout	idle_readonly_transaction_timeout: idle timeout for read-only transactions idle_write_transaction_timeout: idle timeout for read/write transactions idle_transaction_timeout: idle timeout for general transactions	An uncommitted active transaction exists on the connection.	Idle connections within a transaction pose much higher resource consumption risks than general idle connections. Lock resource consumption: may hold unreleased row or table locks, leading to concurrency blocks. Transaction slot consumption: consumes transaction log buffer (transaction slot) resources, reducing system throughput. Delayed garbage collection (GC): hinders the GC process of uncommitted transactions, increasing storage pressure. Therefore, in addition to the idle connection timeout, TaurusDB provides a transaction-level idle timeout mechanism to enforce shorter timeouts for connections in active transactions.	When a timeout occurs, the server executes the following actions: Rolling back the transaction: All uncommitted modifications are reverted. Disconnecting the session: The client receives the "ERROR 2013 (HY000): Lost connection to MySQL server during query" error.

**Table 2** Parameter comparison
Comparison Item	idle_transaction_timeout Parameters	wait_timeout/interactive_timeout
Applicable scenarios	Connections with an uncommitted, active transaction.	All connections, regardless of whether they are inside a transaction.
Default value	0 (They are disabled and must be explicitly configured.)	28800 in units of seconds (8 hours)
Timeout value	Shorter timeout values can be customized separately for read-only and read/write transactions.	A uniform timeout value applied globally, regardless of transaction type.
Timeout behavior	The system disconnects the session and automatically rolls back the uncommitted transaction.	The system disconnects the session and automatically rolls back the uncommitted transaction.
Priority	Within a transaction, *idle_transaction_timeout parameters take precedence over wait_timeout**.	It serves as the baseline timeout. Idle transaction timeout values cannot exceed this value.

Prerequisites

The kernel version must be 2.0.39.230300 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?

Constraints

The default value for all three parameters is 0, meaning that the idle transaction timeout mechanism is disabled by default. It takes effect only when explicitly configured.
Once a timeout is triggered, the uncommitted transaction is automatically rolled back. The client will not receive a descriptive application error message. It will only receive ERROR 2013.
These parameters apply at both the SESSION and GLOBAL levels. You can customize different timeout policies for individual sessions or configure a uniform policy globally.
After a transaction times out, the rolled back data cannot be recovered. Ensure that an appropriate retry mechanism is implemented on the application side.
A shorter timeout is recommended for read/write transactions. Because they hold lock resources, leaving them idle for extended periods has greater impact on other transactions.
Idle timeout is calculated only based on the duration spent waiting for a new client command. It will never be triggered during SQL execution, regardless of how long the execution takes.
The effective idle transaction timeout value will never exceed the configured wait_timeout value. If an idle_*transaction_timeout parameter is set to a value greater than wait_timeout, the actual effective value is still wait_timeout.

Parameters

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

**Table 3** Parameter description
Parameter	Level	Description
idle_readonly_transaction_timeout	Global, Session	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 no limit on the timeout for read-only transaction connections.
idle_transaction_timeout	Global, Session	Time in seconds that the server waits for common idle transactions before killing the connection. If this parameter is set to 0, there is no limit on the timeout for common idle transaction connections.
idle_write_transaction_timeout	Global, Session	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 no limit on the timeout for read/write transaction connections.

The three parameters are not evaluated independently. Instead, they are verified based on their priorities:

idle_readonly_transaction_timeout and idle_write_transaction_timeout have higher priorities than idle_transaction_timeout.
If the value of idle_readonly_transaction_timeout or idle_write_transaction_timeout is 0, the system continues checking idle_transaction_timeout. If the value of idle_transaction_timeout is 0, the idle transaction timeout mechanism is disabled for this type of transaction.
If all three parameters are set to 0, the idle transaction timeout mechanism is disabled and idle transactions will not be terminated.

**Table 4** Mechanism for transaction timeout parameters to take effect
idle_readonly_transaction_timeout	idle_write_transaction_timeout	idle_transaction_timeout	Read-only Transaction Timeout	Read/Write Transaction Timeout
> 0	> 0	Any value	idle_readonly_transaction_timeout takes effect.	idle_write_transaction_timeout takes effect.
> 0	= 0	> 0	idle_readonly_transaction_timeout takes effect.	idle_transaction_timeout takes effect.
> 0	= 0	= 0	idle_readonly_transaction_timeout takes effect.	Disabled
= 0	> 0	> 0	idle_transaction_timeout takes effect.	idle_write_transaction_timeout takes effect.
= 0	> 0	= 0	Disabled	idle_write_transaction_timeout takes effect.
= 0	= 0	> 0	idle_transaction_timeout takes effect.	idle_transaction_timeout takes effect.
= 0	= 0	= 0	Disabled	Disabled

Examples

Example 1: Read-only and Read/Write Transactions Have a Zero Timeout, and Idle Transactions Have a Non-zero Timeout

Parameter Settings
idle_transaction_timeout=10, idle_readonly_transaction_timeout=0, idle_write_transaction_timeout=0

Effect

Both read-only and read/write transactions use a 10-second timeout threshold.

**Table 5** Effect
Transaction Type	Effective Parameter	Timeout Threshold
Read-only transaction	idle_transaction_timeout	10 seconds
Read/Write transaction	idle_transaction_timeout	10 seconds

Verification

Read-only transaction
```
begin;
Query OK, 0 rows affected (0.00 sec)
```
Wait for more than 10s (exceeding the idle_transaction_timeout threshold) and then run another query. The result is as follows:
```
select * from t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
```

Read/Write transaction

Before running the begin statement to start a transaction, run a query. The result is as follows:

select * from t1;
+---------+
| col_int |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

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

insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

Wait for more than 10s (exceeding the idle_transaction_timeout threshold) and then run another query. The result is as follows:

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

Set up a new connection and run a query. The following result shows that the transaction has been rolled back.

select * from t1;
+---------+
| col_int |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Example 2: Read/Write Transactions Have a Non-zero Timeout

Parameter Settings
idle_transaction_timeout=10, idle_readonly_transaction_timeout=0, idle_write_transaction_timeout=15

Effect

Read/write transactions use a 15-second timeout threshold. Read-only transactions use the timeout threshold specified by idle_transaction_timeout because idle_readonly_transaction_timeout is set to 0.

**Table 6** Effect
Transaction Type	Effective Parameter	Timeout Threshold
Read-only transaction	idle_transaction_timeout	10 seconds
Read/Write transaction	idle_write_transaction_timeout	15 seconds

Verification

Before running the begin statement to start a transaction, run a query. The result is as follows:

select * from t1;
+---------+
| col_int |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

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

insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)

Wait for more than 15s (exceeding the idle_write_transaction_timeout threshold) and run another query. The result is as follows:

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

Set up a new connection and run a query. The following result shows that the transaction has been rolled back.

select * from t1;
+---------+
| col_int |
+---------+
|       2 |
+---------+
1 row in set (0.01 sec)

Example 3: Read-only Transactions Have a Non-zero Timeout

Parameter Settings
idle_transaction_timeout=0, idle_readonly_transaction_timeout=15, idle_write_transaction_timeout=0
Effect
Read-only transactions use a 15-second timeout threshold. The idle transaction timeout mechanism is disabled for read/write transactions because both idle_readonly_transaction_timeout and idle_transaction_timeout are set to 0.

Table 7 Effect

Transaction Type

Effective Parameter

Timeout Threshold

Read-only transaction

idle_readonly_transaction_timeout

15 seconds

Read/Write transaction

Disabled

-
Verification
```
begin;
Query OK, 0 rows affected (0.00 sec)
```
Wait for more than 15s (exceeding the idle_readonly_transaction_timeout threshold) and run another query. The result is as follows:
```
select * from t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
```

Example 4: All These Transaction Timeout Parameters Are Non-Zero

Parameter Settings
idle_transaction_timeout=30, idle_readonly_transaction_timeout=15, idle_write_transaction_timeout=10

Effect

Read-only transactions use a 15-second timeout threshold, and read/write transactions use a 10-second timeout threshold.

idle_transaction_timeout is not used by any transaction type under this configuration. Because both idle_readonly_transaction_timeout and idle_write_transaction_timeout are not set to 0, they take priority over idle_transaction_timeout.

**Table 8** Effect
Transaction Type	Effective Parameter	Timeout Threshold
Read-only transaction	idle_readonly_transaction_timeout	15 seconds
Read/Write transaction	idle_write_transaction_timeout	10 seconds

Parent Topic: Transaction Optimization

Previous topic: Transaction Optimization

Next topic: Diagnosis on Large Transactions

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

For any further questions, feel free to contact us through the chatbot.

Chatbot