AliSQL提供Inventory Hint,帮助您快速提交/回滚事务,配合Returning和Statement Queue,能有效提高业务吞吐能力。

背景信息

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

前提条件

实例版本如下:

  • MySQL 8.0
  • MySQL 5.6

语法

新增了三个hint, 支持SELECT、UPDATE、INSERT、DELETE 语句。

  • COMMIT_ON_SUCCESS/ROLLBACK_ON_FAIL

    两个事务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;
  • TARGET_AFFECT_ROW(NUMBER)

    条件hint为TARGET_AFFECT_ROW(NUMBER) :如果当前语句影响行数是指定的就成功,否则语句失败。

    语法:

    /*+ TARGET_AFFECT_ROW(NUMBER) */

    示例:

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

注意事项

  • 事务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 transactinal 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