【MySQL笔记】mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法

时间:2022-07-23 02:34:14

step1:查看

1.1 Mysql命令行里输入“show engines;”查看innoddb数据引擎状态,

1.2 show variables “%_buffer%”里查看innodb_buffer_pool_size的数值,默认是8M(太小,需要改大一点!)

【MySQL笔记】mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法

step2:找配置文件,修改innodb_buffer_pool_size=64M

2.1 在linux里配置文件是my.cnf,windows里是my.ini(注:不是my-default.ini)。Mysql5.7中,my.ini在C:\ProgramData\MySQL\MySQL Server 5.7目录里,设置对应的innodb_buffer_pool_size=64M

2.2 重启mysql服务(平时最好设为手动开启)

配置文件参考资料:

Windows系统中MySQL 5.6的配置文件(my.ini)修改方法:http://blog.csdn.net/skykingf/article/details/19919315

以下是网上的解决教程,
  • 参考一:

下文转自: http://blog.chinaunix.net/uid-25266990-id-3293445.html

在操作mysql数据库表时出现以下错误。

【MySQL笔记】mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法

网上google搜索相关问题,发现一位外国牛人这么解释:

  1. If you're running an operation on a large number of rows within a table that uses the InnoDB storage engine, you might see this error:
  2. ERROR 1206 (HY000): The total number of locks exceeds the lock table size
  3. MySQL is trying to tell you that it doesn't have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_buffer_pool_size and restart MySQL. By default, this is set to only 8MB, which is too small for anyone who is using InnoDB to do anything.
  4. If you need a temporary workaround, reduce the amount of rows you're manipulating in one query. For example, if you need to delete a million rows from a table, try to delete the records in chunks of 50,000 or 100,000 rows. If you're inserting many rows, try to insert portions of the data at a single time.

原来是InnoDB表执行大批量数据的更新,插入,删除操作时会出现这个问题,需要调整InnoDB全局的innodb_buffer_pool_size的值来解决这个问题,并且重启mysql服务。

查看当前数据库存储引擎,在创建时使用 ENGINE=InnoDB类型。

默认的innodb_buffer_pool_size=8M

【MySQL笔记】mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法

修改 innodb_buffer_pool_size的值:

点击(此处)折叠或打开

  1. innodb_buffer_pool_size=64M

再一次重启mysql服务器,执行表操作,成功执行完毕。

  • 参考二:

下文转自: http://www.cnblogs.com/dyllove98/archive/2013/07/28/3221830.html

1. 问题背景
        InnoDB是新版MySQL(v5.5及以后)默认的存储引擎,之前版本的默认引擎为MyISAM,因此,低于5.5版本的mysql配置文件.my.cnf中,关于InnoDB的配置默认是被注释起来的。在实际使用时,发现不少人只是把mysql的配置文件拷贝到需要的路径下后,就启动mysqld,而建表时偏偏又指定engine=innodb。正常情况下,即使不显式配置innodb引擎的参数,该引擎也可以使用(因为MySQL会采用默认的innodb engine参数来管理对应的表),于是,大家用的很happy,因为一切正常啊。
        但随着表中数据量不断增大(如单表数百万记录),问题来了:执行一些模糊查询SQL语句时会因默认的引擎参数太小而报错,典型的错误类型如下:
            ERROR 1206 (HY000): The total number of locks exceeds the lock table size
        比如,在一个200w+记录的单表中执行类似于这样的SQL命令:delete from table_xxx where col_1 like '%http://www.youku.com/%',而符合模糊条件的记录又较多时,InnoDB引擎会因需要锁的行太多而抛出上面给出的那个错误。
        查阅资料(比如 这里 )可知,这类错误是由于InnoDB默认的配置参数不合适导致的,显然,解决这个异常的办法就是修改配置并重启mysqld。

2. 修改.my.cnf中InnoDB的默认配置

配置文件中,InnoDB典型的配置如下:

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /home/root/tools/mysql-5.0.80/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /home/root/tools/mysql-5.0.80/var/
#innodb_log_arch_dir = /home/root/tools/mysql-5.0.80/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

可见,InnoDB相关的配置默认都是注释掉的,开启并指定新值如下(注:具体的配置值应根据部署机器的物理配置而定):

 innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 256M
