Hi Andrey.
Andrey Kotrekhov wrote:
SQL
Hello!
Is there any way to inform mysql client application about changing in
the some table.
One process puts periodically record into the table.
Second process waits new records.
It is very expensive way to do SELECT from this table each second.
Is there any other right way to inform second process?
I was waiting to see if anyone else had any ideas, because I don't think my ideas are
very good. I should say that I think this is generally a hard problem to solve with
generic SQL, but I think some systems do support their own extensions to provide
signalling or notification.
In MySQL, the two things I can think of are:
1) make the polling more efficient. Instead of polling the table you're watching, have
the inserting process set a flag in another, smaller table, and poll that table.
Another thing you can do to make polling more efficient, when it is necessary, is use a
variable wait time. Exponential or Fibonacci has worked well for me. For more on
this, see
http://www.xaprb.com/blog/2006/05/04/how-to-make-a-program-choose-an-optimal-polling-interval/
2) Take advantage of things that use semaphores under the covers. The most obvious one
for me is a string lock with GET_LOCK(). The waiting process and the polling process
could get and release the lock to signal each other. Be careful though, as if you use
more than one lock, there is a potential for deadlock, and if you don't there is a
potential for race conditions.
I have not implemented this myself, and think I would probably write something buggy on
my first attempt, so these are just crude ideas.
Cheers
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
