COUNT (*) waiting for row locks in InnoDB?
Should a MySQL InnoDB table wait for write locks even for a query, for example SELECT COUNT(*) FROM t
?
My situation:
I have a table with 50,000 rows with many updates (number of views per row). InnoDB must put the write lock on the updated row. But when I make a query with only COUNT(*)
this table, MySQL can answer that query without even waiting for write locks, because no UPDATE
will change the number of rows.
Thank you so much!
source to share
No, MySql does not lock InnoDb tables for queries that only read data from tables.
This only applies to old MyIsam tables where all readers have to wait until the author is done and vice versa.
For InnoDb tables they have implemented Multiversion concurrency control
In MySql terminology, it is called Consistent Non-Blocking Reads
In short - when the reader starts a query, the database takes a snapshot of the database at the point in time the query was started, and the reader (query) only sees the changes made visible (credited) up to that point in time, but does not see the changes made later transactions. This allows readers to read data without blocking and wait for writers while still maintaining ACID
There are subtle differences depending on the transaction isolation level, you can find a detailed description here: http://dev.mysql.com/doc/refman/5.6/en /set-transaction.html
In short - read unset, read registered and repeatable read modes, all SELECT statements that only read data (SELECTs without FOR UPDATE or LOCK IN SHARE MODE clasues) are executed without blocking.
In serialized mode, all transactions are serialized and, depending on the autosave mode, the SELECT may be locked in conflicts with other transactions (when autocommit = true) or automatically converted to SELECT ... LOCK IN SHARE MODE (when autocommit = false). All details are described in the links above.
source to share