Cours MariaDB / Vérifier les lignes avant UPDATE et DELETE
Dans MariaDB, UPDATE modifie les valeurs de lignes existantes et DELETE supprime les lignes qui correspondent à une condition. Avant d’écrire, examinez les mêmes critères WHERE, puis vérifiez le résultat dans une transaction InnoDB avant de choisir COMMIT ou ROLLBACK. Cette démarche limite les erreurs de portée, mais ne remplace pas une stratégie de récupération.
Définir la cible et le moment de validation
Les deux essais de cette leçon se terminent par ROLLBACK : le stock modifié et le produit supprimé sont rétablis sur une table indépendante. Vous apprendrez à vérifier avant de valider ; il ne s’agit pas d’une garantie de récupération pour des données de production. Une modification importante demande aussi des sauvegardes restaurables, des droits adaptés et une politique pour les écritures concurrentes.
| Élément à vérifier | Moyen | But |
|---|---|---|
| Serveur et base | Connexion et SELECT DATABASE(). |
Intervenir sur la bonne cible. |
| Moteur de stockage | SHOW CREATE TABLE. |
Utiliser InnoDB pour l’annulation de cet exercice. |
| Lignes concernées | SELECT avec les mêmes critères WHERE. | Contrôler les numéros uniques et les valeurs attendues. |
| Résultat | ROW_COUNT immédiatement, puis nouvelle lecture. | Vérifier le nombre de changements et les valeurs obtenues. |
| Fin de l’opération | COMMIT ou ROLLBACK. | Terminer la transaction de cette connexion. |
Un UPDATE ou DELETE sans WHERE vise toutes les lignes. Une confirmation à l’écran ou l’ajout de LIMIT ne définit pas, à lui seul, une cible correcte. La condition de l’écriture doit encore vérifier les critères appropriés au moment d’exécuter le changement. Le cours sur WHERE et ses regroupements permet de contrôler cette portée avant de modifier les données.
Préparer InnoDB et vérifier le comportement de la connexion
Connectez-vous au serveur de formation avec course_user, puis sélectionnez la base shopcourse. Le guide du client et des fichiers SQL UTF-8 explique la connexion et SOURCE. Exécutez le SQL dans le client MariaDB, pas dans l’invite de commandes du système.
Vérifiez SELECT VERSION(), DATABASE(); puis, dans shopcourse, SHOW TABLES LIKE 'products_18_lab';. Si cette table existe, inspectez sa définition et ses lignes avant toute suppression. La préparation utilise une table indépendante et laisse products ainsi que les autres exercices en place. Le compte doit pouvoir créer, remplir, lire, modifier et supprimer les données et la table de cet exercice.
Enregistrez le bloc suivant dans un fichier UTF-8 nommé lesson18-update-delete.sql. Exécutez SOURCE C:/mariadb-lab/lesson18-update-delete.sql; dans le client connecté, en adaptant le chemin au fichier réel.
USE shopcourse;
CREATE TABLE products_18_lab (
product_id INT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
note VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (product_id),
CONSTRAINT ck_p18_price CHECK (price >= 0),
CONSTRAINT ck_p18_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_18_lab
(product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
(2, '볼펜', '문구', 1500.00, 0, ''),
(3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
(4, '스탠드', '생활', 25000.00, 5, NULL),
(5, '파일', '문구', 1500.00, 50, ''),
(6, '케이블', '전자', 8000.00, 0, '호환 확인');
Les chaînes coréennes originales sont conservées pour vérifier le même jeu UTF-8. Les produits 1 à 6 sont un carnet, un stylo, un mug, une lampe, un dossier et un câble. Les catégories signifient papeterie, maison et électronique. Recopiez les littéraux tels quels et identifiez les produits par leur numéro unique.
Contrôlez ensuite la base, le mode d’autovalidation et le moteur réellement enregistré :
SELECT DATABASE() AS selected_database, @@autocommit AS autocommit_mode;
SHOW CREATE TABLE products_18_lab;
SHOW CREATE TABLE doit indiquer ENGINE=InnoDB. Même si autocommit_mode vaut 1, un START TRANSACTION explicite ouvre une transaction qui se termine par COMMIT ou ROLLBACK. Les instructions de chaque essai doivent rester dans la même connexion. Un outil qui ouvre une nouvelle connexion ou valide après chaque instruction ne permet pas d’appliquer directement cette séquence.
Terminez toute préparation de structure avant de commencer les essais. Vérifiez aussi qu’aucune transaction contenant un autre travail n’est en cours : START TRANSACTION n’est pas un mécanisme de transactions imbriquées.
Verrouiller le stock attendu et contrôler la modification
Le carnet numéro 1 commence avec un stock de 20. Nous allons retirer trois unités, consulter le résultat provisoire, puis annuler le changement. La première lecture montre la cible attendue ; la lecture FOR UPDATE la vérifie à nouveau dans la transaction.
SELECT product_id, product_name, stock FROM products_18_lab
WHERE product_id = 1 AND stock = 20;
START TRANSACTION;
SELECT product_id, product_name, stock FROM products_18_lab
WHERE product_id = 1 AND stock = 20 FOR UPDATE;
UPDATE products_18_lab SET stock = stock - 3
WHERE product_id = 1 AND stock = 20;
SELECT ROW_COUNT() AS changed_rows;
SELECT product_id, stock FROM products_18_lab WHERE product_id = 1;
ROLLBACK;
SELECT product_id, stock FROM products_18_lab WHERE product_id = 1;
| Étape | Résultat initial attendu |
|---|---|
| Lecture du numéro 1 avec stock 20 | Une ligne correspondant au carnet. |
| ROW_COUNT après UPDATE | changed_rows = 1. |
| Lecture dans la transaction | product_id = 1, stock = 17. |
| Lecture après ROLLBACK | product_id = 1, stock = 20. |
SET stock = stock - 3 calcule à partir de la valeur courante. Le WHERE impose à la fois le numéro unique et le stock attendu, 20. Si une autre écriture a modifié ce stock avant votre lecture verrouillante, vous pouvez ne plus retrouver la ligne avec ces critères. Arrêtez alors l’essai et terminez la transaction ; n’enlevez pas le contrôle de stock simplement pour obtenir une correspondance.
Dans la transaction, FOR UPDATE prend des verrous qui empêchent les écritures incompatibles sur les lignes concernées. Leur portée dépend des index, de la condition et du niveau d’isolation. La documentation de FOR UPDATE précise son utilisation avec InnoDB et les transactions ; la lecture verrouillante ne doit pas être isolée dans une connexion autovalidée différente.
Les verrous restent détenus jusqu’à la fin de la transaction. Évitez donc de laisser celle-ci ouverte pendant une longue attente de saisie humaine. Ce ciblage d’une clé primaire ne supprime pas tous les conflits ni les interblocages possibles : en cas d’erreur ou de délai dépassé, vérifiez la situation et terminez explicitement la transaction avant de décider d’une nouvelle tentative. La référence de UPDATE décrit les rôles de SET et WHERE.
Supprimer une seule ligne examinée et vérifier son retour
La cible suivante est le stylo numéro 2 dont le stock vaut zéro. Le câble numéro 6 a également un stock nul, mais il n’appartient pas à cette suppression. Gardez le numéro unique dans la condition au lieu d’élargir l’opération à tous les produits épuisés.
SELECT product_id, product_name, stock FROM products_18_lab
WHERE product_id = 2 AND stock = 0;
START TRANSACTION;
SELECT product_id, product_name, stock FROM products_18_lab
WHERE product_id = 2 AND stock = 0 FOR UPDATE;
DELETE FROM products_18_lab WHERE product_id = 2 AND stock = 0;
SELECT ROW_COUNT() AS deleted_rows;
SELECT COUNT(*) AS rows_inside_transaction FROM products_18_lab;
ROLLBACK;
SELECT COUNT(*) AS rows_after_rollback FROM products_18_lab;
Dans les données initiales, deleted_rows vaut 1, le nombre de lignes à l’intérieur de la transaction descend à 5, puis revient à 6 après ROLLBACK. Consultez aussi SELECT * FROM products_18_lab WHERE product_id = 2; pour vérifier que c’est bien le stylo qui est revenu. Le produit 6 doit toujours être présent.
DELETE retire les lignes choisies sans supprimer les colonnes ni la définition de la table. Il ne doit pas être confondu avec DROP TABLE. La documentation de DELETE précise notamment la portée d’une instruction sans WHERE. Ici, l’essai garde volontairement une condition étroite et annule sa modification.
Interpréter correctement zéro ligne affectée
L’essai suivant attribue au stock sa valeur actuelle, sans la changer :
START TRANSACTION;
UPDATE products_18_lab SET stock = stock
WHERE product_id = 1;
SELECT ROW_COUNT() AS changed_rows;
ROLLBACK;
Dans la connexion habituelle du client en ligne de commande, changed_rows vaut 0. Le produit existe, mais sa valeur n’a pas été modifiée. Zéro peut donc signaler une valeur identique, une absence de ligne ou un critère de valeur attendue qui ne correspond plus ; il faut relire la cible pour distinguer ces situations.
Certains connecteurs peuvent activer CLIENT_FOUND_ROWS et compter les lignes qui correspondent plutôt que les lignes effectivement modifiées. Vérifiez les options du pilote avant d’utiliser ce nombre comme preuve d’un changement. La référence de ROW_COUNT décrit cette différence.
Lisez ROW_COUNT immédiatement après l’écriture, avant une autre instruction qui changerait le résultat observé. Si le nombre ou les valeurs ne correspondent pas à votre plan, ne validez pas : annulez la transaction, puis repartez de la lecture avec les critères initiaux. Pour une opération distincte destinée à conserver les données, choisissez COMMIT après les contrôles et relisez l’état validé. Les essais présentés ici se terminent tous par ROLLBACK et ne conservent pas leurs modifications.
Reconnaître les limites de ROLLBACK et recommencer
ROLLBACK concerne le travail transactionnel non encore validé de cette connexion. Il ne remet pas automatiquement en place un changement déjà COMMIT, une écriture déjà autovalidée ou des modifications sur un moteur non transactionnel. La référence de ROLLBACK définit cette portée.
CREATE, DROP et TRUNCATE ont des frontières de définition de structure et de validation implicite. Ne les intercalez pas dans les essais d’annulation : une telle instruction peut valider une modification antérieure avant même sa propre exécution. La liste MariaDB des validations implicites permet de vérifier ces frontières.
Si un réglage de mises à jour protégées refuse une commande, examinez la condition de clé et la portée de l’instruction avant de désactiver une protection. Après une modification de production involontaire déjà validée, arrêtez les écritures supplémentaires et évaluez la récupération à partir des sauvegardes et journaux avec les responsables de la base. Appliquer simplement le calcul inverse ne garantit pas de rétablir l’état exact.
Pour refaire la leçon, contrôlez SELECT DATABASE();, SHOW CREATE TABLE products_18_lab; et les lignes présentes. Sauvegardez toute donnée utile et terminez les transactions d’essai. Seulement après avoir confirmé qu’il s’agit de la table jetable de cet exercice, supprimez-la explicitement :
DROP TABLE shopcourse.products_18_lab;
DROP TABLE supprime la structure et les données et ne peut pas être annulé par un simple ROLLBACK. Cette commande laisse shopcourse.products et les autres tables de formation intactes. Relancez ensuite le fichier complet pour retrouver les six produits et vérifier à nouveau chaque résultat provisoire puis son annulation.




