Tables locked a bit too tight

foxrings

I want to lock a table so that other pages can not access the table in between the data pull and date update.

But it would seem it locks the same page from updating.

mysql_query('LOCK TABLES ConfigVars READ');
$result = mysql_result( mysql_query('SELECT Counter, AffilateIDcode FROM ConfigVars'), 0);

...

mysql_query('UPDATE ConfigVars SET Counter = (Counter + '."$increment)");
mysql_query('UNLOCK TABLES');

Am I right? If I am, what can be done to fix this problem?

hand

I think your update query is not correct

<?php
mysql_query('LOCK TABLES ConfigVars READ');
$result = mysql_result( mysql_query('SELECT Counter, AffilateIDcode FROM ConfigVars'), 0);

// ... 

$sqlUpdate = "UPDATE ConfigVars SET Counter=(Counter + $increment)";
mysql_query($sqlUpdate)
	or print "&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

mysql_query('UNLOCK TABLES');
?>

foxrings

Ok... I don't know why that fixed it....
😕

But I don't think it is really locking the table. Keeping out the other pages. Perhaps a persistant connection to MySQL?

hand

That fixed your problem, because your defined query was incorrect

mysql_query('UPDATE ConfigVars SET Counter = (Counter + '."$increment)");

foxrings

I must have messed up on the opening and closing of my quotes..
Or the string concatenation with the periods.

And as for getting the SQL "LOCK" to block out the other pages?

hand

Normally it should work, but you can test it for yourself by temporarely using sleep();

<?php
mysql_query('LOCK TABLES ConfigVars READ');
$result = mysql_result( mysql_query('SELECT Counter, AffilateIDcode FROM ConfigVars'), 0);

// ... 
sleep (15);  // Now you have 15 seconds for testing the lock
// ...

$sqlUpdate = "UPDATE ConfigVars SET Counter=(Counter + $increment)";
mysql_query($sqlUpdate)
    or print "&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

mysql_query('UNLOCK TABLES');
?>