PolarDB:Concurrency Control

Your PolarDB cluster must be one of the following versions:

• PolarDB for MySQL 8.0.

• PolarDB for MySQL 5.7 with a minor engine version of 5.7.1.0.6 or later.

  Note

  If your cluster is PolarDB for MySQL 5.7 with a minor engine version of 5.7.1.0.27 or later, CCL is compatible with Thread Pool.

• PolarDB for MySQL 5.6.

How SQL statements are matched with CCL rules

• If the DIGEST value in a CCL rule is empty, the matching method is as follows:

  • If the DIGEST value is empty and the TYPE, SCHEMA, and TABLE values are not empty, the rule takes effect only if the TYPE, SCHEMA, and TABLE in your SQL statement all match the corresponding values in the CCL rule.

  • If the DIGEST value is empty, the SCHEMA and TABLE values are empty, and the TYPE value is not empty, the rule takes effect only if the TYPE of your SQL statement matches the TYPE in the CCL rule.

  Note

  If the KEYWORD value in the CCL rule is not empty, the keyword is also checked:

  • If a single keyword is configured in the CCL rule, a match is successful if the SQL statement contains that keyword.

  • If multiple keywords are configured in the CCL rule, a match is successful only if the SQL statement contains all the configured keywords. The order of the keywords in the SQL statement does not matter.

• If the DIGEST value in a CCL rule is not empty, the rule takes effect only if both the SCHEMA and the DIGEST value of your SQL statement match the corresponding values in the CCL rule.

• If the SCHEMA value in a CCL rule is empty, the rule takes effect if the DIGEST value of your SQL statement matches the DIGEST value in the CCL rule.

A single SQL statement can match only one CCL rule. If a statement matches multiple rules, the rule with the highest priority is used. The priority is determined in the following order. If multiple rules have the same priority, the rule with the lower ID is used.

1. Match by DIGEST value.

2. Match by TYPE, SCHEMA, and TABLE.

3. Match by TYPE only.

You can modify the following parameters in the PolarDB console. For more information, see Set cluster and node parameters.

PolarDB uses a system table named concurrency_control to store CCL rules. The system automatically creates this table at startup, so you do not need to create it manually. The CREATE statement for this system table is as follows:

CREATE TABLE concurrency_control (
  Id bigint AUTO_INCREMENT NOT NULL,
  Type varchar(64),
  Schema_name varchar(64),
  Table_name varchar(64),
  Concurrency_count bigint NOT NULL,
  Keywords text,
  State enum('N','Y') COLLATE utf8_general_ci DEFAULT 'Y' NOT NULL,
  Ordered enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
  Digest varchar(64),
  Digest_text longtext,
  Extra mediumtext,
  PRIMARY KEY Rule_id(id)
) Engine=InnoDB STATS_PERSISTENT=0 CHARACTER SET utf8 COLLATE utf8_bin
  comment='Concurrency control' TABLESPACE=mysql;

The parameters are described in the following table:

To help you manage CCL rules, PolarDB provides the following six local stored procedures in DBMS_CCL.

• add_ccl_rule: Adds a CCL rule that matches by TYPE, SCHEMA, TABLE, and KEYWORD.

  Syntax

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

  Example

  • Add a CCL rule for the SELECT type. When the concurrency reaches 10, subsequent statements are queued or report an error.

    CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');

  • Add a CCL rule for SELECT statements that contain the keyword `key1`. When the concurrency reaches 20, subsequent statements are queued or report an error.

    CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');

  • Add a CCL rule for SELECT statements that contain the keywords `key1`, `key2`, and `key3`. The order of the keywords does not matter. When the concurrency reaches 20, subsequent statements are queued or report an error.

    CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1;key2;key3');

  • Add a CCL rule for SELECT statements on the t table in the test schema. When the concurrency of SELECT statements reaches 10, subsequent statements are queued or report an error.

    CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 10, '');

• add_ccl_digest_rule: Adds a CCL rule that matches by DIGEST value.

  Note

  The add_ccl_digest_rule stored procedure is supported only on the following database engine versions:

  • PolarDB for MySQL 8.0.1 with a minor engine version of 8.0.1.1.31 or later.

  • PolarDB for MySQL 8.0.2 with a minor engine version of 8.0.2.2.12 or later.

  Syntax

  dbms_ccl.add_ccl_digest_rule('<Schema_name>', '<Query>', <Concurrency_count>);

  Example

  • Add a CCL rule that matches the SQL statement SELECT * FROM t1. When the concurrency reaches 10, subsequent statements are queued or report an error.

    CALL dbms_ccl.add_ccl_digest_rule("", "SELECT * FROM t1", 10);

  • Add a CCL rule that matches the SQL statement SELECT * FROM t1 in the test schema. When the concurrency reaches 10, subsequent statements are queued or report an error.

    CALL dbms_ccl.add_ccl_digest_rule("test", "SELECT * FROM t1", 10);

  • Add a CCL rule that matches the SQL statement SELECT * FROM t1 WHERE col1=1. When the concurrency reaches 10, subsequent statements are queued or report an error.

    CALL dbms_ccl.add_ccl_digest_rule("", "SELECT * FROM t1 WHERE col1 = 1", 10);

    Note

    If an SQL statement contains constants, it can be matched even if the constant values are different. For example, the preceding CCL rule also matches the SQL statement SELECT * FROM t1 WHERE col1 = 2.

