Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
mcog_esteban
Forum Contributor
Posts: 127 Joined: Tue Dec 30, 2003 3:28 pm
Post
by mcog_esteban » Wed Mar 28, 2007 8:09 am
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?
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Wed Mar 28, 2007 8:18 am
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 » Wed Mar 28, 2007 8:33 am
So, how can i avoid the other script reading the table CONFIG ?
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Wed Mar 28, 2007 8:46 am
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 » Wed Mar 28, 2007 8:57 am
hum...thats odd.
i have just setted the option to WRITE and the second script was able to read from CONFIG.
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Wed Mar 28, 2007 1:17 pm
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.