Spectrum Report Manager fails with "The total number of locks exceeds the lock table size" in the tomcat log

Document ID : KB000031341
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

Spectrum Report Manager (SRM) fails during schema changes, or during event processing.

Error seen in the tomcat log file:

"Caused by: java.sql.SQLException: The total number of locks exceeds the lock table size"

Log locations:

Windows - $SPECROOT/tomcat/logs/stdout.log

Solaris/Linux - $SPECROOT/tomcat/logs/catalina.out

 

Hypothesis:

Larger queries require more memory to complete.

 

Solution:

1. Navigate to $SPECROOT/MySQL directory

2. Open the my-spectrum.cnf file in a text editor

3. Find the line:

innodb_buffer_pool_size

4. Adjust the memory setting accordingly.

 

This value can, in theory, be set to 80% of the physical memory available on the machine.

However, with Report Manager and potentially other applications like Business Objects we suggest going up in small increments until the issue is resolved.

Going too high can potentially cause competition for physical memory resulting in paging on the OS.

MySQL startup may also be extended although the impact should be minimal. 

 

Starting at 2GB is a good starting point.

innodb_buffer_pool_size = 2048MB

 

5. Restart MySQL service.

  • Navigate to $SPECROOT/bin using a bash shell.
  • Type: ./cmdC <hostname> 2 MYSQL
  • Once stopped, confirm doing a 'ps -ef | grep -i mysql'
  • Now start it back up: ./launchinstdbapp <hostname> MYSQL y MYSQL.OUT
  • Note - on Windows you can use Windows Services Console to start/stop

6. Restart Tomcat Service

Solaris/Linux

  • Navigate to $SPECROOT/tomcat/bin
  • Type: ./stopTomcat
  • Once stopped, type: ./startTomcat

Windows

  • Open a command prompt
  • Type: net stop spectrumtomcat
  • Once stopped, type: net start spectrumtomcat
  • Note - you can also use Windows Services Console to stop/start