All Products
Search
Document Center

PolarDB:Concurrency control

Last Updated:Mar 08, 2024

Alibaba Cloud provides the concurrency control (CCL) feature to ensure the stability of PolarDB clusters and address 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

Your PolarDB cluster uses one of the following versions:

  • PolarDB for MySQL 8.0.

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

    Note

    If the version of the cluster is PolarDB for MySQL 5.7 whose minor engine version is 5.7.1.0.6 or later, the CCL feature cannot be used together with the thread pool feature. For more information about the thread pool feature, see Thread Pool.

  • PolarDB for MySQL 5.6.

Usage notes

  • CCL operations do not generate binary logs. 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.

  • The CCL feature provides a timeout mechanism that resolves transaction deadlocks caused by DML statements. The pending threads also respond to the transaction timeout and kill threads to prevent deadlocks.

Features

Dimensions

Five dimensions are defined in the CCL feature. SQL statements are matched against CCL rules based on these dimensions.

Dimension

Description

TYPE

The SQL statement type, such as SELECT, UPDATE, INSERT, and DELETE.

SCHEMA

The name of the database for the SQL statement.

TABLE

The name of the table or view for the SQL statement.

KEYWORD

The keyword in the SQL statement. You can specify multiple keywords in a CCL rule. Separate multiple keywords with semicolons (;).

DIGEST

The string that is returned in computing the SQL statement hash value. For more information, see STATEMENT_DIGEST().

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.

Matching order

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

Parameters

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

Parameter

Description

loose_ccl_mode

The action for the SQL statement when the maximum number of concurrent threads is reached. Valid values:

  • WAIT (default): The SQL statement waits in a queue until other SQL statements are executed.

  • REFUSE: An error is returned.

Note

This parameter is valid only for PolarDB for MySQL 8.0. For PolarDB for MySQL 5.6 and 5.7 clusters, the SQL statement waits when the maximum number of concurrent threads is reached.

loose_ccl_max_waiting_count

The maximum number of queued SQL statements that match a single CCL rule when the loose_ccl_mode parameter is set to WAIT.

Valid values: 0 to 65536. Default value: 0.

Note

This parameter is valid only for PolarDB for MySQL 5.7 and 8.0.

CCL rule table

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 that you can configure.

Parameter

Description

Id

The ID of the CCL rule.

Type

The SQL statement type, such as SELECT, UPDATE, INSERT, and DELETE.

Schema_name

The database name.

Table_name

The name of the table in the database.

Concurrency_count

The number of concurrent threads.

Note

To enable the SQL blacklist feature and prevent the execution of such queries, you can set Concurrency_count to 0.

Keywords

The keywords. Separate multiple keywords with semicolons (;).

State

Specifies whether to enable the rule. Valid values:

  • Y (default): enables the rule.

  • N: disables the rule.

Ordered

Specifies whether to match multiple keywords in the Keywords parameter in order. Valid values:

  • N (default): do not match multiple keywords in the Keywords parameter in order.

  • Y: matches multiple keywords in the Keywords parameter in order.

Digest

The 64-byte hash string obtained from the Digest_text parameter. For more information, see STATEMENT_DIGEST().

Digest_text

The normalized statement digest of the SQL statement.

Extra

Additional information.

Manage CCL rules

PolarDB provides six stored procedures in the DBMS_CCL package to manage CCL rules in an efficient manner. You can take note of the following descriptions.

  • 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 for a PolarDB for MySQL 8.0.1 cluster whose minor engine version is 8.0.1.1.31 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 on a PolarDB for MySQL 8.0.1 cluster whose minor engine 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, the warning message is returned. You can execute the SHOW WARNINGS; statement to view warning messages. Examples:

    1. Delete the CCL rule whose ID is 100.

      CALL dbms_ccl.del_ccl_rule(100);

      The following result 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 result 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, the Code for a PolarDB for MySQL 8.0 cluster is 7517, the Code for a PolarDB for MySQL 5.7 cluster is 3267, and the Code 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 result 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 result 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 result 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 result 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 result is returned:

    Query OK, 0 rows affected (0.00 sec)‚Äč

Test the feature

  1. Create CCL rules based on the following dimensions.

    CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');  // Set the TYPE dimension to SELECT, the SCHEMA dimension to test, the TABLE dimension to sbtest1, and the maximum number of concurrent threads to 3. 
    call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');       // Set the TYPE dimension to SELECT, the KEYWORD dimension to sbtest2, and the maximum number of concurrent threads to 2. 
    CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');            // Set the TYPE dimension to SELECT and the maximum number of concurrent threads to 2.
  2. Use sysbench to verify the feature in the following scenarios:

    • 64 threads

    • 4 tables

    • select.lua

  3. Execute the following statement to query the number of concurrent threads for the rules:

    CALL dbms_ccl.show_ccl_rule();

    The following result is returned:

    +------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
    | 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 numbers displayed in the RUNNING column are the same as those specified when you create the rules.