List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 21 2005 4:57pm
Subject:Re: Wait for data to change
View as plain text  
Ben Clewett <Ben.Clewett@stripped> wrote on 03/21/2005 11:36:45 

> Dear MySQL,
> I have an application which need to wait for some data to change, then 
> act on this change.
> I am polling the MySQL once a second using "SELECT ..."
> I believe there is an alternate method where a thread can be made to 
> wait for some change, therefore avoiding the expensive polling and 
> improving performance.
> If any member knows what this may be, I would be very interested.
> Regards, Ben Clewett.

I can't think of any way to do this "efficiently" without a trigger, a 
UDF, and a custom daemon. I assume based on your description that you have 
some sort of "client" application that needs to know if some data value 
has changed. However, you don't want to keep "pinging" the server to 
continuously ask "has it changed yet, has it changed yet, has it changed 

Here's how I think you can make this work with some sort of efficiency. 
You need to setup your application to connect to a daemon on some server 
somewhere. The only purpose of this daemon is to broadcast to those 
clients who connect to it a message to the effect that "some data has 
changed on the table you are interested in". If you wanted to get really 
fancy, the message could say what data changed and possibly what the new 
value is. That way your client could possibly act on the data without 
needing to poll the server.

How does the daemon know that data just changed? That's where the trigger 
and UDF come in. Triggers are fired during certain database events (adding 
records, changing records, or deleting records). I don't know for certain 
but I believe triggers can use UDFs in their code. The UDF that is called 
by the trigger detecting a change in the table you are interested in is 
what notifies the daemon (described earlier) that the data has changed. 
And, because the UDF is only called from within a trigger, you only bother 
the daemon when a change actually occurs.

The downside to this design is that triggers are not supported until 5.x 
(still in testing) so I don't know if you have that option. User-defined 
functions (UDFs) may be able to cover the job by themselves IF and only if 
you can control data changes to ALWAYS use your UDF. Otherwise some 
changes may slip through the cracks.

Anyway, that was my idea. Is there any other way for you to communicate a 
change in data than a "passive detect" like you are doing? How much 
control do you have over what can and cannot change your data 
(specifically the field you keep polling)? What other application-based 
options are open to you?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Wait for data to changeBen Clewett21 Mar
  • Re: Wait for data to changeSGreen21 Mar
    • Re: Wait for data to changeEric Bergen21 Mar
    • Re: Wait for data to changeBen Clewett22 Mar