Problèmes de programmation et de traitement des transactions MySQL Innodb

1. Problèmes de soumission en boucles

De nombreux développeurs aiment soumettre des transactions en boucles. Voici un exemple de procédure stockée qu'ils écrivent souvent, comme indiqué ci-dessous :

DROP PROCEDURE IF EXISTS load1;CREATE PROCEDURE load1(count INT UNSIGNED)BEGIN
   DECLARE s INT UNSIGNED DEFAULT 1;
   DECLARE c CHAR(80) DEFAULT REPEAT(&#39;a&#39;,80);   WHILE s <= count DO
      INSERT INTO t1 select NULL,c;
      COMMIT;      SET s=s+1;   END WHILE;END;

Dans l’exemple ci-dessus, l’ajout ou non de la commande commit n’est pas critique. Étant donné que le moteur de stockage de MySQL innodb utilise par défaut la soumission automatique, le résultat de la suppression du commit dans la procédure stockée est le même. Comme indiqué ci-dessous, voici un autre problème qui est facilement négligé par les développeurs :

DROP PROCEDURE IF EXISTS load2; CREATE PROCEDURE load2(count INT UNSIGNED)BEGIN
   DECLARE s INT UNSIGNED DEFAULT 1;
   DECLARE c CHAR(80) DEFAULT REPEAT(&#39;a&#39;,80);   WHILE s <= count DO
      INSERT INTO t1 select NULL,c;      SET s=s+1;   END WHILE;END;

Quelle que soit la procédure stockée ci-dessus, lorsqu'une erreur se produit, la base de données restera à un emplacement inconnu. Par exemple, nous voulons insérer 10 000 éléments de données, mais une erreur s'est produite lors de l'insertion de 5 000 éléments. Cependant, ces 5 000 éléments ont été stockés dans la base de données. Comment devons-nous les gérer ? L'autre est un problème de performances. Les deux procédures stockées ci-dessus ne seront pas plus rapides que la procédure stockée ci-dessous, car la suivante met l'insertion dans une transaction :

DROP PROCEDURE IF EXISTS load3; CREATE PROCEDURE load3(count INT UNSIGNED)BEGIN
   DECLARE s INT UNSIGNED DEFAULT 1;
   DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
   START TRANSACTION;   WHILE s <= count DO
      INSERT INTO t1 select NULL,c;      SET s=s+1;   END WHILE;
   COMMIT;END;

Pour les trois stockages ci-dessus Dans le processus, nous insérons respectivement 1 million de données pour comparer le temps d'exécution, comme indiqué ci-dessous. On voit évidemment que la troisième méthode est beaucoup plus rapide car un journal redo doit être écrit pour chaque mention, donc Load1 et Load2 écrivent en fait 1 million. refaire les journaux. Pour la procédure stockée load3, nous n'avons écrit le journal de rétablissement qu'une seule fois.

Préparez d'abord une table de test

CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT ,`name` varchar(500) NULL ,
PRIMARY KEY (`id`)
) ;

Exécutez le test

09:50:44 test> call load1(1000000);
Query OK, 0 rows affected (1 min 4.90 sec)09:54:23 test> truncate table t1;
Query OK, 0 rows affected (0.05 sec)09:54:25 test> call load2(1000000);
Query OK, 1 row affected (1 min 3.38 sec)09:55:32 test> truncate table t1;
Query OK, 0 rows affected (0.20 sec)09:55:58 test> call load3(1000000);
Query OK, 0 rows affected (33.90 sec)

Pour le deuxième chargement de procédure stockée2, nous pouvons également ouvrir manuellement la transaction, la même chose peut être fait Pour obtenir l'effet de chargement de procédure stockée3, le temps d'exécution est le suivant :

09:57:42 test> begin;
Query OK, 0 rows affected (0.00 sec)09:57:46 test> call load2(1000000);
Query OK, 1 row affected (34.08 sec)09:58:26 test> commit;
Query OK, 0 rows affected (0.76 sec)

2. À propos de l'utilisation de la soumission automatique

Dans certains scénarios particuliers, la soumission automatique n'est parfois pas nécessairement nécessaire. C'est une bonne chose., Comme nous l'avons mentionné ci-dessus à propos du problème de soumission circulaire, la base de données MySQL utilise par défaut la validation automatique (autocommit). Vous pouvez modifier la méthode de soumission MySQL des manières suivantes :

10:35:34 test> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

Vous pouvez également utiliser START TRANSATION ou BEGIN pour démarrer explicitement une transaction. MySQL exécutera automatiquement

SET AUTOCOMMIT=0 et exécutera SET AUTOCOMMIT=1 après que COMMIT ou ROLLBACK termine une transaction.

3. Utiliser la restauration automatique pour gérer les exceptions Que faire lorsqu'une exception se produit dans un processus stocké ? Le moteur de stockage Innodb prend en charge la restauration automatique des transactions via un HANDLER. Si une erreur se produit pendant le processus de stockage, l'opération de restauration sera automatiquement effectuée. A titre d'exemple ci-dessous :

CREATE PROCEDURE sp_auto_rollback_demo()BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
   START TRANSACTION;
   INSERT INTO b select 1;
   INSERT INTO b select 2;
   INSERT INTO b select 1;
   INSERT INTO b select 3;
   COMMIT;END;

La table de test est la suivante

CREATE TABLE `b` (  `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Exécutez la procédure stockée ci-dessus, donc une erreur se produira lors de l'insertion du deuxième enregistrement 1, mais car automatique est activé Pour l'opération de rollback, le résultat de l'exécution de cette procédure stockée est le suivant :

10:09:46 test> call sp_auto_rollback_demo;
Query OK, 0 rows affected (0.01 sec)10:10:04 test> select * from b;Empty set (0.00 sec)

Il semble qu'il n'y ait pas de problème et l'opération est relativement normale, mais lors de l'exécution de sp_auto_rollback_demo, l'exécution a-t-elle réussi ou échoué ? Nous pouvons gérer cela comme suit, l'exemple est le suivant :

DROP PROCEDURE IF EXISTS sp_auto_rollback_demo;CREATE PROCEDURE sp_auto_rollback_demo()BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT -1; END;
   START TRANSACTION;
   INSERT INTO b select 1;
   INSERT INTO b select 2;
   INSERT INTO b select 1;
   INSERT INTO b select 3;
   COMMIT;
   SELECT 1;END;

Lorsqu'une erreur se produit, revenez d'abord en arrière, puis renvoyez -1, indiquant qu'une erreur s'est produite pendant le fonctionnement. Le retour de 1 indique un fonctionnement normal. Les résultats en cours sont les suivants :

10:16:19 test> call sp_auto_rollback_demo\G*************************** 1. row ***************************-1: -1
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
10:16:35 test> select * from b;
Empty set (0.00 sec)

Ce qui précède est le contenu des problèmes de programmation et de traitement des transactions MySQL Innodb. Pour plus de contenu connexe, veuillez prêter attention au site Web PHP chinois (www.php.cn). !