sql - 这会锁定数据库吗？

Question

insert into test.qvalues 
  select * from qcvalues.qvalues;

我想知道上面的行是否锁定了数据库 QCVALUES

Answer 1

对我来说，文档有点不清楚:

Internal Locking Methods建议，在某些情况下，可以在另一个 session 正在读取 MyISAM 表时插入:

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks. That is, you can insert rows into a MyISAM table at the same time other clients are reading from it. Holes can result from rows having been deleted from or updated in the middle of the table. If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data.

然而，Table Locking Issues显示了在 SELECT 完成之前表将被锁定的情况(这符合您的情况):

Table locking is also disadvantageous under the following scenario:

• A session issues a SELECT that takes a long time to run.
• Another session then issues an UPDATE on the same table. This session waits until the SELECT is finished.
• Another session issues another SELECT statement on the same table. Because UPDATE has higher priority than SELECT, this SELECT waits for the UPDATE to finish, after waiting for the first SELECT to finish.

InnoDB 表实现行级锁，因此只会锁定正在读取的行，而不是整个表。

我没有仅仅依赖文档，而是尝试了一些测试:

1. 创建两个具有相同结构的表:table_a 和 table_b。
2. 用 500,000 行填充 table_a。
3. 使用 INSERT INTO ... SELECT 语句将数据从 table_a 复制到 table_b。
4. 在复制过程中，使用另一个 session 将新行插入到table_a。
5. 检查 table_b 是否包含新记录。

当两个表都在 MyISAM 中时，table_b 不包含复制后的新记录。当两个表都在 InnoDB 中时，table_b 确实包含复制后的新记录。我重复了 3 次，不出所料，每次的结果都一样。

所以，简而言之，如果你的表是MyISAM，它就会被锁定。如果是 InnoDB，则不会。当然，这个测试不考虑更新，但我希望结果会相似。