Help needed for table lock

mark renton

Can anyone have a look at my code and tell me why I receive error message whilet attempting to lock a table

code works fine without table lock code

 
<? session_start(); 
include ("doDbase.php");
 $dbHost = "localhost";                                    // database variables: host location
 $dbUser = "root";           // database variables: username to access database
 $dbPass = "welcome1";          // // database variables: password for username above - should encrypt
 $dbDatabase = "library";          // database variables: database name
 
 $db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");  // variable to connect to database
 
 mysql_select_db("$dbDatabase", $db) or die ("couldnt select the database.");  
 
  $pql = "LOCK TABLES item READ";
 if (!mysql_query($pql)){
  die('Error: ' . mysql_error());
 }
 
  $sql="SELECT first_name, last_name, address1 from person";
 
  $result = mysql_query($sql);
 
  echo "<table border='1'>
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>Address</th>
</tr>";
 
  while($row = mysql_fetch_array($result)){
   echo "<tr>";
  echo "<td>" . $row['first_name'] . "</td>";
  echo "<td>" . $row['last_name'] . "</td>";
  echo "<td>" . $row['address1'] . "</td>";
  echo "</tr>";
  }
 
 $pql = "UNLOCK TABLES";
   if (!mysql_query($pql)){
  die('Error: ' . mysql_error());
 }
 
  ?>

cython

john47832 wrote: »

Can anyone have a look at my code and tell me why I receive error message whilet attempting to lock a table

code works fine without table lock code

 
<? session_start(); 
include ("doDbase.php");
 $dbHost = "localhost";                                    // database variables: host location
 $dbUser = "root";           // database variables: username to access database
 $dbPass = "welcome1";          // // database variables: password for username above - should encrypt
 $dbDatabase = "library";          // database variables: database name
 
 $db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");  // variable to connect to database
 
 mysql_select_db("$dbDatabase", $db) or die ("couldnt select the database.");  
 
  $pql = "LOCK TABLES item READ";
 if (!mysql_query($pql)){
  die('Error: ' . mysql_error());
 }
 
  $sql="SELECT first_name, last_name, address1 from person";
 
  $result = mysql_query($sql);
 
  echo "<table border='1'>
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>Address</th>
</tr>";
 
  while($row = mysql_fetch_array($result)){
   echo "<tr>";
  echo "<td>" . $row['first_name'] . "</td>";
  echo "<td>" . $row['last_name'] . "</td>";
  echo "<td>" . $row['address1'] . "</td>";
  echo "</tr>";
  }
 
 $pql = "UNLOCK TABLES";
   if (!mysql_query($pql)){
  die('Error: ' . mysql_error());
 }
 
  ?>

Can you post the text of the error you get please? And what storage engine are you using in MySQL?

mark renton

I have changed storage engine to InnoDB

Code listed is just a sample of a larger code - code contains incorrect "LOCK TABLES item READ"; this should be person - not item


No error message received now but table does not lock

either I need to use alternative user - $dbUser = "root";

and maybe table is not prepped for lock

any thoughts?

Galtee

Sorry, excuse my ignorance, but how are you determining whether the table is locked or not?

mark renton

I put a WRITE lock on it - so I believe 2 reads should not occur - but in this case it seems 2 separate sessions can access the same table data

Galtee

john47832 wrote: »

I put a WRITE lock on it - so I believe 2 reads should not occur - but in this case it seems 2 separate sessions can access the same table data

But the code you posted put a READ lock on it not WRITE lock and more than one session can have a read lock on it but as you correctly say only one session can have a write lock on it. Unless I'm missing something?