Any way to select without causing locking in MySQL

Question

Query:

SELECT COUNT(online.account_id) cnt from online;

But online table is also modified by an event, so frequently I can see lock by running show processlist.

Is there any grammar in MySQL that can make select statement not causing locks?

And I've forgotten to mention above that it's on a MySQL slave database.

After I added into my.cnf:transaction-isolation = READ-UNCOMMITTED the slave will meet with error:

Error 'Binary logging not possible. Message: Transaction level 'READ-UNCOMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'' on query

So, is there a compatible way to do this?

Answer 1

You may want to read this page of the MySQL manual. How a table gets locked is dependent on what type of table it is.

MyISAM uses table locks to achieve a very high read speed, but if you have an UPDATE statement waiting, then future SELECTS will queue up behind the UPDATE.

InnoDB tables use row-level locking, and you won't have the whole table lock up behind an UPDATE. There are other kind of locking issues associated with InnoDB, but you might find it fits your needs.

Answer 2

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 ;

Answer 3

Use

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Version 5.0 Docs are here.

Version 5.1 Docs are here.

Answer 4

From this reference:

If you acquire a table lock explicitly with LOCK TABLES, you can request a READ LOCAL lock rather than a READ lock to enable other sessions to perform concurrent inserts while you have the table locked.

Answer 5

Depending on your table type, locking will perform differently, but so will a SELECT count. For MyISAM tables a simple SELECT count(*) FROM table should not lock the table since it accesses meta data to pull the record count. Innodb will take longer since it has to grab the table in a snapshot to count the records, but it shouldn't cause locking.

You should at least have concurrent_insert set to 1 (default). Then, if there are no "gaps" in the data file for the table to fill, inserts will be appended to the file and SELECT and INSERTs can happen simultaneously with MyISAM tables. Note that deleting a record puts a "gap" in the data file which will attempt to be filled with future inserts and updates.

If you rarely delete records, then you can set concurrent_insert equal to 2, and inserts will always be added to the end of the data file. Then selects and inserts can happen simultaneously, but your data file will never get smaller, no matter how many records you delete (except all records).

The bottom line, if you have a lot of updates, inserts and selects on a table, you should make it InnoDB. You can freely mix table types in a system though.

Answer 6

another way to enable dirty read in mysql is add hint: LOCK IN SHARE MODE

SELECT * FROM TABLE_NAME LOCK IN SHARE MODE;