Help Center/ Data Admin Service/ API Reference/ APIs (in OBT)/ Cloud DBA/ Creating a Concurrency Control Rule for SQL Statements

Updated on 2024-08-05 GMT+08:00

View PDF

Creating a Concurrency Control Rule for SQL Statements

Function

This API is used to create a concurrency control rule for SQL statements. Currently, this function is supported only for MySQL databases. The restrictions are as follows:

Take select~a as an example. select and a are two keywords contained in a concurrency control rule. The keywords are separated by a tilde (~). In this example, the rule restricts the execution of only the SQL statements containing keywords select and a.
If a SQL statement matches multiple concurrency control rules, only the most recently added rule is applied.
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.
Generated keywords may be case-sensitive. Execute show variables like 'rds_sqlfilter_case_sensitive' or go to the console to check parameter settings for specifying case-sensitivity.
Concurrency control rules can only be added for primary instances.
System catalogs are not restricted, queries which do not involve data are not restricted, and the root user is not restricted in specific versions.

URI

POST /v3/{project_id}/instances/{instance_id}/sql-limit/rules

**Table 1** Path parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Project ID. To obtain this value, see Obtaining a Project ID.
instance_id	Yes	String	Instance ID.

Request Parameters

**Table 2** Request header parameters
Parameter	Mandatory	Type	Description
X-Auth-Token	Yes	String	User token.
X-Language	No	String	Language. The value can be: zh-cn en-us

**Table 3** Request body parameters
Parameter	Mandatory	Type	Description
datastore_type	Yes	String	Database type. The value can be: MySQL
sql_limit_rules	Yes	Array of CreateSqlLimitRuleOption objects	Concurrency control rules of SQL statements to be created. A maximum of 5 rules can be created at a time.
database_name	No	String	Database name.

**Table 4** CreateSqlLimitRuleOption
Parameter	Mandatory	Type	Description
sql_type	Yes	String	SQL type. The value can be: SELECT UPDATE DELETE
max_concurrency	Yes	Integer	Maximum number of concurrent SQL statements.
pattern	Yes	String	Concurrency control rules of SQL statements. Enter keywords and separate them with tildes (~), for example, select~a. Take select~a as an example. select and a are two keywords contained in a concurrency control rule. The keywords are separated by a tilde (~). In this example, the rule restricts the execution of only the SQL statements containing keywords select and a.
max_waiting	No	Integer	Maximum waiting time.
his_sql_limit_switch	No	Boolean	Concurrency control for historical SQL statements. This parameter takes effect only for the current SQL concurrency control rule. When this parameter is set to ON, existing sessions that match the SQL concurrency control rule will be killed.

Response Parameters

Status code: 400

**Table 5** Response body parameters
Parameter	Type	Description
error_code	String	Error code. Minimum length: 8 Maximum length: 36
error_msg	String	Error message. Minimum length: 2 Maximum length: 512

Status code: 500

**Table 6** Response body parameters
Parameter	Type	Description
error_code	String	Error code. Minimum length: 8 Maximum length: 36
error_msg	String	Error message. Minimum length: 2 Maximum length: 512

Example Requests

Creating a concurrency control rule of SQL statements

POST https://das.cn-north-1.myhuaweicloud.com/v3/054c630ff780d4cc2f40c00d7f6fb21d/instances/d871e13ee1044e21a473330cd67047cbin01/sql-limit/rules

{
  "sql_limit_rules" : [ {
    "sql_type" : "SELECT",
    "pattern" : "select~t2~var",
    "max_concurrency" : 100
  }, {
    "sql_type" : "DELETE",
    "pattern" : "delete~t1~name",
    "max_concurrency" : 1
  } ],
  "datastore_type" : "MySQL"
}