tencent cloud

TencentDB for MySQL

DocumentationTencentDB for MySQL

Hot Updates: Multi-Hot Update Hints

Download
Focus Mode
Font Size
Last updated: 2026-07-01 14:54:33
AI-Translated

Overview

TencentDB for MySQL introduces two SQL Hints in the MySQL 8.0 20260512 version to support updating multiple hotspot rows within a single transaction or accessing the same hotspot row multiple times within the same transaction. This document focuses on explaining how to use these two Hints.
Hint Name
Scope
Purpose
TXSQL_FORCE_LOCK
transaction-level
Update multiple different hotspot rows within a single transaction.
TXSQL_FORCE_LOCK_INDEPENDENT_GROUP
single-hotspot-row level
Allows the same transaction to access the same hotspot row multiple times.

Usage Notes

Syntax Format

Use the MySQL-standard /*+ ... */ syntax for the Hint, placing it immediately after the UPDATE / INSERT / DELETE keyword:
UPDATE /*+ TXSQL_FORCE_LOCK */ table_name SET col = ... WHERE ...;
UPDATE /*+ TXSQL_FORCE_LOCK TXSQL_FORCE_LOCK_INDEPENDENT_GROUP */ table_name SET col = ... WHERE ...;

Key Placement Rules (Must Be Followed)

TXSQL_FORCE_LOCK must be placed on the first DML statement of the transaction.
Correct Placement: The entire transaction enters FORCE_LOCK mode, and subsequent DML statements no longer require the Hint.
Placed on a Non-First DML: The Hint is ignored with a warning, and the transaction is still executed under the old constraints.
TXSQL_FORCE_LOCK_INDEPENDENT_GROUP must be placed on the DML statement where the transaction first accesses the target hotspot row.
This Hint takes effect only when the transaction is already in TXSQL_FORCE_LOCK mode. Otherwise, it is discarded with a warning during parsing.
The attributes of the target hotspot row are determined once and for all during the first access. Any subsequent access, even with the Hint, will be treated as "a second access to the same hotspot row within the same transaction", resulting in transaction rollback.
The Hint is not inherited across transactions or statements and is automatically invalidated after the transaction ends.

Actual Performance of INDEPENDENT_GROUP at Different Placement Locations

Placement
Actual Effect
On the DML of the transaction's first access to row X
Row X goes to the independent group, and all subsequent accesses to row X by this transaction are allowed.
On the DML of the transaction's second and subsequent accesses to row X
The Hint is equivalent to not being written: Row X was recorded as a "non-independent group" during its first access, and this access is judged as "accessing the same hotspot row a second time", causing the transaction to roll back.
The current transaction is not a TXSQL_FORCE_LOCK transaction.
A warning is generated during the parsing phase + the statement is discarded, then processed as a normal statement.

Use Cases and Complete Examples

Scenario 1: Updating Multiple Different Hotspot Rows in a Single Transaction

Applicable: When you need to modify multiple rows of hotspot data within a single transaction, such as performing batch deductions from different account balances.
Procedure: Add TXSQL_FORCE_LOCK only to the first DML statement of the transaction. Subsequent DML statements do not need to carry the Hint.
BEGIN;
-- ① The first DML statement must carry TXSQL_FORCE_LOCK.
UPDATE /*+ TXSQL_FORCE_LOCK */ t_account SET balance = balance + 100 WHERE id = 1;

-- ② The second and subsequent DML statements do not need to carry the hint, as the transaction is already in force_lock mode.
UPDATE t_account SET balance = balance + 100 WHERE id = 2;
UPDATE t_account SET balance = balance + 100 WHERE id = 3;
COMMIT;

Scenario 2: Multiple Accesses to the Same Hotspot Row in a Single Transaction

Applicable: When you need to perform multiple read and write operations on the same row of hotspot data within a single transaction, such as deduction, verification, and adjustment.
Procedure: On the DML statement where the transaction first accesses that hotspot row, apply both TXSQL_FORCE_LOCK and TXSQL_FORCE_LOCK_INDEPENDENT_GROUP. Do not apply the Hint for subsequent accesses.
BEGIN;
-- ① For the first access to id=1, both hints must be applied.
UPDATE /*+ TXSQL_FORCE_LOCK TXSQL_FORCE_LOCK_INDEPENDENT_GROUP */
       t_account SET balance = balance + 1 WHERE id = 1;

-- ② Subsequent accesses to id=1 do not need and should not carry INDEPENDENT_GROUP.
UPDATE t_account SET balance = balance + 1 WHERE id = 1;
COMMIT;

Counterexample: INDEPENDENT_GROUP Omitted in the First Access

The following is an example of incorrect usage, which will cause the transaction to roll back:
BEGIN;
-- INDEPENDENT_GROUP was omitted during the first access to id=1.
UPDATE /*+ TXSQL_FORCE_LOCK */ t_account SET balance = balance + 1 WHERE id = 1;

-- Adding INDEPENDENT_GROUP only during the second access is already too late.
-- At this point, id=1 has already been recorded as a "non-independent group". This operation will be judged as a "second access to the same hotspot row", causing the transaction to roll back.
UPDATE /*+ TXSQL_FORCE_LOCK_INDEPENDENT_GROUP */
       t_account SET balance = balance + 1 WHERE id = 1;
COMMIT;

Use Constraints

A single transaction can be involved with a maximum of 10 hotspot rows (limited by the capacity of the fixed persistence area for undo pages). Exceeding this limit causes the transaction to roll back and generates a warning.
TXSQL_FORCE_LOCK must be placed on the first DML statement of the transaction. TXSQL_FORCE_LOCK_INDEPENDENT_GROUP must be placed on the DML statement that first accesses the corresponding hotspot row. Placing them incorrectly will cause them to become ineffective or trigger a transaction rollback.
The Hint is not inherited across transactions / statements and must be re-declared for each transaction / statement.
Note:
Before using the feature, verify your business transactions in a test environment to confirm that the Hint is actually effective. You can use SHOW WARNINGS to check whether it was ignored due to incorrect placement.

Supplementary Descriptions

This version also optimizes the performance regression issue during hotspot row cooldown: it resolves unnecessary transaction delays when hotspot traffic decreases from high to low by implementing self-wakeup for the head-of-queue waiter and a sliding-window-based cooldown mechanism. This optimization is transparent to business operations and requires no modifications.
Next Topic: Tutorial do usuário

Help and Support

Was this page helpful?

Help us improve! Rate your documentation experience in 5 mins.

Feedback