the total number of locks exceeds the lock table size怎么解决
答案:2 悬赏:80
解决时间 2021-02-15 16:25
- 提问者网友:泪痣哥哥
- 2021-02-15 03:00
the total number of locks exceeds the lock table size怎么解决
最佳答案
- 二级知识专家网友:怙棘
- 2021-02-15 03:51
在操作mysql数据库表时出现the total number of locks exceeds the lock table size错误。
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:
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
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.
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
如下图所示
修改 innodb_buffer_pool_size的值:
innodb_buffer_pool_size=64M
再一次重启mysql服务器,执行表操作,成功执行完毕。即可。
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:
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
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.
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
如下图所示
修改 innodb_buffer_pool_size的值:
innodb_buffer_pool_size=64M
再一次重启mysql服务器,执行表操作,成功执行完毕。即可。
全部回答
- 1楼网友:罪歌
- 2021-02-15 04:38
the total number of locks exceeds the lock table size
锁的总数超过了锁表的大小
the total number of locks exceeds the lock table size
锁的总数超过了锁表的大小
锁的总数超过了锁表的大小
the total number of locks exceeds the lock table size
锁的总数超过了锁表的大小
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