Mysql LOCK help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mcog_esteban
Forum Contributor
Posts: 127
Joined: Tue Dec 30, 2003 3:28 pm

Mysql LOCK help

Post by mcog_esteban »

Hello.
I was trying to watch the behavior of the LOCK statement, and i did this:

Code: Select all

//script test_lock1.php
<?php
include "private_functions.php";

lock_table("CONFIG");
$prefix = getPrefix("1");
$id = getID("SN");

echo "ID: $prefix".padWithZeros($id, 7)."<br>";

sleep(30);
echo "Sleep done...<br>";
unlock_table("CONFIG");

?>

in private_functions.php i'm locking the table using
... 
mysql_query("LOCK TABLES CONFIG READ", $con);
...
and while this script is running i point the browser to another script

Code: Select all

//script test_lock2.php
<?php
include "private_functions.php";

$prefix = getPrefix("1");
$id = getID("SN");

echo "ID: $prefix".padWithZeros($id, 7)."<br>";
While script test_lock1.php is running i can run test_lock2.php and read from the table ...
Is this suposed to happen?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Re: Mysql LOCK help

Post by volka »

mcog_esteban wrote:...
mysql_query("LOCK TABLES CONFIG READ", $con);
...

While script test_lock1.php is running i can run test_lock2.php and read from the table ...
Is this suposed to happen?
Yes.
mcog_esteban
Forum Contributor
Posts: 127
Joined: Tue Dec 30, 2003 3:28 pm

Post by mcog_esteban »

So, how can i avoid the other script reading the table CONFIG ?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

By setting a WRITE lock
http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html wrote:If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can write to the table. Other threads are blocked from reading or writing the table until the lock has been released.
mcog_esteban
Forum Contributor
Posts: 127
Joined: Tue Dec 30, 2003 3:28 pm

Post by mcog_esteban »

hum...thats odd.
i have just setted the option to WRITE and the second script was able to read from CONFIG.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Then the account doesn't have the necessary privileges (is there any error handling for the sql statements in your script?) or the lock was released.
Post Reply