LockAcquisitionException

[Fred Potter]

Hi,

I was wondering if anyone might have some pointers with this
LockAcquisitionException problem I'm seeing with background Play jobs.

The stack trace looks like...

javax.persistence.PersistenceException:
org.hibernate.exception.LockAcquisitionException: could not insert:
[models.WordPressPostCategory]
at
org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:
614)
at
org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:
226)
at play.db.jpa.JPASupport.save(JPASupport.java:182)
at handlers.blog.WordPressCache.syncBlog(WordPressCache.java:135)
at handlers.blog.WordPressSyncJob$1.doJob(WordPressSyncJob.java:42)
at play.jobs.Job.doJobWithResult(Job.java:37)
at play.jobs.Job.call(Job.java:110)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ScheduledThreadPoolExecutor
$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)
at java.util.concurrent.ScheduledThreadPoolExecutor
$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:207)
at java.util.concurrent.ThreadPoolExecutor
$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor
$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
Caused by: org.hibernate.exception.LockAcquisitionException: could not
insert: [models.WordPressPostCategory]
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:
105)
at org.hibernate.exception.JDBCExceptionHelper.conv
ert(JDBCExceptionHelper.java:66)
at
org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:
64)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:
2176)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:
2656)
at
org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:
71)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
at
org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:
321)
at
org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:
204)
at
org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:
130)
at
org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId(EJB3PersistEventListener.java:
49)
at
org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:
154)
at
org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:
110)
at
org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:
61)
at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:646)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:620)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:624)
at
org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:
220)
... 12 more
Caused by:
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
Deadlock found when trying to get lock; try restarting transaction
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:
39)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:
27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1045)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:
2086)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2371)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2289)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2274)
at
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:
105)
at org.hibernate.id.IdentityGenerator
$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:94)
at
org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:
57)

I'm perhaps doing something a little funky with the way I'm spawning
jobs. I have a main job which runs every 5 minutes, and then that job
spawns a bunch of smaller jobs with .now().

See the following...

@Every("5min")
public class WordPressSyncJob extends Job {
public void doJob() {
List<WordPressBlog> blogs = WordPressBlog.findAll();

for (final WordPressBlog blog : blogs) {
Job syncJob = new Job(){
@Override
public void doJob() throws Exception {
// Do some work...
}
};

// Kick it off
syncJob.now();
}
}
}

It was my understanding that each job runs with its own SQL
transaction, which wasn't really what I wanted. That's why I thought
I'd side-step that by creating a bunch of smaller jobs that could each
commit their work as it was completed. Perhaps I'm not even
accomplishing that, though.

Does anyone have any suggestions on how to avoid these locking errors?

Thanks!
Fred

[Guillaume Bort]

Hi,

Do you really need to lock your tables? You can change the hibernate
isolation level using the hibernate.connection.isolation property.
Check http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html#configuration-hibernatejdbc

I think that the default isolation level for MySQL InnoDB is
REPEATABLE READ, unless it's set explicitly on the JDBC driver.

> --
> You received this message because you are subscribed to the Google Groups "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
>
>

--
Guillaume Bort, http://guillaume.bort.fr

For anything work-related, use g...@zenexity.fr; for everything else,
write guillau...@gmail.com

[Fred Potter]

Thanks a bunch - I'll give this a shot.

On Oct 14, 2:15 am, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> Hi,
>
> Do you really need to lock your tables? You can change the hibernate
> isolation level using the hibernate.connection.isolation property.

> Checkhttp://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-co...

>
> I think that the default isolation level for MySQL InnoDB is
> REPEATABLE READ, unless it's set explicitly on the JDBC driver.
>
>
>
>
>
> On Thu, Oct 14, 2010 at 12:02 AM, Fred Potter <fpot...@gmail.com> wrote:
>
> > Hi,
>
> > I was wondering if anyone might have some pointers with this

> >LockAcquisitionExceptionproblem I'm seeing with background Play jobs.

> Guillaume Bort,http://guillaume.bort.fr

>
> For anything work-related, use g...@zenexity.fr; for everything else,

> write guillaume.b...@gmail.com