Under high concurrency, when an operation on a table is insert update delete, what is the failure of update?

  mysql, question

Such as the title
The specific operations for a table are as follows

An access involves three operations

  1. Users submit data to insert into a temporary table a
  2. Judging from the business logic, one of the fields in update is 3
  3. Insert the processed data from the temporary table into the formal table b and delete the temporary data

Recently, it was found that the update operation will fail when submitting too frequently, and the update is invalid.

May I know under what circumstances this is caused and what solutions are there? Is it related to the lock?

Look at what engine your temporary table is. If it is MyISAM’s, it is recommended that you do not delete it after each processing. You can add a list state to the row and delete it regularly.

In addition, the insert/update/delete MyISAM engine will lock the table, so other operations will be blocked. If your database is configured with a blocking timeout, update may fail.

I also don’t recommend using innodb unless you configure itinnodb_table_per_file=1。

First suggestion:
Don’t put temporary data into database tables, you can consider putting it into cache system.

Second suggestion:
Regularly clean up invalid data.