There is a long query connection, kill ID does not work, state is in transaction

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

Original topic: 有一个长查询连接,kill ID不起作用,state状态是in transaction

| username: baozixiaoge

[TiDB Usage Environment] Production Environment
[TiDB Version]
V6.1.2

[Encountered Problem: Problem Phenomenon and Impact]
There is a long query connection, and killing the ID does not work. The state status is “in transaction.”
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

| username: zhanggame1 | Original post link

Try killing TiDB XXXX.

| username: baozixiaoge | Original post link

Tried using kill tidb query ID but it didn’t work either.

| username: Kongdom | Original post link

According to the documentation, the current version should support global kill.

Check the status of the corresponding statement with show processlist, has it changed?

| username: zhanggame1 | Original post link

Show a screenshot of

select * from INFORMATION_SCHEMA.CLUSTER_PROCESSLIST t
where t.COMMAND <>'Sleep'
| username: 像风一样的男子 | Original post link

Is the amount of data selected very large?

| username: 路在何chu | Original post link

Is the data being rolled back?

| username: 路在何chu | Original post link

Was it killed on the corresponding TiDB node?

| username: baozixiaoge | Original post link

This connection has been up for several days.

| username: baozixiaoge | Original post link

This version already supports global kill connections, right?

| username: 像风一样的男子 | Original post link

Your situation is similar to mine, but I was updating, and it got stuck for several days.

| username: Kongdom | Original post link

There is no need to kill the corresponding TiDB node, global kill is already supported.

| username: Kongdom | Original post link

:yum: For several days like this, you need to check if a colleague wrote SQL without committing the transaction. The ultimate solution is to restart the TiDB node.

| username: 路在何chu | Original post link

Oh, okay.

| username: baozixiaoge | Original post link

It looks like we should wait a couple more days to see if it resolves itself. If it doesn’t, we can try restarting a TiDB node.

| username: 像风一样的男子 | Original post link

If it doesn’t consume resources and doesn’t affect usage, observe it for a few days first. Restarting is the ultimate solution.

| username: Kongdom | Original post link

Actually, it’s the same. Although this is a select, it is within a transaction. It is estimated that, like us, it uses transaction locks such as for update.

| username: 胡杨树旁 | Original post link

Check if there is any change in memory. If there is no change, it might be a bug, and you need to restart the corresponding TiDB node.

| username: baozixiaoge | Original post link

I checked the monitoring, and there are sufficient idle resources. Let’s observe for a few days.

| username: zhanggame1 | Original post link

If you can’t kill it, just restart. It probably won’t fix itself.