Inventory Hint

背景信息

在秒杀等业务场景中，扣减库存是一个常见的需要高并发，同时也需要串行化的任务模型，AliSQL使用排队和事务性Hint来控制并发和快速提交/回滚事务，提高业务吞吐能力。

结合Inventory Hint，RDS的单行热点更新性能可达3.1万TPS（参见单行热点更新测试）。

前提条件

实例版本如下：

• MySQL 8.4

• MySQL 8.0

• MySQL 5.7

• MySQL 5.6

语法

新增了三个Hint，支持SELECT、UPDATE、INSERT、DELETE语句。

• 对于MySQL 5.7和MySQL 8.0、8.4版本：

• COMMIT_ON_SUCCESS：当前语句执行成功就提交事务上下文。

• ROLLBACK_ON_FAIL：当前语句执行失败就回滚事务上下文。

• 两个事务Hint为COMMIT_ON_SUCCESS和ROLLBACK_ON_FAIL：

  语法：

   

  /*+ COMMIT_ON_SUCCESS */
  /*+ ROLLBACK_ON_FAIL */

  示例：

   

  UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ TSET c = c - 1WHERE id = 1;

• 条件Hint为TARGET_AFFECT_ROW(NUMBER) ：

  如果当前语句影响行数是指定的就成功，否则语句失败。

  语法：

   

  /*+ TARGET_AFFECT_ROW(NUMBER) */

  示例：

   

  UPDATE /*+ TARGET_AFFECT_ROW(1) */ TSET c = c - 1WHERE id = 1;

• 对于MySQL 5.6版本：

  语法使用与MySQL 5.7和MySQL 8.0版本类似，不同点仅在于Hint不需要放在注释中。

  示例：

   

  UPDATE COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TSET c = c - 1WHERE id = 1;UPDATE TARGET_AFFECT_ROW(1) TSET c = c - 1WHERE 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: 0mysql> 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