MySQL Forums
Forum List  »  InnoDB

Foreign Key constraint issue in MultiThreading
Posted by: Rishi anjan
Date: January 27, 2016 03:30AM

we are facing Foreign Key constraint issue with MYSql in our multi-threading environment. The application is working fine with Sql Server.

There is one table in which Foreign key point to its own primary key column.
For example:-
column B contains Foreign Key which point to column A(primary key) of same table.

We use Hibernate in our application, which initially delete the previous record,insert new record and after that insert child rows in the same table(Many to one relation).

Table state

id(PK)--------other columns-----------linkedID(ForeignKey with destination id)
1-------------------------------------null

operations in single transaction
id(PK)--------other columns-----------linkedID(ForeignKey with destination id)
1-------------------------------------null - Deleted
2-------------------------------------null - new row inserted
3-------------------------------------2 - new row inserted

above transaction is called with 10 paralled threads all operating on different records.


Application is working fine with single threading, but getting below exception in multithread.

SQL Error: 1213, SQLState: 40001
2016-01-27 11:07:44,162 ERROR "http-bio-8080"-exec-11 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Deadlock found when trying to get lock; try restarting transaction.

We also try to change the isolation label from Repeatable-Read to Rread-commited. But issue still persist.

Options: ReplyQuote


Subject
Views
Written By
Posted
Foreign Key constraint issue in MultiThreading
1835
January 27, 2016 03:30AM


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.