Statement concurrency control

How it works

CCL matches incoming SQL statements against rules you define. Each rule specifies a concurrency threshold for a given SQL pattern. When the threshold is reached, additional matching statements wait in a queue. A statement leaves the queue when a running statement finishes or the wait timeout expires.

Rules match statements on up to three dimensions, which you can combine freely:

• SQL command type: SELECT, UPDATE, INSERT, or DELETE

• Target object: a specific database (schema) and/or table

• Keywords: one or more substrings that must appear in the statement

Scope and behavior

• Instance scope: CCL rules are instance-local. Changes do not replicate to secondary databases, read-only instances, or disaster recovery instances, and no logs are generated.

• Deadlock prevention: Statements waiting in the queue respond to transaction timeouts and KILL commands, preventing DML statements from holding transaction locks indefinitely.

• Rule priority: If a statement matches multiple rules, the rule with the highest ID applies.

Configure global parameters

The following global parameters control CCL behavior across all rules.

Manage CCL rules

PolarDB-X stores CCL rules in the mysql.concurrency_control system table and exposes them through the DBMS_CCL built-in package.

CALL dbms_ccl.add_ccl_rule('<Type>', '<Schema>', '<Table>', <Count>, '<Keywords>');

CALL dbms_ccl.add_ccl_rule_returning('<Type>', '<Schema>', '<Table>', <Count>, '<Keywords>');

-- Cap concurrent SELECT operations on test.sbtest1 at 3.
CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');

-- Cap concurrent SELECT operations containing the keyword 'key1' at 20.
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');

-- Delete the rule with ID 15.
CALL dbms_ccl.del_ccl_rule(15);

+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 7536 | Concurrency control rule 15 is not found in table |
| Warning | 7536 | Concurrency control rule 15 is not found in cache |
+---------+------+---------------------------------------------------+

CALL dbms_ccl.flush_ccl_rule();

Monitor CCL rules

Use show_ccl_rule() to view all active rules and their real-time operational state. This lets you answer questions such as: Which rules are currently throttling traffic? How many statements are waiting? What is the peak concurrency recorded for a rule?

CALL dbms_ccl.show_ccl_rule();

Sample output:

+------+--------+--------+---------+-------+-------+-------------------+---------+---------+---------+------------------+------------+----------+
| ID   | TYPE   | SCHEMA | TABLE   | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | CONCURRENCY_PEEK | KILLED_NUM | KEYWORDS |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+---------+------------------+------------+----------+
|    1 | SELECT | test   | sbtest1 | Y     | N     |                 3 |       0 |       0 |       0 |                0 |          0 |          |
|    2 | SELECT |        |         | Y     | N     |                20 |       0 |       0 |       0 |                0 |          0 | key1     |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+---------+------------------+------------+----------+

Use RUNNING and WAITING to confirm a rule is actively throttling traffic. Use CONCURRENCY_PEEK to understand historical load and tune your thresholds.

End-to-end example

The following example walks through adding a rule, verifying it is active, and checking its runtime statistics.

1. Add a rule to cap concurrent SELECT operations on `test.sbtest1` at 3:

CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');

2. Confirm the rule is loaded and active:

CALL dbms_ccl.show_ccl_rule();

Look for your rule in the output. STATE = Y means the rule is active. RUNNING and WAITING show the current concurrency state.

3. Run queries against `test.sbtest1` and observe throttling:

When more than 3 matching SELECT statements run simultaneously, additional statements queue up. The WAITING counter in show_ccl_rule() increases. After a running statement finishes, the next queued statement executes and WAITING decreases.

4. Remove the rule when no longer needed:

CALL dbms_ccl.del_ccl_rule(<ID>);

Replace <ID> with the ID returned in step 2.