Inventory Hint

Background information

In business scenarios such as seckilling, 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.

Prerequisites

The RDS instance version is one of the following:

• MySQL 8.0
• MySQL 5.7
• MySQL 5.6

Syntax

The following three 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 specified in this hint.

  Syntax:

  /*+ TARGET_AFFECT_ROW(NUMBER) */

  Example:

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

Precautions

• The transactional hints do not support the autocommit mode. If you use a transactional hint in a statement with the 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 transactinal 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

• The conditional hint cannot be used in a SELECT or EXPLAIN statement. If you use the 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 Inventory Hint with Returning for the system to return real-time result sets. 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 Statement Queue for the system to queue statements. 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