How to set max execution timeout for statement like insert

[sykp241095]

Original author: Anirudh_Modi
Original time: 2024-09-25 03:53:39.283703
Original ID/Slug: 10307 / how-to-set-max-execution-timeout-for-statement-like-insert

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

[sykp241095]

Original author: jansu-dev
Original time: 2024-12-23 07:32:02.181299

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.

[sykp241095]

Original author: TiDB-AI-Assistant
Original time: 2024-12-23 07:32:32.958093

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:

1. 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.

2. 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.

3. 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.

4. 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:

• For more details on the max-txn-ttl parameter and its implications, you can refer to the TiDB Configuration File documentation.
• Additional information on transaction timeouts in TiDB can be found in the Timeouts in TiDB documentation.

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.