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

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

Report Manager data is behind for a single or multiple landscapes.

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  org.springframework.jdbc.support.SQLErrorCodesFactory - 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.aprisma.spectrum.app.repmgr.dc.asset.DevicePoller$UpdateDeviceInfo@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='x.x.x.x', SDM_Host_Address='x.x.x.x' 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.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
 at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:549)
 at com.aprisma.spectrum.app.repmgr.dc.asset.AssetManager.updateDeviceModel(AssetManager.java:1115)
 at com.aprisma.spectrum.app.repmgr.dc.asset.DevicePoller$UpdateDeviceInfo.run(DevicePoller.java:223)
 at com.aprisma.util.thread.JobQueue.runJobThread(JobQueue.java:221)
 at com.aprisma.util.thread.JobQueue.access$000(JobQueue.java:15)
 at com.aprisma.util.thread.JobQueue$JobRunnable.run(JobQueue.java:24)
 at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
 at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1648)
 at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1567)
 at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
 at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:538)
 at org.springframework.jdbc.core.JdbcTemplate$1UpdateStatementCallback.doInStatement(JdbcTemplate.java:535)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
 ... 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 ######.  Will not poll domain 

Environment

Release : All Supported Releases
Component: SPCRPT - Report Manager

Resolution

On the OneClick Server(s) running Spectrum Report Manager:

 

• Increase the lock wait timeout value.
• Verify the buffer memory pool size. (In Spectrum 10.4.x this has been increased to 3072M, if set to a lower value increase)

 

In the my-spectrum.cnf file:

$SPECROOT/mysql/my-spectrum.cnf

 

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

innodb_lock_wait_timeout = 120

To

innodb_lock_wait_timeout = 600

 

 

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

 

$ $SPECROOT/tomcat/bin/stopTomcat.sh

# systemctl stop processd  (as root stop the processd, which will also stop the MySQL server service)

 

# systemctl start processd (as root start the processd, which will also start the MySQL server and Tomcat services)