ApsaraDB RDS:Uso de la función inventory hint

Contexto

En casos de uso como las ventas flash, la reducción de inventario es una tarea común que exige alta concurrencia y procesamiento serializado. AliSQL utiliza colas e hints transaccionales para controlar la concurrencia. Esto le permite confirmar o revertir transacciones rápidamente y mejorar el rendimiento del negocio.

La función inventory hint incrementa el rendimiento de una instancia de ApsaraDB RDS for MySQL hasta 31,000 transacciones por segundo (TPS) cuando se actualizan datos activos en una sola fila de una tabla. Para obtener más información, consulte Test method and results of hot data updates on a single row.

Requisitos previos

La instancia de RDS debe ejecutar una de las siguientes versiones de MySQL:

• MySQL 8.4

• MySQL 8.0

• MySQL 5.7

• MySQL 5.6

Sintaxis

A continuación se presentan los hints que puede usar en instrucciones SELECT, UPDATE, INSERT y DELETE:

• Para MySQL 5.7, 8.0 y 8.4:

  • Se introducen los siguientes hints transaccionales:

    • COMMIT_ON_SUCCESS: confirma la transacción si la ejecución de la instrucción a la que se aplica este hint finaliza correctamente.

    • ROLLBACK_ON_FAIL: revierte la transacción si falla la ejecución de la instrucción a la que se aplica este hint.

    Sintaxis:

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

    Ejemplo:

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

  • Se introduce el hint condicional TARGET_AFFECT_ROW(NUMBER).

    Cuando aplica este hint condicional a la instrucción actual, la ejecución finaliza correctamente solo si el número de filas afectadas coincide con el valor especificado en el hint.

    Sintaxis:

    /*+ TARGET_AFFECT_ROW(NUMBER) */

    Ejemplo:

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

• MySQL 5.6

  La sintaxis de los hints en MySQL 5.6 es similar a la de MySQL 5.7 and MySQL 8.0. La única diferencia es que en MySQL 5.7 y MySQL 8.0 no necesita incluir los hints dentro de comentarios.

  Ejemplo:

  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;

Notas de uso

• Los hints deben ir seguidos de los nombres de las tablas.

• Cuando un hint utilizado en una transacción surte efecto, activa una confirmación automática de dicha transacción. Por lo tanto, coloque el hint en la última instrucción SQL de la transacción.

• Los hints transaccionales no admiten el modo autocommit. Si usa un hint transaccional en una instrucción ejecutada en modo autocommit, el sistema reporta un error. Ejemplo:

  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

• No es posible usar hints transaccionales en subinstrucciones. Si incluye uno en una subinstrucción, el sistema reporta un error. Ejemplo:

  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

• Los hints condicionales no se pueden usar en instrucciones SELECT ni EXPLAIN. Si los incluye en alguna de estas instrucciones, el sistema reporta un error. Ejemplo:

  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

  Nota

  Puede especificar un número no válido en TARGET_AFFECT_ROW y comprobar si el sistema reporta errores:

  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)

Integración con la función returning

Combine la función inventory hint con la función returning para que el sistema devuelva conjuntos de resultados en tiempo real. Para obtener más información, consulte Returning. Ejemplo:

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)
            

Integración con la función statement queue

Utilice la función inventory hint junto con la función statement queue para que el sistema coloque las instrucciones en una cola. Para obtener más información, consulte Statement queue. Ejemplo:

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