有什么方法可以在不导致 MySQL 锁定的情况下进行选择?

2021-11-20 00:00:00 locking 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"对于查询时的二进制日志模式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 提出以下建议(来自评论)

Michael Mior suggested the following (from the comments)

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

相关文章