UPDATE tasks SET p_runningonclusternode = ? WHERE p_runningonclusternode = ? AND PK = ? AND hjmpTS = ?

[Former Member]

The following query is killing the entire system. What can be the issue ? The number of tasks in the database is approximately 300. Using hybris 6.6.0.4. Tried everything but this keep popping up time to time and blocks everything.

What i tried already - 1.) task.polling.interval.min=0 2.) task.engine.query.full.executiontime.threshold =0 3.) task.engine.query.full.interval=0

 UPDATE tasks SET p_runningonclusternode = ? WHERE p_runningonclusternode = ? AND PK = ? AND hjmpTS = ?

The stackstrace

 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.695 | INFO  [hybrisHTTP82] [RestHandlerExceptionResolver] Translating exception [de.hybris.platform.servicelayer.exceptions.ModelSavingException]: org.springframework.dao.CannotAcquireLockException: query; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.695 | ERROR [hybrisHTTP82] [RestHandlerExceptionResolver] de.hybris.platform.servicelayer.exceptions.ModelSavingException: org.springframework.dao.CannotAcquireLockException: query; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.708 |         at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.709 |         at de.hybris.platform.webservicescommons.filter.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:42)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.710 | Caused by: de.hybris.platform.persistence.hjmp.HJMPException: org.springframework.dao.CannotAcquireLockException: query; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.711 | Caused by: org.springframework.dao.CannotAcquireLockException: query; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.711 |         at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.711 |         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.711 |         at de.hybris.platform.jdbcwrapper.DataSourceImpl.translateToDataAccessException(DataSourceImpl.java:1030)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.711 | Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:40.711 |         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.727 | Exception in thread "TaskExecutor-master-1214-Task [8796719219638]" java.lang.IllegalStateException: Failed to lock task #8796719219638.
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.727 | Exception in thread "TaskExecutor-master-1215-Task [8796719186870]" Caused by: org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; SQL [UPDATE tasks SET p_runningonclusternode = ? WHERE p_runningonclusternode = ? AND PK = ? AND hjmpTS = ? ]; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.727 |         at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.727 |         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.727 | Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.727 |         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.734 | java.lang.IllegalStateException: Failed to lock task #8796719186870.
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.734 | Caused by: org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; SQL [UPDATE tasks SET p_runningonclusternode = ? WHERE p_runningonclusternode = ? AND PK = ? AND hjmpTS = ? ]; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.734 |         at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.740 |         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.740 | Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:48.740 |         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.762 | Exception in thread "TaskExecutor-master-1217-Task [8796719186870]" java.lang.IllegalStateException: Failed to lock task #8796719186870.
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.762 | Caused by: org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; SQL [UPDATE tasks SET p_runningonclusternode = ? WHERE p_runningonclusternode = ? AND PK = ? AND hjmpTS = ? ]; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.762 |         at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.762 |         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.762 | Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.762 |         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.767 | Exception in thread "TaskExecutor-master-1216-Task [8796719219638]" java.lang.IllegalStateException: Failed to lock task #8796719219638.
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.767 | Caused by: org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; SQL [UPDATE tasks SET p_runningonclusternode = ? WHERE p_runningonclusternode = ? AND PK = ? AND hjmpTS = ? ]; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.767 |         at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.771 |         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.771 | Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
 INFO   | jvm 1    | main    | 2018/09/19 14:52:58.771 |         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)

[blakeberg]

Ok, the official sap support has a solution for this Thank you for your information.

We have identified the exception to be related with a feature in newer versions that enables jdbc interceptors. As a workaround for the initialization, please set the property jdbc.interceptor.enabled=false in your local.properties, then restart hybris & retry the initialization.

Related links: https://jira.hybris.com/browse/ECP-3542 https://jira.hybris.com/browse/ECP-3500

[blakeberg]

Hi there, we have the same problem when initialize with a percona xtra db cluster but not in single mode mysql db. we tried to set the impex and sync workers to 1, that works but 2 doesn't work. We use hybris 1811 and currently only have one hybris node. Can anyone help us?