这会锁定数据库吗? [英] does this lock the database?

问题描述

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

如果以上行锁定了数据库QCVALUES，我想知道

i would like to knwo if the above line locks the database QCVALUES

推荐答案

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

To me, the documentation is a little unclear:

内部锁定方法建议，在某些情况下，有可能在另一个会话正在从中读取时将其插入MyISAM表:

Internal Locking Methods suggests that, in some circumstances, it is possible to insert into a MyISAM table while another session is reading from it:

MyISAM存储引擎支持 并发插入以减少 读者与作家之间的争论 对于给定的表:如果是MyISAM表 中间没有空闲块 数据文件，行始终 插入到数据文件的末尾. 在这种情况下，您可以自由地混合 并发INSERT和SELECT 不带MyISAM表的语句 锁.也就是说，您可以插入行 同时进入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.

但是，表锁定问题显示了一个在SELECT完成之前表将被锁定的情况(这适合您的情况):

However, Table Locking Issues shows a situation where the table will be locked until the SELECT is complete (this fits with your situation):

在以下情况下，表锁定也是不利的 以下情况:

Table locking is also disadvantageous under the following scenario:

• 会话发出一个需要很长时间才能运行的SELECT.
• 然后另一个会话在同一表上发出UPDATE.本届会议 等待直到SELECT完成.
• 另一个会话在同一表上发出另一个SELECT语句. 因为UPDATE具有更高的优先级 而不是SELECT，此SELECT等待 等待更新完成 第一个SELECT完成.

• 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表实现了行级锁定，因此将仅锁定正在读取的行，而不是整个表.

InnoDB table implement row-level locks, so only the row being read will be locked, rather than the whole table.

我不仅仅依赖于文档，还尝试了一些测试:

Rather than relying just on the documentation, I tried a little test:

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

1. Create two tables with the same structure: table_a and table_b.
2. Fill table_a with 500,000 rows.
3. Copy data from table_a to table_b using an INSERT INTO ... SELECT statement.
4. During copy process, use another session to insert a new row into table_a.
5. Check if table_b contains the new record.

当两个表的MyISAM，table_b都不包含复制后的新记录时.当两个表都在InnoDB中时，table_b确实在复制后包含新记录.我已经重复了三遍，并且正如预期的那样，每次的结果都是相同的.

When both tables where MyISAM, table_b did not contain the new record after the copy. When both tables where InnoDB, table_b did contain the new record after the copy. I have repeated this three times, and, as expected, the result was the same each time.

因此，简而言之，如果您的表是MyISAM，它将被锁定.如果是InnoDB，则不会.当然，该测试不会考虑更新，但是我希望结果会相似.

So, in short, if your table is MyISAM, it will be locked. If it's InnoDB, it won't. Of course, this test does not consider updates, but I expect the results will be similar.

这篇关于这会锁定数据库吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！