网站制作学习网→http://www.forasp.cn/→正文:1206 - The total number of locks exceeds the lock table size
1206 - The total number of locks exceeds the lock table size
http://www.forasp.cn/ 2023/6/9 11:17:52 点击:不统计
在mysql 执行更新字段类型或者更新大量数据的时候出现 1206 - The total number of locks exceeds the lock table size
字面意思是 超过了允许锁定table大小。
查看mysql 锁定大小
show variables like "%INNODB_buffer%";
innodb_buffer_pool_chunk_size 134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_in_core_file ON
innodb_buffer_pool_instances 1
innodb_buffer_pool_load_abort OFF
innodb_buffer_pool_load_at_startup ON
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 134217728 # 这里是临时池的大小。
下面说一下解决方案: 如果是4G 则是 4*1024*1024*1024 byte 小b
SET GLOBAL innodb_buffer_pool_size= 4*1024*1024*1024
在mysql 运行上面的语句,可以临时修改 innodb_buffer_pool_size ,然后在执行大量数据更新
如果永久修改则需要在my.conf 中增加/修改
innodb_buffer_pool_size = 4*1024*1024*1024