有什么方法可以在不导致 MySQL 锁定的情况下进行选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/917640/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Any way to select without causing locking in MySQL?
提问by omg
Query:
询问：
SELECT COUNT(online.account_id) cnt from online;
But online table is also modified by an event, so frequently I can see lock by running show processlist.
但是在线表也被一个事件修改，所以我经常可以通过运行show processlist.
Is there any grammar in MySQL that can make select statement not causing locks?
MySQL中是否有任何语法可以使select语句不导致锁定？
And I've forgotten to mention above that it's on a MySQL slave database.
而且我忘记在上面提到它在 MySQL 从数据库上。
After I added into my.cnf:transaction-isolation = READ-UNCOMMITTEDthe slave will meet with error:
我加入my.cnf:transaction-isolation = READ-UNCOMMITTEDslave后会遇到错误：
Error 'Binary logging not possible. Message: Transaction level 'READ-UNCOMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'' on query
错误“无法进行二进制日志记录。消息：InnoDB 中的事务级别“READ-UNCOMMITTED”对于查询时的二进制日志模式“STATEMENT”不安全
So, is there a compatible way to do this?
那么，有没有兼容的方法来做到这一点？
回答by Jon Erickson
Found an article titled "MYSQL WITH NOLOCK"
找到一篇题为“MYSQL WITH NOLOCK”的文章
https://web.archive.org/web/20100814144042/http://sqldba.org/articles/22-mysql-with-nolock.aspx
https://web.archive.org/web/20100814144042/http://sqldba.org/articles/22-mysql-with-nolock.aspx
in MS SQL Server you would do the following:
在 MS SQL Server 中，您将执行以下操作：
SELECT * FROM TABLE_NAME WITH (nolock)
and the MYSQL equivalent is
和 MYSQL 等价物是
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
EDIT
编辑
Michael Miorsuggested the following (from the comments)
Michael Mior提出以下建议（来自评论）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;
回答by Alex Martelli
If the table is InnoDB, see http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html-- it uses consistent-read (no-locking mode) for SELECTs "that do not specify FOR UPDATE or LOCK IN SHARE MODE if the innodb_locks_unsafe_for_binlog option is set and the isolation level of the transaction is not set to SERIALIZABLE. Thus, no locks are set on rows read from the selected table".
如果表是 InnoDB，请参阅http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html-- 它使用一致读取（无锁定模式）进行“选择”如果设置了 innodb_locks_unsafe_for_binlog 选项并且事务的隔离级别未设置为 SERIALIZABLE，则不指定 FOR UPDATE 或 LOCK IN SHARE MODE。因此，不会对从所选表读取的行设置锁”。
回答by NotMe
回答by zombat
You may want to read this pageof the MySQL manual. How a table gets locked is dependent on what type of table it is.
您可能需要阅读MySQL 手册的这一页。表如何被锁定取决于它是什么类型的表。
MyISAM uses table locks to achieve a very high read speed, but if you have an UPDATE statement waiting, then future SELECTS will queue up behind the UPDATE.
MyISAM 使用表锁来实现非常高的读取速度，但是如果您有一个 UPDATE 语句在等待，那么未来的 SELECTS 将在 UPDATE 后面排队。
InnoDB tables use row-level locking, and you won't have the whole table lock up behind an UPDATE. There are other kind of locking issues associated with InnoDB, but you might find it fits your needs.
InnoDB 表使用行级锁定，并且您不会在 UPDATE 之后锁定整个表。还有其他类型的与 InnoDB 相关的锁定问题，但您可能会发现它符合您的需求。
回答by Brent Baisley
Depending on your table type, locking will perform differently, but so will a SELECT count. For MyISAM tables a simple SELECT count(*) FROM table should not lock the table since it accesses meta data to pull the record count. Innodb will take longer since it has to grab the table in a snapshot to count the records, but it shouldn't cause locking.
根据您的表类型，锁定的执行方式会有所不同，但 SELECT 计数也会不同。对于 MyISAM 表，一个简单的 SELECT count(*) FROM 表不应锁定该表，因为它访问元数据以提取记录计数。Innodb 将需要更长的时间，因为它必须在快照中抓取表来计算记录数，但它不应该导致锁定。
You should at least have concurrent_insert set to 1 (default). Then, if there are no "gaps" in the data file for the table to fill, inserts will be appended to the file and SELECT and INSERTs can happen simultaneously with MyISAM tables. Note that deleting a record puts a "gap" in the data file which will attempt to be filled with future inserts and updates.
您至少应该将 concurrent_insert 设置为 1（默认）。然后，如果数据文件中没有要填充的表的“空白”，插入将附加到文件中，并且 SELECT 和 INSERT 可以与 MyISAM 表同时发生。请注意，删除记录会在数据文件中放置一个“间隙”，它将尝试用未来的插入和更新来填充。
If you rarely delete records, then you can set concurrent_insert equal to 2, and inserts will always be added to the end of the data file. Then selects and inserts can happen simultaneously, but your data file will never get smaller, no matter how many records you delete (except all records).
如果您很少删除记录，那么您可以将 concurrent_insert 设置为 2，并且插入将始终添加到数据文件的末尾。然后选择和插入可以同时发生，但您的数据文件永远不会变小，无论您删除多少条记录（所有记录除外）。
The bottom line, if you have a lot of updates, inserts and selects on a table, you should make it InnoDB. You can freely mix table types in a system though.
最重要的是，如果你在一个表上有很多更新、插入和选择，你应该让它成为 InnoDB。不过，您可以在系统中自由混合表类型。
回答by Paul Sonier
From thisreference:
从这个参考：
If you acquire a table lock explicitly with LOCK TABLES, you can request a READ LOCAL lock rather than a READ lock to enable other sessions to perform concurrent inserts while you have the table locked.
如果您使用 LOCK TABLES 显式获取表锁，则可以请求 READ LOCAL 锁而不是 READ 锁，以允许其他会话在您锁定表时执行并发插入。
回答by MarkR
SELECTs do not normally do any locking that you care about on InnoDB tables. The default transaction isolation level means that selects don't lock stuff.
SELECT 通常不会在 InnoDB 表上执行您关心的任何锁定。默认事务隔离级别意味着选择不锁定内容。
Of course contention still happens.
当然，争执还是会发生。
回答by PuGong
another way to enable dirty read in mysql is add hint: LOCK IN SHARE MODE
在 mysql 中启用脏读的另一种方法是添加提示：LOCK IN SHARE MODE
SELECT * FROM TABLE_NAME LOCK IN SHARE MODE;
