How to ensure multiple requests to a database aren't mimicked
I am part of the coding team of a high request game.
We've experienced some problems lately where by multiple requests can be sent in at the exact same time and are syndication duplicate actions (which would not be able to happen if they ran entirely after one another).
The problematic routine calls a row in an InnoDB table and if present continues on it's routine until all other checks are okay and at which point it completes and deletes the row.
What appears to be happening is the reads are hitting the row simultaneously (despite the row level locking) and continuing on down the routine path, by which point the deletes make no difference. What this is causing to happen is that the routine is being duplicated by players smart enough to try their luck.
Does anyone have any suggestions for a way to approach fixing this?
// check database row exists (create the initial lock) // proceed // check quantity in the row // if all is okay (few other checks needed here) // delete the row // release the lock either way (for the next request to go through)
MySQL has a couple different lock modes
I think you'll want to enforce an exclusive lock when executing an update/delete. This way the subsequent requests will wait until the lock is released and the appropriate action has completed.
You may also want to examine the indexes being used for these concurrent queries. An appropriate indexing regime will minimize the number of rows that need to be locked during a given query.