MYSQL table locking with PHP

Question

I have mysql table fg_stock. Most of the time concurrent access is happening in this table. I used this code but it doesn't work:

<?php
    mysql_query("LOCK TABLES fg_stock READ");

    $select=mysql_query("SELECT stock FROM fg_stock WHERE Item='$item'");

    while($res=mysql_fetch_array($select))
    {
        $stock=$res['stock'];
        $close_stock=$stock+$qty_in;
        $update=mysql_query("UPDATE  fg_stock SET stock='$close_stock' WHERE Item='$item' LIMIT 1");
    }

    mysql_query("UNLOCK TABLES");    
?>

Is this okay?

Answer 1

"Most of the time concurrent access is happening in this table"

So why would you want to lock the ENTIRE table when it's clear you are attempting to access a specific row from the table (WHERE Item='$item')? Chances are you are running a MyISAM storage engine for the table in question, you should look into using the InnoDB engine instead, as one of it's strong points is that it supports row level locking so you don't need to lock the entire table.

Answer 2

Why do you need to lock your table anyway?????

   mysql_query("UPDATE fg_stock SET stock=stock+$qty_in WHERE Item='$item'");

That's it! No need in locking the table and no need in unnecessary loop with set of queries. Just try to avoid SQL Injection by using intval php function on $qty_in (if it is an integer, of course), for example.

And, probably, time concurrent access is only happens due to non-optimized work with database, with the excessive number of queries.

ps: moreover, your example does not make any sense as mysql could update the same record all the time in the loop. You did not tell MySQL which record exactly do you want to update. Only told to update one record with Item='$item'. At the next iteration the SAME record could be updated again as MySQL does not know about the difference between already updated records and those that it did not touched yet.