php mysql query with no table locking on myisam table

Posted on 2007-10-02
Medium Priority
Last Modified: 2013-12-13

I'm wondering if there is a way to control table locking in a mysql 'myisam' table with a php transact sql statement?  There are some sql statements that are just reading data from a table and i don't want them to lock the table. I know in mssql you can use the with(nolock) and i was wondering if mysql has something that would do the same? AKA:

mysql_query('SELECT * FROM table');   <-without locking the table
Question by:hobeau
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 19999579
MySQL Manual Says

MySQL uses table-level locking for ISAM, MyISAM, and MEMORY (HEAP) tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.


Expert Comment

ID: 19999595

i am not sure what you are trying to achieve, but myisam engine does not support transactions and AFAIK it locks whole table during WRITE, but not READ.

so what do you want to achieve exactly? :)

best regards,

LVL 17

Accepted Solution

akshah123 earned 2000 total points
ID: 19999599
AFAIK, this is not possible with mysql.  Regardless, it would not be wise to not have read lock on the table while you are performing the select * from table operation.  That would lead to you seeing inconsistent data on the table.  

Usually, a read lock is not a problem as they only block other read locks.  If you are also making simultaneous updates at the same time, then those updates and subsequent selects would start blocking.  Now, myisam storage engine only allows table level locking.  This is fine when you are mostly performing selects and only few updates and or inserts.  If that is not the case, you should look into innodb storage engine.  This engine does row-level locking.  This means that your selects will not block updates and vice-versa if they are accessing different part of the table.

MySQL does not yet have multi-version concurrency control that is used in Oracle and Sql Server.  Multi-version concurrency control does not need read locks while performing select/read operations as it keeps multiple versions of the value available for transactions that need to update. I believe the new storage engine "falcon" in mysql 6.0 alpha will have that.

For more info about locking mechanisms in mysql see...


Author Comment

ID: 19999639
Thanks akshah123,,

quick question, how would row level locking look in a sql statement? would that be something included in the insert or update statement?
LVL 17

Expert Comment

ID: 20000548
>>>quick question, how would row level locking look in a sql statement? would that be something included in the insert or update statement?

You don't have to worry about locking the rows.  MySQL will take care of it. When you perform an insert or an update, mysql will either lock the specific rows if updating only a small set or entire table, if updating large number of rows.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month15 days, 3 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question