有什么方法可以选择而不会导致 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 ;
相关文章