MySQL :: HeatWave User Guide :: 2.6 Running Tasks Asynchronously

HeatWave User Guide / HeatWave MySQL / Running Tasks Asynchronously

2.6 Running Tasks Asynchronously

As of MySQL 9.3.1, you can run SQL statements as asynchronous tasks that run in the background. This lets your current session to continue without waiting for these SQL statements or tasks to complete. It is particularly helpful for long-running tasks such as data processing, report generation, or any workload that would normally block the session if run synchronously. You can run such tasks independent of the session where you initiate them and your session or application stays responsive while the task continues to run in the background.

For applications that have multiple application users, and there is no direct mapping between MySQL users and application users, you can use the application-level variants of the asynchronous task routines. Application-level asynchronous task routines let you filter and track tasks for individual application users using the application-managed user ID.

This topic includes the following sections:

To learn about the limitations of asynchronous tasks, see Section 2.21.1, “Asynchronous Task Limitations”.

Before You Begin

Ensure that you have the following roles and the roles are active:
- To initiate and manage your own asynchronous tasks, you require the mysql_task_user role.
- To manage all the other users' tasks, backup and restore data, or change the maximum task limits, you require the mysql_task_admin role.
Learn how to grant and activate a role.
Ensure that you are connected to the DB System.

Asynchronous Task Components

The mysql_tasks schema includes the following components:

Task metadata: stores information about each task, including the task ID, task name, name of the MySQL user who created the task, MySQL connection ID used to create the task, task type, application-managed user ID used to create the task, and any additional metadata related to the task.
Logs and status: stores details about task progress and log messages.
APIs: stores the routines for creating, monitoring, and terminating asynchronous tasks.

The mysql_tasks.config table stores the following key values that are used to define maximum task limits:

The limits.maximumPreparedStmtAsyncTasks key specifies the maximum number of SQL statements that can run concurrently within the HeatWave Cluster. This value can be between 1 and 128.
The limits.maximumHeatwaveLoadingTasks key specifies the maximum number of VECTOR_STORE_LOAD tasks that can run concurrently for loading unstructured data into the HeatWave Cluster. This value can be between 1 and 10.

The value specified for the limits.maximumHeatwaveLoadingTasks key cannot exceed the value of the limits.maximumPreparedStmtAsyncTasks key.

Updating Maximum Task Limits

To view or update the maximum task limits, perform the following steps:

View the maximum number of concurrent tasks you can run:

Press CTRL+C to copy
 
mysql> SELECT * from mysql_tasks.config;

The output is similar to the following:

Press CTRL+C to copy
 
+----+--------------------------------------------------------------------------------------+
| id | data                                                                                 |
+----+--------------------------------------------------------------------------------------+
|  1 | {"limits": {"maximumHeatwaveLoadingTasks": 5, "maximumPreparedStmtAsyncTasks": 100}} |
+----+--------------------------------------------------------------------------------------+

Update the maximum number of concurrent task limits:

Press CTRL+C to copy
 
mysql> UPDATE mysql_tasks.config
SET data = '{"limits": {"maximumHeatwaveLoadingTasks": MaxVectoStoreLoadTasks, "maximumPreparedStmtAsyncTasks": MaxSQLTasks}}' 
WHERE id = 1;

Replace MaxVectoStoreLoadTasks with the maximum limit for VECTOR_STORE_LOAD tasks and MaxSQLTasks with the maximum limit for SQL statements. For example:

Press CTRL+C to copy
 
mysql> UPDATE mysql_tasks.config
SET data = '{"limits": {"maximumHeatwaveLoadingTasks": 10, "maximumPreparedStmtAsyncTasks": 10}}' 
WHERE id = 1;

Running SQL Statements Asynchronously

Perform the following steps:

To run SQL statements asynchronously, use the execute_prepared_stmt_async routine:
```
Press CTRL+C to copy
 
mysql> CALL mysql_tasks.execute_prepared_stmt_async ('SQLStatements', 'DBName', 'TaskName', 'TaskData', @task_id);
```
Replace the following:
- SQLStatements: one or more SQL statements separated by a semi-colon (;).
- DBName: database name to run the SQL statements. If you are using a specific database in the current session, you can set this to NULL to continue using the current session database.
- TaskName: name for the asynchronous task. If set to NULL, execute_prepared_stmt_async is used as the default task name.
- TaskData: any additional metadata related to the task represented as JSON data. If there is no metadata, set to NULL.
The statement returns a unique task identifier (task ID) for the asynchronous task that is created, which is stored in the @task_id variable.

For example:
```
Press CTRL+C to copy
 
mysql> CALL mysql_tasks.execute_prepared_stmt_async('DO SLEEP(5); 
SELECT JSON_OBJECT("Async Task completed at", now()) into @task_result', 'demo_db', 'DemoAsyncTask', NULL, @task_id);
```
In this example:
- The first SQL statement DO SLEEP(5) simulates a time-consuming task. This can be any long-running task.
- The second statement generates an output that is stored in the reserved session variable @task_result. You can optionally use the reserved session variable @task_result to add a custom task status message for the asynchronous task, or to store an output generated by an SQL statement that you are running asynchronously. The value that you want to store in @task_result must be in JSON format.
- The task ID generated for the asynchronous task that is returned by the routine is stored in the session variable @task_id.
View the task ID that is stored in the session variable @task_id:
```
Press CTRL+C to copy
 
mysql> SELECT @task_id;
```
The output is similar to the following:
```
Press CTRL+C to copy
 
17efb56b-52a1-11f0-bca4-020017205654
```
This task ID is independent of the session. You can use it to monitor or terminate the task from any SQL session that you own.

View the asynchronous task status brief:

Press CTRL+C to copy
 
mysql> SELECT JSON_PRETTY(mysql_tasks.task_status_brief(@task_id));

The output is similar to the following:

Press CTRL+C to copy
 
| {
  "data": {
    "Async Task completed at": "2025-06-26 14:17:38.000000"
  },
  "status": "COMPLETED",
  "message": "Execution finished.",
  "progress": 100
} |

Notice that the JSON value stored in @task_result is included in the task status data field.

Note

Tasks that are completed, canceled, or encountered an error are automatically removed after six days.

To asynchronously run SQL statements on an application level using an application-managed user ID, use the execute_prepared_stmt_from_app_async routine. This routine includes additional parameters that you can use to customise the format for storing task status and log data. Similarly, to view the status brief of an application-level asynchronous task created by an application user, use the app_task_status_brief routine.

What's Next

Learn more about Asynchronous Task Routines.

PREV HOME UP NEXT