問題描述

查詢:

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 ;

這篇關于有什么方法可以在不導致 MySQL 鎖定的情況下進行選擇?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！