All Products
Search
Document Center

ApsaraDB RDS:Statement Concurrency Control

Last Updated:Mar 25, 2025

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.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 hit the same CCL rule and stay in the Concurrency control waiting state. If the number exceeds the value of this parameter, the SQL statements fail to be executed and the ERROR 7534 (HY000): Concurrency control waiting count exceed max waiting count error message is returned. Default value: 0. The default value indicates that no limits are imposed.

ccl_wait_timeout

The maximum period of time that an SQL statement can stay in the Concurrency control waiting state. If the period of time exceeds the value of this parameter, the system continues to execute the SQL statement regardless of the CCL rule. Default value: 86,400. Unit: 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 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.

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 number of concurrent statements. If this parameter is set to 0, all SQL statements that hit the CCL rule are not executed and the ERROR 7535 (HY000): Concurrency control refuse to execute query error message 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

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)

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.