Concurrent update on a row in jbpm_job table - JBoss ESB 4.5.0 GA and jbpm version 3.x

[zerog]

While executing a jbpm process instance in jbpm 3.0 with the default H2 database, I am getting the following stack trace in the JBoss ESB v 4.5.0 GA - Community Edition server log, especially this stack trace snippet (full stack trace follows)->

"Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table JBPM_JOB: another transaction has updated or deleted the same row [90131-68]") : 

The related stack trace for this is as under:

2013-04-18 21:20:13,160 INFO  [com.myco.esb.common.ftp.FtpUtil] File List at FTP Location: .... Bunch of CSV files....
    2013-04-18 21:20:14,929 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 90131, SQLState: 90131
    2013-04-18 21:20:14,930 ERROR [org.hibernate.util.JDBCExceptionReporter] Concurrent update in table JBPM_JOB: another transaction has updated or deleted the same row [90131-68]
    2013-04-18 21:20:14,931 ERROR [org.jbpm.db.JobSession] org.hibernate.exception.GenericJDBCException: could not execute update query
    2013-04-18 21:20:14,932 ERROR [org.jbpm.graph.def.GraphElement] action threw exception: couldn't delete timers by name 'timeout' for Token(/)
    org.jbpm.JbpmException: couldn't delete timers by name 'timeout' for Token(/)
        at org.jbpm.db.JobSession.deleteTimersByName(JobSession.java:208)
        at org.jbpm.scheduler.db.DbSchedulerService.deleteTimersByName(DbSchedulerService.java:64)
        at org.jbpm.scheduler.def.CancelTimerAction.execute(CancelTimerAction.java:46)
        at org.jbpm.graph.def.GraphElement.executeAction(GraphElement.java:284)
        at org.jbpm.graph.def.GraphElement.executeActions(GraphElement.java:241)
        at org.jbpm.graph.def.GraphElement.fireAndPropagateEvent(GraphElement.java:213)
        at org.jbpm.graph.def.GraphElement.fireEvent(GraphElement.java:196)
        at org.jbpm.graph.def.Node.leave(Node.java:466)
        at sun.reflect.GeneratedMethodAccessor323.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.graph.def.Node$$EnhancerByCGLIB$$57fc9b95.leave(<generated>)
        at org.jbpm.graph.exe.Token.signal(Token.java:223)
        at org.jbpm.graph.exe.Token.signal(Token.java:150)
        at sun.reflect.GeneratedMethodAccessor342.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.graph.exe.Token$$EnhancerByCGLIB$$87560911.signal(<generated>)
        at org.jbpm.command.SignalCommand.execute(SignalCommand.java:100)
        at org.jboss.soa.esb.services.jbpm.cmd.AsyncProcessSignal$AsyncSignalAction.execute(AsyncProcessSignal.java:300)
        at org.jbpm.graph.def.Action.execute(Action.java:129)
        at sun.reflect.GeneratedMethodAccessor296.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.graph.def.Action$$EnhancerByCGLIB$$516694f1.execute(<generated>)
        at org.jbpm.graph.def.GraphElement.executeAction(GraphElement.java:284)
        at sun.reflect.GeneratedMethodAccessor339.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.graph.def.Node$$EnhancerByCGLIB$$57fc9b95.executeAction(<generated>)
        at org.jbpm.job.ExecuteActionJob.execute(ExecuteActionJob.java:37)
        at sun.reflect.GeneratedMethodAccessor325.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.job.Job$$EnhancerByCGLIB$$c7b4a44e.execute(<generated>)
        at org.jbpm.job.executor.JobExecutorThread.executeJob(JobExecutorThread.java:166)
        at org.jbpm.job.executor.JobExecutorThread.run(JobExecutorThread.java:60)
    Caused by: org.hibernate.exception.GenericJDBCException: could not execute update query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
        at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
        at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
        at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
        at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
        at org.jbpm.db.JobSession.deleteTimersByName(JobSession.java:204)
        ... 41 more
    Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table JBPM_JOB: another transaction has updated or deleted the same row [90131-68]
        at org.h2.message.Message.getSQLException(Message.java:91)
        at org.h2.message.Message.getSQLException(Message.java:95)
        at org.h2.message.Message.getSQLException(Message.java:73)
        at org.h2.table.TableData.removeRow(TableData.java:282)
        at org.h2.command.dml.Delete.update(Delete.java:68)
        at org.h2.command.CommandContainer.update(CommandContainer.java:69)
        at org.h2.command.Command.executeUpdate(Command.java:197)
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:132)
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:121)
        at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:95)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:365)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
        ... 46 more
    2013-04-18 21:20:14,932 ERROR [org.jbpm.graph.def.GraphElement] action threw exception: couldn't delete timers by name 'timeout' for Token(/)
    org.jbpm.JbpmException: couldn't delete timers by name 'timeout' for Token(/)
        at org.jbpm.db.JobSession.deleteTimersByName(JobSession.java:208)
        at org.jbpm.scheduler.db.DbSchedulerService.deleteTimersByName(DbSchedulerService.java:64)
        at org.jbpm.scheduler.def.CancelTimerAction.execute(CancelTimerAction.java:46)
        at org.jbpm.graph.def.GraphElement.executeAction(GraphElement.java:284)
        at org.jbpm.graph.def.GraphElement.executeActions(GraphElement.java:241)
        at org.jbpm.graph.def.GraphElement.fireAndPropagateEvent(GraphElement.java:213)
        at org.jbpm.graph.def.GraphElement.fireEvent(GraphElement.java:196)
        at org.jbpm.graph.def.Node.leave(Node.java:466)
        at sun.reflect.GeneratedMethodAccessor323.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.graph.def.Node$$EnhancerByCGLIB$$57fc9b95.leave(<generated>)
        at org.jbpm.graph.exe.Token.signal(Token.java:223)
        at org.jbpm.graph.exe.Token.signal(Token.java:150)
        at sun.reflect.GeneratedMethodAccessor342.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.graph.exe.Token$$EnhancerByCGLIB$$87560911.signal(<generated>)
        at org.jbpm.command.SignalCommand.execute(SignalCommand.java:100)
        at org.jboss.soa.esb.services.jbpm.cmd.AsyncProcessSignal$AsyncSignalAction.execute(AsyncProcessSignal.java:300)
        at org.jbpm.graph.def.Action.execute(Action.java:129)
        at sun.reflect.GeneratedMethodAccessor296.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.graph.def.Action$$EnhancerByCGLIB$$516694f1.execute(<generated>)
        at org.jbpm.graph.def.GraphElement.executeAction(GraphElement.java:284)
        at sun.reflect.GeneratedMethodAccessor339.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.graph.def.Node$$EnhancerByCGLIB$$57fc9b95.executeAction(<generated>)
        at org.jbpm.job.ExecuteActionJob.execute(ExecuteActionJob.java:37)
        at sun.reflect.GeneratedMethodAccessor325.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
        at org.jbpm.job.Job$$EnhancerByCGLIB$$c7b4a44e.execute(<generated>)
        at org.jbpm.job.executor.JobExecutorThread.executeJob(JobExecutorThread.java:166)
        at org.jbpm.job.executor.JobExecutorThread.run(JobExecutorThread.java:60)
    Caused by: org.hibernate.exception.GenericJDBCException: could not execute update query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
        at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
        at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
        at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
        at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
        at org.jbpm.db.JobSession.deleteTimersByName(JobSession.java:204)

