Any way to select without causing locking in MySQL?
Query:
SELECT COUNT(online.account_id) cnt from online;
But online table is also modified by an event, so frequently I can see lock by running show processlist.
Is there any grammar in MySQL that can make select statement not causing locks?
And I've forgotten to mention above that it's on a MySQL slave database.
After I added into my.cnf:transaction-isolation = READ-UNCOMMITTED
the slave will meet with error:
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?
Answer
Found an article titled "MYSQL WITH NOLOCK"
https://web.archive.org/web/20100814144042/http://sqldba.org/articles/22-mysql-with-nolock.aspx
in MS SQL Server you would do the following:
SELECT * FROM TABLE_NAME WITH (nolock)
and the MYSQL equivalent is
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
EDIT
Michael Mior suggested the following (from the comments)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;