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

来源：互联网发布：淘宝运费险骗保编辑：程序博客网时间：2024/05/16 15:10

使用语句更新表数据，记录数：1485676条,出现错误：

UPDATE T_Role_Mail SET F_ID=F_ID+?

错误：

java.sql.SQLException: The total number of locks exceeds the lock table sizeat com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) ~[mysql-connector-java-5.1.16.jar:na]at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597) ~[mysql-connector-java-5.1.16.jar:na]at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529) ~[mysql-connector-java-5.1.16.jar:na]at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990) ~[mysql-connector-java-5.1.16.jar:na]at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151) ~[mysql-connector-java-5.1.16.jar:na]at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625) ~[mysql-connector-java-5.1.16.jar:na]at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119) ~[mysql-connector-java-5.1.16.jar:na]at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415) ~[mysql-connector-java-5.1.16.jar:na]at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333) ~[mysql-connector-java-5.1.16.jar:na]at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318) ~[mysql-connector-java-5.1.16.jar:na]at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147) ~[c3p0-0.9.2.1.jar:0.9.2.1]at com.morefuntek.mergertool.MergerRoleMailTable.addMailId(MergerRoleMailTable.java:97) [bin/:na]at com.morefuntek.mergertool.MergerRoleMailTable.beforeMerger(MergerRoleMailTable.java:37) [bin/:na]at com.morefuntek.mergertool.task.MergerTaskAdatapter.beforeMerger(MergerTaskAdatapter.java:21) [bin/:na]at com.morefuntek.mergertool.MainMerger.beforeMerger(MainMerger.java:107) [bin/:na]at com.morefuntek.mergertool.MainMerger.main(MainMerger.java:172) [bin/:na]

原因：

mysql没有足够的空间来存储所有的行锁，执行你的查询。唯一的解决办法是修改innodb_buffer_pool_size并重启MySQL。默认的配置只有8M，这太小了，不能用来做任何事情。如果需要一个临时的解决办法，就是减少一次性操作的记录数，比如将10000行减少到5000行。

mysql官方链接：http://bugs.mysql.com/bug.php?id=15667