Multiple DB Connections, some with locks, some without

[chekote]

Hi,

I am running a script which has seperate functions that connect to different tables. Some of the functions need to lock their tables, while others do not.

I thought that you only had to lock all the tables you were using within a specific connection, not any and all connections you are using in the script.

e.g.

this would require a lock on both address and zip because they are used in the same query (using the same connection resource)

SELECT * FROM address, zip WHERE zip = 77388

However, if I lock address here, I shouldnt have to lock zip because they are different query's using different connection resources:

Using $Connection1 (That already has address locked)
SELECT * FROM address

Using $Connection2 (That doesnt have zip locked)
SELECT * FROM zip

Could someone please verify the situation?

I am having a problem where Function 1 connects and does a query with locked tables. Function 2 then later does another query (With completely different tables, and using a completely different connection resource), when Function 2 tries to do a query, I get an error saying the table its using isnt locked.

Zip ID Query Attempted: MySQL Error: 1100 : Table 'zip' was not locked with LOCK TABLES

Here is a shortened example of what I am doing:

<?PHP

function SHOWERROR()
	{
	return "MySQL Error: " . mysql_errno() . " : " . mysql_error();
	}

function CONTROL_CONNECT($Database_Name)
	{
	// echo "DEBUG: Connect To DBMS<br>";
	if (!($Control_Connection = mysql_connect("127.0.0.1", "*****", "*****")))
		die("&PHPError=Error: Could not connect to database");
	// echo "DEBUG: Select DB<br>";
	if (!(mysql_select_db($Database_Name,$Control_Connection)))
		{
		return "Database '$Database_Name' Selection Attempted: " . SHOWERROR();
		die();
		}

// echo "DEBUG: Return the connection variable<br>";
return $Control_Connection;
}

$Connection1 = CONTROL_CONNECT("users_master");

$SQLLock = 'LOCK TABLES users_master.users WRITE';

if(!mysql_query($SQLLock , $Connection1))
	$this->Output[Errors][code=php][] = 'Lock table attempted on users_master.users: ' . SHOWERROR();

$Connection2 = CONTROL_CONNECT("Constants");

$SQL_Query="SELECT	ZIP_ID
			FROM	zip
			WHERE	ZIP_Code = 77388
			LIMIT 1";

if(!($Result = @ mysql_query($SQL_Query , $Connection2)))
	$Output[Error] = 'Zip ID Query Attempted: ' . SHOWERROR();
else
	{	
	$Row = mysql_fetch_array($Result);
	$Output[Zip_ID]=$Row['ZIP_ID'];
	}

print_r($Output);

?>

I really dont want to have to lock all the tables I used within this object, because there are a LOT and it would be VERY inefficient to do that.

Please help!

Thanks...