In highly concurrent scenarios, when an insertupdatedelete operation is performed on a table, what happens if update fails?

For example, the specific operation on a table in the title. For example, the next access will contain three operations. After the user submits data into a temporary table, a determines based on the business logic, one of the fields in update is the data processed by the 3insert temporary table to the formal Table B. Recently, it was found that the temporary data is submitted too frequently... for example, the specific operation of the title on a table is as follows:

One access will contain three operations

1. Insert data submitted by the user to a temporary Table
2. After judging based on the business logic, one of the update fields is 3
3. Insert the data processed by the temporary table to table B to delete the temporary data.

Recently, it is found that the update operation fails when too many submissions are submitted, and the update is invalid.

What is the cause of this? Is there any solution? Is it related to the lock?

Reply content:

For example, the specific operation of the title on a table is as follows:

One access will contain three operations

1. Insert data submitted by the user to a temporary Table
2. After judging based on the business logic, one of the update fields is 3
3. Insert the data processed by the temporary table to table B to delete the temporary data.

Recently, it is found that the update operation fails when too many submissions are submitted, and the update is invalid.

What is the cause of this? Is there any solution? Is it related to the lock?

Check what engine your temporary table is. If it is MyISAM, we recommend that you do not delete it after processing each time. You can add a clear list to the row and delete it regularly.

In addition, the insert/update/delete MyISAM engine locks the table, so other operations will be blocked. If your database configures the blocking timeout time, the update may fail.

I do not recommend using innodb either, unless you have configuredInnodb_table_per_file = 1.

First, we recommend that you do not place temporary data in database tables. You can consider storing it in the cache system.

Second Suggestion: Clear invalid data regularly.