MySQL Forums
Forum List  »  InnoDB

Re: DeadLock issue in MultiThreading due to foreign key constraint.
Posted by: Peter Brawley
Date: March 30, 2016 11:17AM

Those deadlocks happened on recursive-FK inserts. Are the transactions simple, or are there a lot of statements in them? Have you tried making these transactions the smallest possible logical units of work?

And I should know this, alas I do not. Do you know how much extra time it costs to let the JDBC engine manage transactions rather than code them explicitly? I know, I know, I'm speaking O-O heresy, but you know, O-O assumptions can fail to match reality.

IAC, I'd start by adding retry logic to manage the problem. You're worried about slowness, but slow functionality is better than no functionality at all.

Then I'd continue experimenting with thread count and perhaps other system params to reduce deadlocks, eg what happens if you grow it to 100 (you may need more resources)?

As a last resort I'd replace the recursive FK with Triggers or app code to guarantee referential integrity.

Edited 1 time(s). Last edit at 03/30/2016 11:20AM by Peter Brawley.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.