ApsaraDB RDS:SQL throttling

Prerequisites

Before you begin, ensure that your instance meets one of the following version requirements:

• MySQL 8.0 with minor version 20190816 or later

• MySQL 5.7 with minor version 20200630 or later

How CCL works

CCL rules are stored in a system table named concurrency_control. AliSQL loads these rules into memory at startup and evaluates each incoming SQL statement against them.

Each rule targets SQL statements along one or more of the following dimensions:

• SQL command — The statement type: SELECT, UPDATE, INSERT, or DELETE.

• Object — The object that the SQL command operates on, such as a TABLE or VIEW.

• Keywords — Words that must appear in the statement text.

• Template — A normalized SQL pattern (digest), matched using STATEMENT_DIGEST_TEXT.

When a statement matches a rule and the concurrency limit is reached, the statement enters the Concurrency control waiting state. It resumes execution when a running statement completes and the count drops below the limit.

Rule priority

Each SQL statement matches at most one rule. When multiple rules could apply, AliSQL uses the following priority order (highest to lowest):

1. Template rule

2. Complete rule (SQL command + schema + table)

3. Keyword rule

4. SQL statement rule

Usage notes

• CCL rules affect only the current instance. Because no binary log entries are generated, rule changes on the primary instance are not propagated to standby instances, read-only instances, or disaster recovery instances.

• CCL includes a timeout mechanism to prevent deadlocks caused by DML statements. Threads waiting under a CCL rule also respond to transaction timeouts and thread termination.

Parameters

Configure the following parameters to control queue behavior:

The concurrency_control system table

AliSQL automatically creates the concurrency_control table in the mysql tablespace when the instance starts. The table schema is:

CREATE TABLE `concurrency_control` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Type` enum('SELECT','UPDATE','INSERT','DELETE','TEMPLATE') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'SELECT',
  `Schema_name` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `Table_name` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `Concurrency_count` bigint NOT NULL,
  `Keywords` text COLLATE utf8mb3_bin,
  `State` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'Y',
  `Ordered` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
  `Digest` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `SQL_template` longtext COLLATE utf8mb3_bin,
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 COMMENT='Concurrency control'

The following table describes the columns:

Manage CCL rules

Use the DBMS_CCL stored procedures to add, delete, view, and apply CCL rules without editing the concurrency_control table directly.

dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'<Keywords>');

mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');

mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');

mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, 'key2');

dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'', 'Template_sql');

call dbms_ccl.add_ccl_rule('TEMPLATE', 'test', '', 30, '', 'SELECT c FROM t1 WHERE id=4');

dbms_ccl.del_ccl_rule(<Id>);

mysql> call dbms_ccl.del_ccl_rule(15);

mysql> call dbms_ccl.del_ccl_rule(100);
  Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 7514 | Concurrency control rule 100 is not found in table |
| Warning | 7514 | Concurrency control rule 100 is not found in cache |
+---------+------+----------------------------------------------------+

dbms_ccl.show_ccl_rule();

mysql> call dbms_ccl.show_ccl_rule();
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
|   17 | SELECT | test   | t     | Y     | N     |                30 |       0 |       0 |        0 |          |
|   16 | SELECT |        |       | Y     | N     |                20 |       0 |       0 |        0 | key1     |
|   18 | SELECT |        |       | Y     | N     |                10 |       0 |       0 |        0 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

dbms_ccl.flush_ccl_rule();

mysql> update mysql.concurrency_control set CONCURRENCY_COUNT = 15 where Id = 18;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> call dbms_ccl.flush_ccl_rule();
Query OK, 0 rows affected (0.00 sec)

Test CCL rules

The following example shows how to verify that CCL rules enforce concurrency limits as expected.

Rules:

-- Limit concurrent SELECT statements on test.sbtest1 to 3.
call dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');

-- Limit concurrent SELECT statements containing "sbtest2" to 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');

-- Limit all concurrent SELECT statements to 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');

Test conditions: Sysbench with 64 threads, 4 tables, select.lua.

Results:

mysql> call dbms_ccl.show_ccl_rule();
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE   | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
|   20 | SELECT | test   | sbtest1 | Y     | N     |                 3 |     389 |       3 |        9 |          |
|   21 | SELECT |        |         | Y     | N     |                 2 |     375 |       2 |       14 | sbtest2  |
|   22 | SELECT |        |         | Y     | N     |                 2 |     519 |       2 |       34 |          |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
3 rows in set (0.00 sec)

The RUNNING values match the concurrency limits defined in each rule, confirming that CCL is enforcing the caps correctly.

Error reference

When CCL rejects or queues a statement, your application receives one of the errors above. Add error handling in your application code — for example, retry logic for queued statements that time out (ERROR 7534), and circuit-breaker or fallback behavior for statements rejected by a zero-concurrency rule (ERROR 7535).