調用InventoryHint提高吞吐能力 - ApsaraDB RDS

AliSQL提供Inventory Hint，協助您快速提交/復原事務，配合Returning和Statement Queue，能有效提高業務吞吐能力。

背景資訊

在秒殺等業務情境中，減少庫存是一個常見的需要高並發，同時也需要序列化的任務模型，AliSQL使用排隊和事務性Hint來控制並發和快速提交/復原事務，提高業務吞吐能力。

結合Inventory Hint，RDS的單行熱點更新效能可達3.1萬TPS（參見單行熱點更新測試）。

前提條件

執行個體版本如下：

MySQL 8.0
MySQL 5.7
MySQL 5.6

文法

新增了三個Hint，支援SELECT、UPDATE、INSERT、DELETE語句。

對於MySQL 5.7和MySQL 8.0版本：
- 兩個事務Hint為COMMIT_ON_SUCCESS和ROLLBACK_ON_FAIL：
  - COMMIT_ON_SUCCESS：當前語句執行成功就提交事務上下文。
  - ROLLBACK_ON_FAIL：當前語句執行失敗就復原事務上下文。
  文法：
```
/*+ COMMIT_ON_SUCCESS */
/*+ ROLLBACK_ON_FAIL */
```
  樣本：
```
UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ T
SET c = c - 1
WHERE id = 1;
```
- 條件Hint為TARGET_AFFECT_ROW(NUMBER) ：
  如果當前語句影響行數是指定的就成功，否則語句失敗。
  文法：
```
/*+ TARGET_AFFECT_ROW(NUMBER) */
```
  樣本：
```
UPDATE /*+ TARGET_AFFECT_ROW(1) */ T
SET c = c - 1
WHERE id = 1;
```
對於MySQL 5.6版本：
文法使用與MySQL 5.7和MySQL 8.0版本類似，不同點僅在於Hint不需要放在注釋中。
樣本：
```
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;
```

注意事項

Hint需要加在表名前面。
因Hint生效會自動認可事務，因此Hint需要位於事務的最後一條SQL。

事務Hint不能運行在autocommit模式下，例如：

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

事務Hint不能運行在sub statement下，例如：

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

條件Hint不能運行在SELECT/EXPLAIN statement下，例如：

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

說明

您可以指定target_affect_row為一個無效的number進行測試，系統會有警示。

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)

配合Returning使用

Inventory Hint可以配合Returning使用，即時返回結果集，例如：

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)

配合Statement queue使用

Inventory Hint可以配合Statement Queue進行排隊，例如：

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