We are in the process of creating a web application using mysql as a data source. We are using spring with hibernate as an ORM framework. We have hundreds of entities with relations (manyToMany \ manyToOne etc..).

In the dev envioronment we currently use the hbm2dll in order to create the schemas from time to time. (after huge changes).

last week we encountered a deadlock happening (note that we do not use any explicit locking on our project). on a simple usage of the application.

removing the foreign keys from the table actually solved the issue.

So a couple of questions:

1) are foreign keys "evil"? if removing them from the DB did not affect the application logic and solved the issue - whats the point?

2) should we get rid of all of them?

  • 1
    By removing foreign keys you "compromised" consistency of your data, because now one transaction can insert record which reference non-existing relation (and even if you check its existence in application, other parallel thread might still remove it when the other inserts). You should instead prepare for possibility of deadlocks - they can and will happen so you need to retry the transaction in such case. If there are too many of them then you may need some explicit locking in few places. – jkavalik May 21 '15 at 9:41
  • Do you know any good way to retry the deadlocks on the spring framework? – Urbanleg May 21 '15 at 10:31
  • Not using it, but you can try searching, I found: stackoverflow.com/questions/1762870/… – jkavalik May 21 '15 at 10:38
  • 1
    I think you need to provide more details to get the answer, for instance deadlock trace and definitions for the tables involved in deadlock. In general, deadlocks attributed to flaw in application logic or ad-hoc queries (other reasons, e.g. lock escalation and an option not to have indexes on FK columns are not applicable to Mysql ). Without any details except that removing FK constraints from DB helped, two options seem most likely to me - either having CASCADE actions specified in constraints or not using @DynamicUpdate/@DynamicInsert annotation in entity classes. – a1ex07 May 21 '15 at 14:58

Your Answer

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

Browse other questions tagged or ask your own question.