How do I use "LOCK TABLES"?
| From: | Daevid Vincent | Date: | Thu, 24 Aug 2000 20:58:47 +0000 |
| Subject: | How do I use "LOCK TABLES"? | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-13522@lists.php.net to get a copy of this message | ||
I was reading these excellent notes
http://www.mysql.com/information/presentations/presentation-oscon2000-200007
19/index.html
and noticed something that said I should be using LOCK TABLES, so I tried to
implement it in an existing script, but the script won't work with it in
there. It's not a syntax thing, that is, I don't get errors, it just sorta
stops (but it didn't do what it does without the "LOCK TABLES" statement in
there...
I'm wondering if it has to do with the $linkid stuff... Am I doing that
right? Obviously the script has been highly edited, but it gives you the
idea of what it is doing and on what table. The idea is I connect to a
remote server, grab some data from a 3Million row database, summarize it and
store it locally (in under 180,000 rows ;-) for reporting info. What seems
to happen is it connects, get's some data, then doesn't put it in the new
Tattoo_Table. It just sits there.
That should be a READ lock right? and not a WRITE lock?
------------------ snip --------------
#!/bin/php -q
<?php
Error_Reporting(7); // Set this to 7 for more error reporting...
$linkid = mysql_connect( "localhost", "user", "password");
$linkidWT = mysql_connect( "remoteserver.com", "user", "password");
$result = mysql_db_query("wt_client", "SELECT ... FROM Some_Table ... WHERE
...", $linkidWT);
$count = mysql_num_rows($result);
//once we have the result from the big Player_Table, lock the Tattoo_Table
till we're done using it.
mysql_db_query("tattoo", "LOCK TABLES Tattoo_Table READ", $linkid);
$deleteresult = mysql_db_query("tattoo", "DELETE FROM Tattoo_Table WHERE
...", $linkid);
for ( $i = 0; $i < $count; $i++)
{
$rows = mysql_fetch_array($result);
$sql = "INSERT INTO Tattoo_Table VALUES (...)";
$insertresult = mysql_db_query("tattoo", $sql, $linkid);
if (!$insertresult) { echo "inserting $rows[0]\n".mysql_errno().":
".mysql_error()."\n"; exit; }
}
echo "Completed query for: ".$startofyesterday."\n";
mysql_close($linkidWT);
echo "Forcing all abbreviations to lowercase\n";
$lowerresult = mysql_db_query("tattoo", "UPDATE Tattoo_Table SET
tattoo_abbreviation = LOWER(tattoo_abbreviation)", $linkid);
echo "Fixing any 65535 tattoo_product_id problems...\n";
$aresult = mysql_db_query("tattoo", "SELECT ... FROM Tattoo_Table WHERE
...", $linkid);
$acount = mysql_num_rows($aresult);
for ( $a = 0; $a < $acount; $a++ )
{
$arow = mysql_fetch_array($aresult);
$sql = "SELECT ... FROM Tattoo_Table WHERE ...";
$result = mysql_db_query("tattoo", $sql, $linkid);
$count = mysql_num_rows($result);
for ( $i = 0; $i < $count; $i++)
{
$rows = mysql_fetch_array($result);
$updateSQL = "UPDATE Tattoo_Table SET ... WHERE ... ";
echo "Fixed ".mysql_affected_rows(mysql_db_query("tattoo", $updateSQL,
$linkid))." records\n";
}
}
//once we have the result from the big Player_Table, lock the Tattoo_Table
till we're done using it.
mysql_db_query("tattoo", "UNLOCK TABLES", $linkid);
mysql_close($linkid);
?>