Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 客户端终止，TiDB数据库不终止任务
Bug Report
Long transaction statement execution, client forcibly terminates, but the database side does not terminate and eventually completes the transaction commit
[TiDB Version] 6.3 and earlier
[Impact of the Bug] Affects batch processing, etc.
[Possible Steps to Reproduce the Issue]
create table A;
sql_text = “insert into A select * from (complex query with long execution time for simulation)”
Execute sql_text on the client, expected to run for 20 minutes.
The client forcibly terminates the connection of sql_text, but the statement continues to execute in the database until the transaction is completed.
[Observed Unexpected Behavior]
The client program has timed out and terminated, but the database side continues to execute until the transaction is successfully completed. This poses a risk of duplicate batch processing when the batch is repeatedly triggered.
[Expected Behavior]
After the client interrupts the connection, the database side should immediately interrupt the running session and terminate the statement.
If you want to meet transaction operations using the client, there should be a way to explicitly control the transaction. If the transaction is not finally committed, it will not take effect.
Thank you, but this is just a workaround.
It’s not bypassing; the MySQL client has implicit transactions by default. Even if you operate MySQL, the result is the same.
I tested databases like DB2, PostgreSQL, and MySQL. When the client terminates during the execution of a long transaction (statement), the server immediately disconnects the session (statement). However, TiDB continues to execute until completion. This not only affects the client’s judgment in certain scenarios but also impacts the server’s performance. I hope this can be optimized and improved.
This should be a special feature of TiDB. TiDB currently does not support using the MySQL command line ctrl+c to terminate queries or connections. Starting from v6.1.0, TiDB has introduced the Global Kill feature (controlled by the enable-global-kill configuration item, enabled by default). When the Global Kill feature is enabled, both KILL statements and KILL TIDB statements can terminate queries or connections across nodes without worrying about mistakenly terminating other queries or connections. When you use a client to connect to any TiDB node and execute a KILL statement or KILL TIDB statement, the statement will be forwarded to the corresponding TiDB node. When there is a proxy between the client and TiDB, the KILL and KILL TIDB statements will also be forwarded to the corresponding TiDB node for execution.
This should be a special point of TiDB. TiDB currently does not support using the MySQL command line ctrl+c to terminate queries or connections.
Generally, a database corresponds to an application system, and an application system has multiple application system modules (such as backend queries for business personnel, customer transactions, batch jobs, etc. Each module will not be separately divided into two load-balanced TiDB-servers). When there is a problem with a certain functional module, I can stop that module to reduce the global impact on the database. However, in TiDB, when I stop this module, the resulting slow statements may occupy resources for a long time, leading to a decline in the performance of statements in other modules (transaction volume decreases but success rate does not). If I restart the TiDB-server to quickly recover, the success rate of my other modules will decrease.
You can manually kill slow queries.
The client can interrupt statements using ctrl+c (also applicable to applications connected via JDBC, etc.) in version 7.3:
Corresponding issue: Support CTRL-C or kill <connId> to kill a connection/query by implementing global connection IDs · Issue #8854 · pingcap/tidb · GitHub
So, this feature is already available in the 7.5 LTS version. After testing, when the application side disconnects, the database will simultaneously kill the statement and release resources. [Awesome!!!]
In the past few days, this even triggered an incident. In short, a complex query written by the application took a long time to execute. The application side had a timeout mechanism, and repeated execution after disconnection caused a backlog on the database side, leading to database lag. Although there are other countermeasures, none are as effective as the application disconnecting and automatically releasing database resources! Thumbs up!!!
|
username: 人如其名
|
username: 
|
username: