Set Delay for MySQL Trigger Procedure Execution

When we want to add a pause in MySQL triggers or stored procedures, we use the SLEEP() function. This function stops the execution of a query or procedure for a set amount of time. It is useful in different scenarios such as checking how an app acts when operations slow down or stimulating network slowness.

SLEEP() Function

MySQL has this SLEEP() function that will suspend the operation for a specified time duration. For example, if you want to pause for 5 seconds, you would write -

SLEEP(5);

This causes the database to wait for 5 seconds before the next action.

Example

In this section, we are going to create a stored procedure with SLEEP function that will invoke the delay.

Let's go ahead and create a stored procedure called delayInMessage that pauses for 20 seconds before showing a message.

DELIMITER //
CREATE PROCEDURE delayInMessage()
BEGIN
    SELECT SLEEP(20);
    SELECT 'AFTER SLEEPING 20 SECONDS, BYE!!!';
END //
DELIMITER ;

After waiting for 20 seconds with SLEEP(20), the process prints a message indicating that it has finished.

Syntax to call the stored procedure ?

CALL yourStoredProcedureName();

Following is the query to call the above-stored procedure 'delayInMessage' and check the execution delay ?

call delayInMessage();

Following is the output of the above query ?

1 row in set (20.01 sec)

Look at the above sample output, the execution delay is 20.01 sec. This way you can add delay in MySQL procedure using sleep function.

Conclusion

• Sleep() function is useful to know how the application works under delayed network conditions. Assuring with the guarantee that your program is going to run just fine when faced with slow activities.
• Overuse of SLEEP() in production may slow down your database by blocking connections and affecting other queries. Use it only when necessary, as for troubleshooting or in certain testing scenarios.
• Avoid long delays, especially those lasting longer than a few seconds, as they might accumulate and eventually result in visible lag. Restrict delays to the bare minimum to achieve your objective.