有没有选择可以在不导致锁定MySQL的情况下进行选择? [英] Any way to select without causing locking in MySQL?

问题描述

查询:

SELECT COUNT(online.account_id) cnt from online;

但是联机表也会被事件修改，因此经常可以通过运行show processlist来查看锁定.

But online table is also modified by an event, so frequently I can see lock by running show processlist.

MySQL中是否有任何语法可以使select语句不引起锁定?

Is there any grammar in MySQL that can make select statement not causing locks?

在上面我已经忘记提及它在MySQL从数据库中了.

And I've forgotten to mention above that it's on a MySQL slave database.

添加到my.cnf:transaction-isolation = READ-UNCOMMITTED之后 奴隶会遇到错误:

After I added into my.cnf:transaction-isolation = READ-UNCOMMITTED the slave will meet with error:

错误'无法进行二进制日志记录.消息:对于查询中的二进制记录模式"STATEMENT"，InnoDB中的事务级别"READ-UNCOMMITTED"不安全

Error 'Binary logging not possible. Message: Transaction level 'READ-UNCOMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'' on query

那么，有兼容的方法吗?

So, is there a compatible way to do this?

推荐答案

找到了标题为"MYSQL WITH NOLOCK"的文章

Found an article titled "MYSQL WITH NOLOCK"

https: //web.archive.org/web/20100814144042/http://sqldba.org/articles/22-mysql-with-nolock.aspx

在MS SQL Server中，您将执行以下操作:

in MS SQL Server you would do the following:

SELECT * FROM TABLE_NAME WITH (nolock)

与MYSQL等效的是

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

编辑

Michael Mior (从评论中)提出以下建议

Michael Mior suggested the following (from the comments)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;

这篇关于有没有选择可以在不导致锁定MySQL的情况下进行选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！