innodb_log_file_size = 128M # 注意这里跟默认值不一样!
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0 # 避免频繁flush
innodb_lock_wait_timeout = 50

修改完成后,mysql安装路径下执行"./bin/mysqld_safe &",以重启mysql server。shell终端执行ps aux | grep "mysqld"可看到进程已启动。 
        到这里,似乎大功告成了,但是。。。且慢! 
        命令行登录mysql后,对使用InnoDB的数据表进程操作时会悲催地发现,执行SQL命令会报错: 
            Error 'Unknown table engine 'InnoDB'' on query. 
        mysql命令行输入show engines \G后发现,列出的Engines中没有InnoDB。 
        怎么回事?mysql server进程正常,为什么InnoDB引擎出错?  
        查看mysql安装路径下mysql server的error日志(./var/xxx.err),发现其输出如下:

130701 16:15:20  mysqld started
InnoDB: Error: log file /home/root/tools/mysql/var/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 134217728 bytes!
130701 16:15:21 [Note] /home/root/tools/mysql/libexec/mysqld: ready for connections.
Version: '5.0.80-log' socket: '/home/root/tools/mysql/var/mysql.sock' port: 3306 Source distribution
130701 16:15:37 [ERROR] /home/root/tools/mysql/libexec/mysqld: Incorrect information in file: './data/tv_KEY_PREDEAL.frm'
130701 16:15:37 [ERROR] /home/root/tools/mysql/libexec/mysqld: Incorrect information in file: './data/tv_KEY_PREDEAL.frm'
# 此处省略若干行,均是加载数据表的frm文件失败的Error日志

从日志看到,重启mysql server实例时确实发生了错误,log file对不上导致加载InnoDB引擎失败。 
        how to solve it ?

3. 最终解决方法
        从上面分析可知,我们现在遇到两个错误:
          1)mysql命令行抛出的: Error 'Unknown table engine 'InnoDB'' on query.
          2)mysql error日志输出:InnoDB: Error: log file /home/root/tools/mysql/var/ib_logfile0 is of different size 0 5242880 bytes
        从因果关系看,后者是出错的根本原因,因此,只需解决这个error即可。
        根据*上的这篇帖子给出的解决方法,执行以下操作:
           1)删除mysql数据文件夹下的ib_logfile0和ib_logfile1(更安全的做法是将它们mv备份到其它路径下)
           2)重启mysql server
        此时,查看mysql启动日志无ERROR,同时,在mysql命令行show engines可看到innodb对应的"Support"一列为YES状态,表明mysql server已经成功加载该引擎,最后,执行SQL查询命令也不再报错。
        至此,问题才算彻底解决。

备注: mysql 5.0.22版本的bug 
        特别需要注意的是,修改配置导致InnoDB不可用的现象并没有在5.0.22上复现。虽然其mysql启动日志也输出了类似于"InnoDB: Error: log file /mysql/var/ib_logfile0 is of different size 0 5242880 bytes"这样的Error信息,且show engines表明InnoDB引擎处于DISABLED状态,但奇怪的是,对使用innodb引擎的table执行sql查询时,并没有报错"Error 'Unknown table engine 'InnoDB'' on query.",而是一切正常。
        执行show table status where name = 'demo_table'后发现,该表的引擎居然自动变成了MyISAM,难怪查询不报错。
        一番寻觅后,在 这里 找到了答案,原来是5.0.22版本的已知bug,囧。。。

【参考资料】
1. *: Unknown table engine 'InnoDB' 
2. StackExchange: InnoDB: Error: log file ./ib_logfile0 is of different size
3. MySQL BUG ISSUES - bug about v5.0.22

================ EOF ===============



