homepage Welcome to WebmasterWorld Guest from 54.167.182.201
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
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

5+ Year Member



 
Msg#: 7715 posted 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

WebmasterWorld Administrator jatar_k us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 7715 posted 6:31 pm on Apr 7, 2005 (gmt 0)

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

bluedalmatian

5+ Year Member



 
Msg#: 7715 posted 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

5+ Year Member



 
Msg#: 7715 posted 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

5+ Year Member



 
Msg#: 7715 posted 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

5+ Year Member



 
Msg#: 7715 posted 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

10+ Year Member



 
Msg#: 7715 posted 8:23 pm on Apr 7, 2005 (gmt 0)

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

bluedalmatian

5+ Year Member



 
Msg#: 7715 posted 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