Update SQL with Variables Freezes

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

Original topic: update sql 带变量卡死

| username: TiDBer_Ok0TdPp0

[TiDB Usage Environment] Production Environment
[TiDB Version] v7.5.1
[Reproduction Path] set @tidb_seq_id=1;

select @tidb_seq_id;

update tb_uhome_acct_item a, sdc_dws.tb_uhome_fee_item_type b, map_organ_list c
set a.group_id=b.group_id
WHERE b.fee_item_type_id=a.fee_item_type_id
and a.community_id=c.organ_id
and c.seq_id=@tidb_seq_id;
[Encountered Problem: Phenomenon and Impact] When the update statement includes the variable @tidb_seq_id, it hangs. If the variable is not used, the update completes in seconds.

| username: TiDBer_Ok0TdPp0 | Original post link

The image is not visible. Please provide the text you need translated.

| username: TIDB-Learner | Original post link

Post the results of the analyze.
Have you tried using join?
Is the table data large?

| username: TiDBer_Ok0TdPp0 | Original post link

Not large, more than 30 million. It takes more than 2 minutes to run. If the SQL is directly changed to specific values, it updates successfully very quickly. Join also doesn’t work.

| username: kkpeter | Original post link

Send the execution plan.

| username: zhaokede | Original post link

It is necessary to analyze based on the execution plan.

| username: 随缘天空 | Original post link

How about directly associating the entire select statement?

| username: Kongdom | Original post link

Try running EXPLAIN ANALYZE to check. It is likely that type conversion is preventing the use of the primary key or index.

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

Please post the execution plan. It’s probably due to not using the index.

| username: TiDBer_RjzUpGDL | Original post link

It is estimated that the index was not used, which is indeed a problem.

| username: yytest | Original post link

Is there an execution plan? Can you explain it?

| username: db_user | Original post link

The variable has been parsed into a string, right?

| username: changpeng75 | Original post link

Try using prepared statements

| username: FutureDB | Original post link

What is the data type of seq_id?

| username: TIDB-Learner | Original post link

The variable didn’t use the index.

| username: 这里介绍不了我 | Original post link

Show the execution plan.

| username: zhh_912 | Original post link

Check if it’s the peak business period causing the locks.