SQL Statement Concurrency Control

Precautions

• Concurrency control keeps TaurusDB instances stable regardless of how many SQL statements are concurrently submitted.
• The following TaurusDB kernel versions are supported:
  • 2.0.28.15 and later and earlier than 2.0.28.40
  • 2.0.29.1 and later
  • Concurrency control of INSERT in 2.0.54.240600 and later

• A single concurrency control rule can contain 128 keywords. Single backslashes (\) or single null characters (' ') cannot be used as keywords.
• Spaces at the beginning and end of a keyword and special null characters (such as \'t', \'r', and \'n') will be 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. If a rule contains a~and~b, the system only matches xxx a>1 and b>2.
• If a SQL statement matches multiple concurrency control rules, only the latest rule takes effect.
• 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.
• The length of all rules and concurrent queries of a single SQL type (SELECT, UPDATE, DELETE, or INSERT) cannot exceed 4,000 bytes. The length of a single rule cannot exceed 1,000 bytes.
• If you add many concurrency control rules, execution of SELECT, UPDATE, INSERT, or DELETE statements will slow down.
• Concurrency control rules are applied based on SQL statement prefixes. For example, if a concurrency control rule is SELECT~COUNT~t1, SELECT COUNT(*) FROM t1 and SELECT COUNT(*) FROM t1 LIMIT 1 will both be intercepted.
• If concurrency control is triggered, error "ERROR 1317 (70100): Query execution was interrupted" is reported.
• Concurrency control does not limit:
  • System tables
  • SQL statements not used to query data, for example, select sleep(xxx)
  • The root account for kernel versions earlier than 2.0.45.230900
  • SQL statements stored in the query cache

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 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, toggle on Concurrency Control.
   Figure 2 Enabling 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 3 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.

     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.