“锁的总数超过了锁表的大小".删除267条记录 [英] "The total number of locks exceeds the lock table size" Deleting 267 Records

问题描述

我正试图从大约4000万条记录中删除267条记录.查询如下:

I'm trying to delete 267 records out of about 40 million. The query looks like:

delete from pricedata
where
pricedate > '20120413'

priceate是一个char(8)字段.

pricedate is a char(8) field.

我知道如何调整innodb_buffer_pool_size，但是如果可以的话

I know about adjusting innodb_buffer_pool_size, but if I can do

select from pricedata
where
pricedate > '20120413'

并获得267条记录(仅此而已)，没有错误，为什么它会导致删除操作阻塞?

and get 267 records (and that's all there are), no errors, why does it choke on the delete?

如果无法调整innodb_buffer_pool_size，该怎么办?

And if adjusting innodb_buffer_pool_size doesn't work, what should I do?

推荐答案

有效的方法:将innodb_buffer_pool_size更改为256M(请参阅Quassnoi原始注释下的注释).

What worked: changing innodb_buffer_pool_size to 256M (see comments under Quassnoi's original comment).

这篇关于“锁的总数超过了锁表的大小".删除267条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！