ApsaraDB RDS:Statement concurrency control

The RDS instance runs one of the following MySQL versions:

• MySQL 8.0

• MySQL 5.7

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. Sample command:

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

  Examples:

  Create a rule for which the number of concurrent threads of the SELECT statement is 10.

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

  Create a rule for which the number of concurrent threads of the SELECT statement is 20 and the keyword of the statement is key1.

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

  Create a rule for which the number of concurrent threads of the SELECT statement in the test.t table is 20.

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

  Note

  The rule with a large Id value has a high priority.

• add_ccl_template_rule

  Create a template rule. Sample command:

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

  Note

  • You do not need to specify the Table_name and Keywords parameters. You must specify one SQL statement for the SQL query parameter.

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

  Examples:

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

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

• del_ccl_rule

  Delete a rule. Sample command:

  dbms_ccl.del_ccl_rule(<Id>);

  Examples:

  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. Sample command:

  dbms_ccl.show_ccl_rule();

  Examples:

  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 threads to be run under the rule.

• flush_ccl_rule

  If you modify the rules in the concurrency_control table, you must enable the rules again. Sample command:

  dbms_ccl.flush_ccl_rule();

  Examples:

  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)