jBPM process execution stalls due to MySQL deadlocks

Solution In Progress - Updated -

Issue

We are using jBPM configured with Quartz and MySQL 5.6 for persistence.
The processes stop working in several points.

In the logs, we can find ERRORs reporting a database deadlock, and MySQL shows the same:

14:47:51,323 DEBUG [org.jbpm.executor.impl.ExecutorImpl] (Thread-12 (HornetQ-client-global-threads-1928954123)) Sending JMS message to trigger job execution for job 159771
14:47:51,324 DEBUG [org.jbpm.executor.impl.ExecutorImpl] (Thread-12 (HornetQ-client-global-threads-1928954123)) Scheduled request for Command: org.jbpm.process.core.async.AsyncSignalEventCommand - requestId: 159771 with 3 retries
...
14:47:51,729 DEBUG [org.jbpm.process.core.timer.impl.GlobalTimerService] (Thread-12 (HornetQ-client-global-threads-1928954123)) Returning  timers [] for session 477556
14:47:51,849 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Thread-12 (HornetQ-client-global-threads-1928954123)) SQL Error: 1213, SQLState: 40001
14:47:51,849 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (Thread-12 (HornetQ-client-global-threads-1928954123)) Deadlock found when trying to get lock; try restarting transaction

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-05-28 14:47:53 7f5280991700
*** (1) TRANSACTION:
TRANSACTION 83397248, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 34 lock struct(s), heap size 6544, 24 row lock(s), undo log entries 81
insert into EventTypes (InstanceId, element) values (9131, 'signalStopSubProcessSupply')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2108 page no 22 n bits 400 index `FK_nrecj4617iwxlc65ij6m7lsl1` of table `bpmkie`.`EventTypes` trx id 83397248 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 83397289, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
34 lock struct(s), heap size 6544, 23 row lock(s), undo log entries 81
insert into EventTypes (InstanceId, element) values (9132, 'signalStopSubProcessSupply')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2108 page no 22 n bits 400 index `FK_nrecj4617iwxlc65ij6m7lsl1` of table `bpmkie`.`EventTypes` trx id 83397289 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2108 page no 22 n bits 400 index `FK_nrecj4617iwxlc65ij6m7lsl1` of table `bpmkie`.`EventTypes` trx id 83397289 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

====

"Thread-12 (HornetQ-client-global-threads-1928954123)" tid=215 RUNNABLE in sun.management.ThreadImpl.getThreadInfo1()
        at sun.management.ThreadImpl.getThreadInfo1(Native Method)
        at sun.management.ThreadImpl.getThreadInfo(ThreadImpl.java:176)
        at com.mysql.jdbc.MysqlIO.appendDeadlockStatusInformation(MysqlIO.java:4021)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3961)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
        at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
        at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:543)
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
        ...
        at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1195)
        ...
        at org.drools.persistence.jta.JtaTransactionManager.commit(JtaTransactionManager.java:236)
        at org.drools.persistence.SingleSessionCommandService$TransactionInterceptor.execute(SingleSessionCommandService.java:607)
        ...
        at org.drools.core.command.impl.CommandBasedStatefulKnowledgeSession.signalEvent(CommandBasedStatefulKnowledgeSession.java:243)
        at org.jbpm.process.core.async.AsyncSignalEventCommand.execute(AsyncSignalEventCommand.java:46)
        at org.jbpm.executor.impl.AbstractAvailableJobsExecutor.executeGivenJob(AbstractAvailableJobsExecutor.java:122)
        at org.jbpm.executor.impl.jms.JmsAvailableJobsExecutor.onMessage(JmsAvailableJobsExecutor.java:47)
        at org.kie.server.jms.executor.KieExecutorMDB.onMessage(KieExecutorMDB.java:87)

The most common deadlocks we have experienced are involving either the EventTypes or the RequestInfo tables.

Environment

  • Red Hat JBoss BPM Suite
    • 6.4.x
  • MySQL
    • 5.x

Subscriber exclusive content

A Red Hat subscription provides unlimited access to our knowledgebase of over 48,000 articles and solutions.

Current Customers and Partners

Log in for full access

Log In