1

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?

  • I'm afraid that one of your assumptions ("race conditions are not an issue") might not be true. Otherwise, most probably there wouldn't be deadlocks in this case. – joanolo Jan 21 '17 at 13:06
  • @joanolo I believe the deadlocks may be happening due to foreign keys, where a lock on one table is preventing a change to another. I don't believe race conditions should be happening here, yet they are. And, if they do happen here, I'm considering it to be a lesser issue as it's just a quick, aggregate count that doesn't require complete accuracy. – Niet the Dark Absol Jan 22 '17 at 20:36
2

Any approach will lock something. Even your SELECT. You cannot avoid it. The best approach is to minimize the locks.

Normalization

Let's look at the bigger picture. If the purpose of this is "normalization", then perform the the upsert outside the main transaction. This prevents many possible deadlocks and speeds up the processing.

If you are doing "batch normalization", see my tips here . Those two statements (not unlike your 2-steps) are designed to efficiently copy multiple items into a normalization table, and do it without "burning" AUTO_INCREMENT ids. Again, do them with autocommit=1, not inside of some bigger transaction.

If the main transaction eventually rolls back, then the worst that could happen is that you inserted something unused in the normalization table. This is not harmful.

INSERTing 100 rows in a single statement is typically about 10 times as fast as using 100 1-row INSERTs. This speed, alone, helps avoid deadlocks.

Regardless of what prevention measures you take, deadlocks will occur. Write your code to re-run the entire transaction, as this is usually the 'right fix'.

Likes, upvotes, etc

If the purpose of the UPDATE is a high-volume 'increment', then it may be best to move the counter out of the main table into its own table (together with a minimal PK). That way, "regular" processing is not being held up by "Likes".

  • I specifically said I'm avoiding those because they involve locking the rows involved, which is a performance issue during periods of heavy load. – Niet the Dark Absol Jan 22 '17 at 20:35
  • OK. I rewrote my answer. – Rick James Jan 22 '17 at 21:04
  • And added another use case. – Rick James Jan 22 '17 at 21:09
1

I'm experiencing issues with this insert .. on duplicate key update as well. But:

  • I only have to use it because of a surrogate (autoincremental) primary key in the table. If I didn't, I could simply use REPLACE INTO without a fear to overcome the id field's capacity (max allowed number) and/or loosing links with other entities — since they all refer records in this table by id and REPLACE INTO does not preserve original ids.

  • I had to add this key because the ORM did not (and still doesn't) support composite primary keys.

In short words, I've traded simplicity on high-level: ORM, for complexity on the low (and, what's worse: less known to mw) level: Database. Time to weight my choices, really. Because I'd better fight ORM in well-known PHP, than the Database, where there are so much layers: FS, DB own configuration, Engines, Keys and on and on and on…

The answer part: consider not only the ways to deal with current structure. But also the ways to re-structure your DB tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.