Is there a way for SLEEP () in a stored procedure?

Question

I have a stored procedure that I would like to run forever, but sleep for one second in a loop. When he wakes up, he will poll the table to see if it should work. The work needs to be done every minute, so there is no worry that the table for the survey falls under updates from two authors at the same time.

What is the best method for SLEEP() for an interval in a stored procedure? It would be nice if he could sleep 200 milliseconds, but one second would also work.

Answer 1

I ran into the same problem. After much searching, I found out that we can use

 SELECT SLEEP(<seconds>); 

to delay our procedures for this many seconds. In your case using

 SELECT SLEEP(0.2); 

will be just fine.

Answer 2

You can use:

 DO SLEEP(0.2); 

Link: http://dev.mysql.com/doc/refman/5.7/en/do.html

or

 SELECT SLEEP(0.2); 

Link: http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_sleep

Answer 3

MySQL has an event scheduler baked at. Https://dev.mysql.com/doc/refman/5.6/en/events-overview.html

Sample:

 CREATE EVENT performance_schema_snapshots.fill_events_statements_summary_by_digest_history1 ON SCHEDULE -- every day at 6 am EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 14 HOUR DO -- take snapshot CALL performance_schema_snapshots.events_statements_summary_by_digest_snapshot_reset (); 

Answer 4

You will not indicate which database you are using, but in general, the way to achieve what you want is to not have sproc running indefinitely, but to have some kind of external component - for example, a scheduler or SQL Server agent in MSSQL - run sproc like this often.

Answer 5

I would use cron (linux) or the Windows Task Scheduler (windows) to schedule it to run every minute, and your stored procedure will complete its task, and then exit.

Saving procs run "forever" is a really bad idea. You have long-standing connections (which may die), you can accidentally lock the table (forever), etc.

Since they have nothing to do with each other, separating the “cycle” from your “task” is a good design, which means that you can use the (different) tools that are most suitable for each part.