Alibaba Cloud provides the concurrency control (CCL) feature to ensure the stability of ApsaraDB PolarDB MySQL-compatible edition clusters to handle request spikes, resource-consuming statements, and SQL access model changes. Alibaba Cloud also provides the DBMS_CCL package for you to use the CCL feature.

Limits

  • One of the following PolarDB clusters must be used:
    • ApsaraDB PolarDB MySQL-compatible edition 8.0
    • A cluster of ApsaraDB PolarDB MySQL-compatible edition 5.7 whose minor version of the kernel is 5.7.1.0.6 or later
    • ApsaraDB PolarDB MySQL-compatible edition 5.6
  • If the version of the current cluster is ApsaraDB PolarDB MySQL-compatible edition 5.7 and its minor version of the kernel is 5.7.1.0.6 or later, the CCL and thread pool features cannot be used together due to mutual exclusion.

Precautions

  • CCL operations do not generate binlogs. Only the current node is affected by CCL operations. For example, CCL operations performed on the primary node are not synchronized to read-only nodes.
  • CCL provides a timeout mechanism that resolves transaction deadlocks caused by DML statements. The pending threads also respond to the transaction timeout and kill threads to prevent deadlocks.

Features

CCL provides features based on the following dimensions.

Dimension Description
SQL command The SQL statement type, 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.

Create a CCL table

PolarDB uses a system table named concurrency_control to store CCL rules. The system creates the table when the system is started. The following statement is used to create this system table:
CREATE TABLE `concurrency_control` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Type` enum('SELECT','UPDATE','INSERT','DELETE') NOT NULL DEFAULT 'SELECT',
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Table_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Concurrency_count` bigint(20) DEFAULT NULL,
  `Keywords` text COLLATE utf8_bin,
  `State` enum('N','Y') NOT NULL DEFAULT 'Y',
  `Ordered` enum('N','Y') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB 
DEFAULT CHARSET=utf8 COLLATE=utf8_bin
STATS_PERSISTENT=0 COMMENT='Concurrency control'
Parameter Description
Id The ID of the CCL rule.
Note A greater value indicates a higher priority. For more information about how to modify the priority, see Manage CCL rules.
Type The SQL statement type, such as SELECT, UPDATE, INSERT, and DELETE.
Schema_name The name of the database.
Table_name The name of the table in the database.
Concurrency_count The number of concurrent threads.
Keywords The keywords. Separate multiple keywords with semicolons (;).
State Specifies whether the rule is enabled. Valid values: N and Y. Default value: Y. A value of N indicates that the rule is disabled. A value of Y indicates that the rule is enabled.
Ordered Specifies whether to match multiple keywords in the Keywords parameter in order. Valid values: N and Y. Default value: N. A value of N indicates that multiple keywords in the Keywords parameter are not matched in order. A value of Y indicates that multiple keywords in the Keywords parameter are matched in order.

Manage CCL rules

PolarDB provides four on-premises storage rules in the DBMS_CCL package. You can use these rules to manage CCL rules, as shown in the following sections:
  • add_ccl_rule

    To create a rule, execute the following statement:

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

    Example:

    • The number of concurrent threads of the SELECT statement is 10.
      mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
    • 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');
    • The number of concurrent threads of the SELECT statement executed on the test.t table is 10.
      mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, '');
  • del_ccl_rule

    To delete a rule, execute the following statement:

    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 CCL rule that you want to delete does not exist, an error is returned. 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

    To view enabled rules in the memory, execute the following statement:

    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 |          |
    +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+​
    Note The following section describes the MATCHED, RUNNING, and WAITTING parameters:
    • MATCHED: The number of times that the rule was matched.
    • RUNNING: The number of concurrent threads under the rule.
    • WAITTING: The number of pending threads under this rule.
  • flush_ccl_rule

    If you update the concurrency_control table to modify a rule, you must execute the following statement to make the rule 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)​
  • You can execute the UPDATE statement to modify the ID of a rule to modify the priority of the rule.
    update mysql.concurrency_control set ID = xx where ID = xx;

    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 |          |
    +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
    
    mysql> update mysql.concurrency_control set ID = 20 where ID = 17;
    
    mysql> call dbms_ccl.show_ccl_rule();
    +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
    | ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
    +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
    |   16 | SELECT |        |       | Y     | N     |                20 |       0 |       0 |        0 | key1     |
    |   18 | SELECT |        |       | Y     | N     |                10 |       0 |       0 |        0 |          |
    |   20 | SELECT | test   | t     | Y     | N     |                30 |       0 |       0 |        0 |          |
    +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

Test the feature

  • Execute the following statements to create rules for three dimensions:
    call dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');  // The SELECT statement is executed on the sbtest1 table. The number of concurrent threads is 3.
    call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');       // The keyword of the SELECT statement is sbtest2. The number of concurrent threads is 2.
    call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');            // The number of concurrent threads of the SELECT statement is 2.
  • Use sysbench to verify the feature in the following scenarios:
    • 64 threads
    • 4 tables
    • select.lua
  • 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.