Large Transaction Error: Transaction is too large, size: 104857768

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

Original topic: 大事务报错 Transaction is too large, size: 104857768

| username: 像风一样的男子

I have a large transaction that failed to execute. According to the documentation, I need to modify the txn-total-size-limit parameter in the configuration file. However, it’s too troublesome to reload the cluster after making the changes. Is there a way to temporarily modify this by setting a system variable?

| username: 像风一样的男子 | Original post link

Or is there any expert who can help optimize the SQL to execute in a loop?

UPDATE 
  a, b 
SET b.material_code = a.new_material_no
WHERE a.old_material_no = b.material_code;
| username: MrSylar | Original post link

txn-total-size-limit

| username: 像风一样的男子 | Original post link

Version 5.4 does not have this feature.

| username: TiDBer_jYQINSnf | Original post link

Is this going to update the entire table? Use the primary key to limit it?

| username: MrSylar | Original post link

Batch | PingCAP Documentation Center

batch update

| username: yulei7633 | Original post link

There should be no way to temporarily modify the configuration parameters of tidb-server; you still need to reload tidb-server.

| username: 像风一样的男子 | Original post link

Yes, there is a table with more than 20 million rows that needs to update a field for the entire table.

| username: 像风一样的男子 | Original post link

5.4 does not support batch

| username: WinterLiu | Original post link

Learned
txn-total-size-limit

| username: 小龙虾爱大龙虾 | Original post link

Use this solution: 6.1.4 一种高效分页批处理方案 | tidb-in-action

| username: zhaokede | Original post link

Combine window functions to divide the data into small segments, implemented with multiple SQL statements.

| username: 像风一样的男子 | Original post link

Writing it this way, pagination still needs to be done manually. With over 20 million records, the workload is quite large.

| username: 像风一样的男子 | Original post link

I calculated that it needs to be divided into more than 100 SQL segments…

| username: 小龙虾爱大龙虾 | Original post link

No, the pagination range is calculated by the window function. You can also directly use SQL to piece together the final SQL to be executed and then execute it.

| username: 江湖故人 | Original post link

Using SQL is the most straightforward.
You can also use Notepad++'s column editor or Vim’s recorder to write it; the workload is actually not that big.

| username: TiDBer_jYQINSnf | Original post link

Split it up. Going all-in at once can easily crash the cluster.

| username: zhanggame1 | Original post link

set tidb_mem_quota_query to a very large value before executing

| username: 像风一样的男子 | Original post link

The error is related to the transaction parameter txn-total-size-limit and has nothing to do with the query limit.

| username: 像风一样的男子 | Original post link

I wrote a loop using window functions and executed it, which ended up crashing the test environment.