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

User · Answer

SELECTs do not normally do any locking that you care about on InnoDB tables  The default transaction isolation level means that selects don t lock stuff   Of course contention still happens

User · Answer

Use   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED   Version 5 0 Docs are here   Version 5 1 Docs are here

User · Answer

another way to enable dirty read in mysql is add hint  LOCK IN SHARE MODE  SELECT   FROM TABLE NAME LOCK IN SHARE MODE

User · Answer

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

User · Answer

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

User · Answer

Here is an alternative programming solution that may work for others who use MyISAM IF  important  you don t care if an update has happened during the middle of the queries  As we know MyISAM can cause table level locks  especially if you have an update pending which will get locked  and then other select queries behind this update get locked too  So this method won t prevent a lock  but it will make a lot of tiny locks  so as not to hang a website for example which needs a response within a very short frame of time  The idea here is we grab a range based on an index which is quick  then we do our match from that query only  so it s in smaller batches  Then we move down the list onto the next range and check them for our match  Example is in Perl with a bit of pseudo code  and traverses high to low     object id must be an index so it s fast   First get the range of object id  as it may not start from 0 to reduce empty queries later on   my    first id   last id      db- gt db query array    sql   gt  q  SELECT MIN object id   MAX object id  FROM mytable       my  keep running   1  my  step size      1000  my  next id         last id   while   keep running          my  sql   q   SELECT object id  created  status FROM        SELECT object id  created  status FROM mytable AS is1 WHERE is1 object id  lt     ORDER BY is1 object id DESC LIMIT     AS is2 WHERE status  live  ORDER BY object id DESC            my  sth    db- gt db query  sql   gt   sql  args   gt     step size   next id           while  my   object id   created   status      sth- gt fetchrow array                 last id    object id                      do your stuff             if    last id              next id -   step size    There weren t any matched in the range we grabbed       else            next id    last id - 1    There were some  so we ll start from that              keep running   0 if  next id  lt  1     next id  lt   first id

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Any way to select without causing locking in MySQL?

Examples related to mysql

Examples related to locking