有什么方法可以选择而不会导致 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:

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

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 的等价物是

and the MYSQL equivalent is

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

编辑

Michael Mior建议以下(来自评论)

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

这篇关于有什么方法可以选择而不会导致 MySQL 锁定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！