List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 9 1999 4:34pm
Subject:Re: check db
View as plain text  
On Thu, 1999-09-09 15:46:44 +0100, A. Brandic wrote:
> How can I check if my database is busy (ie. if someone is writting
> to a table from which I only want to read, I want a message on the
> screen saying "Try Later").

Well, you maybe could look at the output of
  SHOW processlist;

But I guess, a better way is to use locks:


LOCK TABLES/UNLOCK TABLES
=========================
If you want to disable read and write access to a table for others
while you're writing to this table, just use
   LOCK TABLES yourtable WRITE;
   ... do the chnages you want to do, then ...
   UNLOCK TABLES;

If you just want to disable write access to a table for others, so
you can read from this table and know for shure that there are no
table changes while you're reading, use
   LOCK TABLES yourtable READ;
   ... read the data you need, then ...
   UNLOCK TABLES;

See chapters "7.23 LOCK TABLES/UNLOCK TABLES syntax" and "10.11 How
MySQL locks tables" of the MySQL manual for more detailed information.

Particularly, using table locks means that other treads trying to read
or write the locked table are just blocked, and continue working if
the lock is released.  So you can't give a "Try later" message, but
just have a delay.  This might be okay for your application, maybe
it's not ...


GET_LOCK/RELEASE_LOCK
=====================
Maybe there's another way using semaphores with the MySQL functions
GET_LOCK() and RELEASE_LOCK().

This means, that you write all your database applications so that
they issue
  GET_LOCK('some_string_you_don't_use_elsewhere_with_get_lock',0);
before accessing the table.

If no other thread has done this, yet, this commands runs fine.

But if a thread comes second (or 3rd, ...), then it won't get this
semaphore, and because we set timeout to 0, this command immediately
fails.

So you easily can test, if the table is in use or not.
(Assumed, that all other players follow the rule to do the
 get_lock as their first action!)

Just don't forget to release the semaphore again when you're done
with the table, or no other thread will get a chance to access is:
  RELEASE_LOCK('some_string_you_don't_use_elsewhere_with_get_lock');
  (of course the same string as above with GET_LOCK!)


Attached to this mail you'll find a more in-depth explanation of these
functions by Paul DuBois.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7

Attachment: [message/rfc822] get_lock.mail
Thread
general error handling with MySQLAndre Bajew7 Sep
  • Re: general error handling with MySQLsinisa8 Sep
Re: general error handling with MySQLA. Bajew9 Sep
  • Re: general error handling with MySQLMartin Ramsch9 Sep
    • check dbA. Brandic9 Sep
      • Re: check dbMartin Ramsch9 Sep
        • setting root password - installing from rpmGeorge Georgalis21 Oct
      • Re: check dbChristian Mack13 Sep