We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

MYSql Data insertion issue: The total number of locks exceeds the lock table size

sathish_raos
sathish_raos asked
on
Medium Priority
891 Views
Last Modified: 2012-05-11
I am trying to insert 20000 records into MYSQL database table. I am using Open JPA to insert data .

MYSQL Database Engine is InnoDB. I configured buffered pool size as innodb_buffer_pool_size=2048M which is equivalent to 2 GB.  I increased to 12GB, but still i am facing the same problem. Can any suggest me the way to resolve the issue.

Detailed stack trace is provided below.

Caused by: <openjpa-1.2.0-r422266:683325 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: The total number of locks exceeds the lock table size {prepstmnt 1122871931 INSERT INTO TrendHour (avgValueOH, avgValueRH, maxValueOH, maxValueRH, minValueOH, minValueRH, periodEnd, periodStart, stdDevOH, stdDevRH, totalRecordsOH, totalRecordsRH, objectId, resourceId, resourceTypeId, parameterGroupId, parameterId) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [params=(double) 1.894, (double) 1.894, (double) 2.661, (double) 2.661, (double) 0.782, (double) 0.782, (Timestamp) 2011-04-26 14:59:59.0, (Timestamp) 2011-04-26 14:00:00.0, (double) 0.6339328, (double) 0.6339328, (int) 5, (int) 5, (int) 1179, (int) 11539, (null) null, (int) 18, (int) 22]} [code=1206, state=HY000]
FailedObject: netvalence.capstone.manager.database.tables.TrendHourTO@47969363
      at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4238)
      at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4203)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
      at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:72)
      at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:131)
      at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:82)
      at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:89)
      at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:72)
      at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:543)
      at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:105)
      at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
      at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
      at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
      at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:655)
      at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
Comment
Watch Question

You are correct in attempting to increase the buffer size by increasing the setting value of innodb_buffer_pool_size. However this is not always a fool proof way to avoid this error when working with large amounts of data. In your case the setting is massive, be aware that you need to restart the MySQL service for this setting to take effect so if you haven't done that then I would imagine that's your problem.

If all else fails you are going to have to split up the inserts into smaller batches (for instance 20 inserts of a 1000 records each).

Author

Commented:
I restarted MYSQL server after changing the value of "innodb_buffer_pool_size", but still I am facing the problem.
Any way that you can split the inserts up into smaller batches?

Author

Commented:
I can do that, do do that I have to change lot of code.

Is there any way other than the above 2 solutions?
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
The suggested solution is helpful
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.