php mysql query with no table locking on myisam table

Posted on 2007-10-02
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
    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.
    LVL 6

    Expert Comment


    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

    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

    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

    >>>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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now