Hi,
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.
Get all your locks at the beginning. If you're going to want to make any
updates, get write locks at the beginning.
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?
ATB,
Wesley.
---------------------------------------------------------------------
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
