MySQL - Table Locks

book

Article ID: 41009

calendar_today

Updated On:

Products

CA Application Test Service Virtualization CA Continuous Application Insight (PathFinder)

Issue/Introduction

Issue: 

The following error is found in the registry.log or other log files.
java.sql.SQLException: The total number of locks exceeds the lock table size
Also, data may be missing from reports, agents and/or transactions may not display in the portal, etc.

Environment:  

All supported DevTest platforms.
MySql – All supported versions

Cause: 

The MySql InnoDB buffer pool size has run out of space.

Resolution:

Note: The values below are for reference only, the value needed to solve your issue can vary.

MySql Version 5.7.5 and higher:
1. Open the MySql Command Line Client, MySql Workbench or similar interface.
2.   To view the current value, execute:
     SELECT @@innodb_buffer_pool_size;
3.   Increase this value. Example: to set the value to 786M, execute:
     SET GLOBAL innodb_buffer_pool_size=824633720832;
4.   Retest issue using DevTest.
5.   Repeat steps 2 and 3 until resolved.

MySql Version 5.7.4 and earlier:
Windows File Name – my.ini
Linux File Name = my.cnf

1.   Locate configuration file.
     Windows tip: Open the properties dialog box for the MySql service. The location of the configuration file is specified with the parameter --defaults-file
2.   Edit the configuration file.
3.   Search for: innodb_buffer_pool_size
4.   Increase the value for this property. Example: to set the value to 2 Gig
     innodb_buffer_pool_size=2G
5.   Restart the MySql Service.
6.   Retest issue using DevTest.
7.   Repeat steps 4-6 until resolved.

 

Environment

Release: LSASVR99000-8.1-LISA-Server
Component: