Use Statement Concurrency Control (CCL) rules - ApsaraDB RDS

Alibaba Cloud provides the concurrency control (CCL) feature and the DBMS_CCL package to ensure the stability of ApsaraDB RDS for MySQL instances in case of unexpected request traffic, resource-consuming statements, and SQL access model changes. The CCL feature is developed based on syntax rules. You can use the DBMS_CCL package to limit the maximum number of concurrently executed SQL statements of specific types. If the number of concurrently executed SQL statements reaches the upper limit, the extra SQL statements enter the waiting state until the number is less than the upper limit.

Prerequisites

The RDS instance runs one of the following MySQL versions:

MySQL 8.4
MySQL 8.0 with minor engine version 20190816 or later
MySQL 5.7 with a minor engine version of 20200630 or later

Parameters

Parameter	Description
ccl_max_waiting_count	The maximum number of SQL statements that match the same CCL rule and can be in the `Concurrency control waiting` state. If this limit is exceeded, the statements fail and return the `ERROR 7534 (HY000): Concurrency control waiting count exceed max waiting count` error. The default value is 0, which indicates that no limit is imposed.
ccl_wait_timeout	The maximum time that an SQL statement can wait in the `Concurrency control waiting` state. If this limit is exceeded, the SQL statement is executed and no longer throttled by CCL. The default value is 86400s.

Usage notes

CCL operations only affect the current RDS instance because no logs are generated. For example, CCL operations that are performed on the primary RDS instance are not synchronized to the secondary RDS instance, read-only RDS instance, or disaster recovery RDS instance.
CCL provides a timeout mechanism to resolve transaction deadlocks caused by DML statements. The pending threads also respond to the transaction timeout and thread termination to prevent deadlocks.

Feature description

CCL provides features based on the following dimensions:

SQL command: The type of an SQL command, such as SELECT, UPDATE, INSERT, or DELETE.
Object: The object on which an SQL command is run, such as a TABLE or VIEW.
Keywords: The keywords within an SQL command.
Template: The template of an SQL command.

Create a CCL table

AliSQL uses a system table named concurrency_control to store CCL rules. The system automatically creates the table when the system is started. You can execute the following statements to create the table:

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'

Parameter	Description
Id	The ID of the CCL rule.
Type	The type of the SQL statement.
Schema_name	The name of the database.
Table_name	The name of the table in the database.
Concurrency_count	The concurrency count. If you set this parameter to 0, all SQL statements that match the rule are denied and return the `ERROR 7535 (HY000): Concurrency control refuse to execute query` error.
Keywords	The keyword. Separate multiple keywords with semicolons (;).
State	Specifies whether to enable the rule.
Ordered	Specifies whether to match multiple keywords in sequence.
Digest	The 64-byte hash string that is obtained by performing hash calculation based on SQL_template.
SQL_template	The template of the SQL statement.

Manage CCL rules

AliSQL provides four management interfaces in the DBMS_CCL package. The following list describes the interfaces:

add_ccl_rule: Add a rule

Syntax

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

Examples
- Set the concurrency count for all SELECT statements to 10.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
```
- Set the concurrency count to 20 for SELECT statements that contain the keyword key1.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
```
- Set the concurrency count to 20 for SELECT statements that run on the test.t table and contain the keyword key2.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, 'key2');
```

add_ccl_template_rule: Add a template rule

Prerequisites
To add a template rule, your instance must run one of the following versions:
- MySQL 8.4
- MySQL 8.0 with minor engine version 20230630 or later
- MySQL 5.7 with minor engine version 20241231 or later

Syntax
```
dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'', 'Template_sql');
```
Note
- You do not need to specify the Table_name parameter. You must specify one SQL statement for the Template_sql parameter.
- You can use the STATEMENT_DIGEST_TEXT built-in function to view the SQL template corresponding to the SQL statement.

Example
Set the concurrency count to 30 for SQL statements in the test database that match the 'SELECT c FROM t1 WHERE id=?' template.
```
call dbms_ccl.add_ccl_rule('TEMPLATE', 'test', '', 30, '', 'SELECT c FROM t1 WHERE id=4');
```
Note
- Each SQL statement can match only one rule.
- The rules of different types are listed in descending order: template rule, complete rule, keyword rule, SQL statement rule. Each rule set is checked in turn to determine if a rule is hit.

del_ccl_rule: Delete a rule

Syntax
```
dbms_ccl.del_ccl_rule(<Id>);
```

Example

Delete the CCL rule whose ID is 15.

mysql> call dbms_ccl.del_ccl_rule(15);

Note

If the rule that you want to delete does not exist, the system reports a warning. You can run the show warnings; command to view the warning details.

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 |
+---------+------+----------------------------------------------------+

show_ccl_rule: View enabled rules

Syntax
```
dbms_ccl.show_ccl_rule();
```

Example

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 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

The following table describes MATCHED, RUNNING, and WAITTING.

Parameter	Description
MATCHED	The number of times the rule is matched.
RUNNING	The number of threads that are concurrently running under the rule.
WAITTING	The number of pending threads under the rule.

flush_ccl_rule: Apply rules

Syntax
If you directly modify rules in the concurrency_control table, the modifications do not immediately take effect. You must run the following command to apply the rules:
```
dbms_ccl.flush_ccl_rule();
```

Example

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)

Feature testing

Test script

Execute the following statements to create the rules for three dimensions.

-- Create a rule for which the number of concurrent SELECT statements with the sbtest1 keyword is 3.
call dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');

-- Create a rule for which the number of concurrent SELECT statements with the sbtest2 keyword is 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');  

-- Create a rule for which the number of concurrent SELECT statements is 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');

Test scenario
Use sysbench for the testing in the following scenarios:
- 64 threads
- 4 tables
- select.lua

Test results

Execute the following statement to query the number of concurrent threads under the rules:

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 numbers displayed in the RUNNING column are the same as those specified when you create the rules.