Update Stuck

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

Original topic: update 更新卡住

| username: 小鱼吃大鱼

Partitioned tables and non-partitioned tables, performing a left join operation, querying over 20 million rows based on partitions, the update has been stuck and not committing.

| username: 小鱼吃大鱼 | Original post link

The default value of tidb_enable_clustered_index is INT_ONLY, which means that only tables with integer primary keys will use clustered indexes by default.

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

“SELECT * FROM information_schema.CLUSTER_TIDB_TRX; Let’s see if there are any locks.”

| username: TiDBer_pkQ5q1l0 | Original post link

It probably didn’t finish executing.

| username: xfworld | Original post link

Try reducing the data size; it might be too large, causing a timeout and rollback.

| username: 小鱼吃大鱼 | Original post link

There is a lock, START_TIME: 2023-04-03 09:47:39.223000
CURRENT_SQL_DIGEST: 6cf71fa94dd0666d7ec67ce208da45d6115dbbfd501714b40dbb061221f33454
STATE: LockWaiting
WAITING_START_TIME: 2023-04-03 12:48:42.544918

The information_schema.data_lock_waits table is empty, how can I check which SQL caused it?

| username: 小鱼吃大鱼 | Original post link

The status has changed to Idle again, is it still executing?

| username: xingzhenxiang | Original post link

You can add a limit to the update statement.

20 million should not be executed directly; even if killed, it will take a long time to roll back.

| username: 小鱼吃大鱼 | Original post link

Sorry, it’s not 20 million, it’s 2 million. I added an extra zero. It kept executing without any locks, and now I’ve killed it. It’s quite strange. It’s a partitioned table, and the execution was done in batches according to the partitions. Data larger than this partition has already been executed.

| username: Min_Chen | Original post link


Please provide the cluster version and the following information. The methods are as follows:

  1. EXPLAIN FOR CONNECTION connection_id
    connection_id is the session id currently executing this SQL, obtained through show processlist.

  2. curl http://{tidb-ip}:10080/debug/zip?seconds=60 --output debug.zip
    tidb-ip is the IP of the TiDB instance currently executing this SQL, and 10080 is the default value for the status port.