You know the feeling when you think you've had the best idea but then realise you might not know what you're talking about? This is one of those moments, and I'm hoping my idea can be confirmed or refuted.
I have a database table that saves activity in the form of "date, sender, recipient, action count", that counts how many interactions one player of the game (which you just lost :p) gives to another on a given day.
Right now, it uses insert..on duplicate key update (with the unique key being (date, sender, recipient) and while it works I have noticed that it causes deadlock errors during periods of intense activity.
I have fixed deadlock errors in other parts of the game by avoiding "insert..select from", "insert ignore" and "insert..on duplicate key update" and this appears to be one of the few, if not the only one left.
My new idea is as follows:
- Attempt update
- If the number of affected rows is zero...
- Perform insert
Essentially it's become "update..on not found insert", and done manually to try and avoid locking rows.
Caveats I've thought of:
Race conditions are not an issue because the client-side code already includes batching operations, and a given user should therefore only be sending one request of this type at a time. If the client bypasses this code (never trust the client!) then the worst case is their displayed numbers will be lower than expected.
This may execute two queries instead of one, but I believe an update matching zero rows is basically a no-op.
That's about it really. Am I missing anything here or should this solve my issues?