Is it possible to issue a select query in mysql without taking any read locks?

Question

It seems that mysql select content (as opposed to e.g. count) queries always take at least a table read lock on myisam tables and a row read lock on innodb tables. Is there a way to issue a select content query in mysql (I could change table type if that's required) without having it to grab any locks? I don't mind if the data returned is inconsistent since I will use it for a search index.

Answer 1

With InnoDB you achieve this by setting the transaction isolation level to: READ UNCOMMITTED.

In this isolation level:

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a “dirty read.” Otherwise, this isolation level works like READ COMMITTED.

You can either change the default transaction isolation level from the MySQL option file, or else it can be enabled and disabled for a single session:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM table_name;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Further Reading: MySQL Documentation: Set Transaction