Use inventory hint - ApsaraDB RDS - Alibaba Cloud Documentation Center

When many concurrent transactions contend on a single row — for example, in flash sales or inventory deduction workloads — explicit COMMIT and ROLLBACK round trips become a bottleneck. AliSQL's inventory hint lets you commit or roll back a transaction automatically based on the outcome of a single DML statement, eliminating those extra round trips. Combined with the Returning and statement queue features, inventory hint can sustain up to 31,000 transactions per second (TPS) for single-row hot data updates. See Test method and results of hot data updates on a single row for benchmark details.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for MySQL instance running MySQL 5.6, MySQL 5.7, or MySQL 8.0

Hints at a glance

Hint	Type	Behavior	Supported statements
`COMMIT_ON_SUCCESS`	Transactional	Commits the current transaction if the statement succeeds	SELECT, UPDATE, INSERT, DELETE
`ROLLBACK_ON_FAIL`	Transactional	Rolls back the current transaction if the statement fails	SELECT, UPDATE, INSERT, DELETE
`TARGET_AFFECT_ROW(N)`	Conditional	Marks the statement as failed if the number of affected rows does not equal N	UPDATE, INSERT, DELETE

All three hints are commonly used together in a single statement:

UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ t
SET col1 = col1 - 1
WHERE id = 1;

Syntax

MySQL 5.7 and MySQL 8.0

Embed hints in an optimizer hint comment (/*+ ... */) immediately after the DML keyword:

/*+ COMMIT_ON_SUCCESS */
/*+ ROLLBACK_ON_FAIL */
/*+ TARGET_AFFECT_ROW(N) */

Example with all three hints combined:

UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ t
SET col1 = col1 - 1
WHERE id = 1;

MySQL 5.6

The hints work the same way, but are placed inline without the comment wrapper:

UPDATE COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL T
SET c = c - 1
WHERE id = 1;

UPDATE TARGET_AFFECT_ROW(1) T
SET c = c - 1
WHERE id = 1;

Usage notes

Hint placement

Place hints immediately after the DML keyword, followed by the table name.

Because a transactional hint triggers an automatic commit, place it in the last SQL statement of the transaction.

Autocommit mode

Transactional hints (COMMIT_ON_SUCCESS, ROLLBACK_ON_FAIL) do not support autocommit mode. Using them in an autocommit statement returns an error:

mysql> UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t
    -> SET col1 = col1 + 1
    -> WHERE id = 1;
ERROR 7531 (HY000): Inventory transactional hints didn't allowed in autocommit mode

Substatements

Transactional hints cannot be used inside stored procedures or triggers. Using them in a substatement returns an error:

mysql> CREATE TRIGGER tri_1
    -> BEFORE INSERT ON t
    -> FOR EACH ROW
    -> BEGIN
    -> INSERT /*+ commit_on_success */ INTO t1 VALUES (1);
    -> end//

mysql> INSERT INTO t VALUES (2, 1);
ERROR HY000: Inventory transactional hints didn't alllowed in stored procedure

SELECT and EXPLAIN statements

Conditional hints (TARGET_AFFECT_ROW) cannot be used in SELECT or EXPLAIN statements:

mysql> EXPLAIN UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t
    -> SET col1 = col1 + 1
    -> WHERE id = 1;
ERROR 7532 (HY000): Inventory conditional hints didn't match with result

Passing an invalid value to TARGET_AFFECT_ROW (for example, a negative number) in an EXPLAIN statement does not return an error. MySQL silently drops the invalid hint. Run SHOW WARNINGS after the EXPLAIN to see the warning:

mysql> EXPLAIN UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(-1) */ t
    -> SET col1 = col1 + 1
    -> WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | t     | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error near '-1) */ t set col1=col1+1 where id =1' at line 1                                                       |
| Note    | 1003 | update /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ `test`.`t` set `test`.`t`.`col1` = (`test`.`t`.`col1` + 1) where (`test`.`t`.`id` = 1) |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Work with the Returning feature

Combine inventory hint with the Returning feature to get the affected rows back in the same call. The dbms_trans.returning() procedure takes the column list and the DML statement as arguments.

mysql> CALL dbms_trans.returning("*", "update /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t
                                       set col1=col1+1 where id=1");
+----+------+
| id | col1 |
+----+------+
|  1 |   13 |
+----+------+
1 row in set (0.00 sec)

mysql> CALL dbms_trans.returning("*", "insert /*+ commit_on_success rollback_on_fail target_affect_row(1) */ into
                                       t values(10,10)");
+----+------+
| id | col1 |
+----+------+
| 10 |   10 |
+----+------+
1 row in set (0.01 sec)

For more information, see Returning.

Work with the statement queue feature

Combine inventory hint with the statement queue feature to serialize concurrent updates on the same row. The ccl_queue_field and ccl_queue_value hints route statements into the same queue slot based on a field value or a literal value.

-- Queue by field value
mysql> UPDATE /*+ ccl_queue_field(id) commit_on_success 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

-- Queue by literal value
mysql> UPDATE /*+ ccl_queue_value(1) commit_on_success 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

For more information, see Statement queue.