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?

Answer 1

If the table is InnoDB, see http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html -- it uses consistent-read (no-locking mode) for SELECTs "that do not specify FOR UPDATE or LOCK IN SHARE MODE if the innodb_locks_unsafe_for_binlog option is set and the isolation level of the transaction is not set to SERIALIZABLE. Thus, no locks are set on rows read from the selected table".

Answer 2

SELECTs do not normally do any locking that you care about on InnoDB tables. The default transaction isolation level means that selects don't lock stuff.

Of course contention still happens.