Inventory Hint - ApsaraDB RDS - Alibaba Cloud Documentation Center

Background information

In business scenarios such as flash sales, inventory reduction is a common task model that requires high concurrency and serialization. In this model, AliSQL uses queues and transactional hints to control concurrency and commit or roll back transactions. This increases the throughput of your business.

You can use the Inventory Hint feature to increase the performance of an ApsaraDB RDS for MySQL instance to up to 31,000 transactions per second (TPS) when you update hot data in a single row of a table. For more information, see Test method and results of hot data updates on a single row.

Prerequisites

The RDS instance runs one of the following MySQL versions:

MySQL 8.0
MySQL 5.7
MySQL 5.6

Syntax

The following hints are introduced to specify tables in SELECT, UPDATE, INSERT, and DELETE statements.

COMMIT_ON_SUCCESS and ROLLBACK_ON_FAIL
These are two transactional hints.
- COMMIT_ON_SUCCESS: specifies to commit the transaction if the execution of the statement to which this hint is applied succeeds.
- ROLLBACK_ON_FAIL: specifies to roll the transaction back if the execution of the statement to which this hint is applied fails.
Syntax:
```
/*+ COMMIT_ON_SUCCESS */
/*+ ROLLBACK_ON_FAIL */
```
Example:
```
UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ T
SET c = c - 1
WHERE id = 1;
```
TARGET_AFFECT_ROW(NUMBER)
This is a conditional hint. After you apply it to a statement, the execution of the statement succeeds only when the number of affected rows is the same as the number that is specified in this hint.

Syntax:
```
/*+ TARGET_AFFECT_ROW(NUMBER) */
```
Example:
```
UPDATE /*+ TARGET_AFFECT_ROW(1) */ T
SET c = c - 1
WHERE id = 1;
```

Precautions

Hints must be followed by table names.

Transactional hints do not support the autocommit mode. If you use a transactional hint in a statement that is executed in autocommit mode, an error is reported. Example:

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

Transactional hints cannot be used in substatements. If you use a transactional hint in a substatement, an error is reported. Example:

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

Conditional hints cannot be used in a SELECT or EXPLAIN statement. If you use a conditional hint in a SELECT or EXPLAIN statement, an error is reported. Example:

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

Note You can specify an invalid number in the TARGET_AFFECT_ROW hint and check whether the system reports errors:

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 Returning

You can use the Inventory Hint feature together with the Returning feature to allow the system to return real-time result sets. For more information, see Returning. Example:

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)

Work with Statement Queue

You can use Inventory Hint with the Statement Queue feature for the system to queue statements. For more information, see Statement Queue. Example:

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

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