Alibaba Cloud provides the concurrency control (CCL) feature to ensure the stability of PolarDB for MySQL instances 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.

Prerequisites

The version of the cluster is PolarDB for MySQL 5.6 or PolarDB for MySQL 8.0.

Precautions

  • CCL operations do not generate binlogs. Therefore, 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 data manipulation language (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 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.

Create a CCL table

PolarDB uses a system table named concurrency_control to store CCL rules. The system automatically 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 larger value of the ID specifies a higher priority. For more information about how to modify the priority, see Manage CCL rules.
Type The SQL statements, such as SELECT, UPDATE, INSERT, and DELETE.
Schema_name The name of a database.
Table_name The name of a table in the database.
Concurrency_count The number of concurrent threads.
Keywords The keyword. Separate multiple keywords by semicolons (;).
State Specifies whether the rule is enabled. Valid values: N (No) and Y (Yes). Default value: Y.
Ordered Specifies whether to match multiple keywords in the Keywords parameter in order. Valid values: N (No) and Y (Yes). Default value: N.

Manage CCL rules

PolarDB provides four local 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 that has the keyword key1 is 20.
      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 20.
      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, the system returns 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

    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 table lists the MATCHED, RUNNING, and WAITTING parameters.
    • MATCHED: The number of times that the rule is 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 need to 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 a rule ID to modify the priority of the target 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, '');  // Execute the SELECT statement on table sbtest1. The number of concurrent threads is 3.
    call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');       // The keyword of the SELECT statement is sbtest2 and 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 number displayed in the RUNNING column are the same as the specified numbers when you create rules.