Page 1 of 1

Mysql LOCK help

Posted: Wed Mar 28, 2007 8:09 am
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?

Re: Mysql LOCK help

Posted: Wed Mar 28, 2007 8:18 am
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.

Posted: Wed Mar 28, 2007 8:33 am
by mcog_esteban
So, how can i avoid the other script reading the table CONFIG ?

Posted: Wed Mar 28, 2007 8:46 am
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.

Posted: Wed Mar 28, 2007 8:57 am
by mcog_esteban
hum...thats odd.
i have just setted the option to WRITE and the second script was able to read from CONFIG.

Posted: Wed Mar 28, 2007 1:17 pm
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.