MySQL 如何改进 INSERT INTO ... SELECT 锁定行为

How to improve INSERT INTO ... SELECT locking behavior

提问by Artem

In our production database, we ran the following pseudo-code SQL batch query running every hour:

在我们的生产数据库中，我们每小时运行以下伪代码 SQL 批处理查询：

INSERT INTO TemporaryTable
    (SELECT FROM HighlyContentiousTableInInnoDb
     WHERE allKindsOfComplexConditions are true)

Now this query itself does not need to be fast, but I noticed it was locking up HighlyContentiousTableInInnoDb, even though it was just reading from it. Which was making some other very simple queries take ~25 seconds (that's how long that other query takes).

现在这个查询本身不需要很快，但我注意到它正在锁定HighlyContentiousTableInInnoDb，即使它只是从中读取。这使得其他一些非常简单的查询需要大约 25 秒（这是其他查询需要的时间）。

Then I discovered that InnoDB tables in such a case are actually locked by a SELECT! https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

然后我发现这种情况下的 InnoDB 表实际上是被 SELECT 锁定的！https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

But I don't really like the solution in the article of selecting into an OUTFILE, it seems like a hack (temporary files on filesystem seem sucky). Any other ideas? Is there a way to make a full copy of an InnoDB table without locking it in this way during the copy. Then I could just copy the HighlyContentiousTableto another table and do the query there.

但是我真的不喜欢选择进入 OUTFILE 的文章中的解决方案，这似乎是一个 hack（文件系统上的临时文件似乎很糟糕）。还有其他想法吗？有没有办法制作 InnoDB 表的完整副本，而无需在复制过程中以这种方式锁定它。然后我可以将其复制HighlyContentiousTable到另一个表并在那里进行查询。

采纳答案by Morgan Tocker

The answer to this question is much easier now: - Use Row Based Replication and Read Committed isolation level.

这个问题的答案现在容易多了： - 使用基于行的复制和读提交隔离级别。

The locking you were experiencing disappears.

您遇到的锁定消失了。

Longer explaination: http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html

更长的解释：http: //harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html

回答by diamonddog

You can set binlog format like that:

您可以像这样设置 binlog 格式：

SET GLOBAL binlog_format = 'ROW';

Edit my.cnf if you want to make if permanent:

如果您想永久创建，请编辑 my.cnf：

[mysqld]
binlog_format=ROW

Set isolation level for the current session before you run your query:

在运行查询之前为当前会话设置隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO t1 SELECT ....;

If this doesn't help you should try setting isolation level server wide and not only for the current session:

如果这没有帮助，您应该尝试在服务器范围内设置隔离级别，而不仅仅是针对当前会话：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Edit my.cnf if you want to make if permanent:

如果您想永久创建，请编辑 my.cnf：

[mysqld]
transaction-isolation = READ-UNCOMMITTED

You can change READ-UNCOMMITTED to READ-COMMITTED which is a better isolation level.

您可以将 READ-UNCOMMITTED 更改为 READ-COMMITTED，这是更好的隔离级别。

回答by Vipin Jain

Everyone using Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use some modifiers such as LOCK IN SHARE MODE or FOR UPDATE, SELECT statements will not lock any rows while running.

每个使用 Innodb 表的人都可能已经习惯了 Innodb 表执行非锁定读取这一事实，这意味着除非您使用一些修饰符，例如 LOCK IN SHARE MODE 或 FOR UPDATE，否则 SELECT 语句在运行时不会锁定任何行。

This is generally correct, however there a notable exception – INSERT INTO table1 SELECT * FROM table2. This statement will perform locking read (shared locks) for table2 table. It also applies to similar tables with where clause and joins. It is important for tables which is being read to be Innodb – even if writes are done in MyISAM table.

这通常是正确的，但是有一个值得注意的例外 – INSERT INTO table1 SELECT * FROM table2。该语句将对 table2 表执行锁定读取（共享锁）。它也适用于具有 where 子句和连接的类似表。对于正在读取的表是 Innodb 很重要——即使写入是在 MyISAM 表中完成的。

So why was this done, being pretty bad for MySQL Performance and concurrency ?

那么为什么要这样做，对 MySQL 性能和并发性非常不利？

The reason is – replication. In MySQL before 5.1 replication is statement based which means statements replied on the master should cause the same effect as on the slave. If Innodb would not locking rows in source table other transaction could modify the row and commit before transaction which is running INSERT .. SELECT statement. This would make this transaction to be applied on the slave before INSERT… SELECT statement and possibly result in different data than on master. Locking rows in the source table while reading them protects from this effect as other transaction modifies rows before INSERT … SELECT had chance to access it it will also be modified in the same order on the slave. If transaction tries to modify the row after it was accessed and so locked by INSERT … SELECT, transaction will have to wait until statement is completed to make sure it will be executed on the slave in proper order. Gets pretty complicated ? Well all you need to know it had to be done fore replication to work right in MySQL before 5.1.

原因是——复制。在 MySQL 5.1 之前的复制是基于语句的，这意味着在 master 上回复的语句应该会产生与在 slave 上相同的效果。如果 Innodb 不会锁定源表中的行，其他事务可以修改行并在运行 INSERT .. SELECT 语句的事务之前提交。这将使该事务在 INSERT…SELECT 语句之前应用于从属设备，并可能导致与主设备不同的数据。在读取它们时锁定源表中的行可以防止这种影响，因为其他事务在 INSERT ... SELECT 有机会访问它之前修改行，它也将在从属上以相同的顺序进行修改。如果事务在访问后尝试修改该行并被 INSERT ... SELECT 锁定，事务必须等到语句完成以确保它会以正确的顺序在从站上执行。变得相当复杂？那么你需要知道它必须在复制之前完成才能在 5.1 之前的 MySQL 中正常工作。

In MySQL 5.1 this as well as few other problems should be solved by row based replication. I'm however yet to give it real stress tests to see how well it performs :)

在 MySQL 5.1 中，这个以及其他一些问题应该通过基于行的复制来解决。然而，我还没有对其进行真正的压力测试，看看它的表现如何:)

