Deadlock exceptions

[jorell]

I've been seeing a lot of the below exceptions in my logs. I am running 5.15 with multiple servers running on a single database. Not sure what the cause is or how to debug this. Any idea?


Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction\n### The error may involve org.activiti.engine.impl.persistence.entity.VariableInstanceEntity.updateVariableInstance-Inline\n### The error occurred while setting parameters\n### SQL: update ACT_RU_VARIABLE     set     REV_ = ?,     EXECUTION_ID_ = ?,     BYTEARRAY_ID_ = ?,     TYPE_ =  ?,     DOUBLE_ = ?,     LONG_ = ?,     TEXT_ = ?,     TEXT2_ = ?     where ID_ = ?     and REV_ = ?\n### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction">,log_context <key: "/log/throwable",value <proto <type_name: "com.google.common.logging.ThrowableProto",data: "\n\ufffd\017\n1org.apache.ibatis.exceptions.PersistenceException\022\ufffd\005\n### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction\n### The error may involve org.activiti.engine.impl.persistence.entity.VariableInstanceEntity.updateVariableInstance-Inline\n### The error occurred while setting parameters\n### SQL: update ACT_RU_VARIABLE     set     REV_ = ?,     EXECUTION_ID_ = ?,     BYTEARRAY_ID_ = ?,     TYPE_ =  ?,     DOUBLE_ = ?,     LONG_ = ?,     TEXT_ = ?,     TEXT2_ = ?     where ID_ = ?     and REV_ = ?

[frederikherema1]

What is the transaction isolation level? MySQL may do row-locking, depending on the isolation level of you're transactions. if multiple servers try to update a variable in the same process, this can result in deadlocks. If it's a different process-instance, MySQL is doing full-table-locks…

[jorell]

The isolation level is Read uncommitted. Mysql shouldn't be doing full table locks for this isolation level. Also are these deadlocks anything I need to be concerned with or would Activiti roll back the transaction on its own and retry?

[frederikherema1]

Read uncommitted allows reading dirty rows in other ongoing uncommitted transactions. In order to get this behaviour consistent, I can imagine some locking going on (no separated snapshots of data in reads/writes, so SOMETHING needs to be locked at some point). I'm not a DBA, so I can only guess what's going on - IMHO, the READ UNCOMMITTED is causing this.

Activiti will rollback the transaction. In case the API-call was initiated from you're code, you'll get an ActivitiException and need to retry it yourself. In case the deadlocked occurred while executing a job (a timer that fired, an async activity that's executed), the deadlock will be caught and reported in the ACT_RU_JOB table (Job entity) and retried when the next executor slot is available. This will be attempted by default 3 times (configurable), until the retries are given up and the job is asleep. The job won't be deleted, rather it's present in the table with a retry-count of 0, allowing you to execute it manually raise the retry-count again using the API.