PolarDB:Concurrency control

Your PolarDB cluster runs one of the following versions:

• PolarDB for MySQL 8.0.

• PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.6 or later.

  Note

  If your cluster runs PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.27 or later, you can use the CCL feature together with the thread pool feature in the cluster.

• PolarDB for MySQL 5.6.

Methods to match SQL statements against CCL rules

• If the DIGEST value in the CCL rule that you specify is empty, the following matching methods are used:

  • If the DIGEST value is empty but the TYPE, SCHEMA, and TABLE values are not empty, the TYPE, SCHEMA, and TABLE values in the SQL statement must match those in the CCL rule. Otherwise, the CCL rule is invalid.

  • If the DIGEST value is empty and the SCHEMA and TABLE values are empty, but the TYPE value is not empty, the TYPE value in the SQL statement must match that in the CCL rule. Otherwise, the CCL rule is invalid.

  Note

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

  • If a single keyword is specified in the CCL rule and the keyword is included in the SQL statement, the SQL statement and CCL rule is matched.

  • If multiple keywords are specified in the CCL rule and all keywords are included in the SQL statement, the SQL statement and CCL rule is matched. The multiple keywords in the CCL rule are not matched in order.

• If the DIGEST value in the CCL rule is not empty, the SCHEMA and DIGEST values of the SQL statement must match those in the CCL rule. Otherwise, the CCL rule is invalid.

• If the SCHEMA value in the CCL rule is empty and the DIGEST value in the SQL statement matches that in the CCL rule, the CCL rule is valid.

A single SQL statement can match only a single CCL rule. If a single SQL statement matches multiple CCL rules, the CCL rule with the highest priority is selected. If the priority is the same, the CCL rule with the smallest ID is selected. The priority is determined by the following dimension values in descending order:

1. DIGEST value

2. TYPE, SCHEMA, and TABLE values

3. TYPE value

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

PolarDB uses a system table named concurrency_control to store CCL rules. The table is automatically created when the system is started. The following statement is used to create the concurrency_control table:

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.

PolarDB provides the following six stored procedures in the DBMS_CCL package to allow you to manage CCL rules in an efficient manner:

• add_ccl_rule: adds CCL rules that match SQL statements based on the TYPE, SCHEMA, TABLE, and KEYWORD values.

  Syntax

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

  Examples

  • Add a CCL rule with the TYPE dimension set to SELECT. The maximum number of concurrent threads is 10. When this number is reached, the SQL statement waits in a queue or an error is returned.

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

  • Add a CCL rule with the TYPE dimension set to SELECT and the KEYWORD dimension set to key1. The maximum number of concurrent threads is 20. When this number is reached, the SQL statement waits in a queue or an error is returned.

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

  • Add a CCL rule with the TYPE dimension set to SELECT and the KEYWORD dimension set to key1, key2, and key3. The maximum number of concurrent threads is 20. When this number is reached, the SQL statement waits in a queue or an error is returned. The multiple keywords in the CCL rule are not matched in order.

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

  • Add a CCL rule with the TYPE dimension set to SELECT, the SCHEMA dimension set to test, and TABLE set to t. The maximum number of concurrent threads is 10. When this number is reached, the SQL statement waits in a queue or an error is returned.

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

• add_ccl_digest_rule: adds a CCL rule that matches the DIGEST value.

  Note

  The add_ccl_digest_rule stored procedure is supported only if the cluster runs one of the following versions:

  • PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.31 or later.

  • PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.12 or later.

  Syntax

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

  Examples

  • Add a CCL rule with the DIGEST dimension set to SELECT * FROM t1. The maximum number of concurrent threads is 10. When this number is reached, the SQL statement waits in a queue or an error is returned.

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

  • Add a CCL rule with the SCHEMA dimension set to test and the DIGEST dimension set to SELECT * FROM t1. The maximum number of concurrent threads is 10. When this number is reached, the SQL statement waits in a queue or an error is returned.

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

  • Add a CCL rule with the DIGEST dimension set to SELECT * FROM t1 WHERE col1=1. The maximum number of concurrent threads is 10. When this number is reached, the SQL statement waits in a queue or an error is returned.

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

    Note

    If the SQL statement contains a constant, it is matched even if the constant value is different. For example, the preceding CCL rule can match the SELECT * FROM t1 WHERE col1 = 2 statement.

• add_ccl_digest_rule_by_hash: adds a CCL rule that matches the DIGEST value and uses the calculated DIGEST value instead of the SQL statement.

  Note

  The add_ccl_digest_rule_by_hash stored procedure is supported only if the cluster runs PolarDB for MySQL 8.0.1 whose revision version is 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 of 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. The maximum number of concurrent threads is 10. When this number is reached, the SQL statement waits in a queue or an error is returned.

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

    533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a is the DIGEST value obtained by computing SELECT * FROM t1. You can execute the SELECT statement_digest("SELECT * FROM t1") statement to calculate the DIGEST value or obtain the DIGEST value from other modules.

  • Add a CCL rule with the SCHEMA dimension set to test and that matches the DIGEST value of 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. The maximum number of concurrent threads is 10. When this number is reached, the SQL statement waits in a queue 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>);

  Examples

  Delete the CCL rule whose ID is 15.

  CALL dbms_ccl.del_ccl_rule(15);

  If the CCL rule does not exist, a warning message is returned. You can execute the SHOW WARNINGS; statement to view warning messages. Example:

  1. Delete the CCL rule whose ID is 100.

     CALL dbms_ccl.del_ccl_rule(100);

     The following output is returned:

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

  2. Execute the following statement to view warning messages:

     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

  The Code value for a PolarDB for MySQL 8.0 cluster is 7517, the Code value for a PolarDB for MySQL 5.7 cluster is 3267, and the Code value for a PolarDB for MySQL 5.6 cluster is 3045.

• show_ccl_rule: views the enabled CCL rules in memory.

  Syntax

  dbms_ccl.show_ccl_rule();

  Examples

  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 section describes the MATCHED, RUNNING, and WAITTING parameters:

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

  • RUNNING: The number of concurrent threads for the rule.

  • WAITTING: The number of pending threads for this rule.

• You can execute the UPDATE statement to modify the ID of a rule to change the priority.

  Syntax

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

  Examples

  1. Execute the following statement to view enabled rules in the 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. Execute the following statement to modify the ID of the CCL rule from 17 to 20:

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

  3. Execute the following statement to view the enabled CCL rule whose ID is modified:

     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: validates a CCL rule after you modify the rule in the concurrency_control table.

  Syntax

  dbms_ccl.flush_ccl_rule();

  Examples

  Execute the UPDATE statement to modify the ID of a rule to change the priority.

  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

  Execute the following statement to validate the modified CCL rule:

  CALL dbms_ccl.flush_ccl_rule();

  The following output is returned:

  Query OK, 0 rows affected (0.00 sec)