锁的总数超过了锁表大小

Question

我正在MySQL中运行一个报表。其中一个查询涉及将大量行插入到临时表中。当我试图运行它时，出现以下错误：

错误代码1206：锁定数超过锁表大小。

相关的查询如下：

create temporary table SkusBought(
customerNum int(11),
sku int(11),
typedesc char(25),
key `customerNum` (customerNum)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into skusBought
select t1.* from
    (select customer, sku, typedesc from transactiondatatransit
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondatadelaware
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondataprestige
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku) t1
join
(select customernum from topThreetransit group by customernum) t2
on t1.customer = t2.customernum;

我读过更改配置文件以增加缓冲池大小将有所帮助，但实际上并没有任何作用。有什么方法可以修复这个问题，无论是临时解决还是永久性修复？

编辑：更改了查询的一部分。不应该影响它，但我做了一个查找替换并没有意识到它破坏了那个部分。不影响问题。

编辑2：在t1中添加了typedesc。我在查询中进行了更改，但在这里没有更新。

- maxman92

---

你为什么在子查询中进行分组？ - Tim

我觉得这很难理解。如果t2.customernum = t1.customer，那么仅从topThreetransit中选择customernum就没有意义了。毫无疑问，SkusBought.typedesc与第一列的客户代码是相同的。 - RedGrittyBrick

t2 是 t1 中的客户子集。连接是为了清除在 t1 中不存在于 t2 的客户。typedesc 的代码实际上是不正确的。我已经在实际的 SQL 脚本中更改了它，但没有在这里更改。Typedesc 是 transactiondata 的另一列（全部三个）。我会更改它，使之正确并更加合理。 - maxman92

可能是重复的问题，参考"The total number of locks exceeds the lock table size" Deleting 267 Records。 - user3338098

你解决了这个问题吗？ - Pranav MS

Answer 1

可以通过设置MySQL变量innodb_buffer_pool_size的更高值来解决此问题。默认值为8,388,608。

要更改innodb_buffer_pool_size的设置值，请参见以下设置：

1. 从服务器上找到文件my.cnf。对于Linux服务器，这通常位于/etc/my.cnf。
2. 将行innodb_buffer_pool_size=64MB添加到此文件中
3. 重新启动MySQL服务器

要重新启动MySQL服务器，您可以使用以下两个选项之一：

1. service mysqld restart
2. /etc/init.d/mysqld restart

参考The total number of locks exceeds the lock table size

- PHP Bugs

---

诀窍在于找到并修改正确的my.cnf文件。在我的Mac上，我至少有20个带有这个名称的文件，来自以前的版本、操作系统等。对我起作用的my.cnf文件一经修改如建议所示：/usr/local/mysql-5.6.23-osx10.8-x86_64/my.cnf. - Toren

在Wamp中，my.ini是需要翻译的内容。 - Enrique

请注意，现在的最低要求是128MB，因此64MB不可行，您需要提供大于128MB的容量。 - Noam Rathaus

从MySQL 5.7.5开始，您可以简单地运行SET GLOBAL innodb_buffer_pool_size=268435456;而无需查找my.cnf文件并重新启动MySQL。https://dev59.com/7m025IYBdhLWcg3w96wW#38333056 - Klim

Answer 2

我找到了另一种解决方法 - 使用表锁。当然，它可能不适合你的应用程序 - 如果需要同时更新表。

参见： 尝试使用LOCK TABLES锁定整个表，而不是InnoDB的MVCC行级锁定的默认操作。如果我没有记错，“lock table”是指InnoDB内部结构存储行和版本标识符的MVCC实现中的一个位，用于标识正在语句中修改的行，并且对于具有6000万行的表来说，可能超过了分配给它的内存。 LOCK TABLES命令应该通过设置表级锁而不是行级锁来缓解这个问题：

SET @@AUTOCOMMIT=0;
LOCK TABLES avgvol WRITE, volume READ;
INSERT INTO avgvol(date,vol)
SELECT date,avg(vol) FROM volume
GROUP BY date;
UNLOCK TABLES;

Jay Pipes，MySQL Inc.北美社区关系经理。

- Michael Goltsman

---

我按照这个答案中的指示操作，但完全没有任何改善。我的版本是5.6。 - mbmast

这个解决方案对我也没用，我使用的是Mysql 5.1。也许最近的版本不再适用了？ - frances

在我的情况下，隔离级别并不重要，我将其更改为READ UNCOMMITTED。这解决了锁定的问题。但这取决于您的用例。 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; - itstata

锁定该表似乎无法阻止单独锁定行。因此，锁定表的大小仍然超过了限制。 - B. Bogart

Answer 3

根据你已阅读的MySQL文档：

1206 (ER_LOCK_TABLE_FULL)

锁定的总数超过了锁表大小。为避免此错误，请增大innodb_buffer_pool_size的值。在单个应用程序中，一种解决方法是将大操作分成较小的部分。例如，如果出现大插入的错误，请执行几个较小的插入操作。

如果增加innodb_buffer_pool_size没有帮助，那么请按照粗体部分的指示将您的INSERT分成3个部分。跳过UNION并进行3个INSERT操作，每个操作都与topThreetransit表连接。

- MicSim

Answer 4

首先，你可以使用SQL命令show global variables like 'innodb_buffer%';来检查缓冲区大小。

解决方案是找到你的my.cnf文件并添加以下内容：

[mysqld]
innodb_buffer_pool_size=1G # depends on your data and machine

请不要忘记添加[mysqld]，否则它将无法工作。

在我的情况下，Ubuntu 16.04中的my.cnf位于文件夹/etc/mysql/下。

- Bowen Xu

Answer 5

我正在运行带有MySQL Workbench的MySQL Windows。

进入“Server”>“Server status”

在顶部，它会显示配置文件：“路径”（C:\ProgramData\MySQL\...\my.ini）

然后在“my.ini”文件中按下Ctrl+F，查找buffer_pool_size。

将值设置得更高，我建议使用64 MB（默认值为8 MB）。

通过转到“Instance”>“Startup/Shutdown”>“Stop server”（稍后再启动服务器）来重新启动服务器。

在我的情况下，我无法从表中删除条目。

- dejoma

Answer 6

修复错误代码1206：锁定表大小超过锁定数目。

在我的情况下，我使用运行在Windows上的WampServer 2.5的MySQL Workbench（5.6.17）。

对于Windows/WampServer，您必须编辑my.ini文件（而不是my.cnf文件）。

要查找此文件，请转到MySQL Workbench的菜单服务器/服务器状态，并查看服务器目录/基本目录下面的内容。

在my.ini文件中，为不同的设置定义了不同的部分，查找[mysqld]部分（如果不存在，则创建它），并添加以下命令：innodb_buffer_pool_size=4G

[mysqld]
innodb_buffer_pool_size=4G

缓冲池文件的大小将取决于您特定的计算机，在大多数情况下，2G或4G可以解决问题。

记得重新启动服务器以获取新的配置，这对我纠正了问题。

希望能帮到您！

- Efrain Plaza

Answer 7

我在运行SQL脚本时遇到了与MYSQL相关的问题，请查看下方图片。 错误代码1206：锁的数量超过了锁表大小图片 这是Mysql配置问题，因此我对my.ini进行了一些更改，在我的系统上运行良好且问题已解决。

我们需要在以下路径中的C:\ProgramData\MySQL\MySQL Server 5.7\my.ini找到my.ini文件，并在该配置文件字段中更新以下更改。

key_buffer_size=64M
read_buffer_size=64M
read_rnd_buffer_size=128M
innodb_log_buffer_size=10M
innodb_buffer_pool_size=256M
query_cache_type=2
max_allowed_packet=16M

在进行上述所有更改后，请重新启动MYSQL服务。 请参考以下图片： Microsoft MYSQL服务图片

- DhanRaj Wanjare

Answer 8

在Windows系统下：如果你安装了MySQL Workbench，打开“服务器状态”页面，查找正在运行的服务器文件所在位置。在我的情况下，它的位置是：

C:\ProgramData\MySQL\MySQL Server 5.7

打开my.ini文件并查找buffer_pool_size。将值设为高。默认值为8M。 这是我解决了这个问题的方法。

- Ch HaXam

Answer 9

如果您正确地构建了表格，使每个表格都包含相对唯一的值，那么更少耗费资源的方法是使用三个独立的"insert-into"语句，每个表格一个，同时为每个插入设置联接过滤器。

INSERT INTO SkusBought...

SELECT t1.customer, t1.SKU, t1.TypeDesc
FROM transactiondatatransit AS T1
LEFT OUTER JOIN topThreetransit AS T2
ON t1.customer = t2.customernum
WHERE T2.customernum IS NOT NULL

请重复以上步骤，对另外两个表执行相同操作。可以使用复制/粘贴的方法，只需更改FROM表的名称即可。 ** 如果您想要防止在SkusBought表中出现重复条目，可以在每个部分的WHERE子句之前添加以下连接代码。

LEFT OUTER JOIN SkusBought AS T3
ON  t1.customer = t3.customer
AND t1.sku = t3.sku

-然后是WHERE子句的最后一行-

AND t3.customer IS NULL

您最初的代码使用了许多子查询，而UNION语句可能会很昂贵，因为它将首先创建自己的临时表来从三个单独的来源填充数据，然后插入到您想要的表中，并运行另一个子查询来过滤结果。

- Forrest Pugh

Answer 10

以下答案并不直接回答OP的问题。但是，我在这里添加这个答案，因为当你在谷歌上搜索“总锁数超过锁表大小”时，这个页面是第一个结果。

---

如果您运行的查询正在解析跨越数百万行的整个表格，您可以尝试使用while循环而不是更改配置中的限制。

while循环将其分成几个部分。以下是一个示例，循环遍历一个索引列，该列为DATETIME。

# Drop
DROP TABLE IF EXISTS
new_table;

# Create (we will add keys later)
CREATE TABLE
new_table
(
    num INT(11),
    row_id VARCHAR(255),
    row_value VARCHAR(255),
    row_date DATETIME
);

# Change the delimimter
DELIMITER //

# Create procedure
CREATE PROCEDURE do_repeat(IN current_loop_date DATETIME)
BEGIN

    # Loops WEEK by WEEK until NOW(). Change WEEK to something shorter like DAY if you still get the lock errors like.
    WHILE current_loop_date <= NOW() DO

        # Do something
        INSERT INTO
            user_behavior_search_tagged_keyword_statistics_with_type
            (
                num,
                row_id,
                row_value,
                row_date
            )
        SELECT
            # Do something interesting here
            num,
            row_id,
            row_value,
            row_date
        FROM
            old_table
        WHERE
            row_date >= current_loop_date AND
            row_date < current_loop_date + INTERVAL 1 WEEK;

        # Increment
        SET current_loop_date = current_loop_date + INTERVAL 1 WEEK;

    END WHILE;

END//

# Run
CALL do_repeat('2017-01-01');

# Cleanup
DROP PROCEDURE IF EXISTS do_repeat//

# Change the delimimter back
DELIMITER ;

# Add keys
ALTER TABLE
    new_table
MODIFY COLUMN
    num int(11) NOT NULL,
ADD PRIMARY KEY
    (num),
ADD KEY
    row_id (row_id) USING BTREE,
ADD KEY
    row_date (row_date) USING BTREE;

如果您的表格没有使用日期，您也可以将其调整为循环遍历“num”列。

希望这能帮助到某些人！

- Joseph Shih