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 following matching method is used:

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

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

  Note

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

  • If a single KEYWORD is specified in the CCL rule, a match occurs if the SQL statement contains that keyword.

  • If multiple KEYWORDS are specified in the CCL rule, a match occurs only if the SQL statement contains all the specified keywords. The order of the keywords in the SQL statement does not affect the match.

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

• If the SCHEMA in a CCL rule is empty, the rule takes effect if the DIGEST value of the SQL statement matches the DIGEST value in the 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. Priority is determined in the following order. If multiple rules have the same priority level, the rule with the lower ID is matched first:

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 the concurrency_control system table to store CCL rules. This table is automatically created when the system starts. The `CREATE TABLE` 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 following table describes the parameters.

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

• 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>');

  Examples

  • Add a CCL rule for the SELECT type. If the concurrency count reaches 10, subsequent statements are queued to wait or an error is reported.

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

  • Add a CCL rule for SELECT statements that contain the keyword `key1`. If the concurrency count reaches 20, subsequent statements are queued to wait or an error is reported.

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

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

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

  • Add a CCL rule for SELECT statements with the SCHEMA set to test and the TABLE set to t. If the concurrency count for these SELECT statements reaches 10, subsequent statements are queued to wait or an error is reported.

    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 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>);

  Examples

  • Add a CCL rule that matches the SQL statement SELECT * FROM t1. If the concurrency count reaches 10, the statement is queued or an error is reported.

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

  • Add a CCL rule for the test schema that matches the SQL statement SELECT * FROM t1. If the concurrency count reaches 10, the statement is queued or an error is reported.

    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. If the concurrency count reaches 10, the statement is queued or an error is reported.

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

    Note

    If an SQL statement contains a constant, the rule matches the statement even if the constant value is 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 Concurrency Control (CCL) rule that matches a 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>);

  Examples

  • Add a CCL rule that matches the DIGEST value 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. If the concurrency count reaches 10, the statement is queued to wait or an error is reported.

    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 retrieve it from other modules.

  • Add a CCL rule for the test schema that matches the DIGEST value 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. If the concurrency count reaches 10, the statement is queued to wait or an error is reported.

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

• del_ccl_rule: Deletes a specified CCL rule.

  Syntax

  dbms_ccl.del_ccl_rule(<Id>);

  Example

  Delete the CC rule with ID 15.

  CALL dbms_ccl.del_ccl_rule(15);

  If the specified rule does not exist, the system reports a warning. You can run the SHOW WARNINGS; command to view the warning. For example:

  1. Delete the CC rule with ID 100.

     CALL dbms_ccl.del_ccl_rule(100);

     The following result is returned:

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

  2. Run the following command to view the warning.

     SHOW WARNINGS;

     The following is returned:

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

  In the preceding example, PolarDB for MySQL 8.0 has a Code of 7517, PolarDB for MySQL 5.7 has a Code of 3267, and PolarDB for MySQL 5.6 has a Code of 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 following result is returned:

  +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
  | 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 following table describes the MATCHED, RUNNING, and WAITING parameters.

  • MATCHED: The number of times the rule is matched.

  • RUNNING: The number of threads concurrently running for this rule.

  • WAITING: The number of threads waiting to run for this rule.

• You can use the UPDATE statement to change 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 following result is returned:

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

  2. Run the following command to adjust the priority of the CCL rule with ID 17 by changing its ID to 20.

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

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

     CALL dbms_ccl.show_ccl_rule();

     The following result is returned:

     +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
     | 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 directly modifying the concurrency_control table, you must run the following command to apply the change.

  Syntax

  dbms_ccl.flush_ccl_rule();

  Example

  You can adjust the priority of a destination rule using an UPDATE statement to modify its CC rule ID.

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

  The following output is returned:

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

  Run the following command to apply the setting.

  CALL dbms_ccl.flush_ccl_rule();

  The following output is returned:

  Query OK, 0 rows affected (0.00 sec)​