Utiliser l'indication d'inventaire pour améliorer le débit - ApsaraDB RDS

Cette rubrique décrit la fonctionnalité d'indication d'inventaire fournie par AliSQL. Utilisez cette fonctionnalité conjointement avec les fonctionnalités returning et statement queue pour valider et annuler des transactions à haute vitesse, augmentant ainsi le débit de votre application.

Informations générales

Dans des cas d'utilisation tels que les ventes flash, la réduction des stocks est une tâche courante qui nécessite à la fois une forte concurrence et un traitement sérialisé. AliSQL utilise la mise en file d'attente et les indications transactionnelles pour contrôler la concurrence, ce qui permet de valider ou d'annuler rapidement les transactions et d'améliorer le débit métier.

La fonctionnalité d'indication d'inventaire permet d'augmenter les performances d'une instance ApsaraDB RDS for MySQL jusqu'à 31 000 transactions par seconde (TPS) lors de la mise à jour de données fréquemment accédées dans une seule ligne d'une table. Pour plus d'informations, consultez la section Méthode de test et résultats des mises à jour de données fréquemment accédées sur une seule ligne.

Prérequis

L'instance RDS exécute l'une des versions MySQL suivantes :

MySQL 8.4
MySQL 8.0
MySQL 5.7
MySQL 5.6

Syntaxe

Les indications suivantes sont disponibles pour les instructions SELECT, UPDATE, INSERT et DELETE :

Pour MySQL 5.7, 8,0 et 8,4 :
- Les indications transactionnelles suivantes sont disponibles :
  - COMMIT_ON_SUCCESS : valide la transaction si l'exécution de l'instruction associée à cette indication réussit.
  - ROLLBACK_ON_FAIL : annule la transaction si l'exécution de l'instruction associée à cette indication échoue.
  Syntaxe :
```
/*+ COMMIT_ON_SUCCESS */
/*+ ROLLBACK_ON_FAIL */
```
  Exemple :
```
UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ T
SET c = c - 1
WHERE id = 1;
```
- L'indication conditionnelle TARGET_AFFECT_ROW(NUMBER) est disponible.
  
  L'exécution de l'instruction ne réussit que si le nombre de lignes affectées correspond au nombre spécifié dans cette indication.
  
  Syntaxe :
```
/*+ TARGET_AFFECT_ROW(NUMBER) */
```
  Exemple :
```
UPDATE /*+ TARGET_AFFECT_ROW(1) */ T
SET c = c - 1
WHERE id = 1;
```
MySQL 5.6

La syntaxe des indications dans MySQL 5.6 est similaire à celle des indications dans MySQL 5.7 et MySQL 8.0. La seule différence réside dans le fait qu'il n'est pas nécessaire d'inclure les indications entre commentaires dans MySQL 5.6.

Exemple :
```
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;
```

Remarques d'utilisation

Les indications doivent être suivies des noms de table.
Lorsqu'une indication utilisée dans une transaction prend effet, elle déclenche une validation automatique de la transaction. Placez donc l'indication dans la dernière instruction SQL de la transaction.
Les indications transactionnelles ne prennent pas en charge le mode autocommit. Si vous utilisez une indication transactionnelle dans une instruction exécutée en mode autocommit, une erreur se produit. Exemple :
```
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
```

Les indications transactionnelles ne peuvent pas être utilisées dans des sous-instructions. Si vous utilisez une indication transactionnelle dans une sous-instruction, une erreur se produit. Exemple :

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

Les indications conditionnelles ne peuvent pas être utilisées dans une instruction SELECT ou EXPLAIN. Si vous utilisez une indication conditionnelle dans une instruction SELECT ou EXPLAIN, une erreur se produit. Exemple :

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

Remarque

Spécifiez un nombre invalide dans TARGET_AFFECT_ROW pour vérifier si le système signale des erreurs :

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)

Utilisation conjointe avec la fonctionnalité returning

Utilisez la fonctionnalité d'indication d'inventaire conjointement avec la fonctionnalité returning pour permettre au système de renvoyer des ensembles de résultats en temps réel. Pour plus d'informations, consultez la section Returning. Exemple :

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)

Utilisation conjointe avec la fonctionnalité statement queue

Utilisez la fonctionnalité d'indication d'inventaire conjointement avec la fonctionnalité statement queue pour mettre les instructions en file d'attente. Pour plus d'informations, consultez la section Statement queue. Exemple :

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