search cancel

Spectrum SRM Error - "java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction"


Article ID: 188063


Updated On:


CA Spectrum DX NetOps


SRM bucket tables are not being processed and the following MySQL error is seen in the Spectrum OneClick Server (Spectrum Tomcat) log file:

020-04-01 17:48:52,991 INFO - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
Apr 01, 2020 17:48:53.007 - SRM/DeviceModelPoll/updates: Error invoking job (com.apr[email protected]6062f3e1) : org.springframework.dao.CannotAcquireLockException: StatementCallback; SQL [UPDATE model SET mtype_h=mtype_h , model_name='xxxxxxxxxxxxxxxxxxxx', model_class=4, security_string='OPS', network_address='', SDM_Host_Address='' WHERE model_key = 2110]; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
 at org.springframework.jdbc.core.JdbcTemplate.execute(
 at org.springframework.jdbc.core.JdbcTemplate.update(
 at com.aprisma.util.thread.JobQueue.runJobThread(
 at com.aprisma.util.thread.JobQueue.access$000(
 at com.aprisma.util.thread.JobQueue$
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
 at com.mysql.jdbc.SQLError.createSQLException(
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(
 at com.mysql.jdbc.MysqlIO.sendCommand(
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(
 at com.mysql.jdbc.ConnectionImpl.execSQL(
 at com.mysql.jdbc.StatementImpl.executeUpdate(
 at com.mysql.jdbc.StatementImpl.executeUpdate(
 at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(
 at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(
 at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(
 at org.springframework.jdbc.core.JdbcTemplate.execute(
 ... 7 more

A Different Occurrence of the same problem

Apr 29, 2021 00:00:56.264 (SRM/LandscapeManager/LandscapeThread_1) (SRM_Events) - (ERROR) - Failed to insert event into event table.  SQL Exception code: 1205  SQL Exception message: Lock wait timeout exceeded; try restarting transaction  Last known event time = 1615817534000

Apr 29, 2021 00:00:56.264 (SRM/LandscapeManager/LandscapeThread_1) (SRM_Events) - (ERROR) - event record (mh=0x10723c, type=0x10801, time=2021-03-15 09:12:14.0)

Apr 29, 2021 00:01:01.052 (SRM/LandscapeManager/LandscapeThread_1) (SRM_Events) - (ERROR) - Database ERROR processing events for domain spectroserver01.  Will not poll domain 


Release : All Supported Releases
Component: SPCRPT - Report Manager


It is suggested to increase the lock wait timeout value. In addition, it is also a good time to check the buffer memory pool size. In Spectrum 10.4.x this has been increased to 3072M, if set to a lower value please increase.
In the my-spectrum.cnf file:


Change (if it does not exist add with a value of 300)

innodb_lock_wait_timeout = 120


innodb_lock_wait_timeout = 600

MySQL and Tomcat will need to be restarted for this to take effect.