I got a deadlock problem with mysql and an application that I am developing. The application, based on spring boot, integration and jpa, has different threads and all of them can access this service:
@Override
@Transactional()
public List<TwitterUser> storeTwitterUsers(List<TwitterUser> twitterUsers)
{
logger.debug("Store list of users, total: " + twitterUsers.size());
List<TwitterUser> savedUsers = new ArrayList<>();
for ( TwitterUser twitterUser : twitterUsers ) {
TwitterUser user = getTwitterUserById(twitterUser.getTwitterId());
if ( user != null ) {
twitterUser.setId(user.getId());
user = entityManager.merge(twitterUser);
} else {
//HERE THE EXCEPTION IS THROWN
entityManager.persist(twitterUser);
user = twitterUser;
}
entityManager.flush();
savedUsers.add(user);
}
return savedUsers;
}
@Override
@Transactional(readOnly = true)
public TwitterUser getTwitterUserById(Long id)
{
Query query = entityManager.createQuery("from TwitterUser u where u.twitterId=:id");
query.setParameter("id", id);
TwitterUser twitterUser = null;
//Throw Exception NoResultException
try {
twitterUser = (TwitterUser)query.getSingleResult();
} catch (NoResultException e) {
//no result found
}
return twitterUser;
}
When more than one thread is within the method storeTwitterUsers, mysql throw this error:
Deadlock found when trying to get lock; try restarting transaction
This is the full stack track of the error:
I already read those two questions:
How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'
Getting "Deadlock found when trying to get lock; try restarting transaction"
but my problem seems slightly different because I got the exception when almost any thread tries to persist the object.
Is there a clean and easy way to resolve the problem without implementing a low level code check? Can Spring JPA automatically manage the deadlock situation?
Any help is really appreciated, I am struggling with that error!