On 19 Aug 2001 19:29:17 +0100, Wesley Darlington wrote:
> Hi,
Howdy.
> On Thu, Aug 16, 2001 at 02:04:25PM +0100, Ken Guest wrote:
> > At the moment, what I am doing is:
> >
> > execute "LOCK TABLES foo READ"
> > select info from foo
> > //next lock implictly unlocks previous one
> > //as it's done by the same thread/process
> > //chances of conflicts occurring are probably
> > //only theoritical.
> > execute "LOCK TABLES foo WRITE"
> > manipulate info
> > update_sql = ...
> > execute update_sql
> > execute "UNLOCK TABLES"
> >
> > and it seems to work fairly ok, but I'd like to get an opinion on
> > this from somebody experienced.
>
> I don't think this will work. AIUI, a "LOCK TABLES foo WRITE" will unlock
> any tables you currently have locked and then try to lock table foo. It
> doesn't `upgrade' any locks you currently hold.
>
> As a test, open up two mysql clients onto the same database. In the first
> client, get a read lock on table foo. In the second client, try to get a
> write lock on the same table foo - it shouldn't return, it should wait
> until it can get its read lock. Now, in the first client try to get a write
> lock on the table foo. What you would like to happen is that this first
> client should `upgrade' its lock to a write lock. What actually happens
> is that the second client receives its write lock and the first client
> hangs, waiting for its write lock.
I ran your test, and you're right - I've revised my understanding of
the 'lock granting' process.
> Get all your locks at the beginning. If you're going to want to make any
> updates, get write locks at the beginning.
ok. I'm going with that for the moment:
I have moved the 'write lock' to the beginning of the procedure
(replacing the 'read lock' that I was getting).
Things seem to be fine at the moment.
> Have you looked at using the various transaction-able tables and SELECT ...
> FOR UPDATE? Or perhaps redesiging your schema with MySQL's capabilities
> in mind?
Looks like I might need to consult the FAQs on that, Wesley.
All the best, and thanks again,
Ken.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
