Table locking stopping update query

Remove locks & it runs fine.

[bluedalmatian]

I'm using table locking (because even the official mysql manual doesnt tell you how to do row locking)

the problem is it's not letting me update a table.

first of all i lock the table like this:
mysql_query("lock tables event write");
mysql_query("lock tables event read");

i then do several select queries, all of which work

i then try to do an update on the event table like this:

$updateplaces = mysql_query("UPDATE event SET available_places = '$available_places' WHERE event_id = '$event_id'");
//place seized (or aborting due to DB error), unlock
mysql_query("UNLOCK TABLES");
if ($updateplaces == false)
{
mysql_close($connection);
return "ERROR: Could not update spare places for this event";
}

At which point it exists with the error i defined above because $updateplaces is == false.

If I remove the locking code this query works fine.

Anyone know why this is happening?

Table types are InnoDB on MySQL 4.1

[edited by: bluedalmatian at 6:34 pm (utc) on April 7, 2005]

[jatar_k]

try returning mysql_error and see if that gives you any more information.

[bluedalmatian]

"try returning mysql_error and see if that gives you any more information. "

Empty string :S
But I've manually checked the DB and the query is certainly not running.

[gliff]

Acording to the mysql manual [dev.mysql.com], InnoDB tables don't support table level locking. If you want to use table locks, you'll need to switch table types.

Otherwise, read up on "Row Locking" (aka, Transactions [dev.mysql.com]) in the mysql manual, as well as Innodb [dev.mysql.com] in general.

[bluedalmatian]

"According to the mysql manual, InnoDB tables don't support table level locking."

Well it works from the mysql command line. It's just from within PHP that I'm having a problem.

[bluedalmatian]

When I said it worked from the command line I was only doing one of the locks. It would seem that doing a read lock and a write lock results in the following error:

ERROR 1099 (HY000): Table 'event' was locked with a READ lock and can't be updated

Can someone please clarify my understanding locks, I want to lock it so no other process can read the table or write to it, and i thought doing

mysql_query("lock tables event write");
mysql_query("lock tables event read");

would ensure that?

Seemingly Im going to have to release the read lock to update it, but surely doing that will allow other processes to read it, and it seems somewhat illogical anyway?

Please dont point me in the direction of the MySQL manual it has very poor spartan coverage of this topic.

[jollymcfats]

You only need LOCK TABLES event WRITE. WRITE implies READ.

[bluedalmatian]

aaah. thanks v much