Help & Documentation>TencentDB for MariaDB>Best Practice>Using Hotspot Update for Flash Sales

Using Hotspot Update for Flash Sales

Last updated: 2025-07-16 18:10:51

On this page:

1. Preamble

1.1. Background

In scenarios such as "flash sales" and "limited-time promotions," a large number of users request a massive amount of products within a very short period. In the context of a MySQL database, each product is undoubtedly stored as a single row, leading to numerous threads competing for InnoDB row locks. As concurrency increases, the number of waiting threads also grows, causing a decline in TPS and an increase in RT, which severely impacts the database's throughput. This document describes the special optimization implemented by MariaDB to address the challenges posed by "flash sales" and "limited-time promotions" – the hotspot update technology.

1.2. How to use

Hotspot Update: Use the following example statement to frequently update a specific data object. Currently, only Percona 5.7.17 is supported, which can be purchased on the MariaDB Purchase Page.
UPDATE COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL QUEUE_ON_PK 88 TARGET_AFFECT_ROW 1 table_name  SET k=k+1 WHERE id=88

Change of UPDATE and INSERT Syntax

You can add new keywords to the SQL statement UPDATE/INSERT/SELECT to indicate hotspot update. The words in red are newly added.

2.1. UPDATE syntax

UPDATE [LOW_PRIORITY]
[COMMIT_ON_SUCCESS] [ROLLBACK_ON_FALL] [QUEUE_ONPK expr1] [TARGET_AFFECT_ROW expr2]
[IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]

2.2. INSERT syntax

INSERT [LOW_PRIORITY | DLAYED | HIGH_PRIORITY]
[COMMIT_ON_SUCESS] [ROLLBACK_ON_FAIL] [QUEUE_ON_PK expr]
[IGNORE]
[INTO] tbl_name
[PARTITION (partition_name,...)]

2.3. Description

1. UPDATE only supports updating a single object, i.e., single-table-syntax but not multiple-table-syntax.
2. Only single-server scenarios are supported. Iterative versions in the XA scenario are implemented by proxy.
3. Three types of INSERT syntax are supported, and only one is described below.
4. For standard syntax, refer to the official standards: UPDATE Syntax, INSERT Syntax.
5. For objects with the value of expr specified by QUEUE_ON_PK, implement the hotspot update feature. Typically, the value of expr is a positive integer.
6. Parameter description:
COMMIT_ON_SUCCESS: Commit immediately after a successful update operation. Suitable for single statements as a transaction.
ROLLBACK_ON_FAIL: Immediately roll back after the update operation fails. Suitable for single statements as a transaction.
QUEUE_ON_PK expr: Specifies the hotspot update object, and locks and unlocks the updated object. The total number of updated objects does not exceed hot_commodity_query_size, meaning the number of different values for expr does not exceed hot_commodity_query_size. The value of expr is flexible, but it is recommended to be consistent with the primary key, although it can also be different.
TARGET_AFFECT_ROW expr: Specifies the data rows affected by the hotspot update. expr is a positive integer ([1, MAX], where MAX is the maximum value of an 8-bit positive number). Typically, expr is set to 1, indicating that only one row is affected.

2.4. Suggestions

When you use hotspot update, we recommend that you add all newly added parameters only to single-statement transactions.
UPDATE COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL QUEUE_ON_PK 88 TARGET_AFFECT_ROW 1 table_name  SET k=k+1 WHERE id=88

2.5. Sample code

CREATE DATABASE hc_db;

CREATE TABLE hc_tbl(a INT PRIMARY KEY, b INT, c INT);

CREATE TABLE hc_tbl_2(a INT PRIMARY KEY, b INT, c INT);

2.5.1. Sample code of INSERT


INSERT COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL QUEUE_ON_PK 1 INTO hc_tbl VALUES(1, 1, 1);

INSERT COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL QUEUE_ON_PK 1 INTO hc_tbl SET a= 2;

INSERT COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL QUEUE_ON_PK 1 INTO hc_tbl_2 SELECT * FROM hc_tbl;

2.5.2. Sample code of UPDATE

UPDATE COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL QUEUE_ON_PK 1 TARGET_AFFECT_ROW 1 hc_tbl SET b= b+1 WHERE a = 1;

expr in QUEUE_ON_PK expr is not necessarily the same as that in the WHERE clause.

UPDATE COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL QUEUE_ON_PK 2 TARGET_AFFECT_ROW 1 hc_tbl SET b= b+1 WHERE a = 1;

Newly Added Parameter Description

Parameter
SDK
Local Disk Types
Default value
Others
hot_commodity
_enable
Enables/disables hotspot update
Boolean
true Enable the hotspot update feature
To disable this parameter during runtime, new transactions will no longer use the hotspot update feature. It is recommended to set this parameter before system startup rather than changing it during runtime.
hot_commodity
_trace
Enables/disables tracing
Boolean
false Disable tracking feature
When it is enabled, trace information will be exported to standard output
hot_commodity
_query_size
Controls the number of hotspot objects that can be updated/inserted
Value
10000
Used for throttling
hot_commodity
_query_size_modify_enable
Control whether to modify hot_commodity_query_size
Boolean
False: Modifying hot_commodity_query_size is not allowed.
Easily modify hot_commodity_query_size in unit tests.
Note
If the MySQL server is started with the hot_commodity_enable parameter disabled, it needs to be enabled and the server restarted to initialize the global data object table. However, if the hot_commodity_query_size value is 0, the hotspot update cannot be used even if hot_commodity_enable is enabled. Therefore, to use the hotspot update feature, both parameters must be set:
hot_commodity_enable=ON
hot_commodity_query_size=10000 is a value greater than 0, recommended to be around 10,000 or 20,000. The suitable value should be determined through testing based on the hardware environment and application pressure.
Start the server

中国站 - English