Any way to select without causing locking in MySQL?

Question

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?

Accepted 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 ;
```

Any way to select without causing locking in MySQL?

Solution

Problem