How SLEEP() Works in MariaDB

In MariaDB, SLEEP() is a built-in function that sleeps (pauses) for the number of seconds specified by its argument.

If uninterrupted, it returns 0, if interrupted, it returns 1.

Syntax

The syntax goes like this:

SLEEP(duration)

Where duration is the number of seconds you want the function to sleep for. This can include microseconds.

Example 1

Here’s an example to demonstrate:

SELECT SLEEP(3);

Result:

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

In this case, the operation succeeded without interruption, and so 0 was returned.

We can see that the operation took 3.005 seconds to complete (3 of which were presumably caused by the SLEEP() function).

Example 2

Here’s another example that uses SLEEP() in between two other function calls:

SELECT CURTIME();
SELECT SLEEP(3);
SELECT CURTIME();

Result:

MariaDB [PetHouse]> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 06:22:38  |
+-----------+
1 row in set (0.000 sec)

MariaDB [PetHouse]> SELECT SLEEP(3);
SELECT CURTIME();
+----------+
| SLEEP(3) |
+----------+
|        0 |
+----------+
1 row in set (3.006 sec)

MariaDB [PetHouse]> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 06:22:41  |
+-----------+
1 row in set (0.000 sec)

Microseconds

As mentioned, you can include microseconds:

SELECT CURTIME(6);
SELECT SLEEP(3.555555);
SELECT CURTIME(6);

Result:

MariaDB [PetHouse]> SELECT CURTIME(6);
+-----------------+
| CURTIME(6)      |
+-----------------+
| 06:25:41.010538 |
+-----------------+
1 row in set (0.000 sec)

MariaDB [PetHouse]> SELECT SLEEP(3.555555);
SELECT CURTIME(6);
+-----------------+
| SLEEP(3.555555) |
+-----------------+
|               0 |
+-----------------+
1 row in set (3.561 sec)

MariaDB [PetHouse]> SELECT CURTIME(6);
+-----------------+
| CURTIME(6)      |
+-----------------+
| 06:25:44.573544 |
+-----------------+
1 row in set (0.000 sec)

Null Argument

Calling SLEEP() with null returns 0 without a pause:

SELECT SLEEP(null);

Result:

+-------------+
| SLEEP(null) |
+-------------+
|           0 |
+-------------+
1 row in set (0.000 sec)

Invalid Number of Arguments

Calling SLEEP() with no arguments, or with the wrong number of arguments, results in an error:

SELECT SLEEP();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'SLEEP'

And:

SELECT SLEEP(1, 2);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'SLEEP'

Note that statements using the SLEEP() function are not safe for replication.