Note:
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.
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.
“SELECT * FROM information_schema.CLUSTER_TIDB_TRX; Let’s see if there are any locks.”
Try reducing the data size; it might be too large, causing a timeout and rollback.
There is a lock, START_TIME: 2023-04-03 09:47:39.223000
CURRENT_SQL_DIGEST: 6cf71fa94dd0666d7ec67ce208da45d6115dbbfd501714b40dbb061221f33454
CURRENT_SQL_DIGEST_TEXT: NULL
STATE: LockWaiting
WAITING_START_TIME: 2023-04-03 12:48:42.544918
MEM_BUFFER_KEYS: 0
MEM_BUFFER_BYTES: 0
SESSION_ID: 671
The information_schema.data_lock_waits table is empty, how can I check which SQL caused it?
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.
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.
Hello,
Please provide the cluster version and the following information. The methods are as follows:
EXPLAIN FOR CONNECTION connection_id
connection_id is the session id currently executing this SQL, obtained through show processlist.
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.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: