Help Center> Data Admin Service> User Guide> Intelligent O&M> MySQL DB Instances> SQL> SQL Statement Concurrency Control
Updated on 2023-03-07 GMT+08:00
View PDF

SQL Statement Concurrency Control

Scenarios

SQL statement concurrency control aims to keep MySQL instances running stably even if there is a sudden increase in concurrent SQL statements.

Prerequisites

  • The kernel version of primary RDS for MySQL instances must be 5.6.50.3, 5.7.31.4, 8.0.25.1, or later.
  • The kernel version of RDS for MySQL read replicas must be 5.6.51.6, 5.7.37.1, 8.0.25.1, or later.
  • Separate SQL statement concurrency rules can be added for RDS for MySQL read replicas of the kernel version 5.7.38-221000 or later.

Precautions

  • If a SQL statement matches multiple concurrency rules, only the latest rule takes effect.
  • Keywords in a concurrency control rule are sorted in a specific order, and the system will match them from first to last. For example, "a" and "b" are two keywords contained in a concurrency control rule and in the order "xxx a>1 and b>2". The keywords are separated by a tidle (~). In this example, the system matches only "xxx a>1~b>2", rather than "xxx b>2~a>1".
  • If the replication delay is too long, adding or deleting a concurrency control rule for a read replica does not take effect immediately.
  • If you replicate data from an instance to a third-party instance based on binlogs, deleting or adding SQL statement concurrency rules from or to the instance will interrupt the replication process.
  • SQL statement concurrency control does not limit concurrency for:
    • System tables
    • SQL statements used to query data, for example, select sleep(xxx)
    • Certain RDS for MySQL versions when the root account is used
    • SQL statement concurrency control is not available to stored procedures, triggers, and functions.
  • Empty characters before and after each keyword will be ignored, for example, spaces, \n, \r, and \t.

Procedure

  1. Log in to the DAS console.
  2. On the Overview page, click Go to Intelligent O&M.
  3. Select the required instance and click Details.
  4. Choose SQL > SQL Statement Concurrency Control.
  5. On the displayed page, enable SQL Statement Concurrency Control.
  6. Click View supported versions.
  7. Click Add Concurrency Control Rule. In the displayed dialog box, select the SQL statement type, enter keywords, and specify the maximum number of concurrent SQL statements. You can enter keywords or copy an existing SQL statement to the text box and click Generate Keyword.

    Keyword: For example, if you set Keyword to select~id~name~from~t3~where~age~>~27, the created concurrency control rule takes effect for any SQL statement that contains keywords select, id, name, from, t3, where, age, >, and 27 that are separated by ~ and in the preset sequence.

    Max. Concurrency: Maximum concurrent statements meeting the same rule. If the current statements exceed the upper limit, they will not be executed.

    If you add a concurrency control rule and set Max. Concurrency to 0, the concurrency of following SQL statements will be controlled:

    "select id, name from t3 where age > 27"

    "select id, name, age from t3 where age > 27"

    "select id, name from t3 where age > 5 and id < 27"

    But the concurrency of the following SQL statement will not be controlled:

    "select name, age from t3 where age > 27"

  8. Confirm the settings and click OK.
  9. If a rule is no longer needed, locate the rule, click Delete in the Operation column, and click Yes.
Parent topic: SQL