Hot Update Enhancement: Merge Optimization
Download
Focus Mode
Font Size
Feature Introduction
The hotspot update enhancement feature of TencentDB for MySQL (TXSQL): Merge Optimization, is a specialized performance optimization for high-concurrency hotspot row update scenarios. In business scenarios such as e-commerce flash sales and financial account deposits, a large number of concurrent transactions frequently update the same row of records. The traditional row-locking mechanism leads to severe lock wait queues, resulting in extremely low TPS and extremely high latency.
Hotspot Update Enhancement Feature: Merge Optimization builds upon the "Hotspot Update: Queuing Mechanism" by introducing the concept of Transaction Merging (Group Commit): it automatically organizes concurrently arriving hotspot update transactions into a
group. Within the group, the leader is responsible for acquiring locks, while the remaining followers can execute hotspot updates without waiting for lock release. Other statements within the transaction can execute concurrently with other transactions. This mechanism significantly improves the parallelism of hotspot row updates, delivering particularly notable results in high-transaction-latency scenarios such as semi-sync replication and high network latency.Applicable Scenarios
E-commerce Flash Sales: A large number of concurrent requests deduct from the same product inventory row.
Financial Account Deposits: The account balance row is updated by high-concurrency fund flows.
Points/Counters: Frequent incremental operations are performed on the same counting row.
Inventory Management: The inventory rows for popular products are concurrently updated in a multi-warehouse system.
Note:
Hotspot Update Enhancement Feature: Merge Optimization is suitable for configurations with high transaction latency, such as scenarios where semi-sync replication is enabled, or where there is high network latency between the client and the database node.
Feature Principles
Limitations of the "Hot Update: Queuing Mechanism" Solution
The "Hotspot Update: Queuing Mechanism" employs the concept of queued serialization: before acquiring a lock, a transaction determines whether it is a hotspot row. If it is, the transaction enters a waiting queue, and the next transaction is only awakened after the previous one is committed. This mechanism yields some effectiveness in low-latency scenarios. However, in scenarios with semi-sync replication or high latency, where each transaction's execution time is longer, the throughput improvement brought by serialized waiting is limited.
Hot Update Enhancement Solution: Transaction Merging (Group Commit)
The core idea of the hotspot update enhancement solution is to logically merge a group of transactions using a
group, thereby improving parallelism.Concurrent Transactions ──▶ Group 1 ──▶ Group 2 ──▶ Group 3 ──▶ ...│ │Leader LeaderFollower×N Follower×N
Key Mechanisms
Role | Action |
leader | Responsible for locking and unlocking hotspot rows, releasing locks and waking up the next leader when the transaction ends. |
follower | Does not need to wait for lock release, can be executed after the leader completes the hotspot row update, and other statements within the transaction are executed concurrently with other transactions. |
Execution Flow
1. Concurrently arriving hotspot update transactions are automatically organized into a
group. The first transaction that enters becomes the leader, and the rest serve as followers.2. The
leader acquires the lock and performs the hotspot row update.3. After completing the hotspot row update, the
leader immediately grants the next follower permission to execute, without waiting for the leader to commit.4. After being awakened, the
follower re-reads the latest current data and directly performs the hotspot row update (skipping the lock acquisition step).5. The
leader releases the row lock when the transaction ends, awakens the next leader, and a new group is formed accordingly.Because the
followers within a group no longer wait for the leader to commit, the lock waiting time for transactions is significantly reduced, and the improvement effect is particularly pronounced in high-latency scenarios.Correctness Guarantee
To ensure the correctness of Merge Optimization after the constraints of traditional 2PL are broken, TencentDB for MySQL (TXSQL) implements the following guarantee mechanisms:
1. Submission Order Guarantee
All transactions within a
group must be committed sequentially according to the order of hotspot row updates, ensuring MVCC (Multi-Version Concurrency Control) data visibility and correctness.In implementation, the system assigns a globally increasing sequence identifier to each hotspot update transaction and, through a mechanism, ensures that transactions enter the commit queue strictly according to this sequence, thereby guaranteeing orderly commits.
2. Rollback Order Guarantee
If a transaction within a
group needs to be rolled back, all its subsequently executed transactions must be rolled back in reverse order.Server layer: Notifies all transactions waiting to commit to roll back through a signaling mechanism.
InnoDB layer: By maintaining a dependency list for hotspot update transactions, it ensures that only the transaction at the end of the list can be rolled back, while other transactions wait for the preceding transaction's rollback to complete.
3. Crash Recovery
Transaction sequence information is persisted to the undo log. After a database restart, the sequence information of uncommitted transactions can be recovered from the undo log, and all uncommitted hotspot update transactions are rolled back in reverse order to ensure data consistency.
Performance Improvement Results
The following tests were conducted in semi-sync replication mode, simulating a hotspot update accounting service (single hotspot row, sysbench test).
Note:
The concurrency threshold for triggering hotspot update merge optimization is set to 30, which can be configured via the innodb_hot_update_threshold parameter.
TPS Comparison
Number Of Concurrent Threads | Hotspot Update Disabled | Hotspot Update Enabled | Speedup Ratio |
8 | 671.15 | 668.64 | 1.0× |
32 | 660.69 | 6,927.31 | 10.5× |
128 | 635.14 | 15,304.53 | 24.1× |
512 | 594.28 | 13,709.24 | 23.1× |
1024 | 551.57 | 12,936.15 | 23.5× |
95th Percentile Latency (ms) Comparison
Number of Concurrent Threads | Hotspot Update Disabled | Hotspot Update Enabled | Latency Reduction |
8 | 12.08 | 12.08 | 1.0× |
32 | 49.21 | 5.18 | 9.5× |
128 | 204.11 | 9.56 | 21.4× |
512 | 877.61 | 37.56 | 23.4× |
1024 | 1903.57 | 77.19 | 24.7× |
Based on the comparison of the test results above, it can be seen that under 128 concurrent threads, TPS increases by approximately 24 times, and P95 latency decreases by approximately 21 times. The higher the concurrency, the more significant the optimization effect.
Parameter Description
Parameter Name | Default Value | Description |
innodb_hot_update_detect | OFF | Whether to enable hotspot update detection. After it is enabled, the system automatically identifies hotspot rows and triggers merge optimization. |
innodb_hot_update_threshold | 30 | The concurrency threshold for triggering hotspot update merge optimization. The hotspot update mechanism is triggered when the number of concurrent updates to the same row exceeds this threshold. |
Parameter Tuning Recommendations
innodb_hot_update_threshold: If hotspot concurrency in the business is low, you can appropriately lower this threshold to trigger hotspot optimization earlier.
Use Limits
When the hotspot update feature is used, note the following limitations. If a limitation is violated, the related transaction will report an error and be rolled back.
Item | Description |
Only supports single-row updates via primary key index. | Both SELECT FOR UPDATE and UPDATE must be performed as single-row operations based on the primary key index, and the update conditions must be consistent. If SELECT FOR UPDATE acquires a lock on a secondary index, the hotspot optimization fails to take effect. |
SELECT FOR UPDATE must be executed before UPDATE. | If a transaction contains both SELECT FOR UPDATE and UPDATE operations on a hotspot row, SELECT FOR UPDATE must be executed first. When UPDATE arrives, if it has already been queued, it skips the queue and is executed directly. |
Multiple UPDATE operations on the same hotspot row are not allowed within a single transaction. | After the first UPDATE is completed, the lock is released to the next transaction. If the same row is UPDATE again, the commit order will be disrupted, and the system will report an error and roll back the entire transaction. |
Conflicting SQL statements are not allowed after a hotspot row UPDATE. | After a transaction updates a hotspot row, subsequent statements must not cause lock conflicts with other hotspot update transactions; otherwise, an error is reported and the entire transaction is rolled back. |
Deleting hotspot records is not allowed during hotspot updates. | If a DELETE request attempts to operate on a record that is being updated as a hotspot, an error is reported and the entire transaction is rolled back. |
Updating hotspot records via secondary indexes is not allowed during hotspot updates. | If a record that is being updated as a hotspot is updated via a secondary index, an error is reported and the entire transaction is rolled back. |
Thread pool mode is not currently supported. | On instances where the thread pool (Thread Pool) is enabled, the hotspot update feature is temporarily unavailable. |
Usage
Enabling Hot Update
The hotspot update feature is disabled by default. You can enable the feature by setting the parameter binlog_order_commits = ON. After it is enabled, the feature can automatically identify hotspot rows and trigger merge optimization.
You can view the current parameter status using the following SQL:
SHOW VARIABLES LIKE 'innodb_hot_update%';
Typical Business SQL Examples
Scenario 1: Directly UPDATE a Hotspot Row (Simplest Scenario)
BEGIN;UPDATE account SET balance = balance - 100 WHERE id = 1; -- Hotspot row, single-row update based on the primary key-- Other non-conflicting business logic...COMMIT;
Scenario 2: First SELECT FOR UPDATE, Then UPDATE (Recording Operation History)
BEGIN;SELECT * FROM account WHERE id = 1 FOR UPDATE; -- Based on the primary key, it goes to the hotspot waiting queue.INSERT INTO account_log (account_id, amount, op_time) VALUES (1, -100, NOW()); -- Record the operation logUPDATE account SET balance = balance - 100 WHERE id = 1; -- Hotspot row UPDATE, which triggers granting to the next followerCOMMIT;
Note:
The
SELECT FOR UPDATE and UPDATE WHERE conditions must be consistent and must both be single-row operations based on the primary key.Verifying Whether Hot Update Is Effective
You can confirm that the feature is effective by running the
select * from information_schema.INNODB_HOT_ROW_UPDATE_STATS command, observing hotspot row-related statistics from monitoring metrics, or noting a significant improvement in TPS/latency metrics.Precautions
Deadlock Scenario: If a transaction, after updating a hotspot row, encounters a lock conflict with another transaction on a non-hotspot row (meaning both transactions have updated the same hotspot row and then attempt to lock the same non-hotspot row), the automatic detection and handling of such deadlocks is not supported in the current version. Users must proactively
KILL the related transactions.Secondary Server Replay: After hotspot update is enabled, TencentDB for MySQL (TXSQL) has been specifically optimized for this scenario to avoid generating a large number of lock waits during parallel replay on the secondary server, thereby preventing excessive primary/secondary delay.
Latency in the Fixed TPS Access Model: In TPS throttling scenarios, the wait queue may become empty, causing transactions to fail to be woken up. TencentDB for MySQL (TXSQL) has optimized this issue through a mechanism of "new transactions actively acquiring locks and opening a new
group". However, if high latency is encountered, you can appropriately adjust the innodb_hot_update_wakeup_timeout parameter.Multiple Hotspot Rows Scenario: In the current version, multiple hotspot rows share the same set of sequential control structures. In extreme cases, a transaction rollback on one hotspot row may cause transactions on other hotspot rows to also be rolled back. This is a known limitation and will be optimized in future versions.
Supported Versions
Kernel version MySQL 5.7 20250330 or later.
Kernel version MySQL 8.0 20231130 or later.
References
Help and Support
Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback