# [KB]SQL query 卡住，show processlist 显示处于执行状态 ## Key Words query hang, indexhashjoin, limit ## Description TiDB server 运行一条 SQL query 卡住，show processlist 显示处于执行状态。排除集群存在性能瓶颈，日志没有明显报错。抓取 TiDB goroutine，有如下内容： ```golang goroutine 7663764 [chan receive, 1486 minutes]: github.com/pingcap/tidb/executor.(*IndexNestedLoopHashJoin).Close(0xc019e7c840, 0x0, 0x0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/index_lookup_hash_join.go:317 +0xdc github.com/pingcap/tidb/executor.(*baseExecutor).Close(0xc016453a00, 0x0, 0x0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:181 +0x7e github.com/pingcap/tidb/executor.(*MergeJoinExec).Close(0xc016453a00, 0x0, 0xc01ca8d710) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/merge_join.go:292 +0xa5 github.com/pingcap/tidb/executor.(*baseExecutor).Close(0xc04c631b00, 0x24c, 0x42c4888) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:181 +0x7e github.com/pingcap/tidb/executor.(*ProjectionExec).Close(0xc04c631b00, 0x3d31e00, 0xc022d729b0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/projection.go:329 +0x138 github.com/pingcap/tidb/executor.(*baseExecutor).Close(0xc009cc9970, 0xc01679d8b0, 0x0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:181 +0x7e github.com/pingcap/tidb/executor.(*LimitExec).Close(0xc009cc9970, 0x1289325, 0xc022d72948) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:1151 +0x45 github.com/pingcap/tidb/executor.(*baseExecutor).Close(0xc016710000, 0xc022d729b0, 0x5f42700) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:181 +0x7e github.com/pingcap/tidb/executor.(*HashJoinExec).Close(0xc016710000, 0x1010366a000, 0xc02c38c4f0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/join.go:153 +0x334 github.com/pingcap/tidb/executor.(*SortExec).Close(0xc01667db80, 0x0, 0x0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/sort.go:81 +0xee github.com/pingcap/tidb/executor.(*baseExecutor).Close(0xc016710240, 0xc022d72ae8, 0x5f42700) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:181 +0x7e github.com/pingcap/tidb/executor.(*HashJoinExec).Close(0xc016710240, 0x0, 0x0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/join.go:153 +0x334 github.com/pingcap/tidb/executor.(*SortExec).Close(0xc01667dcc0, 0x0, 0x0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/sort.go:81 +0xee github.com/pingcap/tidb/executor.(*baseExecutor).Close(0xc003dc8b00, 0x29, 0x42c4888) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:181 +0x7e github.com/pingcap/tidb/executor.(*ProjectionExec).Close(0xc003dc8b00, 0x0, 0xc01ca8d9b0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/projection.go:329 +0x138 github.com/pingcap/tidb/executor.(*baseExecutor).Close(0xc003dc8c00, 0x28, 0x42c4888) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/executor.go:181 +0x7e github.com/pingcap/tidb/executor.(*ProjectionExec).Close(0xc003dc8c00, 0x4000, 0x3345) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/projection.go:329 +0x138 github.com/pingcap/tidb/executor.(*recordSet).Close(0xc01679d770, 0x34acb85, 0xc022d72ec0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/executor/adapter.go:171 +0x38 github.com/pingcap/tidb/session.(*execStmtResult).Close(0xc02509d980, 0x34b787a, 0xc049b3c080) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/session/session.go:1741 +0x42 github.com/pingcap/tidb/server.(*tidbResultSet).Close(0xc01679d7c0, 0x3345, 0x0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/driver_tidb.go:324 +0x4a github.com/pingcap/tidb/parser/terror.Call(0xc022d72fd8) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/parser/terror/terror.go:298 +0x3f github.com/pingcap/tidb/server.(*clientConn).executePreparedStmtAndWriteResult(0xc00b958140, 0x42c3cf0, 0xc0a651a540, 0x42f6688, 0xc010f4e620, 0xc0ae1a77a0, 0x3, 0x3, 0x0, 0x0, ...) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn_stmt.go:255 +0x45e github.com/pingcap/tidb/server.(*clientConn).handleStmtExecute(0xc00b958140, 0x42c3c48, 0xc0a651a540, 0xc000778a51, 0x29, 0x29, 0x0, 0x0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn_stmt.go:205 +0x645 github.com/pingcap/tidb/server.(*clientConn).dispatch(0xc00b958140, 0x42c3c48, 0xc02917f6c0, 0xc000778a50, 0x2a, 0x29, 0x0, 0x0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1346 +0x110f github.com/pingcap/tidb/server.(*clientConn).Run(0xc00b958140, 0x42c3cf0, 0xc0b878b7a0) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/conn.go:1079 +0x29b github.com/pingcap/tidb/server.(*Server).onConn(0xc0011bc0d0, 0xc00b958140) /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:553 +0xaa5 created by github.com/pingcap/tidb/server.(*Server).startNetworkListener /home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/server/server.go:455 +0x91c ``` ## Root Cause SQL 使用 Limit 执行计划走上 IndexHashJoin 使触发了[bug](https://github.com/pingcap/tidb/pull/35820)。bug 描述：LimitExec.Close() 会调用 IndexHashJoinExec.Close() ，IndexHashJoinExec 的 main routine 不再从 channel 接收 join 出来的结果（因为接收数据的逻辑在 IndexHashJoinExec.Next() 中），导致 channel 被塞满，而 worker routine 卡在往 channel 塞数据那里，main routine 又卡在等待 worker routine 结束那里，导致整个 hang 住。 ## Diagnostic Steps 1. 通过 TiDB 监控发现 TiDB、TiKV 不存在瓶颈问题。 2. TiDB server 日志没有明显报错。 3. 抓取 goroutine，发现有 goroutine 处于 (\*IndexNestedLoopHashJoin).Close 阶段很长时间。 ## Resolution 升级到未受影响版本 ## Workaround 使用 hint 或者 SQL binding，用 IndexJoin 替换 IndexHashJoin。 ## Affected Versions v4.0.x v5.0.0~v5.0.6 v5.1.0~v5.1.4 v5.2.0~v5.2.4 v5.3.0~v5.3.3 v5.4.0~v5.4.2 v6.0.0 v6.1.0