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?