Table remains locked after exiting script - MySQL

[Taras_96]

Hi all,

I've been told that when a PHP script exits, then all tables that were
locked from within the PHP script are unlocked.. obviously this is a
good idea.

However, I'm experiencing a problem in the website that I'm currently
coding; the table doesn't unlock after I exit the script. Below is the
PHP snippet relating to the problem area.

$com->setQuery('LOCK TABLE updates WRITE, doents WRITE');
$com->execute();
$com->reset();

exit();

$com is a reference to an object that we use to build up and execute
queries, and I don't think is that important as it doesn't do anything
special (it's essentially an extra layer we've added to ensure that
any strings that are used in the query are properly escaped).

The user that is used in executing this query is called
'lockedDocManip'. When the Server connections are viewed in MySQL
Admin, this user has a connection after exit() is called because
persistent connections are being used. After running the PHP script
above, I've clicked the 'home' link, which uses SQL to check that the
user is currently logged in (an SQL user called 'login').

This new connection that is established is in a 'locked' state when
viewed in Mysql Admin, which confirms that the tables are still
locked.

What could be causing this problem?

The reason why I'm using locks is that a field exists that multiple
users can edit, but it can not be re-edited once it has been edited
once. I thus lock the tables, see if anyone has edited the field, and
if not, edit it. If I don't lock the tables a race condition occurs.
Is there perhaps an alternative solution to this problem?

Thanks

Taras

[Captain]

On 15 Mar, 11:42, "Taras_96" <com> wrote: 

How about not using persistant connections and/or issuing a and UNLOCK
TABLES command?

[Peter]

On 15 Mar 2007 04:42:10 -0700, Taras_96 wrote: 

Presuming you're NOT using mysql_pconnect() in your abstraction layer...
It could be the phenomenon that Axel was talking about in the "What does
'Init DB' mean in the log?" thread, where MySQL can reuse connection
from the same host/user/password.
 

You get the "Good Poster" award for the day, for supplying the actual
problem. A way to avoid the race condition is to make the value of the
latching field part of the UPDATE's WHERE condition:

UPDATE my_table
SET latch_col = 'myvalue'
WHERE user_key = 'key_value' AND
latch_col = 'oldvalue'

where oldvalue is whatever you picked up in the same query used to
determin whether the column was updated previously or not. If someone
else updates the column in the meantime, the query will have zero
affected rows, and you can go determine why.

A better way is to incorportate the whole mess into a stored procedure.
Not so much for atomicity's sake but for reliable connection. A return
value can tell you whether the update took, whether the record was
ineligable, whether the field was changed during processing, whether
there was some other update error, etc.
not updated because it changed while the

--
"... I've seen Sun monitors on fire off the side of the multimedia lab.
I've seen NTU lights glitter in the dark near the Mail Gate.
All these things will be lost in time, like the root partition last week.
Time to die...". -- Peter Gutmann in the scary.devil.monastery

[Jerry]

Taras_96 wrote: 

Why in the world would you use persistent connections?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
net
==================