SQL Statement Concurrency Control

Precautions

• This function is available only to the GaussDB(for MySQL) instances that meet the following requirements:
  • 2.0.28.40 > kernel version ≥ 2.0.28.15
  • Kernel version ≥ 2.0.29.1

• Each SQL statement concurrency control rule can contain a maximum of 128 keywords.
• The keyword cannot contain \t, \r, or \n and cannot be empty or a backslash (\).
• Spaces at the start, end of or in the middle of a keyword are ignored.
• The concurrency control rule cannot end with a tilde (~).
• 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.
• Each SQL concurrency control rule applies to only the SQL statements that your database received after the rule is created.
• If different rules are created for the primary node and read replicas of an instance, the rules still apply to the node and read replicas after their roles are switched over.
• If a SQL statement matches multiple concurrency control rules, only the latest rule takes effect.
• SQL statements that have been executed before a concurrency control rule is added are not counted.
• The total length of all rules for SELECT, UPDATE, or DELETE statements and the Concurrency value in each rule cannot exceed 1024 bytes.
• If you add many SQL statement concurrency control rules for your instance, the execution of SELECT, UPDATE, or DELETE statements will slow down.
• SQL concurrency control rules are applied based on prefix match. For example, if the concurrency control rule is SELECT~COUNT~t1, SQL statements SELECT COUNT(*) FROM t1 and SELECT COUNT(*) FROM t1 LIMIT 1 will both be intercepted.
• After concurrency control is triggered, an execution error is reported on the service side, indicating that query execution was interrupted. The error code is ERROR 1317 (70100).
• SQL statement concurrency control does not limit concurrency for:
  • System tables
  • SQL statements used to query data, for example, select sleep(xxx)
  • The root account for kernel versions earlier than 2.0.45.230900
  • SQL statements in 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 instance that you want to control concurrency of SQL statements for and click Details.
7. Choose SQL > SQL Statement Concurrency Control.
8. On the displayed page, enable SQL Statement Concurrency Control.
9. 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 2 Adding a SQL statement 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.

     

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

     You can enter one or more keywords. Separate keywords with tildes (~). For example, if you enter keyword SELECT~a>1, this rule applies only to the SQL statements that contain SELECT and a>1, with SELECT placed before a>1.

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

10. Confirm the settings and click OK.

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