Statement Queue

When many Data Manipulation Language (DML) statements target the same row simultaneously, InnoDB row-level locking serializes them. As concurrency grows, threads queue behind locks, and throughput drops. Statement queue addresses this by routing conflicting statements into shared buckets using a hash algorithm, so they wait in the queue rather than competing for the lock. This reduces the overhead from repeated lock acquisition and release cycles.

In benchmark tests running 128 concurrent UPDATE statements on a single row, PolarDB with statement queue achieves approximately four times the throughput of standard MySQL.

When to use statement queue

Statement queue is most effective when a high volume of DML statements target the same rows — for example, a leaderboard counter, an inventory update, or a ticket reservation system where many concurrent writes hit the same primary key.

It is less effective for workloads where statements target different rows, because there is little lock contention to reduce.

If you have enabled hot row optimization by setting the hotspot variable to ON, statement queue is automatically bypassed. Use one or the other, not both.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for MySQL cluster running one of the following minor engine versions:
- PolarDB for MySQL 8.0, minor engine version 8.0.1.1.10 or later
- PolarDB for MySQL 5.7, minor engine version 5.7.1.0.6 or later
- PolarDB for MySQL 5.6, minor engine version 20200601 or later

Syntax

PolarDB for MySQL 5.6

Place the POLARDB_STATEMENT_CONCURRENT_QUEUE keyword immediately after the DML keyword (SELECT, UPDATE, INSERT, or DELETE). The keyword applies to all four DML statement types.

Queue by value

Assign statements to a bucket based on an explicit integer or string value. Statements that share the same value land in the same bucket.

Syntax:

POLARDB_STATEMENT_CONCURRENT_QUEUE [int | string]

Examples:

INSERT POLARDB_STATEMENT_CONCURRENT_QUEUE 1 INTO t VALUES(1, 1, 'xpchild');
UPDATE POLARDB_STATEMENT_CONCURRENT_QUEUE 1 t SET c=c+1 WHERE id = 1;
UPDATE POLARDB_STATEMENT_CONCURRENT_QUEUE "xpchild" t SET col1 = col1+1 WHERE id = 1;

Queue by WHERE clause field

Assign statements to a bucket based on the runtime value of a column in the WHERE clause. Statements with the same field value land in the same bucket.

Syntax:

POLARDB_STATEMENT_CONCURRENT_QUEUE [field]

Examples:

SELECT POLARDB_STATEMENT_CONCURRENT_QUEUE id * FROM t WHERE 3 = id;
UPDATE POLARDB_STATEMENT_CONCURRENT_QUEUE id t SET c=c+1 WHERE id = 1 AND name = 'xpchild';

The WHERE clause must use binary operations on original columns only. The right-hand side of each binary operation must be a literal number or string. Functions and calculations on original columns are not supported.

PolarDB for MySQL 5.7 and 8.0

Use the hint syntax /*+ ... */ placed immediately after the DML keyword (SELECT, UPDATE, INSERT, or DELETE). The hint applies to all four DML statement types.

Queue by value

Syntax:

/*+ CCL_QUEUE_VALUE([INT | STRING]) */

Examples:

UPDATE /*+ ccl_queue_value(1) */ t SET c=c+1 WHERE id = 1;
UPDATE /*+ ccl_queue_value('xpchild') */ t SET c=c+1 WHERE name = 'xpchild';

Queue by WHERE clause field

Syntax:

/*+ CCL_QUEUE_FIELD(STRING) */

Example:

UPDATE /*+ ccl_queue_field("id") */ t SET c=c+1 WHERE id = 1 AND name = 'xpchild';

The WHERE clause must use binary operations on original columns only. The right-hand side of each binary operation must be a literal number or string. Functions and calculations on original columns are not supported.

Configure queue size

Two variables control the queue's capacity. Set them based on your concurrency requirements.

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

Monitor queue status

Use the following stored procedures to inspect and reset queue state.

Check current status

CALL dbms_ccl.show_ccl_queue();

Example output:

+------+-------+-------------------+---------+---------+----------+
| 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 |
+------+-------+-------------------+---------+---------+----------+

Output fields:

Field	Description
`CONCURRENCY_COUNT`	Maximum concurrent statements allowed in this bucket
`MATCHED`	Total statements that have matched this bucket's rule
`RUNNING`	Statements currently executing in this bucket
`WAITTING`	Statements currently waiting in queue

Reset queue data

dbms_ccl.flush_ccl_queue() clears in-memory queue statistics and returns the current status.

CALL dbms_ccl.flush_ccl_queue();
CALL dbms_ccl.show_ccl_queue();

Example output after flush:

+------+-------+-------------------+---------+---------+----------+
| 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 |
+------+-------+-------------------+---------+---------+----------+

Apply queue hints without modifying application code

In PolarDB for MySQL 5.7 and 8.0, you can combine statement queue with Statement outline to inject queue hints into existing SQL statements at the server level — no application code changes required.

This integration is not available for PolarDB for MySQL 5.6, which uses a different syntax.

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

Test 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;

Target statement:

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

Procedure:

Create a statement outline that injects the queue hint online.

CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1,
    ' /*+ ccl_queue_field("id") */ ',
    "UPDATE sbtest1 SET c='xpchild' WHERE id=0");

Confirm the outline was created.

CALL dbms_outln.show_outline();

Expected output:

+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| 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` = ? |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+

Verify the hint is active by running EXPLAIN on the target statement.

EXPLAIN UPDATE sbtest1 SET c='xpchild' WHERE id=0;
SHOW WARNINGS;

The SHOW WARNINGS output should show the hint injected:

+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update /*+ CCL_QUEUE_FIELD('id') */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xpchild' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+

Check that the queue shows no activity before the test starts.
```
CALL dbms_ccl.show_ccl_queue();
```

Run 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

Check queue status during the test to verify statements are being routed correctly.

CALL dbms_ccl.show_ccl_queue();

Expected output during the test:

+------+-------+-------------------+---------+---------+----------+
| 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 |
+------+-------+-------------------+---------+---------+----------+

Cross-check against the outline hit count:

CALL dbms_outln.show_outline();

In this test run, 115,795 statements matched the outline rule, 10,996 of those hit the queue, 63 were executing concurrently, and 4 were waiting.

What's next

Statement outline — inject optimizer hints without changing application code