Statement queue-PolarDB(PolarDB)-阿里云帮助中心

When multiple sessions concurrently update the same row, InnoDB row locks force those statements to serialize — and the contention overhead grows with concurrency, degrading overall throughput. The statement queue feature in PolarDB-X Standard Edition reduces this overhead by routing statements that target the same resource into the same bucket, where they queue and execute in order rather than colliding on locks.

Prerequisites

Before you begin, ensure that you have:

A PolarDB-X instance running Standard Edition with the MySQL 8.0 engine

How it works

PolarDB-X applies a hash algorithm to a value you specify in an optimizer hint. Statements that hash to the same bucket are placed in the same queue and executed sequentially within that bucket. Statements in different buckets still run concurrently.

The following example shows how two concurrent UPDATE statements targeting the same row behave with and without a hint:

Scenario	Session 1	Session 2	Result
Without hint	`UPDATE t SET c=c+1 WHERE id=1;`	`UPDATE t SET c=c+1 WHERE id=1;`	Both acquire row locks. One session waits, retries, and creates contention. Throughput drops as concurrency increases.
With `ccl_queue_field(id)`	`UPDATE /+ ccl_queue_field(id) / t SET c=c+1 WHERE id=1;`	`UPDATE /+ ccl_queue_field(id) / t SET c=c+1 WHERE id=1;`	Both statements hash to the same bucket and execute one after the other. Contention overhead is reduced.

Variables

Two variables control the shape of the statement queue:

Variable	Description	Valid values	Default
`ccl_queue_bucket_count`	Number of buckets in the queue	1–64	4
`ccl_queue_bucket_size`	Maximum concurrent statements per bucket	1–4096	64

Optimizer hints

PolarDB-X provides two optimizer hints for statement queuing. Both use a hash algorithm to assign statements to buckets; they differ in how the hash value is determined.

ccl_queue_value

Hash on an explicit value — an integer or string literal you provide directly in the hint.

/*+ ccl_queue_value([int | string]) */

Example:

update /*+ ccl_queue_value(1) */ t set c=c+1 where id = 1;
update /*+ ccl_queue_value('xyz') */ t set c=c+1 where name = 'xyz';

ccl_queue_field

Hash on the runtime value of a field in the WHERE clause.

/*+ ccl_queue_field(string) */

Example:

update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';

Usage rules

The following rules apply to both hints. Violating them silently disables concurrency control (CCL) queuing for the affected statement.

Do place the hint immediately after update. Placing it elsewhere causes the hint to be ignored.
Do not specify more than one field in a single ccl_queue_field hint. /*+ ccl_queue_field(id name) */ is a syntax error and disables CCL for that statement.
Do not duplicate the hint. If /*+ ccl_queue_field(id) ccl_queue_field(name) */ appears, only the first hint takes effect.
Do use a field that appears in the WHERE clause. Specifying a field not in the WHERE clause produces no queuing effect.
Do use raw field references only. The WHERE clause must compare a raw field — not wrapped in a function or computation — against a number or string literal using a binary operator.

Monitor and reset the queue

Check queue status

Call dbms_ccl.show_ccl_queue() to see the current state of all buckets:

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       1 |       0 |        0 |
|    2 | QUEUE |                64 |   40744 |      65 |        6 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.01 sec)

Column	Description
`CONCURRENCY_COUNT`	Maximum concurrent statements allowed in this bucket
`MATCHED`	Total number of statements that hit the specified rules
`RUNNING`	Statements currently executing in this bucket
`WAITTING`	Statements waiting in this bucket's queue

Flush queue data

Call dbms_ccl.flush_ccl_queue() to clear in-memory queue statistics. This resets all MATCHED, RUNNING, and WAITTING counters to zero.

mysql> call dbms_ccl.flush_ccl_queue();
Query OK, 0 rows affected (0.00 sec)

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       0 |       0 |        0 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.00 sec)

Apply the hint without modifying application code

Statement queue works with the statement outline feature, which lets you inject optimizer hints into matching statements at runtime — no application code changes required.

The following example uses Sysbench with the update_non_index script to demonstrate this approach.

Test environment

Table schema:

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 MAX_ROWS=1000000;

Test statement:

UPDATE sbtest1 SET c='xyz' WHERE id=0;

Sysbench script:

./sysbench \
  --mysql-host={$ip} \
  --mysql-port={$port} \
  --mysql-db=test \
  --test=./sysbench/share/sysbench/update_non_index.lua \
  --oltp-tables-count=1 \
  --oltp_table_size=1 \
  --num-threads=128 \
  --mysql-user=u0

Test procedure

Create a statement outline that injects the ccl_queue_field hint into matching UPDATE statements.

mysql> CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1,
                                             ' /*+ ccl_queue_field(id) */ ',
                         "UPDATE sbtest1 SET c='xyz' WHERE id=0");
Query OK, 0 rows affected (0.01 sec)

Verify the outline was created.

mysql> call dbms_outln.show_outline();
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| ID   | SCHEMA | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                           | HIT  | OVERFLOW | DIGEST_TEXT                                 |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
|    1 | test   | 7b945614749e541e0600753367884acff5df7e7ee2f5fb0af5ea58897910f023 | OPTIMIZER |       |    1 |  /*+ ccl_queue_field(id) */  |    0 |        0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
1 row in set (0.00 sec)

Confirm the hint takes effect by running EXPLAIN and checking the warning.

mysql> explain UPDATE sbtest1 SET c='xyz' WHERE id=0;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | sbtest1 | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update /*+ ccl_queue_field(id) */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xyz' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The hint appears in the rewritten statement, confirming the outline is active.

Check the queue status before starting the test to establish a baseline.

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       0 |       0 |        0 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.00 sec)

Start the Sysbench test.

sysbench \
  --mysql-host={$ip} \
  --mysql-port={$port} \
  --mysql-db=test \
  --test=./sysbench/share/sysbench/update_non_index.lua \
  --oltp-tables-count=1 \
  --oltp_table_size=1 \
  --num-threads=128 \
  --mysql-user=u0

Verify the test result.

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |   10996 |      63 |        4 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.03 sec)

mysql> call dbms_outln.show_outline();
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
| ID   | SCHEMA | DIGEST    | TYPE      | SCOPE | POS  | HINT                           | HIT    | OVERFLOW | DIGEST_TEXT                                 |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
|    1 | test   | xxxxxxxxx | OPTIMIZER |       |    1 |  /*+ ccl_queue_field(id) */  | 115795 |        0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
1 row in set (0.00 sec)

The results show that 115,795 statements matched the outline rule and received the hint, 10,996 of those were routed to the statement queue, 63 were executing concurrently, and 4 were waiting.