Does COUNT(*) wait for row locks in InnoDB?

Question

Does MySQL InnoDB table wait for write locks even for query such as SELECT COUNT(*) FROM t?

My situation:

I've got table with 50000 rows with many updates (views count in every row). InnoDB should put a write lock on the updated row. But when I make a query with only COUNT(*) on this table, MySQL could answer this query even without waiting for write locks because no UPDATE will change the number of rows.

Thanks a lot!

Answer 1

No, MySql doesn't lock InnoDb tables for queries that only read data from tables.
This is only the case for old MyIsam tables, where all readers must wait until the writer is done and vice versa.

For InnoDb tables they implemented Multiversion concurrency control

In MySql terminology it is called Consistent Nonlocking Reads

In short - when the reader starts the query, the database makes a snapshot of the database at a point in time when the query was started, and the reader (the query) sees only changes made visible (commited) up to this point in time, but doesn't see changes made by later transactions. This allows readers to read data without locking and waiting for writers, but still keeping ACID

There are subtle differences depending on the transaction isolation level, you can find detailed description here: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

In short - in read uncommited, read commited and repeatable read modes, all SELECT statements that only read data (SELECTs without FOR UPDATE or LOCK IN SHARE MODE clasues) are performed in a nonlocking fashion.
In serializable mode all transacions are serialized and, depending on autocommit mode, SELECT can be blocked when conflicts with other transactions (when autocommit=true), or is automatically converted to SELECT ... LOCK IN SHARE MODE (when autocommit=false). All details are explained in the above links.