Différence entre SET autocommit=1 et START TRANSACTION en mysql (Ai-je manqué quelque chose?)

Question

Je me renseigne sur les transactions en MySQL et je ne suis pas sûr d'avoir bien saisi quelque chose de spécifique, et je veux être sûr d'avoir bien compris, alors voici. Je sais ce qu'une transaction est censée faire, je ne suis juste pas sûr d'avoir compris la sémantique de l'instruction ou non.

Donc, ma question est, y a-t-il quelque chose de faux, (et, si c'est le cas, qu'est-ce qui ne va pas) avec ce qui suit :

Par défaut, le mode autocommit est activé dans MySQL.

Maintenant, SET autocommit=0; démarre une transaction, SET autocommit=1; commet implicitement. Il est possible de COMMIT; ainsi que de ROLLBACK;, dans les deux cas autocommit est toujours défini sur 0 par la suite (et une nouvelle transaction démarre implicitement).

START TRANSACTION; sera essentiellement SET autocommit=0; jusqu'à ce qu'un COMMIT; ou un ROLLBACK; ait lieu.

En d'autres termes, START TRANSACTION; et SET autocommit=0; sont équivalents, à l'exception du fait que START TRANSACTION; fait l'équivalent d'ajouter implicitement un SET autocommit=1; après COMMIT; ou ROLLBACK;

Si c'est le cas, je ne comprends pas http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_serializable - étant donné qu'avoir un niveau d'isolation implique qu'il y a une transaction, ce qui signifie que autocommit devrait être désactivé de toute façon ?

Et s'il y a une autre différence (autre que celle décrite ci-dessus) entre le démarrage d'une transaction et le réglage d'autocommit, quelle est-elle ?

Answer 1

Être conscient de la gestion de transaction (autocommit, explicite et implicite) pour votre base de données peut vous éviter d'avoir à restaurer des données à partir d'une sauvegarde.

Les transactions contrôlent les déclarations de manipulation des données pour garantir qu'elles sont atomiques. Être "atomique" signifie que la transaction se produit ou non. La seule façon de signaler l'achèvement de la transaction à la base de données est d'utiliser soit une instruction COMMIT ou ROLLBACK (conformément à l'ANSI-92, qui n'incluait malheureusement pas de syntaxe pour créer/débuter une transaction, donc c'est spécifique au fournisseur). COMMIT applique les modifications (si présentes) effectuées dans la transaction. ROLLBACK ignore toutes les actions qui ont eu lieu dans la transaction - hautement souhaitable lorsque qu'une déclaration UPDATE/DELETE fait quelque chose de non intentionnel.

Typiquement, les déclarations DML (Insert, Update, Delete) sont exécutées dans une transaction en autocommit - elles sont validées dès que la déclaration est exécutée avec succès. Ce qui signifie qu'il n'y a pas d'opportunité de revenir en arrière dans la base de données à l'état précédant l'exécution de la déclaration dans des cas comme le vôtre. Lorsqu'il y a une erreur, la seule option de restauration disponible est de reconstruire les données à partir d'une sauvegarde (si elle existe). Dans MySQL, l'autocommit est activé par défaut pour InnoDB - MyISAM ne prend pas en charge les transactions. Il peut être désactivé en utilisant :

SET autocommit = 0

Une transaction explicite est lorsque des déclarations sont encapsulées dans un bloc de code de transaction explicitement défini - pour MySQL, c'est START TRANSACTION. Cela nécessite également une instruction explicite COMMIT ou ROLLBACK à la fin de la transaction. Les transactions imbriquées dépassent le cadre de ce sujet.

Les transactions implicites sont légèrement différentes des explicites. Elles ne nécessitent pas de définition explicite d'une transaction. Cependant, comme les transactions explicites, elles nécessitent une instruction COMMIT ou ROLLBACK à être fournie.

Conclusion

Les transactions explicites sont la solution la plus idéale - elles exigent une déclaration, COMMIT ou ROLLBACK, pour finaliser la transaction, et ce qui se passe est clairement indiqué pour que d'autres puissent le lire en cas de besoin. Les transactions implicites sont acceptables si vous travaillez avec la base de données de manière interactive, mais les instructions COMMIT ne doivent être spécifiées que lorsque les résultats ont été testés et ont été complètement validés.

Cela signifie que vous devriez utiliser :

SET autocommit = 0;

START TRANSACTION;
  UPDATE ...;

...et n'utilisez COMMIT; que lorsque les résultats sont corrects.

Cela dit, les déclarations UPDATE et DELETE ne retournent généralement que le nombre de lignes affectées, sans détails spécifiques. Convertissez ces déclarations en déclarations SELECT & examinez les résultats pour garantir la précision avant de tenter la déclaration UPDATE/DELETE.

Addendum

Les déclarations de LDD (Langage de Définition de Données) sont automatiquement validées - elles ne nécessitent pas d'instruction COMMIT. Par ex : créations ou modifications de tables, index, procédures stockées, bases de données et vues.

Answer 2

Dans InnoDB vous avez START TRANSACTION;, qui dans ce moteur est la manière officiellement recommandée de faire des transactions, au lieu de SET AUTOCOMMIT = 0; (ne pas utiliser SET AUTOCOMMIT = 0; pour les transactions en InnoDB sauf s'il s'agit d'optimiser les transactions en lecture seule). Validez avec COMMIT;.

Vous pouvez vouloir utiliser SET AUTOCOMMIT = 0; dans InnoDB à des fins de test, et non précisément pour les transactions.

Dans MyISAM vous n'avez pas START TRANSACTION;. Dans ce moteur, utilisez SET AUTOCOMMIT = 0; pour les transactions. Validez avec COMMIT; ou SET AUTOCOMMIT = 1; (Différence expliquée dans le commentaire de l'exemple MyISAM ci-dessous). Vous pouvez faire des transactions de cette manière aussi en InnoDB.

Source: http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_autocommit

Exemples d'utilisation générale des transactions:

/* InnoDB */
START TRANSACTION;

INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');

COMMIT; /* SET AUTOCOMMIT = 1 pourrait ne pas remettre AUTOCOMMIT à son état précédent */

/* MyISAM */
SET AUTOCOMMIT = 0;

INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');

SET AUTOCOMMIT = 1; /* La déclaration COMMIT à la place ne restaurerait pas AUTOCOMMIT à 1 */

Answer 3

https://dev.mysql.com/doc/refman/8.0/fr/lock-tables.html

La bonne façon d'utiliser LOCK TABLES et UNLOCK TABLES avec des tables transactionnelles, telles que les tables InnoDB, est de commencer une transaction avec SET autocommit = 0 (pas START TRANSACTION) suivi de LOCK TABLES, et de ne pas appeler UNLOCK TABLES tant que vous n'aurez pas commit explicitement la transaction. Par exemple, si vous devez écrire dans la table t1 et lire dans la table t2, vous pouvez faire ceci :

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;... faites quelque chose avec les tables t1 et t2 ici ...
COMMIT;
UNLOCK TABLES;

Answer 4

Si vous voulez utiliser un rollback, utilisez début de la transaction et sinon oubliez tout cela,

Par défaut, MySQL valide automatiquement les modifications dans la base de données.

Pour forcer MySQL à ne pas valider ces modifications automatiquement, exécutez ce qui suit:

SET autocommit = 0;
//OU    
SET autocommit = OFF

Pour activer explicitement le mode autocommit:

SET autocommit = 1;
//OU    
SET autocommit = ON;