Application environment:
Local development environment
TiDB version:
pingcap/tidb:v8.2.0
Reproduction method:
NA
Problem:
It was mentioned in the doc that after v6.4.0 max_execution_time
will only be applicable for read only statements.
I want to know how can we timeout queries like insert or delete which exceed a certain threshold
Resource allocation:
NA
Attachment:
NA
Hi, @Anirudh_Modi
Looks there’s no way to control non-read query.
But, IMO, It could be kinda a workaround using [max-txn-ttl]( TiDB Configuration File | TiDB Docs) to control the whole TXN time.
1 Like
The max-txn-ttl
configuration in TiDB can indeed be used as a workaround to control the execution time of non-read queries, such as INSERT
or DELETE
. This parameter sets the maximum time a transaction can hold locks, and if this time is exceeded, the transaction might be rolled back. This effectively limits the execution time of transactions that involve data modifications.
Explanation:
-
Purpose of max-txn-ttl
: The max-txn-ttl
parameter is designed to limit the maximum time a transaction can hold locks. If a transaction exceeds this time, its locks might be cleared, preventing the transaction from being successfully committed. This is particularly useful for controlling long-running transactions that could otherwise hold locks indefinitely, potentially leading to resource contention and performance issues.
-
Application to Non-Read Queries: While max-txn-ttl
is not specifically designed to control the execution time of non-read queries, it indirectly serves this purpose by limiting how long a transaction can remain active. For example, if you have a long-running INSERT INTO t10 SELECT * FROM t1
operation, it will be rolled back if it exceeds the max-txn-ttl
limit, thus controlling its execution time.
-
Configuration Details: The default value for max-txn-ttl
is 60 minutes (3600000 milliseconds), but it can be adjusted based on your requirements. For transactions executed in “bulk” DML mode, the maximum TTL can be the greater of the configured max-txn-ttl
value or 24 hours.
-
Example Usage:
- If you set
max-txn-ttl
to 30 minutes, any transaction that holds locks longer than this will be subject to rollback, effectively limiting its execution time.
SQL Example:
-- Assuming max-txn-ttl is set to 1800000 milliseconds (30 minutes)
BEGIN;
INSERT INTO t10 SELECT * FROM t1;
-- If this operation takes longer than 30 minutes, it will be rolled back.
COMMIT;
References:
By configuring max-txn-ttl
, you can effectively manage the execution time of non-read queries, ensuring that long-running transactions do not adversely affect the performance and stability of your TiDB environment.