Transactions MySQL

Les transactions MySQL sont principalement utilisées pour gérer des données avec de gros volumes d'opérations et une grande complexité. Par exemple, dans un système de gestion du personnel, lorsque vous supprimez une personne, vous devez supprimer non seulement les informations de base de la personne, mais aussi les informations liées à cette personne, telles que les boîtes aux lettres, les articles, etc. Ainsi, ces instructions d'opération de base de données forment une transaction !

Dans MySQL, une transaction est l'exécution d'un ensemble d'instructions SQL considérées comme une seule unité de travail.

  • Dans MySQL, seules les bases de données ou les tables utilisant le moteur de base de données InnoDB prennent en charge les transactions.
  • Le traitement des transactions peut être utilisé pour maintenir l'intégrité de la base de données, en garantissant qu'un lot d'instructions SQL soit entièrement exécuté, soit pas du tout.
  • Les transactions sont utilisées pour gérerinsert、update、deleteinstructions

Généralement, une transaction doit satisfaire 4 conditions (ACID) : Atomicité (Aatomicité, ou indivisibilité), Cohérence (Ccohérence), Isolation (Iisolation, également appelée indépendance), Durabilité (Durabilité)。

  • Atomicité :Toutes les opérations d'une transaction (transaction) doivent soit être toutes terminées, soit ne pas être terminées du tout, et la transaction ne doit pas s'arrêter à une étape intermédiaire. Si une erreur se produit pendant l'exécution de la transaction, elle sera annulée et ramenée à l'état précédant le début de la transaction, comme si la transaction n'avait jamais été exécutée.

  • Cohérence :L'intégrité de la base de données n'est pas compromise avant le début de la transaction et après la fin de la transaction. Cela signifie que les données écrites doivent pleinement respecter toutes les règles prédéfinies, y compris l'exactitude et la concaténation des données, ainsi que la capacité de la base de données à achever spontanément le travail planifié par la suite.

  • Isolation :La capacité d'une base de données à permettre à plusieurs transactions concurrentes de lire, écrire et modifier ses données en même temps. L'isolation peut éviter les incohérences de données causées par une exécution croisée lorsque plusieurs transactions s'exécutent simultanément. L'isolation des transactions est divisée en différents niveaux, notamment Read uncommitted, Read committed, Repeatable read et Serializable.

  • Durabilité :Une fois le traitement de la transaction terminé, les modifications apportées aux données sont permanentes et ne seront pas perdues même en cas de défaillance du système.

Dans les paramètres par défaut de la ligne de commande MySQL, les transactions sont automatiquement validées, c'est-à-dire qu'après l'exécution d'une instruction SQL, une opération COMMIT est immédiatement effectuée. Par conséquent, pour démarrer explicitement une transaction, vous devez utiliser la commande BEGIN ou START TRANSACTION, ou exécuter la commande SET AUTOCOMMIT=0 pour désactiver la validation automatique pour la session en cours.

Instructions de contrôle des transactions :

  • BEGIN ou START TRANSACTION démarre explicitement une transaction ;

  • COMMIT, ou COMMIT WORK, bien que les deux soient équivalents. COMMIT valide la transaction et rend permanentes toutes les modifications effectuées sur la base de données ;

  • ROLLBACK, ou ROLLBACK WORK, bien que les deux soient équivalents. Le rollback met fin à la transaction de l'utilisateur et annule toutes les modifications non validées en cours ;

  • SAVEPOINT identifier. SAVEPOINT permet de créer un point de sauvegarde dans une transaction, et une transaction peut avoir plusieurs SAVEPOINT ;

  • RELEASE SAVEPOINT identifier supprime un point de sauvegarde d'une transaction. Lorsque le point de sauvegarde spécifié n'existe pas, l'exécution de cette instruction lèvera une exception ;

  • ROLLBACK TO identifier annule la transaction jusqu'au point marqué ;

  • SET TRANSACTION est utilisé pour définir le niveau d'isolation d'une transaction. Le moteur de stockage InnoDB fournit des niveaux d'isolation de transaction, notamment READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ et SERIALIZABLE.

Il existe principalement deux méthodes de traitement des transactions MySQL :