One more thing to keep into account – INSERT … SELECT actually performs read in locking mode and so partially bypasses versioning and retrieves latest committed row. So even if you're operation in REPEATABLE-READ mode, this operation will be performed in READ-COMMITTED mode, potentially giving different result compared to what pure SELECT would give. This by the way applies to SELECT .. LOCK IN SHARE MODE and SELECT … FOR UPDATE as well.

还要考虑的另一件事 - INSERT ... SELECT 实际上在锁定模式下执行读取，因此部分绕过版本控制并检索最新提交的行。因此，即使您在 REPEATABLE-READ 模式下操作，此操作也会在 READ-COMMITTED 模式下执行，与纯 SELECT 给出的结果相比，可能会给出不同的结果。顺便说一下，这也适用于 SELECT .. LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE 。

One my ask what is if I'm not using replication and have my binary log disabled ? If replication is not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which generally gives better concurrency. However as the name says it makes locks unsafe fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog option with caution.

我的一个问题是如果我不使用复制并且禁用了我的二进制日志会怎样？如果不使用复制，您可以启用 innodb_locks_unsafe_for_binlog 选项，这将放松 Innodb 在语句执行时设置的锁，这通常会提供更好的并发性。然而，顾名思义，它会使锁不安全的前复制和时间点恢复，因此请谨慎使用 innodb_locks_unsafe_for_binlog 选项。

Note disabling binary logs is not enough to trigger relaxed locks. You have to set innodb_locks_unsafe_for_binlog=1 as well. This is done so enabling binary log does not cause unexpected changes in locking behavior and performance problems. You also can use this option with replication sometimes, if you really know what you're doing. I would not recommend it unless it is really needed as you might not know which other locks will be relaxed in future versions and how it would affect your replication.

注意禁用二进制日志不足以触发宽松锁。您还必须设置 innodb_locks_unsafe_for_binlog=1 。这样做是为了启用二进制日志不会导致锁定行为和性能问题的意外更改。如果您真的知道自己在做什么，有时也可以将此选项用于复制。除非确实需要，否则我不会推荐它，因为您可能不知道在未来版本中会放宽哪些其他锁以及它将如何影响您的复制。

回答by Azho KG

If you can allow some anomalies you can change ISOLATION LEVEL to the least strict one - READ UNCOMMITTED. But during this time someone is allowed to read from ur destination table. Or you can lock destination table manually (I assume mysql is giving this functionality?).

如果您可以允许某些异常，您可以将 ISOLATION LEVEL 更改为最不严格的级别 - READ UNCOMMITTED。但是在此期间，允许有人从您的目标表中读取。或者您可以手动锁定目标表（我假设 mysql 提供此功能？）。

Or alternatively you can use READ COMMITTED, which should not lock source table also. But it also locks inserted rows in destination table till commit.

或者，您可以使用 READ COMMITTED，它也不应该锁定源表。但它也会锁定目标表中插入的行直到提交。

