Any way to select without causing locking in MySQL?

Solution 1:

Found an article titled "MYSQL WITH NOLOCK"

https://web.archive.org/web/20100814144042/http://sqldba.org/articles/22-mysql-with-nolock.aspx

in MS SQL Server you would do the following:

SELECT * FROM TABLE_NAME WITH (nolock)

and the MYSQL equivalent is

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

EDIT

Michael Mior suggested the following (from the comments)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;

Solution 2:

If the table is InnoDB, see http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html -- it uses consistent-read (no-locking mode) for SELECTs "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".

Solution 3:

Use

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Version 5.0 Docs are here.

Version 5.1 Docs are here.