We are trying this out with TRANSACTION_READ_COMMITTED for now. Is this ok and are there any other settings/configuration in the jbpm-ds.xml or any other file which we must change ?

Please help me fix this.

TIA..

[tcunning]

Can you try with a more recent version of JBoss ESB (you mentioned in your other message that you were using ESB 4.5)?

[zerog]

Unfortunately that is not an option Tom. Is there anyway you can help me troubleshoot this ? I did look at this post (https://community.jboss.org/thread/156167) and have rolled some changes locally (not sure if there is something equiv for MySQL for this :- Enabling SQLServer snapshots ->

• ALTER DATABASE YOUR_DB_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
• ALTER DATABASE YOUR_DB_NAME SET READ_COMMITTED_SNAPSHOT ON;

) but there seems to be some different issues being reported w.r.t. the smooks layer now... Just need to show if there is a best practice on setting the transaction isolation level e.g., TRANSACTION_READ_COMMITTED.

[tcunning]

The post you cite suggest you can't use READ_UNCOMMITTED.

Whether or not you see upgrading to ESB 4.12 as something you can do for your project or not, try it out on ESB 4.12 to see whether your issue is fixed.      It might be possible to port over a change singly, but you won't know whether that's possible unless you try your project out on a newer version.

Also, I'm not sure if you are suggesting you have flexibility to switching to a different db like mysql - if you can, that's something you might want to explore.

[zerog]

AFAIK, from the community edition perspective, looks like 4.5.0 GA is the latest version which supports jbpm 3.x. Please let me know if otherwise. I am constrained by the jbpm version while selecting the esb version.

[tcunning]

I'm not going to use the term "support", because that's really reserved for the enterprise product, but jbpm 3.x ships with 4.12 and has been tested.        It'd be great if you could try your project out on there for a comparison.