Foreign Key constraint issue in MultiThreading
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.
Subject
Views
Written By
Posted
Foreign Key constraint issue in MultiThreading
1835
January 27, 2016 03:30AM
777
January 29, 2016 06:32PM
959
February 02, 2016 10:37PM
1055
February 03, 2016 11:12PM
746
February 04, 2016 10:33PM
825
February 07, 2016 10:01PM
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.