MySQL插入大批量数据时报错“The total number of locks exceeds the lock table size”的解决办法

本文主要是介绍MySQL插入大批量数据时报错“The total number of locks exceeds the lock table size”的解决办法，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

事情的原因是：我执行了一个load into语句的SQL将一个很大的文件导入到我的MySQL数据库中，执行了一段时间后报错“The total number of locks exceeds the lock table size”。

首先使用命令 show variables like '%storage_engine%' 查看MySQL的存储引擎：

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.00 sec)

可以看到InnoDB是MySQL的默认引擎。

报错“The total number of locks exceeds the lock table size”说明MySQL的默认配置已经无法满足你的需求了，
InnoDB表执行大批量数据的更新，插入，删除操作时会出现这个问题，
需要调整InnoDB全局的innodb_buffer_pool_size的值来解决这个问题，并且重启mysql服务。

首先我们通过命令 show variables like "%_buffer_pool_size%" 查看MySQL缓存池的大小：

mysql> show variables like "%_buffer_pool_size%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set, 1 warning (0.00 sec)

可以看到，默认的缓存池大小是 8388608 = 8 * 1024 * 1024 = 8 MB。我们需要把它改大一点。
那么到底是多少呢，就是说你剩多少内存，用多少内存咯，我估计我有个3个G的内存可以用，
那么我可以将innodb_buffer_pool_size的值设成310241024*1024=3221225472。

然后我们配置一下``文件（MySQL Installer安装的话，这个是配置文件的默认位置）,将

innodb_buffer_pool_size=8M

修改为：

innodb_buffer_pool_size=3G

对于这个值的配置，其实在配置文件中也给了说明：

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.

然后重启mysqld服务。(可通过命令行执行services.msc进入服务窗口)
然后在命令行执行命令查看此时的缓存池大小：

mysql> show variables like "%_buffer_pool_size%";
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 3221225472 |
+-------------------------+------------+
1 row in set, 1 warning (0.00 sec)

可以看到这个值已经修改成了我们想要的大小 —— 3GB。

再次运行我的导入文件的SQL，发现可以了，而且还很快呢。

但是内存也是有些吃紧的。