Hotspot updates
When thousands of concurrent transactions update the same row — such as decrementing a single inventory counter during a flash sale — each transaction must wait for the previous one to acquire the lock, update, and release. This serialization caps throughput at a few hundred transactions per second (TPS) regardless of thread count. PolarDB-X resolves this with a kernel-level group commit mechanism that batches concurrent updates to the same row into a single lock acquisition, scaling TPS up to 37x at 512 concurrent threads.
How it works
The standard update path follows this sequence:
Lock the row
Update the row
Unlock the row
Each concurrent transaction repeats this cycle serially, creating contention.
With group commit enabled, the sequence becomes:
Lock the row
Batch multiple pending updates into a single group update
Unlock the row
A designated leader thread collects concurrent updates targeting the same row, applies them as one operation, and commits the batch. Follower threads receive their results without individually acquiring the lock.
Limitations
Review these constraints before setting up hotspot updates:
PolarDB-X Enterprise Edition MySQL 5.7 only. This feature is not available in other editions or versions.
Single shard only. The inventory hint optimizes updates within a single database shard. It does not support distributed scenarios where data spans multiple shards or databases.
Primary key or unique key required. The
WHEREclause must use an equality condition on a primary key or unique key.No global indexes. Tables with global indexes are not supported. Local indexes are allowed.
XA transaction policy required. Set the transaction type to XA before using inventory hints. Changing the transaction policy may cause unexpected results.
Shared ReadView must be disabled. Disabling shared ReadView may slow down some queries.
Prerequisites
Before you begin, ensure that you have:
A PolarDB-X Enterprise Edition MySQL 5.7 instance
A privileged account on the instance
Enable hotspot updates
SET GLOBAL changes take effect only for new sessions created after the configuration. Reconnect after running these commands.
Step 1: Enable hotspot features
Use a privileged account to enable hotspot-related features:
SET GLOBAL HOTSPOT=ON;
SET GLOBAL HOTSPOT_LOCK_TYPE=ON;Step 2: Disable shared ReadView
Check whether shared ReadView is enabled:
SHOW VARIABLES LIKE '%SHARE_READ_VIEW%';If enabled, disable it globally:
SET GLOBAL SHARE_READ_VIEW = FALSE;Disabling shared ReadView may slow down some queries.
Step 3: Set the transaction policy to XA
Set the transaction policy at the global level:
SET GLOBAL TRANSACTION_POLICY = 'XA';
SET GLOBAL ENABLE_XA_TSO = FALSE;Or set it at the session level before each use:
SET SESSION TRANSACTION_POLICY = 'XA';
SET SESSION ENABLE_XA_TSO = FALSE;Verify the setting:
SHOW VARIABLES LIKE 'TRANSACTION_POLICY';If the transaction policy is not XA, switch to XA before using hotspot updates. Setting it at the session level before each use is also valid. This change may cause unexpected results. Proceed with caution.
Syntax
Add an inventory hint to the UPDATE statement and place it as the last statement in the transaction:
BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(number)*/ table_reference
SET assignment_list
[WHERE where_condition];
COMMIT | ROLLBACK;The WHERE condition must be an equality condition on the primary key or unique key. Tables with global indexes are not supported (local indexes are allowed).
Hint parameters
All three parameters apply within the same hint comment. commit_on_success is required; the other two are optional and can be combined.
|
Parameter |
Required |
Description |
|
|
Yes |
Auto-commits the transaction (including previous uncommitted statements) if the update succeeds. |
|
|
No |
Auto-rolls back the transaction (including previous uncommitted statements) if the update fails. |
|
|
No |
Checks the number of updated rows. If the actual count does not match |
Examples
Create test tables
CREATE TABLE table_test (
id INT AUTO_INCREMENT PRIMARY KEY,
c INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE table_test_2 (
id INT AUTO_INCREMENT PRIMARY KEY,
c INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Auto-commit on success
Add commit_on_success to automatically commit the transaction when the update succeeds:
BEGIN;
UPDATE /*+ commit_on_success*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT;Choose COMMIT or ROLLBACK based on the update status.
Auto-rollback on failure
Add rollback_on_fail to automatically roll back if the update fails:
BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT;Validate the affected row count
Add target_affect_row(1) to fail the update if the number of affected rows does not match:
BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1)*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT;DML operations before the inventory hint
Run other DML operations before the inventory hint UPDATE:
BEGIN;
INSERT INTO table_test_2 VALUES (1,1);
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1)*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT;Verify group commit status
After enabling hotspot updates, verify that group commit is active.
Check status variables
SHOW GLOBAL STATUS LIKE "%Group_update%";Sample output:
+---------------------------------------+--------+
| Variable_name | Value |
+---------------------------------------+--------+
| Group_update_fail_count | 54 |
| Group_update_follower_count | 962869 |
| Group_update_free_count | 2 |
| Group_update_group_same_count | 0 |
| Group_update_gu_leak_count | 0 |
| Group_update_ignore_count | 0 |
| Group_update_insert_dup | 0 |
| Group_update_leader_count | 168292 |
| Group_update_lock_fail_count | 0 |
| Group_update_mgr_recycle_queue_length | 0 |
| Group_update_recycle_queue_length | 0 |
| Group_update_reuse_count | 23329 |
| Group_update_total_count | 2 |
+---------------------------------------+--------+Key variables to monitor:
Group_update_leader_count— Number of transactions that acted as the leader (acquired the lock and applied the batch). An increasing value confirms the optimization is active.Group_update_follower_count— Number of transactions that piggybacked on a leader without acquiring the lock. A high ratio of follower to leader count indicates effective batching.Group_update_fail_count— Number of group update failures. A persistently high value may indicate lock contention or constraint violations worth investigating.
Check the process list
SHOW physical FULL processlist WHERE command != 'Sleep';If the State column shows hotspot wait for commit, group commit is active for that connection.
Performance benchmarks
Test setup
Instance: PolarDB-X Enterprise Edition, 2 compute nodes + 2 data nodes, each with 4 CPU cores and 8 GB of memory
Tool: Sysbench. For more information, see Sysbench tests.
Test table:
CREATE TABLE sbtest(id INT UNSIGNED NOT NULL PRIMARY KEY, c BIGINT UNSIGNED NOT NULL);Test statement:
UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ sbtest SET c=c+1 WHERE id = 1;Test results
|
Scenario |
1 thread |
4 threads |
8 threads |
16 threads |
32 threads |
64 threads |
128 threads |
256 threads |
512 threads |
|
Hotspot update |
298 |
986 |
1,872 |
3,472 |
6,315 |
10,138 |
13,714 |
15,803 |
23,262 |
|
Common update |
318 |
423 |
409 |
409 |
412 |
428 |
448 |
497 |
615 |
All values are measured in TPS. Without inventory hints, common updates plateau around 400–600 TPS regardless of thread count due to lock contention. With inventory hints, TPS scales with concurrency, reaching 23,262 TPS at 512 threads — a 37x improvement over common updates.
The parameters are set to 1 for the sampled data used in the test.
Actual TPS depends on instance specifications, request concurrency, and the update statement. These results are for reference only.