How to execute MySQL query after 1 minute

Question

I have a query, but I want to call it now and wish it to be executed after few mins and seconds. Please note: I don't want to use SLEEP(), as it 'freezes' the entire database. I want one of the queries to be delayed while I perform the others queries in a default manner.

Small example that doesn't works:

WAITFOR DELAY '00:00:10'
update users set 'id' = 10 where `id` > 5

_{WAITFOR DELAY is a SQL Server command}

How do I solve this problem?

• danblack: This is an odd thing to ask. Why do you want a delay? What high problem are you solving? If you are having trouble accessing a table or updating it make sure its Innodb and not MyISAM as it uses table level locks to preserve integrity.
• Beržas Su Jumis: I am about to make small game. I have no troubles accessing tables.
• danblack: "small game" bit high level and brief in describing the problem that you want to address at a SQL level.
• Beržas Su Jumis: lets say user clicks 'bake food'. food as 'baked' should be marked as complete in table after 1 hour.
• UdIt Solanki: @BeržasSuJumis you really should handle this outside sql. It will keep the connection with database open for 1 hour in given case and that's not good..

Answer 1

You could create an event.

Rather than SLEEP in the same statement as an update try: 'SELECT SLEEP(360); UPDATE ....'.

• Beržas Su Jumis: Its a drawback, as event making requires SUPER privillegies. Anything like that?: CREATE EVENT DO_INSERT_OP ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND DO INSERT INTO mytable (name,surname) VALUES ('john','john')
• Akina: @BeržasSuJumis Event mentioning must be skipped. SELECT SLEEP(360); UPDATE .... is a solution you need (don't forget to check that your connector allows multi-queries).

Answer 2

Or have a table

| item | state | time |

You can insert a new state at any time. When you retrieve an item SELECT item, state FROM table WHERE item='X' AND time > NOW() ORDER BY time DESC LIMIT 1

Answer 3

So you are creating a job, create separate cron process which runs on your server every x minutes and checks the stuff needs to be done. On your baking field add timestamp when it was submitted.

1) on sql site every enter has current timestamp when even was submitted and how long it should take 2) Create cron job runs every minute which checks if the goods are done runs your second query on it

Answer 4

Another way to do the same as in danblack's solution (quote):

Rather than SLEEP in the same statement as an update try:

SELECT SLEEP(360); UPDATE ....

---

UPDATE (SELECT SLEEP(360)) dummy, -- may use JOIN instead of comma
 tableset_for_update
SET fieldset_to_update = new_values
WHERE conditions

Can be applied to multi-table update (always) or single-table update (if it do not contain ORDER BY clause).

Answer 5

You may also want to look into using named locks. Here are the functions:

• GET_LOCK() : Tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. A negative timeout value means infinite timeout. The lock is exclusive. While held by one session, other sessions cannot obtain a lock of the same name. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out (for example, because another client has previously locked the name), or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill).
• RELEASE_LOCK() : Releases the lock named by the string str that was obtained with GET_LOCK(). Returns 1 if the lock was released, 0 if the lock was not established by this thread (in which case the lock is not released), and NULL if the named lock did not exist. The lock does not exist if it was never obtained by a call to GET_LOCK() or if it has previously been released.
• RELEASE_ALL_LOCKS() : Releases all named locks held by the current session and returns the number of locks released (0 if there were none).
• Please read carefully the MySQL Documentation on these.

In your case, you may want to do something like this:

DO GET_LOCK('update_roadblock',10);
update users set 'id' = 10 where `id` > 5;
DO RELEASE_LOCK('update_roadblock');

This will attempt to create a named locked called update_roadblock:

• If the lock is acquired in less than 10 seconds, it will execute the update.
• If the lock is not acquired in 10 seconds, lock attempt times out and executes the update.

In both cases, the delay is up to 10 seconds.

Since you are coding a game, then you may want to run DO GET_LOCK('update_roadblock',-1); to own the lock somewhere else in the code. When done with the part of the game needing the lock first, you simply call DO RELEASE_LOCK('update_roadblock'); or DO RELEASE_ALL_LOCKS();. Then, the part of the code that is waiting to run update users set 'id' = 10 where id > 5 will proceed if the delay is less than 10 seconds or proceed after 10 seconds.

GIVE IT A TRY !!!