JPA: PersistenceException, could not update

Question

I'm using Play Framework 1.2.5 with Java but this is more of a JPA question.

In my project I often search users by their email, so I created the following method:

public static User getUserByEmail(String email) {
    User user = User.find("email = ?", email).first();
    return user;
}

and I call it like this from various methods:

User user = User.getUserByEmail("test@email.com");

When I'm trying to modify a field of User like this:

User user = User.getUserByEmail("kospol@test.com");
user.name = "kospol";
user.save();

I often get a the following exception resulting in total freeze:

Execution exception (In /app/controllers/*******.java around line 46)
PersistenceException occured : update User set activated=?, ... registered=?,      registeredFrom=?, version=? where id=? and version=?


play.exceptions.JavaExecutionException: update User set activated=?, ... registered=?,     registeredFrom=?, version=? where id=? and version=?
    at play.mvc.ActionInvoker.invoke(ActionInvoker.java:237)
    at Invocation.HTTP Request(Play!)
Caused by: javax.persistence.PersistenceException: update User set activated=?, ... registered=?, registeredFrom=?, version=? where id=? and version=?
    at play.db.jpa.JPABase._save(JPABase.java:44)
    at play.db.jpa.GenericModel.save(GenericModel.java:204)
    at controllers.PushService.register(PushService.java:46)
    at play.mvc.ActionInvoker.invokeWithContinuation(ActionInvoker.java:557)
    at play.mvc.ActionInvoker.invoke(ActionInvoker.java:508)
    at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:484)
    at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:479)
    at play.mvc.ActionInvoker.invoke(ActionInvoker.java:161)
    ... 1 more
Caused by: javax.persistence.PersistenceException:  org.hibernate.exception.GenericJDBCException: could not update: [models.User#3606]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1389)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1317)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1323)
    at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:965)
    at play.db.jpa.JPABase._save(JPABase.java:41)
    ... 8 more
Caused by: org.hibernate.exception.GenericJDBCException: could not update: [models.User#3606]
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2613)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2495)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2822)
    at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:113)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:185)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:345)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
    at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:962)
    ... 9 more
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2683)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2144)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2444)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2347)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2595)
    ... 19 more

I have added the @Version annotation to the model. It seems that the problem is the Lock wait timeout exceeded. How can I avoid this?

I have tried to use the .merge() method with no results and I'm thinking to remove the getUserByEmail method and get the model straight.

Answer 1

If i am in the right track this is the problem you are facing..!!

The problem is With the getUserByEmail function that you have added inside the JPA.

Entity Class is always checked in full, so if you want to create a some variables or functions inside the JPA. You must tell what the Program must do with the function or variable. For that you can use annotations

Here in your case you must tell the program to ignore the function. For that you can use

@Transient

This annotation specifies that the property or field is not persistent. It is used to annotate a property or field of an entity class, mapped superclass, or embeddable class.

In you case you can use this in your JPA

@Transient
public static User getUserByEmail(String email) {
    User user = User.find("email = ?", email).first();
    return user;
}

Answer 2

Disclaimer : it's not easy to answer your question without having the whole project, but here are 2 potential issues to investigate.

I'm pretty sure that the problem isn't caused by the method getUserByEmail itself. (especially because sometimes it works and sometime not).

The problem is caused by another very heavy query locking the User table (or maybe only the row your searching for) for a very long time (exceeding the wait timeout).

So investigate the heavy queries (one way to do that is to log all sql queries and see queries that are running when you got the exception).

Another option is a deadlock (if you don't see any long queries) : here is a typical scenario.

Thread 1 needs to lock table User and table B. thread 2 needs to lock table B and table User.

At time 0:

• thread 1 receive a lock on table User
• thread 2 receive a lock on table B

At time 1:

• thread 1 block and wait for a lock on table B
• thread 2 block and wait for a lock on table User

At time 2:

• wait lock timeout occurs for one thread, the lock is released and the exception is thrown.

At time 3:

• the other thread receive finally the required lock and execute it's query.

Answer 3

Did you make a try with explicit save?

According with Play framework persistence docs you can do something like this:

public static void save(Long id) {
    User user = User.findById(id);
    user.edit(params);
    validation.valid(user);

    if(validation.hasErrors()) {
        // Here we have to explicitly discard the user modifications...
        user.refresh();
        edit(id);
    }
    show(id);
}

Maybe you have to use edit() method (I am very new to play framework and do not know if works).

Hope this helps.