How the SLEEP() function works in Mariadb?

The SLEEP() function in MariaDB is used to introduce a delay or pause in the execution of a SQL statement or stored procedure for a specified number of seconds. This function is primarily used for debugging purposes, testing purposes, or when you need to intentionally introduce a delay in your application logic.

Syntax

The syntax for the MariaDB SLEEP() function is as follows:

SLEEP(duration)

• duration: The number of seconds to pause or delay the execution. It should be a non-negative number.

The function returns 0 after the specified duration has elapsed.

Examples

Example 1: Introducing a 5-second delay

SELECT SLEEP(5);

The following is the output:

+----------+
| SLEEP(5) |
+----------+
|        0 |
+----------+

This example will pause the execution for 5 seconds before returning the value 0.

Example 2: Using SLEEP() in a stored procedure

DELIMITER $$
CREATE PROCEDURE delay_example()
BEGIN
    SELECT 'Starting delay...' AS message;
    SELECT SLEEP(3);
    SELECT 'Delay finished!' AS message;
END$$
DELIMITER ;

CALL delay_example();

The following is the output:

+-------------------+
| message           |
+-------------------+
| Starting delay... |
+-------------------+
1 row in set (0.000 sec)

+----------+
| SLEEP(3) |
+----------+
|        0 |
+----------+
1 row in set (3.000 sec)

+-----------------+
| message         |
+-----------------+
| Delay finished! |
+-----------------+
1 row in set (3.000 sec)

In this example, we create a stored procedure that introduces a 3-second delay between two SELECT statements.

Example 3: Using SLEEP() in a loop

DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, value INT);

DELIMITER $$
CREATE PROCEDURE insert_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10 DO
        INSERT INTO test_table (value) VALUES (i);
        SELECT SLEEP(0.5); -- Delay for 0.5 seconds
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

CALL insert_data();

The following is the output:

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (0.501 sec)

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (1.073 sec)

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (1.704 sec)

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (2.270 sec)

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (2.845 sec)

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (3.420 sec)

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (3.995 sec)

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (4.570 sec)

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (5.145 sec)

+------------+
| SLEEP(0.5) |
+------------+
|          0 |
+------------+
1 row in set (5.720 sec)

There are 10 rows in the table:

SELECT * FROM test_table;

The following is the output:

+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
|  4 |     4 |
|  5 |     5 |
|  6 |     6 |
|  7 |     7 |
|  8 |     8 |
|  9 |     9 |
| 10 |    10 |
+----+-------+

This example inserts 10 rows into a table with a 0.5-second delay between each insert operation, demonstrating the use of SLEEP() in a loop.

Related Functions

The following are some functions related to the MariaDB SLEEP() function:

• MariaDB BENCHMARK() function is used to execute an expression repeatedly and measure the time taken for execution.
• MariaDB GET_LOCK() function is used to acquire a lock on a specific name.
• MariaDB RELEASE_LOCK() function is used to release a lock acquired by the GET_LOCK() function.

Conclusion

The SLEEP() function in MariaDB is a useful tool for introducing intentional delays or pauses in the execution of SQL statements or stored procedures. While it is primarily used for debugging, testing, or synchronization purposes, it should be used judiciously in production environments to avoid performance issues. By understanding the syntax and usage of the SLEEP() function, along with other related functions, you can better control the flow and timing of your database operations.