homepage Welcome to WebmasterWorld Guest from 54.204.141.129
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Table locking stopping update query
Remove locks & it runs fine.
bluedalmatian




msg:1312139
 6:06 pm on Apr 7, 2005 (gmt 0)

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




msg:1312140
 6:31 pm on Apr 7, 2005 (gmt 0)

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

bluedalmatian




msg:1312141
 6:42 pm on Apr 7, 2005 (gmt 0)

"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




msg:1312142
 6:45 pm on Apr 7, 2005 (gmt 0)

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




msg:1312143
 7:03 pm on Apr 7, 2005 (gmt 0)

"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




msg:1312144
 8:01 pm on Apr 7, 2005 (gmt 0)

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




msg:1312145
 8:23 pm on Apr 7, 2005 (gmt 0)

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

bluedalmatian




msg:1312146
 8:33 pm on Apr 7, 2005 (gmt 0)

aaah. thanks v much

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved