java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

Camunda 7 Topics Discussion & Questions
1

I was using camunda-springboot-rest starter. camunda 7.9.0 version.
Its a springboot application with camunda starters.

Getting below error:

2019-01-24 11:30:19 [JobExecutor[org.camunda.bpm.engine.spring.components.jobexecutor.SpringJobExecutor]] [] [] ERROR org.camunda.bpm.engine.jobexecutor - ENGINE-14019
Exception during job acquisition ENGINE-03083 Exception while executing Batch Database Operations with message '
### Error flushing statements.  Cause: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.JobEntity.updateMessage (batch
index #1) failed. Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
### Cause: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.JobEntity.updateMessage (batch index #1) failed. Cause: jav
a.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
'. Flush summary:
 [
  UPDATE MessageEntity[58f6e0c2-1fca-11e9-a1a0-0a64e2e674b2]
  UPDATE MessageEntity[58fb74a8-1fca-11e9-a1a0-0a64e2e674b2]
  UPDATE MessageEntity[58fbc2cb-1fca-11e9-a1a0-0a64e2e674b2]
]
org.camunda.bpm.engine.ProcessEngineException: ENGINE-03083 Exception while executing Batch Database Operations with message '

Detailed log attached here server.log (7.7 KB)

I was using MYSQL database.

When i run the query

show variables like ‘innodb_lock_wait_timeout’;
it was 50, then i reset to 100.

When i run the below query, i found transaction Isolation level was : REPEATABLE-READ

show variables like ‘%tx_isolation%’;

I hope this need to be set to READ-COMMITTED

And also,
image

Any suggestions please?

2

Hi @aravindhrs,

we recommend to run Camunda BPM in conjunction with a database configured with the isolation level READ COMMITTED. Please see the documentation about Isolation Level Configuration.

Cheers,
Tassilo

1 Like
3

Yeah understood. But i need the reason why deadlock occurred in camunda database tables when we have ISOLATION_LEVEL=REPEATABLE READS?

Can you tell me a few use cases in which scenario deadlock occurs in camunda db tables.

4

Hi @aravindhrs,

Isolation level REPEATABLE READS can lead to deadlocks because Camunda BPM uses optimistic locking to handle concurrent requests.

You can find out more details on this here: https://docs.camunda.org/manual/7.10/user-guide/process-engine/transactions-in-processes/#optimistic-locking-vs-pessimistic-locking

Please let me know if that helped.

regards,
ilias

1 Like