CODEWITHSUNDEEP
phpmysqllocking
9pixle
9pixle

Reputation: 556

MYSQL table locking with PHP

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?

Upvotes: 3

Views: 10210

Answers (3)

Cheery
Cheery

Reputation: 16214

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.

Upvotes: 4

Mike Purcell
Mike Purcell

Reputation: 19999

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

Upvotes: 4

Kieran Andrews
Kieran Andrews

Reputation: 5885

http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

mysql> LOCK TABLES real_table WRITE, temp_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM temp_table;
mysql> DELETE FROM temp_table;
mysql> UNLOCK TABLES;

So your syntax is correct.

Also from another question:

Troubleshooting: You can test for table lock success by trying to work with another table that is not locked. If you obtained the lock, trying to write to a table that was not included in the lock statement should generate an error.

You may want to consider an alternative solution. Instead of locking, perform an update that includes the changed elements as part of the where clause. If the data that you are changing has changed since you read it, the update will "fail" and return zero rows modified. This eliminates the table lock, and all the messy horrors that may come with it, including deadlocks. PHP, mysqli, and table locks?

Upvotes: 1

Related Questions