Help Center/ Data Admin Service/ User Guide/ MySQL/ Intelligent O&M (New Version)/ SQL/ SQL Statement Concurrency Control
Updated on 2024-08-29 GMT+08:00
View PDF
Share

SQL Statement Concurrency Control

Concurrency Control restricts the execution of SQL statements based on specified rules when there are SQL statements that cannot be optimized timely or a resource (for example, vCPU) bottleneck occurs.

Precautions

  • 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 concurrency rules can be added for RDS for MySQL read replicas of the kernel version 5.7.38-221000 or later.
  • If a SQL statement matches multiple concurrency control 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, if one rule contains the keyword a~and~b, the system only matches xxx a>1 and b>2.
  • 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.
  • Each SQL statement concurrency control rule can contain a maximum of 128 keywords.
  • Only SELECT, UPDATE, DELETE, and INSERT statements are supported for concurrency control.
  • Empty characters before and after each keyword will be ignored, for example, spaces, \n, \r, and \t.
  • Too many keywords or rules may affect performance. Retain only required concurrency control rules.
  • Up to 100 concurrency control rules can be retained.
  • If a SQL statement has been stored in query cache, it is not subject to concurrency control.
  • SQL statement concurrency control does not limit concurrency for:
    • System tables
    • SQL statements used to query data, for example, select sleep(xxx)
    • User root in versions including 5.6, 5.7.41.230700 and earlier versions in 5.7, and 8.0.28.230701 and earlier versions in 8.0
    • Stored procedures, triggers, and functions

Procedure

  1. Log in to the DAS console.
  2. Click in the upper left corner and select a region and project.
  3. Click in the upper left corner, and under Databases, click Data Admin Service.
  4. In the navigation pane, choose Intelligent O&M > Instance List.

    Alternatively, on the Overview page, click Go to Intelligent O&M.

  5. In the upper right corner of the Instance List page, search for instances by engine type, instance name, or instance IP.

    Figure 1 Searching for instances

  6. Locate the box containing your target instance and click Details.
  7. Choose SQL > SQL Statement Concurrency Control.
  8. On the displayed page, toggle on Concurrency Control.

    Figure 2 Enabling SQL statement concurrency control

  9. In the dialog box at the top of the page, click View Versions.

    Figure 3 Viewing versions

  10. Click Add Rule. In the displayed dialog box, specify SQL Type, Keyword, and Max. Concurrency.

    Figure 4 Add Rule

    • Keyword: You can enter keywords or copy an existing SQL statement to the text box and click Generate Keyword.
      • Keywords generated from an original SQL statement are only for reference.
      • Each SQL statement concurrency control rule can contain a maximum of 128 keywords.

      The following explains how a rule matches SQL statements based on keywords:

      For example, if you enter the keywords select~id~name~from~t3~where~age~>~27 (the keywords are separated by a tilde (~)) for a rule, the system will match them from first to last and restrict the execution of any SQL statement that contains these keywords.

    • Max. Concurrency: SQL statements that meet the specified SQL type and keywords and exceed the value of Max. Concurrency will not be executed.

      SQL statements that have been executed before a concurrency control rule is added are not counted.

      If you add a concurrency control rule and set Max. Concurrency to 0, the concurrency of the 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"

  11. Confirm the settings and click OK.

    If a SQL statement concurrency control rule is no longer needed, click Delete in the Operation column.

Parent topic: SQL