【MySQL笔记】mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法的更多相关文章

  1. mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法

    1. 问题背景         InnoDB是新版MySQL(v5.5及以后)默认的存储引擎,之前版本的默认引擎为MyISAM,因此,低于5.5版本的mysql配置文件.my.cnf中,关于InnoD ...

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

    事情的原因是:我执行了一个load into语句的SQL将一个很大的文件导入到我的MySQL数据库中,执行了一段时间后报错"The total number of locks exceeds ...

  3. MySQL配置文件路径及‘The total number of locks exceeds the lock table size’问题

    在删除mysql中的数据时,遇到报错: ERROR 1206 (HY000): The total number of locks exceeds the lock table size 查了查,发现 ...

  4. MYSQL 遭遇 THE TOTAL NUMBER OF LOCKS EXCEEDS THE LOCK TABLE SIZE

    今天进行MySql 一个表数据的清理,经过漫长等待后出现 The total number of locks exceeds the lock table size 提示.以为是table_cache ...

  5. MYSQL碰到The total number of locks exceeds the lock table size 问题解决记录

    解决记录如下: 在mysql里面进行修改操作时提示:The total number of locks exceeds the lock table size ,通过百度搜到innodb_buffer ...

  6. MySQL解决[Err] 1206 - The total number of locks exceeds the lock table size问题

    MySQL解决[Err] 1206 - The total number of locks exceeds the lock table size问题 查看MySQL版本:mysql>show ...

  7. mysql 数据库缓存调优之解决The total number of locks exceeds the lock table size错误

    环境: mysql5.6.2  主从同步(备注:需操作主库和从库) 一.InnoDB表执行大批量数据的更新,插入,删除操作时会出现这个问题,需要调整InnoDB全局的innodb_buffer_poo ...

  8. mysql:The total number of locks exceeds the lock table size

    使用mysql InnoDB存储引擎进行大量数据的更新,删除的时候容易引发”The total number of locks exceeds the lock table size”问题,解决方法之 ...

  9. 【转】Mysql解决The total number of locks exceeds the lock table size错误

    InnoDB表执行大批量数据的更新,插入,删除操作时会出现这个问题,需要调整InnoDB全局的innodb_buffer_pool_size的值来解决这个问题,并且重启mysql服务. windows ...

随机推荐

  1. 笔记 线程(threads)

    线程:CPU使用的基本单元(线程ID.程序计数器.寄存器集合.栈). 多线程:一个进程有多个线程 多线程的优点: 增加响应度:当一个交互程序部分阻塞,该程序能继续执行 一个应用程序在同一地址空间有多个 ...

  2. Vijos 1816统计数字(计数排序)

    传送门 Description 某次科研调查时得到了n个自然数,每个数均不超过1500000000(1.5*10^9).已知不相同的数不超过10000个,现在需要统计这些自然数各自出现的次数,并按照自 ...

  3. MySQL分表自增ID解决方案(转)

    当我们对MySQL进行分表操作后,将不能依赖MySQL的自动增量来产生唯一ID了,因为数据已经分散到多个表中. 应尽量避免使用自增IP来做为主键,为数据库分表操作带来极大的不便. 在postgreSQ ...

  4. JavaScript 的数据类型 相关知识点

    (1)基本数据类型介绍 JavaScript的数据类型分为两类:原始类型(primitive type)和对象类型(object type) 或者说是:可以拥有方法的类型和不能拥有方法的类型 或者说是 ...

  5. jQuery.merge()

    jQuery.merge( first, second ) //返回Array 合并两个数组内容到第一个数组. first第一个用来合并的数组,元素是第二数组加进来的. second第二个数组合并到第 ...

  6. php面向对象(二) $this

    <!doctype html> <html> <head> <meta charset="UTF-8"> <title> ...

  7. Express难点解析

    app.js 应用程序入口文件1.// view engine setup 设置视图引擎app.set('views', path.join(__dirname, 'views'));//告诉expr ...

  8. Jobbox&period;io(职位盒子)&colon; 新兴的面向技术人才的职场招聘众推平台

    人才招聘市场一个主要问题在于猎头中介费昂贵.这对于大公司而言,或可接受. 但对于海量小微企业而言招聘成本和效率之间的平衡是非常大的一个问题. 现在产品猎场(Product Hunt)上出现了一些新的招 ...

  9. &lbrack;LeetCode&rsqb; Car Fleet 车队

    N cars are going to the same destination along a one lane road.  The destination is target miles awa ...

  10. java设计模式(1)---总则

    设计模式总则 一.概述 1.什么是设计模式 设计模式是一套被反复使用.多数人知晓的.经过分类编目的.代码设计经验的总结. 解释下: 分类编目:就是说可以找到一些特征去划分这些设计模式,从而进行分类. ...