Inventory Hint

背景信息

在秒杀等业务场景中，减少库存是一个常见的需要高并发，同时也需要串行化的任务模型，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