I would choose second one.

我会选择第二个。

回答by smg

Probably you could use Create View command (see Create View Syntax). For example,

可能您可以使用 Create View 命令（请参阅Create View Syntax）。例如，

Create View temp as SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true

After that you could use your insert statement with this view. Something like this

之后，您可以在此视图中使用插入语句。像这样的东西

INSERT INTO TemporaryTable (SELECT * FROM temp)

This is only my proposal.

这只是我的提议。

回答by Internet Friend

Disclaimer: I'm not very experienced with databases, and I'm not sure if this idea is workable. Please correct me if it's not.

免责声明：我对数据库不是很有经验，我不确定这个想法是否可行。如果不是，请纠正我。

How about setting up a secondary equivalent table HighlyContentiousTableInInnoDb2, and creating AFTER INSERTetc. triggers in the first table which keep the new table updated with the same data. Now you should be able to lock HighlyContentiousTableInInnoDb2, and only slow down the triggers of the primary table, instead of all queries.

如何设置二级等效表HighlyContentiousTableInInnoDb2，并AFTER INSERT在第一个表中创建等触发器，以使用相同的数据更新新表。现在您应该能够锁定HighlyContentiousTableInInnoDb2，并且只减慢主表的触发器，而不是所有查询。

Potential problems:

潜在问题：

• 2 x data stored
• Additional work for all inserts, updates and deletes
• Might not be transactionally sound

• 2 x 数据存储
• 所有插入、更新和删除的额外工作
• 交易上可能不健全

回答by Philipp Andre

The reason for the lock (readlock) is to secure your reading transaction not to read "dirty" data a parallel transaction might be currently writing. Most DBMS offer the setting that users can set and revoke read & write locks manually. This might be interesting for you if reading dirty data is not a problem in your case.

锁定 (readlock) 的原因是为了保护您的读取事务，而不是读取并行事务当前可能正在写入的“脏”数据。大多数 DBMS 提供用户可以手动设置和撤销读写锁的设置。如果在您的情况下读取脏数据不是问题，这对您来说可能很有趣。

I think there is no secure way to read from a table without any locks in a DBS with multiple transactions.

我认为在具有多个事务的 DBS 中没有任何锁的情况下，没有安全的方法可以从表中读取。

But the following is some brainstorming: if space is no issue, you can think about running two instances of the same table. HighlyContentiousTableInInnoDb2for your constantly read/write transaction and a HighlyContentiousTableInInnoDb2_shadowfor your batched access. Maybe you can fill the shadow table automated via trigger/routines inside your DBMS, which is faster and smarter that an additional write transaction everywhere.

但是下面是一些头脑风暴：如果空间没有问题，可以考虑运行同一个表的两个实例。HighlyContentiousTableInInnoDb2用于您的持续读/写事务和HighlyContentiousTableInInnoDb2_shadow用于批量访问。也许您可以通过 DBMS 中的触发器/例程自动填充影子表，这比随处增加的写入事务更快、更智能。

Another idea is the question: do all transactions need to access the whole table? Otherwise you could use views to lock only necessary columns. If the continuous access and your batched access are disjoint regarding columns, it might be possible that they don't lock each other!

另一个想法是问题：是否所有事务都需要访问整个表？否则，您可以使用视图仅锁定必要的列。如果连续访问和批量访问在列方面不相交，则它们可能不会相互锁定！

回答by MEMark

I'm not familiar with MySQL, but hopefully there is an equivalent to the transaction isolation levels Snapshotand Read committed snapshotin SQL Server. Using any of these should solve your problem.

我不熟悉 MySQL，但希望有一个等效于事务隔离级别Snapshot和Read committed snapshotSQL Server。使用其中任何一个都可以解决您的问题。

回答by Glauco Cattalini Lins

I was facing the same issue using CREATE TEMPORARY TABLE ... SELECT ...with SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction.

我在使用CREATE TEMPORARY TABLE ... SELECT ...with 时遇到了同样的问题SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction。

Based on your initial query, my problem was solved by locking the HighlyContentiousTableInInnoDbbefore starting the query.

根据您的初始查询，我的问题是通过HighlyContentiousTableInInnoDb在开始查询之前锁定 来解决的。

LOCK TABLES HighlyContentiousTableInInnoDb READ;
INSERT INTO TemporaryTable
    (SELECT FROM HighlyContentiousTableInInnoDb
    WHERE allKindsOfComplexConditions are true)
UNLOCK TABLES;