PolarDB:Concurrency control

Your PolarDB cluster must run one of the following versions:

• PolarDB for MySQL 8.0.

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

  Note

  If your cluster runs PolarDB for MySQL 5.7 with a revision version of 5.7.1.0.27 or later, the CCL feature is compatible with the thread pool feature.

• PolarDB for MySQL 5.6.

Matching logic

• If the DIGEST value in a CCL rule is not specified:

  • If the TYPE, SCHEMA, and TABLE values are specified, an SQL statement must match all three dimensions for the rule to apply.

  • If only the TYPE value is specified, an SQL statement must match the TYPE dimension for the rule to apply.

  Note

  If the KEYWORD dimension is also specified, additional checks are performed:

  • If a single keyword is specified, the SQL statement must contain that keyword for the match to be successful.

  • If multiple keywords are specified, the SQL statement must contain all specified keywords for the match to be successful. The order of the keywords in the SQL statement does not matter.

• If the DIGEST value in a CCL rule is specified, the DIGEST value of the SQL statement must match the DIGEST value in the rule. If the SCHEMA value is also specified in the rule, the SCHEMA value of the statement must also match.

• If the SCHEMA value in a CCL rule is not specified, an SQL statement must match only the DIGEST value for the rule to apply.

An SQL statement can match only one CCL rule. If a statement matches multiple rules, PolarDB applies the rule with the highest priority. If multiple rules have the same priority, PolarDB uses the rule with the smallest ID. The priority of a rule is based on its matching dimensions, in the following descending order:

1. DIGEST

2. TYPE, SCHEMA, and TABLE

3. TYPE only

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

PolarDB stores CCL rules in the system table concurrency_control. The system automatically creates this table at startup, and you do not need to create it manually. The following CREATE TABLE statement shows the table's structure:

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 in the concurrency_control table.

PolarDB provides the following six stored procedures in the DBMS_CCL package for managing CCL rules:

• add_ccl_rule: Adds a CCL rule based on 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 SELECT statements with a concurrency count of 10. When this limit is reached, the system queues or refuses subsequent statements.

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

  • Add a CCL rule for SELECT statements that contain the keyword key1, with a concurrency count of 20.

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

  • Add a CCL rule for SELECT statements that contain the keywords key1, key2, and key3, with a concurrency count of 20. The keywords can appear in any order.

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

  • Add a CCL rule with TYPE as SELECT, SCHEMA as test, and TABLE as t. When the concurrency count of SELECT statements reaches 10, the statements are queued or an error is reported.

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

• add_ccl_digest_rule: Adds a CCL rule based on a statement digest.

  Note

  The following database engine versions support the add_ccl_digest_rule stored procedure:

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

  • PolarDB for MySQL 8.0.2 with a revision 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 to queue requests or report an error when the concurrency count reaches 10.

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

  • Add a CCL rule for the schema test and for SQL statements that match SELECT * FROM t1 to queue requests or report an error when the concurrency count is 10.

    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 to queue requests or report an error when the concurrency count is 10.

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

    Note

    If an SQL statement contains a constant, a match occurs 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 based on a pre-calculated digest hash.

  Note

  The add_ccl_digest_rule_by_hash stored procedure is supported only if the PolarDB for MySQL engine version is 8.0.1 and the revision version is 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 for SQL statements whose DIGEST value matches 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a to queue requests or report an error when the concurrency count is 10.

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

    In this case, 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a is the DIGEST value calculated for SELECT * FROM t1. You can use the SELECT statement_digest("SELECT * FROM t1") command to calculate this value or obtain it from other modules.

  • Add a CCL rule for SQL statements where the SCHEMA is test and the DIGEST value of the SQL statement matches 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. When the concurrency count reaches 10, requests are queued or an error is returned.

    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 an ID of 15.

  CALL dbms_ccl.del_ccl_rule(15);

  If the rule to be deleted does not exist, the system reports a warning. You can use the SHOW WARNINGS; command to view the warning content. The following is an example:

  1. Delete the CCL rule with an ID of 100.

     CALL dbms_ccl.del_ccl_rule(100);

     The following output is returned:

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

  2. Run the following command to view the warning message:

     SHOW WARNINGS;

     The following output 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 version 8.0 has a Code of 7517, PolarDB for MySQL version 5.7 has a Code of 3267, and PolarDB for MySQL version 5.6 has a Code of 3045.

• show_ccl_rule: Displays the enabled CCL rules currently in memory.

  Syntax

  dbms_ccl.show_ccl_rule();

  Example

  CALL dbms_ccl.show_ccl_rule();

  The following output is returned:

  +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
  | 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 describes the MATCHED, RUNNING, and WAITTING columns.

  • MATCHED: The total number of times the rule has matched a statement.

  • RUNNING: The number of threads currently running under this rule.

  • WAITTING: The number of threads currently waiting to run under this rule.

• You can use an UPDATE statement to modify a rule's ID, which changes 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 output is returned:

     +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
     | 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 change 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 modified rules:

     CALL dbms_ccl.show_ccl_rule();

     The following output is returned:

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

• flush_ccl_rule: If you modify a CCL rule by modifying the content in the concurrency_control table, you also need to use the following command to make the rule take effect.

  Syntax

  dbms_ccl.flush_ccl_rule();

  Example

  Use an UPDATE statement to change a rule's concurrency count.

  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 change:

  CALL dbms_ccl.flush_ccl_rule();

  The following output is returned:

  Query OK, 0 rows affected (0.00 sec)​