How to Cancel Batch Deletion Tasks

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 怎么取消批量删除任务

| username: 飞翔的草莓

【TiDB Usage Environment】Production Environment
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
Executed a batch delete, deleting all data before June 1st from a certain table.
BATCH ON id LIMIT 1000 DELETE FROM mg_control_inventory WHERE create_time < ‘2024-06-01 00:00:00’

【Encountered Issue: Issue Phenomenon and Impact】
After executing SHOW FULL PROCESSLIST, it was found that there was no DELETE statement being executed, but in the management backend, the DELETE statement was shown to be executing. How can I find the corresponding task?

【Attachments: Screenshots/Logs/Monitoring】

| username: 小龙虾爱大龙虾 | Original post link

Can’t see the execution of batch statements? :thinking:

| username: Daniel-W | Original post link

SELECT * FROM information_schema.cluster_processlist;
Let’s see if there is any.

| username: tidb菜鸟一只 | Original post link

Is your SQL still executing? If it is executing normally, you should be able to see it with SHOW PROCESSLIST;.

| username: 飞翔的草莓 | Original post link

Now it appears, but after I KILL it, it automatically reappears after a while. How can I cancel that batch delete task?

| username: 飞翔的草莓 | Original post link

I can see it, but after I KILL it, it reappears after a while.

| username: tidb菜鸟一只 | Original post link

KILL TIDB <processlist_id>
Is this how you kill it? Why do you have multiple? How many windows did you open to execute this SQL?

| username: forever | Original post link

Is it a recurring task being executed? Should we stop the task first?

| username: 飞翔的草莓 | Original post link

We need to stop this task, which is executed in batch mode, but we can’t find the root task.

| username: 飞翔的草莓 | Original post link

The KILL command only kills one session, and after it’s deleted, it comes back again.

| username: TIDB-Learner | Original post link

Is the client running a loop script that continuously sends requests? For example, find the process of the (non-TiDB) client and delete it?
I previously wrote a loop query synchronization script using DataX. Deleting the Python request process solved the issue. If there are 100 loop statements, you would have to delete them 100 times in TiDB. Not sure if this is of any reference.

| username: porpoiselxj | Original post link

It seems that there is a scheduling thread, and it needs to be found and killed.

| username: zhaokede | Original post link

Couldn’t find the main process, right?

| username: tidb菜鸟一只 | Original post link

So, what tool did you use to execute this SQL? It might be an issue with your tool. Try killing the tool’s process and see if that helps.

| username: zhanggame1 | Original post link

The output of select * from INFORMATION_SCHEMA.CLUSTER_PROCESSLIST is incomplete.

| username: TiDBer_3Cusx9uk | Original post link

If it doesn’t work, try restarting the host.

| username: TiDBer_7S8XqKfl-1158 | Original post link

TiDB provides some system tables that can help you monitor and manage running tasks. You can query the INFORMATION_SCHEMA.cluster_processlist table to view the list of processes on all TiDB instances in the cluster.

| username: TiDBer_rvITcue9 | Original post link

INFORMATION_SCHEMA.cluster_processlist table

| username: vincentLi | Original post link

show processlist only displays tasks on the TiDB server node you are connected to, which is not comprehensive. You need to check INFORMATION_SCHEMA.cluster_processlist.
### Can the kill command be executed in TiDB?