Hey.

The following situation occurred:

In the game for social. the network has a bot that appears after a certain time and which is common to all users. The logic is that when attacking this bot, it is checked in battle or not. If in combat, the INSERT is made into the battles table, if the bot is already in combat, then the UPDATE is added to add the user to the team against the bot. Because in social. networks, a huge number of users who can simultaneously attack the bot, which leads to an error and therefore there is a need in this case to protect against simultaneous INSERT and UPDATE .

I decided to do LOCK of certain tables at the time the user entered the battle, but I’m interested in the opinion of more experienced developers, how correct it is and whether there is a more suitable / correct method.

DB: Mysql Engine: InnoDB

PS Lock is somehow not very happy, the performance has decreased significantly. Maybe there is some faster mechanism in NoSQL solutions?

  • It is possible that I did not understand your task correctly, but perhaps transactions ( PDO :: beginTransaction () ) do not solve the problem? - Deonis
  • @Deonis Thanks for the comment, but did not quite understand, but how will transactions help in this case from 2 simultaneous INSERTs? - Sever
  • one
    @Sever, well, if you consider that single requests are executed in atomic mode, i.e. one request is one transaction, then it is possible ( I repeat that I can ignore some features of operations ), your two requests can be combined into one using the INSERT ... ON DUPLICATE KEY UPDATE construct. In my opinion, this can solve the problem of parallel queries. And I'm not against locks, I just know that this can significantly reduce performance. If one thread has locked the resource, then hundreds of others will hang and wait for their turn. Maybe I am mistaken, but whenever possible I avoid blocks. - Deonis
  • @Deonis, I do not like blocks, and I never used them at all and hoped that in the future I will not. About INSERT ... ON DUPLICATE KEY UPDATE completely forgot, thanks for reminding, testing. - Sever

1 answer 1

Deonis comment reply

If we consider that single queries are executed in atomic mode, i.e. one request is one transaction, then it is possible ( I repeat that I can ignore some features of operations ), your two requests can be combined into one using the construction

 INSERT ... ON DUPLICATE KEY UPDATE

In my opinion, this can solve the problem of parallel queries. A lock can significantly reduce performance if one thread has blocked a resource, then hundreds of others will hang and wait for their turn.