The transaction still exists after using kill tidb query to terminate the query

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

Original topic: 查询事务用kill tidb query之后还存在

| username: Johnpan

[TiDB Usage Environment]
tidb v5.4.0

[Overview] Scenario + Problem Overview
There is an SQL query involving multiple table joins that is relatively slow. I used kill tidb query 645; to kill the process, but after execution, the transaction remains in the “in transaction” state. This happens repeatedly. Has it become a zombie process? Is there any way to remove it?

[Background] Operations performed

[Phenomenon] Business and database phenomena

[Problem] Current issue encountered

[Business Impact]

[TiDB Version]

[Application Software and Version]

[Attachments] Relevant logs and configuration information

  • TiUP Cluster Display information
  • TiUP Cluster Edit config information

Monitoring (

  • TiDB-Overview Grafana monitoring
  • TiDB Grafana monitoring
  • TiKV Grafana monitoring
  • PD Grafana monitoring
  • Corresponding module logs (including logs one hour before and after the issue)

If the question is related to performance optimization or troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output results for upload.

| username: Johnpan | Original post link

| username: db_user | Original post link

May I ask if the kill command is executed on the corresponding host? That is, you need to execute the kill tidb command on the TiDB where the query is running.

| username: BraveChen | Original post link

If you can come to ask, you won’t make this kind of mistake, haha.

| username: BraveChen | Original post link

I recently tested this issue. In TiDB 5.3.2, for this type of SQL (most likely multi-table joins with a large limit m, n), after each query, a zombie session with continuously increasing time appears in the background and cannot be killed. In TiDB 5.4.2, the zombie session still appears but can be killed. In TiDB 6.1.1, the zombie session does not appear based on actual tests.

| username: CAICAI | Original post link

I also encountered this situation in version 5.4. Killing the TiDB ID (transaction ID) and killing the TiDB session_id, even operating on the corresponding TiDB host, still couldn’t kill it. Do I have to upgrade to 6.1?

| username: forever | Original post link

Looking at previous posts, it’s always about restarting the TiDB server. :sweat_smile:

| username: BraveChen | Original post link

Our production version is 5.3.2. We are currently considering how to address this issue and whether we need to upgrade. A temporary solution is to force the index hash join in the relevant SQL to be hinted as an index join, which can bypass this bug. This has been tested and is effective.

| username: BraveChen | Original post link

Restarting can indeed kill zombie sessions, but you can’t keep restarting. :rofl::rofl::see_no_evil::see_no_evil:

| username: buddyyuan | Original post link

You encountered this issue:

It should be fixed in version 5.4.1.

| username: BraveChen | Original post link

This issue is similar to [indexHashJoin hang in handleTask · Issue #35638 · pingcap/tidb (], you can refer to it. However, this has already been fixed in version 5.4 and cannot be reproduced anymore.

| username: buddyyuan | Original post link

Actually, it has already been killed, but it still shows up here. To clear it, just restart the tidb-server; it doesn’t have much impact. To specifically determine whether it has been killed, observe the tidb logs. As long as there is a “kill” entry in the tidb logs, it means the kill has been initiated.

| username: Johnpan | Original post link

Okay, let me take a look.

| username: Johnpan | Original post link

Yes, I just re-executed kill tidb 645; and it worked.

| username: 张雨齐0720 | Original post link

For this issue, the lower version requires a restart each time. Maybe consider upgrading.

| username: Johnpan | Original post link

Well, I also want to upgrade, but the architect doesn’t agree.

| username: 张雨齐0720 | Original post link

If they can accept this bug, then so be it.
Sometimes when we need to upgrade versions, we also encounter various obstacles.
Make a report on the cause of the problem and the solution, send it to them, and let them make the decision.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.