2.6 Running Tasks Asynchronously

2.6.1 Required Roles

2.6.2 Asynchronous Task Schema and Table

2.6.3 Running SQL Statements Asynchronously

2.6.4 Monitoring and Managing Asynchronous Tasks

2.6.5 Monitoring and Managing Application Tasks

As of MySQL 9.3.1, you can run SQL statements in the background as independent asynchronous tasks. 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 that can run independent of the session that initiated them.

In previous versions of MySQL, all SQL operations are executed synchronously within a session.

Note the following about asynchronous tasks:

• Inactive tasks are automatically removed after six days.

• The unique task identifiers are independent of the session. This lets you manage tasks across different sessions, or by using monitoring tools.

• The maximum number of asynchronous tasks that can run concurrently is specified by the limits.maximumPreparedStmtAsyncTasks key, which is present in the mysql_tasks.config table. The maximum number of asynchronous VECTOR_STORE_LOAD tasks that can run concurrently is specified by the limits.maximumHeatwaveLoadingTasks key, which is present in the mysql_tasks.config table as well. The value specified for the limits.maximumHeatwaveLoadingTasks key cannot exceed the value of the limits.maximumPreparedStmtAsyncTasks key.

  To view the maximum number of concurrent tasks allowed:

  Press CTRL+C to copy
   
  mysql> SELECT * from mysql_tasks.config;
  +----+--------------------------------------------------------------------------------------+
  | id | data                                                                                 |
  +----+--------------------------------------------------------------------------------------+
  |  1 | {"limits": {"maximumHeatwaveLoadingTasks": 5, "maximumPreparedStmtAsyncTasks": 100}} |
  +----+--------------------------------------------------------------------------------------+

• Only the SQL operations that are allowed in MySQL Events are supported for asynchronous task creation.

• Any SQL operation that depends on the return value of the SESSION_USER() or USER() function might behave differently when run asynchronously. This is because MySQL Events are controlled by the event_scheduler system variable, and the SESSION_USER() and USER() functions return the value event_scheduler instead of the actual session user name.

• If a lock cannot be acquired within the timeout period, an error is generated and the following message is displayed: Cannot acquire lock. Try again later.