SQL Statement Concurrency Control

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.
• Currently, SQL Statement Concurrency Control supports only keywords SELECT, UPDATE, and DELETE.
• 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 management 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. In the upper right corner of the page, click Go to Old Edition.
   Figure 2 Accessing Intelligent O&M of the old version
   

8. Choose SQL > SQL Statement Concurrency Control.
9. On the displayed page, enable SQL Statement Concurrency Control.
   Figure 3 Enabling SQL statement concurrency control
   

10. Click View Supported Versions.
    Figure 4 Viewing version constraints
    

11. Click Add Concurrency Control Rule. In the displayed dialog box, select a SQL statement type, enter keywords, and specify the maximum number of concurrent SQL statements.
    Figure 5 Adding a concurrency control 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.
      Figure 6 Adding a concurrency control rule
      

      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"

12. Confirm the settings and click OK.

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