There is a table of statistics. It is written per day for each user, game, etc.
I have a unique hash_id index. I just build a hash for the desired values, then check if there is an entry with such a hash_id . And if it is, then you need to update, otherwise insert.
InnoDB table.

Yesterday, it seems that a situation occurred when making a SELECT , there was no record, but at the same time a record was inserted by another process, and Integrity constraint violation was received ...

I wanted to immediately LOCK TABLES , but they write that for InnoDB tables this is wrong.
How to block in this case the table?

Ps INSERT ... ON DUPLICATE UPDATE does not offer, because mysql does not know how to replicate it.

  • one
    hash for the desired values - if “values” means columns, then it is better to create a composite unique index for them - let the subd do the work for which it is “sharpened”. - aleksandr barakin
  • @alexanderbarakin, there were some circumstances that forced me to make a hash manually. The composite index will not solve the problem. - Ray
  • Maybe you then use the INSERT ... ON DUPLICATE KEY UPDATE construct? - ApInvent
  • @ApInvent, I am also blind - several questions with ru.so led to this construction, and then I saw that they were asking not to offer it - BOPOH

1 answer 1

Since there were no more suitable options, I had to use LOCK TABLES .
In principle, the table is blocked for a very short time.