Use an inventory hint to quickly commit or roll back a transaction - PolarDB

Inventory hints let you embed transaction control logic directly in SQL statements — committing or rolling back a transaction based on the outcome of the statement. In high-concurrency write scenarios such as flash sales and inventory deduction, multiple transactions compete to update the same row simultaneously, causing lock contention that degrades throughput. Inventory hints resolve each competing write in a single round trip, eliminating the need for application-level retry logic. Use them together with the statement queue feature to serialize conflicting writes into buckets and maximize throughput.

Supported versions

Inventory hints are available on the following PolarDB for MySQL versions:

Version	Minimum kernel minor version
PolarDB for MySQL 8.0	8.0.1.1.1
PolarDB for MySQL 5.7	5.7.1.0.17
PolarDB for MySQL 5.6	All minor versions

To check your cluster's kernel minor version, see Query the engine version.

How it works

Inventory hints support SELECT, UPDATE, INSERT, and DELETE statements. Three hints are available:

Hint	Effect	Triggers when
`COMMIT_ON_SUCCESS`	Commits the current transaction	Statement executes successfully
`ROLLBACK_ON_FAIL`	Rolls back the current transaction	Statement fails
`TARGET_AFFECT_ROW(n)`	Marks the statement as failed if affected rows do not equal `n`	Statement execution

The hints work together to drive both the DML operation and the transaction outcome in a single statement:

COMMIT_ON_SUCCESS commits the transaction immediately when the statement succeeds.
ROLLBACK_ON_FAIL rolls it back immediately when the statement fails.
TARGET_AFFECT_ROW(n) converts a row-count mismatch — for example, updating a sold-out item — into a statement failure, which ROLLBACK_ON_FAIL then catches.

Combining all three hints with statement queue serializes conflicting writes into buckets and resolves each transaction in a single round trip.

Syntax

The syntax differs between PolarDB for MySQL 5.6 and PolarDB for MySQL 5.7/8.0.

COMMIT_ON_SUCCESS

Commits the transaction when the statement succeeds.

PolarDB for MySQL 5.6

UPDATE COMMIT_ON_SUCCESS t
SET c = c - 1
WHERE id = 1;

PolarDB for MySQL 5.7 and 8.0

UPDATE /*+ COMMIT_ON_SUCCESS */ t
SET c = c - 1
WHERE id = 1;

ROLLBACK_ON_FAIL

Rolls back the transaction when the statement fails.

PolarDB for MySQL 5.6

UPDATE ROLLBACK_ON_FAIL t
SET c = c - 1
WHERE id = 1;

PolarDB for MySQL 5.7 and 8.0

UPDATE /*+ ROLLBACK_ON_FAIL */ t
SET c = c - 1
WHERE id = 1;

TARGET_AFFECT_ROW

Marks the statement as failed if the number of affected rows does not match the specified value. Setting the value to 1 means the statement succeeds if at least one record is updated. If no rows match the WHERE clause, the statement fails and returns an error.

PolarDB for MySQL 5.6

UPDATE TARGET_AFFECT_ROW 1 t
SET c = c - 1
WHERE id = 1;
-- ERROR HY000: The affected row number does not match that of user specified.

PolarDB for MySQL 5.7 and 8.0

UPDATE /*+ TARGET_AFFECT_ROW(1) */ t
SET c = c - 1
WHERE id = 1;
-- ERROR HY000: The affected row number does not match that of user specified.

Use with statement queue

Combine COMMIT_ON_SUCCESS, ROLLBACK_ON_FAIL, and TARGET_AFFECT_ROW with statement queue to queue conflicting writes into the same bucket and resolve each transaction in a single pass.

PolarDB for MySQL 5.6 — uses POLARDB_STATEMENT_CONCURRENT_QUEUE with a key expression or literal value:

UPDATE COMMIT_ON_SUCCESS POLARDB_STATEMENT_CONCURRENT_QUEUE id ROLLBACK_ON_FAIL TARGET_AFFECT_ROW 1 t
SET col1 = col1 + 1
WHERE id = 1;
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

UPDATE COMMIT_ON_SUCCESS POLARDB_STATEMENT_CONCURRENT_QUEUE 1 ROLLBACK_ON_FAIL TARGET_AFFECT_ROW 1 t
SET col1 = col1 + 1
WHERE id = 1;
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

PolarDB for MySQL 5.7 and 8.0 — uses CCL_QUEUE_VALUE inside the /*+ ... */ hint block:

UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) CCL_QUEUE_VALUE('id') */ t
SET col1 = col1 + 1
WHERE id = 1;
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) CCL_QUEUE_VALUE(1) */ t
SET col1 = col1 + 1
WHERE id = 1;
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

Constraints

Constraint	Scope	Details
`autocommit` mode	PolarDB for MySQL 8.0 only	`COMMIT_ON_SUCCESS` and `ROLLBACK_ON_FAIL` cannot run when `autocommit` is enabled. Disable `autocommit` before using these hints, or wrap the statement in an explicit transaction.

Running COMMIT_ON_SUCCESS or ROLLBACK_ON_FAIL in autocommit mode returns the following error:

UPDATE /*+ ROLLBACK_ON_FAIL */ t
SET c = c - 1
WHERE id = 1;
-- ERROR 7531 (HY000): Inventory transactinal hints didn't allowed in autocommit mode