ApsaraDB RDS:Statement Concurrency Control

The RDS instance runs one of the following MySQL versions:

• MySQL 8.0 with a minor engine version of 20190816 or later

• MySQL 5.7 with a minor engine version of 20200630 or later

CCL provides features based on the following dimensions:

• SQL command

  The types of SQL statements, such as SELECT, UPDATE, INSERT, and DELETE.

• Object

  The objects managed by SQL statements, such as tables and views.

• keywords

  The keywords of SQL statements.

• Template

  The templates of SQL statements.

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'

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

• add_ccl_rule

  Create a rule.

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

  Example:

  Create an SQL statement rule for which the number of concurrent SELECT statements is 10.

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

  Create a keyword rule for which the number of concurrent SELECT statements with the key1 keyword is 20.

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

  Create a complete rule for which the number of concurrent SELECT statements with the key2 keyword in the test.t table is 20.

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

• add_ccl_template_rule

  Create a template rule.

  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.

  • This rule is supported only for RDS instances that run MySQL 8.0 and run a minor engine version of 20230630 or later.

  Example:

  Create a template rule for which the number of concurrent SQL statements with the 'SELECT c FROM t1 WHERE id=?' template in the test database is 30.

  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.

  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 an error. You can execute the SHOW WARNINGS; statement to view the error 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 the memory.

  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 WAITTING parameters.

  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

  If you modify the rules in the concurrency_control table, you must enable the rules again for the modification to take effect.

  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)