Batch Update Using Prepare, Only Updating Partial Data

Translated
1

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

Original topic: batch update 用prepare，只更新部分数据

| username: foxchan

[TiDB Usage Environment] Production Environment
17A8L4~5J(H8K65K1}BX}3D
A total of 16,000 records, running 1000 loops,

[2023/04/27 16:07:16.536 +08:00] [INFO] [conn.go:1149] [“command dispatched failed”] [conn=8770465605188048951] [connInfo=“id:8770465605188048951, addr:192.168.6.29:30006 status:1, collation:utf8_general_ci, user:dspwrite2022”] [command=Prepare] [status=“inTxn:1, autocommit:0”] [sql=“UPDATE account_info SET platform_agency_id = ?,project_id = ?,account_name = ?,operators = ?,item_oper = ?,is_self_oper = ?,agency_type = ?, customer_id = ?, customer_short_name = ?, sales_id = ?, sales_name = ?, operator_id = ?, operator_name = ?, sales_main_dept = ?, sales_dept_name = ?, industry_id=?,industry_name=?,product_id=?,product_name = ?, secondary_id=? ,secondary_name=? ,secondary_group=? ,secondary_group_name=? ,eu_id=? ,operator_status=? ,sales_status=? ,balance=? ,media_id=? ,platform_name=? where account_id=? and platform=? ;UPDATE account_info SET platform_agency_id = ?,project_id = ?,account_name = ?,operators = ?,item_oper = ?,is_self_oper = ?,agency_type = ?, customer_id = ?, customer_short_name = ?, sales_id = ?, sales_name = ?, operator_id = ?, operator_name = ?, sales_main_dept = ?, sales_dept_name = ?, industry_id=?,industry_name=?,product_id=?,product_name = ?, secondary_id=? ,secondary_name=? ,secondary_group=? ,secondary_group_name=? ,eu_id=? ,operator_status=? ,sales_status=? ,balance=? ,media_id=? ,platform_name=? where account_id=? and platform=? ;UPDATE account_info SET platform_agency_id = ?,project_id = ?,account_name = ?,operators = ?,item_oper = ?,is_self_oper = ?,agency_type = ?, customer_id = ?, customer_short_name = ?, sales_id = ?, sales_name = ?, operator_id = ?, operator_name = ?, sales_main_dept = ?, sales_dept_name = ?, industry_id=?,industry_name=?,product_id=?,product_name = ?, secondary_id=? ,secondary_name=? ,secondary_group=? ,secondary_group_name=? ,eu_id=? ,operator_status=? ,sales_status=? ,balance=? ,media_id=? ,platform_name=? where account_id=? and platform=? ;UPDATE account_info SET platform_agency_id = ?,project_id = ?,account_name = ?,operators = ?,item_oper = ?,is_self_oper = ?,agency_type = ?, customer_id = ?, customer_short_name = ?, sales_id = ?, sales_name = ?, operator_id = ?, operator_name = ?, sales_main_dept = ?, sales_dept_name = ?, industry_id=?,industry_name=?,product_id=?,product_name = ?, secondary_id=? ,secondary_name=? ,secondary_group=? ,secondary_group_name=? ,eu_id=? ,operator_status=? ,sales_status=? ,balance=? ,media_id=? ,platform_name=? where account_id=? and platform=? ;UPDATE account_info SET platform_agency_id = ?,project_id = ?,account_name = ?,operators = ?,item_oper = ?,is_self_oper = ?,agency_type = ?, customer_id = ?, customer_short_name = ?, sales_id = ?, sales_name = ?, operator_id = ?, operator_name = ?, sales_main_dept = ?, sales_dept_name = ?, industry_id=?,industry_name=?,product_id=?,product_name = ?, secondary_id=? ,secondary_name=? ,secondary_group=? ,secondary_group_name=? ,eu_id=? ,operator_status=? ,sales_status=? ,balance=? ,media_id=? ,platform_name=? where account_id=? and platform=? ;UPDATE account_info SET platform_agency_id = ?,project_id = ?,account_name = ?,operators = ?,item_oper = ?,is_self_oper = ?,agency_type = ?, customer_id = ?, customer_short_name = ?, sales_id = ?, sales_name = ?, operator_id = ?, operator_name = ?, sales_main_dept = ?, sales_dept_name = ?, industry_id=?,industry_name=?,product_id=?,product_name = ?, secondary_id=? ,secondary_name=? ,secondary_group=? ,secondary_group_name=? ,eu_id=? ,operator_status=? ,sales_status=? ,balance=? ,media_id=? ,platform_name=? where account_id=? and platform=? ;UPDATE account_info SET platform_agency_id = ?,project_id = ?,account_name = ?,operators = ?,item_oper = ?,is_self_oper = ?,agency_type = ?, customer_id = ?, customer_short_name = ?, sales_id = ?, sales_name = ?, operator_id = ?, operator_name = ?, sales_main_dept = ?, sales_dept_name = ?, industry_id=?,industry_name=?,product_id=?,product_name = ?, secondary_id=? ,secondary_name=? ,secondary_group=? ,secondary_group_name=? ,eu_id=? ,operator_status=? ,sales_status=? ,balance=? ,media_id=? ,platform_name=? where account_id=? and platform=? ;UPDATE account_info SET platform_agency_id = ?,project_id = ?,account_name = ?,operators = ?,item_oper = ?,is_self_oper = ?,agency_type = ?, customer_id = ?, customer_short_name = ?, sales_id = ?, sales_name = ?, operator_id = ?, operator_name = ?, sal(len: 259676)”] [txn_mode=PESSIMISTIC] [timestamp=441078980529356911] [err=“[executor:8115]Can not prepare multiple statements”]

jdbc

)@DFI4HRM8W
)@DFI4HRM8W848×235 11.5 KB

2
| username: dba-kit | Original post link

This sentence is key.

3
| username: dba-kit | Original post link

What is the current behavior? Is there an update error, or is the update not reporting an error but not meeting expectations?

4
| username: foxchan | Original post link

A total of 16,000 records, and 200 of them have been updated.

5
| username: TiDBer_NP4MeytK | Original post link

The code did not report an error. But the TiDB log indicates “command dispatched failed.” There are more than ten thousand entries, with only some being updated or even none at all. The same code logic, such as the statement “insert into tableA xxx ON DUPLICATE KEY UPDATE,” works fine.

6
| username: xfworld | Original post link

Have you tried a standard update statement?

7
| username: TiDBer_NP4MeytK | Original post link

The standard update statement is slow to execute, and the parsing time is too long.
image
image

8
| username: TiDBer_NP4MeytK | Original post link

It is precisely because standard statements are slow that I thought of using the prepare method.

9
| username: xfworld | Original post link

Update can also be prepared.

There are examples here…

Be sure to control the size of the transaction.

10
| username: TiDBer_NP4MeytK | Original post link

Using prepare for update still doesn’t work. Changing to insert ON DUPLICATE KEY UPDATE and using prepare works fine.