sql delete lock

Question

I execute a query in JDBC

delete * from mytable where ...

I got:

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

I have about 200k records in the table. how to fix this?

Answer 1

I just Googled this up. Assuming you're using MySQL, increase your innodb_buffer_pool_size if you have a default size set.

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

Answer 2

this can happen when deleting a large # of rows in mysql/innodb, the suggested workaround is to increase your innodb_buffer_pool_size until it works.

http://bugs.mysql.com/bug.php?id=15667