La mise à jour massive des données dans plusieurs tables liées est une tâche classique en programmation SQL. Avec le développement des applications commerciales, il est devenu nécessaire de mettre à jour de grands volumes de données simultanément tout en garantissant leur cohérence. Historiquement, on a utilisé des scénarios itératifs, ce qui entraînait de faibles performances et de longues verrouillages. Plus tard, des opérateurs DML avancés (comme MERGE), des constructions transactionnelles et des approches avec des tables de staging ont vu le jour.
Le problème réside dans le fait que la mise à jour des données concerne de nombreuses tables avec des relations (par exemple, les commandes et les détails de commande), ce qui risque de créer des « lignes orphelines » (orphan rows), une perte de performance due aux verrouillages et une charge imprévisible sur le SGBD.
La solution repose sur l'utilisation de transactions atomiques, d'opérations UPDATE/DELETE/MERGE avec des conditions JOIN, ainsi que le traitement par lots des données. Une bonne pratique consiste à différer les modifications agrégées dans des tables de staging temporaires, puis à les appliquer par batch via une transaction. Exemple pour SQL Server utilisant MERGE:
BEGIN TRANSACTION; -- Exemple de mise à jour massive de la table principale et de la table liée avec MERGE MERGE INTO orders AS tgt USING temp_order_updates AS src ON tgt.id = src.id WHEN MATCHED THEN UPDATE SET tgt.status = src.status, tgt.updated_at = src.updated_at; MERGE INTO order_details AS tgt USING temp_detail_updates AS src ON tgt.order_id = src.order_id AND tgt.sku = src.sku WHEN MATCHED THEN UPDATE SET tgt.price = src.price, tgt.qty = src.qty; COMMIT;
Caractéristiques clés :
Est-il possible d'effectuer simplement une mise à jour de la table principale, puis de manière séparée les tables liées, sans transactions, si les exigences de vitesse sont strictes ?
Des UPDATE séparés en dehors d'une transaction entraînent une terrible incohérence des données en cas d'erreur à l'une des étapes - par exemple, si les commandes ont été mises à jour mais pas les détails, la logique est violée. Dans les SGBD modernes, l'utilisation de transactions n'augmente presque pas les frais généraux lors du traitement par lots.
La performance va-t-elle chuter si l'on effectue une grande UPDATE avec une sous-requête ? Cela peut-il entraîner des verrouillages ?
Oui, les UPDATE monolithiques sur de grandes tables entraînent une escalade des verrouillages, des locks de tableau et des temps d'arrêt pour les autres utilisateurs. Il est préférable de diviser le traitement en lots avec une limitation via WHERE ... AND rownum/id/limit.
Exemple de lot :
UPDATE orders SET status = 'closed' WHERE status = 'pending' AND id BETWEEN 100000 AND 199999;
MERGE garantit-il l'atomicité et le bon ordre de traitement des tables liées ?
Non, MERGE fonctionne dans le cadre d'une seule table. Pour mettre à jour des tables liées, il faut un MERGE ou UPDATE distinct, et il est impératif de placer les deux actions dans une seule transaction.
Une entreprise a mis à jour le statut ("Terminé") dans un million de commandes avec des requêtes séparées en dehors d'une transaction : d'abord la table principale orders, puis les détails order_details. Sous charge, le serveur "s'effondrait" - en cas d'erreur, les détails restaient avec le statut "ouvert". Avantages :
Inconvénients :
Des tables de staging ont été mises en œuvre, et un traitement groupé à l'intérieur d'une transaction. D'abord, toutes les modifications étaient calculées et stockées dans des tables temporaires, puis les deux tables principales étaient mises à jour par lots. En cas d'échec - retour complet. Avantages :
Inconvénients :