1. Implémenter avec BEGIN, ROLLBACK, COMMIT

  • BEGIN ou START TRANSACTION: utilisé pour démarrer une transaction.
  • ROLLBACKAnnulation de la transaction, annulant les modifications précédentes.
  • COMMIT: Validation de la transaction, validant la transaction, rendant les modifications permanentes.

2. Utiliser directement SET pour modifier le mode de validation automatique de MySQL :

  • SET AUTOCOMMIT=0Désactiver la validation automatique
  • SET AUTOCOMMIT=1Activer la validation automatique

BEGIN ou START TRANSACTION-- Utilisé pour démarrer une transaction :

BEGIN; -- 或者使用 START TRANSACTION;

COMMIT-- Utilisé pour valider la transaction, en enregistrant définitivement toutes les modifications dans la base de données :

COMMIT;

ROLLBACK-- Utilisé pour annuler la transaction, en annulant toutes les modifications effectuées depuis la dernière validation :

ROLLBACK;

SAVEPOINT-- Utilisé pour définir un point de sauvegarde dans la transaction, afin de pouvoir revenir à ce point plus tard :

SAVEPOINT savepoint_name;

ROLLBACK TO SAVEPOINT-- Utilisé pour revenir à un point de sauvegarde défini précédemment :

ROLLBACK TO SAVEPOINT savepoint_name;

Voici un exemple simple d'une transaction MySQL :

Exemple

-- Démarrer la transaction
START TRANSACTION;

-- Exécuter quelques instructions SQL
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- Déterminer s'il faut valider ou annuler
IF (Condition) THEN
    COMMIT; -- Valider la transaction
ELSE
    ROLLBACK; -- Annuler la transaction
END IF;

Exemple

Un exemple simple de transaction :

Test de transaction

mysql> use EXAMPLE; Base de données modifiée mysql> CREATE TABLE example_transaction_test( id int(5)) engine=innodb; # Créer une table Requête OK, 0 lignes affectées (0.04 sec) mysql> select * from example_transaction_test; Ensemble vide (0.01 sec) mysql> begin; # Démarrer la transaction Requête OK, 0 lignes affectées (0.00 sec) mysql> insert into example_transaction_test value(5); Requête OK, 1 lignes affectées (0.01 sec) mysql> insert into example_transaction_test value(6); Requête OK, 1 lignes affectées (0.00 sec) mysql> commit; # Valider la transaction Requête OK, 0 lignes affectées (0.01 sec) mysql> select * from example_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql> begin; # Démarrer la transaction Requête OK, 0 lignes affectées (0.00 sec) mysql> insert into example_transaction_test values(7); Requête OK, 1 lignes affectées (0.00 sec) mysql> rollback; # Annulation Requête OK, 0 lignes affectées (0.00 sec) mysql> select * from example_transaction_test; # Les données n'ont pas été insérées à cause du rollback +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql>

Exemple d'utilisation des transactions en PHP

Test de MySQL ORDER BY :

<?php $dbhost = 'localhost'; //adresse de l'hôte du serveur MySQL $dbuser = 'root'; //nom d'utilisateur MySQL $dbpass = '123456'; //Mot de passe de l'utilisateur MySQL $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Échec de la connexion :' . mysqli_error($conn)); } //Définir l'encodage pour éviter les caractères chinois corrompus mysqli_query($conn, "set names utf8"); mysqli_select_db( $conn, 'EXAMPLE' ); mysqli_query($conn, "SET AUTOCOMMIT=0"); //Défini pour ne pas valider automatiquement, car MySQL exécute immédiatement par défaut mysqli_begin_transaction($conn); //Début de la définition de transaction if(!mysqli_query($conn, "insert into example_transaction_test (id) values(8)")) { mysqli_query($conn, "ROLLBACK"); //Déterminer d'effectuer un rollback lorsque l'exécution échoue } if(!mysqli_query($conn, "insert into example_transaction_test (id) values(9)")) { mysqli_query($conn, "ROLLBACK"); //Déterminer d'effectuer un rollback en cas d'échec d'exécution } mysqli_commit($conn); //Exécuter la transaction mysqli_close($conn); ?>
Autres extensions