Concurrency Control

Constraints

• The rule you are creating will be applied only to the current instance.
• If a SQL statement matches multiple concurrency control rules, only the most recently added rule is applied.
• SQL statements that have been executed before a concurrency control rule is added are not counted.
• If the replication delay is too long, adding or deleting a concurrency control rule for a read replica does not take effect immediately.
• You are advised to upgrade the minor kernel version to the latest version.
• Too many concurrency control rules affect the database performance. Delete unnecessary rules after using them.
• This function controls how many statements can run at the same time. However, it does not limit concurrency for:
  • System tables
  • Queries where no database data is involved, such as SELECT sleep(xxx);
  • Account root
  • 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 a project.
3. Click in the upper left corner of the page and choose Databases > Relational Database Service.
4. On the Instances page, click the target DB instance name.
5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
6. Choose SQL Explorer > Concurrency Control.
7. Click Add Rule. Configure the parameters listed in Table 1.
   Figure 1 Adding a rule
   

   Table 1 Parameter description

   Parameter	Description
   SQL Type	There are three options: SELECT, UPDATE, and DELETE.
   Keyword	A maximum of 128 keywords (case-insensitive) are supported. You can specify keywords in either of the following ways: Enter keywords: 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. Generate keywords from a SQL statement: You can enter a SQL statement and then click Generate Keyword. The generated keywords are for reference only. Exercise caution when using them. SQL statements match the keywords from first to last. For example, if one rule contains the keyword a~and~b, the statement xxx a>1 and b>2 can match the keyword, but xxx b>2 and a>1 cannot.
   Max. Concurrency	If the number of concurrent SQL statements matching the keyword exceeds this limit, the SQL statements will not be executed. The value ranges from 0 to 1,000,000,000.
   Kill existing sessions that match this rule	Selecting this option will not kill the connection sessions of user root.

8. Confirm the settings and click OK.
9. Toggle on the concurrency control switch .
   

   Concurrency control rules take effect only after concurrency control is enabled.

Related Operations

To delete a concurrency control rule, locate it in the rule list and click Delete in the Operation column. In the displayed dialog box, click OK.

Figure 2 Deleting a rule