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

Question

It seems that mysql fetch queries (as opposed to, for example, count) always occupy at least a table read lock on myisam tables and a row read lock on innodb tables. Is there a way to post a content selection request to mysql (I could change the table type if necessary) without having to capture any locks? I don't mind if the returned data is inconsistent, as I will use it for the search index.

Answer 1

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

At this isolation level:

SELECT statements are executed in a non-blocking manner, but perhaps an earlier version of the string may be used. Therefore, using this isolation level, such readings are incompatible. This is also called "dirty reading." Otherwise, this isolation level works like READ COMMITTED.

You can either change the default transaction isolation level from the MySQL options file, or you can enable or disable it 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 up a transaction

Answer 2

LOCK TABLES, myisam , - ...

innodb " " ( " " ) , , , :

REPEATABLE READ ( ), ,

...

- InnoDB SELECT READ COMMITTED READ. , , .

...

InnoDB , INSERT INTO... SELECT, UPDATE... (SELECT) CREATE TABLE... SELECT, FOR UPDATE LOCK IN SHARE MODE, innodb_locks_unsafe_for_binlog SERIALIZABLE. , , .

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html