This document describes various timeouts in TiDB to help you troubleshoot errors.
TiDB's transaction implementation uses the MVCC (Multiple Version Concurrency Control) mechanism. When the newly written data overwrites the old data, the old data will not be replaced, but kept together with the newly written data. The versions are distinguished by the timestamp. TiDB uses the mechanism of periodic Garbage Collection (GC) to clean up the old data that is no longer needed.
By default, each MVCC version (consistency snapshots) is kept for 10 minutes. Transactions that take longer than 10 minutes to read will receive an error
GC life time is shorter than transaction duration.
If you need longer read time, for example, when you are using Dumpling for full backups (Dumpling backs up consistent snapshots), you can adjust the value of
tikv_gc_life_time in the
mysql.tidb table in TiDB to increase the MVCC version retention time. Note that
tikv_gc_life_time takes effect globally and immediately. Increasing the value will increase the life time of all existing snapshots, and decreasing it will immediately shorten the life time of all snapshots. Too many MVCC versions will impact TiKV's processing efficiency. So you need to change
tikv_gc_life_time back to the previous setting in time after doing a full backup with Dumpling.
For more information about GC, see GC Overview.
In scenarios where a transaction starts but is neither committed nor rolled back, you might need a finer-grained control and a shorter timeout to prevent prolonged lock holding. In this case, you can use
tidb_idle_transaction_timeout (introduced in TiDB v7.6.0) to control the idle timeout for transactions in a user session.
GC does not affect ongoing transactions. However, there is still an upper limit to the number of pessimistic transactions that can run, with a limit on the transaction timeout and a limit on the memory used by the transaction. You can modify the transaction timeout by
max-txn-ttl under the
[performance] category of the TiDB profile,
60 minutes by default.
SQL statements such as
INSERT INTO t10 SELECT * FROM t1 are not affected by GC, but will be rolled back due to timeout after exceeding
TiDB also provides a system variable (
0 by default, indicating no limit) to limit the execution time of a single SQL statement. Currently, the system variable only takes effect for read-only SQL statements. The unit of
ms, but the actual precision is at the
100ms level instead of the millisecond level.
MySQL JDBC's query timeout setting for
setQueryTimeout() does NOT work for TiDB, because the client sends a
KILL command to the database when it detects the timeout. However, the tidb-server is load balanced, and it will not execute this
KILL command to avoid termination of the connection on a wrong tidb-server. You need to use
MAX_EXECUTION_TIME to check the query timeout effect.
TiDB provides the following MySQL-compatible timeout control parameters.
- wait_timeout, controls the non-interactive idle timeout for the connection to Java applications. Since TiDB v5.4, the default value of
28800seconds, which is 8 hours. For TiDB versions earlier than v5.4, the default value is
0, which means the timeout is unlimited.
- interactive_timeout, controls the interactive idle timeout for the connection to Java applications. The value is
8 hoursby default.
- max_execution_time, controls the timeout for SQL execution in the connection, only effective for read-only SQL statements. The value is
0by default, which allows the connection to be infinitely busy, that is, an SQL statement is executed for an infinitely long time.
However, in a real production environment, idle connections and indefinitely executing SQL statements have a negative effect on both the database and the application. You can avoid idle connections and indefinitely executing SQL statements by configuring these two session-level variables in your application's connection string. For example, set the following: