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

It's not that the result in itself would be inconsistent - it would be terribly wrong (i.e., you could get some random binary strings, not meaning anything at all). You can't read lines if there are a read lock in the table because you could get corrupt results.

Answer 2

in the absence of LOCK TABLES, myisam should be equivalent to read uncommitted mode, but it doesn't actually support any transaction types...

innodb runs in "consistent read" mode (at "repeatable read" isolation level) by default, which the docs suggest won't lock:

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction

...

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

...

InnoDB uses a consistent read for select in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT 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.

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

Answer 3

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