How to use concurrency control CCL rules - ApsaraDB RDS - Alibaba Cloud Documentation Center

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 supported instance versions are as follows:

MySQL 8.0 with a minor engine version of 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 can be in the `Concurrency control waiting` state for the same CCL rule. If this limit is exceeded, the SQL statement fails and returns the error code `ERROR 7534 (HY000): Concurrency control waiting count exceed max waiting count`. The default value is 0, which means no limit.
ccl_wait_timeout	The maximum time that an SQL statement can wait in the `Concurrency control waiting` state. If this time limit is exceeded, the SQL statement continues to execute and is no longer restricted by the CCL rule. The default value is 86,400 seconds.

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 SQL command, such as SELECT, UPDATE, INSERT, or DELETE.
Object: The object that the SQL command operates on, such as a TABLE or VIEW.
Keywords: The keywords in the SQL command.
Template: The template of the 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 set to 0, all SQL statements that match this rule are rejected, and the error code `ERROR 7535 (HY000): Concurrency control refuse to execute query` is returned.
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

To simplify the management of CCL rules, AliSQL provides the following four local storage rules in DBMS_CCL:

add_ccl_rule: Add a rule

Command template

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

Examples
- Add a rule for an SQL command type: Set the concurrency for all SELECT statements to 10.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
```
- Add a rule for a keyword: Set the concurrency for SELECT statements that contain the keyword `key1` to 20.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
```
- Add a complete rule: Set the concurrency for SELECT statements on the `test.t` table that contain the keyword `key2` to 20.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, 'key2');
```

add_ccl_template_rule: Add a template-based rule

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

Command template
```
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
Add a template-based rule to set the concurrency to 30 for SQL statements in the `test` database that match the template 'SELECT c FROM t1 WHERE id=?'.
```
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

Command template
```
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 message.

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 in memory

Command template
```
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 the MATCHED, RUNNING, and WAITING columns.

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 rule changes

Command template
If you directly modify rules in the concurrency_control table, the changes do not take effect immediately. You must run the following command to apply the changes:
```
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 rule

The following three rules correspond to 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.