• add_ccl_digest_rule_by_hash: Adds a CCL rule that matches by DIGEST value. This procedure uses a pre-calculated DIGEST value instead of an SQL statement.

  Note

  The add_ccl_digest_rule_by_hash stored procedure is supported only on PolarDB for MySQL 8.0.1 with a minor engine version of 8.0.1.1.31 or later.

  Syntax

  dbms_ccl.add_ccl_digest_rule_by_hash('<Schema_name>', '<Digest>', <Concurrency_count>);

  Example

  • Add a CCL rule that matches the DIGEST value 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. When the concurrency reaches 10, subsequent statements are queued or report an error.

    CALL dbms_ccl.add_ccl_digest_rule_by_hash('', '533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a', 10);

    The value 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a is the DIGEST value calculated for SELECT * FROM t1. You can calculate this value by running the SELECT statement_digest("SELECT * FROM t1") command or obtain it from other modules.

  • Add a CCL rule for the test schema that matches the DIGEST value 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. When the concurrency reaches 10, subsequent statements are queued or report an error.

    CALL dbms_ccl.add_ccl_digest_rule_by_hash('test', '533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a', 10);

• del_ccl_rule: Deletes a CCL rule.

  Syntax

  dbms_ccl.del_ccl_rule(<Id>);

  Example

  Delete the CCL rule with ID 15.

  CALL dbms_ccl.del_ccl_rule(15);

  If the rule that you want to delete does not exist, the system reports a warning. You can run the SHOW WARNINGS; command to view the warning details. The following is an example:

  1. Delete the CCL rule with ID 100.

     CALL dbms_ccl.del_ccl_rule(100);

     The output is as follows:

     Query OK, 0 rows affected, 2 warnings (0.00 sec)

  2. Run the following command to view the warning details.

     SHOW WARNINGS;

     The output is as follows:

     +---------+------+----------------------------------------------------+
     | Level   | Code | Message                                            |
     +---------+------+----------------------------------------------------+
     | Warning | 7517 | Concurrency control rule 100 is not found in table |
     | Warning | 7517 | Concurrency control rule 100 is not found in cache |
     +---------+------+----------------------------------------------------+

  Note

  For example, the Code for PolarDB for MySQL 8.0 is 7517, the Code for PolarDB for MySQL 5.7 is 3267, and the Code for PolarDB for MySQL 5.6 is 3045.

• show_ccl_rule: Displays the enabled CCL rules in memory.

  Syntax

  dbms_ccl.show_ccl_rule();

  Example

  CALL dbms_ccl.show_ccl_rule();

  The output is as follows:

  +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
  | ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING  | 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 MATCHED, RUNNING, and WAITING parameters are described as follows:

  • MATCHED: The number of times the rule was successfully matched.

  • RUNNING: The number of threads that are concurrently running under this rule.

  • WAITING: The number of threads that are waiting to run under this rule.

• You can use the UPDATE statement to modify the ID of a CCL rule to adjust its priority.

  Syntax

  UPDATE mysql.concurrency_control SET ID = xx WHERE ID = xx;

  Example

  1. Run the following command to view the enabled CCL rules in memory.

     CALL dbms_ccl.show_ccl_rule();

     The output is as follows:

     +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
     | 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 |          |
     +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

  2. Run the following command to adjust the priority of the CCL rule with ID 17. This changes the ID to 20.

     UPDATE mysql.concurrency_control SET ID = 20 WHERE ID = 17;

  3. Run the following command to view the updated enabled CCL rules.

     CALL dbms_ccl.show_ccl_rule();

     The output is as follows:

     +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
     | ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING  | 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 |          |
     +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

• flush_ccl_rule: If you modify a CCL rule by changing the concurrency_control table, you must run this command for the change to take effect.

  Syntax

  dbms_ccl.flush_ccl_rule();

  Example

  You can adjust the priority of the destination rule by modifying the CCL rule ID in an UPDATE statement.

  UPDATE mysql.concurrency_control SET CONCURRENCY_COUNT = 15 WHERE Id = 18;

  The output is as follows:

  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0

  Run the following command for the setting to take effect.

  CALL dbms_ccl.flush_ccl_rule();

  The output is as follows:

  Query OK, 0 rows affected (0.00 sec)​