Discussion :

[Tanebisse]

Bonjour,
On dit toujours qu'il faut gérer les règles métier sur la couche métier. Mais peut-on quand même utiliser la puissance des BDD pour gérer la suppression en cascade grâce à "ON DELETE CASCADE".
Exemple : quand je supprime une instance d'un objet voiture je supprime aussi ses 4 roues.
Merci d'argumenter avec des références.
Est-ce une bonne ou une mauvaise pratique ? Pourquoi ?

Cordialement.

[SQLpro]

Bonjour,
On dit toujours qu'il faut gérer les règles métier sur la couche métier. Mais peut-on quand même utiliser la puissance des BDD pour gérer la suppression en cascade grâce à "ON DELETE CASCADE".
Exemple : quand je supprime une instance d'un objet voiture je supprime aussi ses 4 roues.
Merci d'argumenter avec des références.
Est-ce une bonne ou une mauvaise pratique ? Pourquoi ?

Cordialement.

Généralement NON, particulièrement oui.

Par exemple si les filles sont systématiquement liées, en lien immuable et en petit nombre => OUI,
si les filles peuvent être déplacées des références et en grand nombre non.

Mais il existe d'autres techniques comme SET DEFAULT ou SET NULL qui permettent d'arriver au même résultat via des traitements déportés en batch à condition d'utiliser des vues.

A +

[Tanebisse]

Je n'ai pas bien compris ta réponse "si les filles peuvent être déplacées des références " ni la technique de "SET DEFAULT ou SET NULL".
La vraie question que je pose c'est faut-il systématiquement gérer ça en JAVA ou autres (couche métier) ou peut-on utiliser des DELETE CASCADE. Pourquoi c'est bien ou pourquoi c'est mal ?

[Tanebisse]

Mais il existe d'autres techniques comme SET DEFAULT ou SET NULL qui permettent d'arriver au même résultat via des traitements déportés en batch à condition d'utiliser des vues.

OK j'ai vu pour SET DEFAULT ou SET NULL par contre pourquoi des batchs ou traitement déportés ?

[SQLpro]

Je n'ai pas bien compris ta réponse "si les filles peuvent être déplacées des références " ni la technique de "SET DEFAULT ou SET NULL".
La vraie question que je pose c'est faut-il systématiquement gérer ça en JAVA ou autres (couche métier) ou peut-on utiliser des DELETE CASCADE. Pourquoi c'est bien ou pourquoi c'est mal ?

Aucune couche de programmation ne peut simuler l'équivalent des contraintes de bases de données à l'exception des contraintes de domaines.
Il est impossible à un programme JAVA ou C d'effectuer ce travail de fait de deux problématique :

• le fonctionnement naturellement ensemblistes des SGBDR
• la problématique de gestion des accès simultanés concurrentiels.

Donc, non, vous n'obtiendrez jamais d'équivalent en JAVA à ce que fait nativement un SGBDR !

A +

[SQLpro]

OK j'ai vu pour SET DEFAULT ou SET NULL par contre pourquoi des batchs ou traitement déportés ?

SET DEFAULT et SET NULL vont présenter de fausses informations puisqu'un client par défaut (par exemple Âne Onyme) comme un client NULL c'est pas terrible pour une facture !

Dans ce cas on présente aux applications des vues qui éliminent les fausses lignes (effacement logique).

La nuit, aux heures creuses, on lance les bach de traitement de purge effective qui vont supprimer les "fausses" ligne par lot de 500 par exemple.

A +

[StringBuilder]

Pour faire simple, si un DELETE CASCADE a pour effet de supprimer quelques dizaines de lignes dans une poignée de tables, l'impact sur le moteur de base de données sera relativement faible.
En revanche, s'il porte sur des millions de lignes dans des centaines de tables, là, pour faire tenir tout ça dans une transaction, ça va poser des locks un peu partout, avec le risque de deadlocks (être bloqué par une autre transaction qui elle-même est bloquée par la nôtre) et risque d'effrondrer les performances du SGBD, voir même de le bloquer.

En alternative aux valeurs "virtuelles" que propose SQL Pro, vous pouvez, depuis votre application cliente, gérer la suppression de toutes les filles connues au moment de la suppression, avant de terminer par un DELETE CASCADE pour shooter les quelques lignes qui auraient pu passer entre les mailles (en raison des traitements concurrents).

Sinon, en plus simple, vous pouvez apposer un flag sur vos tables, que vous pourrez propager par trigger au besoin : ces lignes seront écartées des traitements applicatifs, et vous aurez tout le loisir de les supprimer par lot, sans risque d'accès concurrent, à un moment plus calme.

[Tanebisse]

Il est impossible à un programme JAVA ou C d'effectuer ce travail de fait de deux problématique :

En java je fais 2 actions

1. je supprime les roues
2. je supprime la voiture

le problème c'est que si je dois gérer en plus la suppression des pneus et des enjoliveurs, ça fait beaucoup de code pour pas grand chose alors qu'avec un DELETE CASCADE c'est beaucoup plus simple.
J'ai du me tromper de catégorie pour ma question car c'est plus une question de développeur que de DBA.
Mais merci pour les tuyaux.

[Tanebisse]

Merci StringBuilder pour tes précisions.

[SQLpro]

En java je fais 2 actions

1. je supprime les roues
2. je supprime la voiture

le problème c'est que si je dois gérer en plus la suppression des pneus et des enjoliveurs, ça fait beaucoup de code pour pas grand chose alors qu'avec un DELETE CASCADE c'est beaucoup plus simple.
J'ai du me tromper de catégorie pour ma question car c'est plus une question de développeur que de DBA.
Mais merci pour les tuyaux.

Et si votre code Java tombe en panne au milieu vous faites quoi ?????

A +

[julien94320]

Et si votre code Java tombe en panne au milieu vous faites quoi ?????

Il remonte la voiture ... j'ai bon ?

Plus sérieusement :

Effectivement la gestion des transactions n'est pas gérer du tout de la même manière en java/C/C# par rapport au moteur de base de données qui lui peut faire un rollback a tout moment si la transaction ne va pas au bout.

Comme expliqué prenez le temps d'étudier tous les risques/bénéfices d'utiliser cette fonctionnalitée

Si j'avais a résumer ça serai le cas suivant :

• Suppression d'un perimètre restrein sur des données pas trop utilisé a l'aide d'accès concurent => bonne idée
• Suppression de données en masses avec beaucoup d'accès concurrent => cherchez une solution altérnative de bonnes idées ont déjà été fournies en retour d'experience

A+

[François DORIN]

Bonjour,

Pour ma part, pour réaliser la suppression d'entités complexes (comprenant plusieurs liaisons), j'utilise des procédures stockées, avec ROLLBACK en cas d'erreur. Ainsi, j'ai toujours des données cohérentes.

Mais pour l'instant, je n'ai jamais eu à mettre en place des suppressions de masse. A chaque fois, c'est une suppression d'une entité sur une demande express d'un utilisateur.

[StringBuilder]

Le problème de la procédure stockée, c'est que sans apposer de lock, tu as les mêmes risques qu'avec Java : entre le moment où tu supprimes des données liées et le moment où tu supprimes la donnée mère, une nouvelle donnée liée peut avoir été créée.

De la même manière, si la procédure stockée doit supprimer des millions de lignes, la transaction risque de devenir gigantesque, pénalisant fortement le serveur (et augmentant le risque de dead locks).

A mon sens, seule une partie doit être "transactionnelle" : le flag initial indiquant que la donnée mère (et ses filles) ne doivent plus être utilisées.

Ensuite, tu peux supprimer tranquillement les données liées : d'un point de vue logique, les autres transactions savent qu'il ne faut pas créer de nouvelles filles.

Reste plus qu'à faire à la fin un petit cascade sur la suppression de la mère, au cas où une transaction débutée avant notre changement de flag ait ajouté une ligne après nos suppressions. Mais à ce moment le cascade ne porte plus que sur quelques lignes isolées "créées par erreur" (normalement impossible si on gère proprement le flag, par exemple avec des controles par trigger).

[François DORIN]

Je suis d'accord avec toi. C'est bien pour cela que j'ai précisé que d'une part, il faut une transaction, et que d'autre part, je n'ai utilisé cela que dans le cadre de suppression de quelques lignes

La seule suppression de masse que j'ai eu à faire pour l'instant, c'était dans le cadre d'une maintenance. Donc, un oneshot pendant lequel l'application avait même été stoppée momentanément pour éviter tout soucis

[Tanebisse]

Et si votre code Java tombe en panne au milieu vous faites quoi ?????

C'est géré en Java par exemple avec le Framework Spring MVC on englobe le tout dans une transaction grâce à l'annotation @Transactional. Derrière ça crée une transaction SQL.

[SQLpro]

C'est géré en Java par exemple avec le Framework Spring MVC on englobe le tout dans une transaction grâce à l'annotation @Transactional. Derrière ça crée une transaction SQL.

Donc c'est bien une transaction faite sur le serveur et non par Java...
Autrement dit vous commencez par envoyer la commande BEGIN TRANSACTION par le biais de l'appli java...

• Java envoie la commande BEGIN transaction (10 µs)
• trame d'envoi java vers SQL : 10 ms
• Le serveur sql traite la commande (10 µs de traitement dans le serveur SQL)
• Trame de retour server sql vers Java (10 ms)
• Java renvoie la première commande de mise à jour (10 µs de traitement du code)
• trame d'envoi java vers SQL : 10 ms
• Traitement au niveau du serveur SQL : 25 µs pour effectuer la mise à jour --> Le serveur pose un verrou sur la table
• trame de retour vers Java : 10 ms
• Java renvoie la seconde commande de mise à jour (10 µs de traitement du code)
• trame d'envoi java vers sql : 10 ms
• Traitement au niveau du serveur SQL : 25 µs pour effectuer la seconde mise à jour --> Le serveur pose un second verrou sur la seconde table
• trame de retour vers Java : 10 ms
• analyse de la valeur de retour et renvoie par java de la commande COMMIT ou ROLLBACK (10 µs)
• trame d'envoi java vers sql : 10 ms
• Le serveur SQL traite la commande (15 µs) --> Les verrous sont relâchés
• Trame de renvoi de SQL Vers java (10 ms)
• Traitement JAVA pour voir si tout c'est bien passé (10 µs)

Vous avez donc consacré 80 ms au réseau, 50 µs dans Java et 75 sur le serveur SQL, soit au total : 80125 µs

Si vous aviez fait une procédure stockées, la séquence serait alors la suivante :

• Java envoie la commande EXCUTE PROCEDURE ... (10 µs)
• trame d'envoi java vers SQL : 10 ms
• Le serveur sql traite la procédure dns son intégralité (75 µs de traitement dans le serveur SQL)
• Trame de renvoi de SQL Vers java (10 ms)
• Traitement JAVA pour voir si tout c'est bien passé (10 µs)

Si vous avez une intégrité référentielle, le code est encore plus rapide parce que c'est pas du code interprété (cas du SQL et du Transact Sql) mais du C... (même pas du C++ !)

Vous avez donc consacré 20 ms au réseau, 10 µs dans Java et 75 sur le serveur SQL, soit au total : 20085 µs, soit 4 fois moins...

Pire...

• Dans votre solution sans procédure, vous avez verrouillé la première table pendant 40 ms + 45 µs
• Dans la solution procédurale, vous avez verrouillé la première table pendant au plus, la durée d'exécution de la procédure soit 75 µs.

Vous avez donc mobilisé la table 500 fois plus longtemps !!!
Autrement dit, vous aurez dans votre base 500 fois moins de concurrence avec votre merveilleuse approche Java par rapport à une procédure stockée !!!

Qu'en concluez vous ???

A +

[StringBuilder]

Dans tous les cas, utiliser une procédure stockée et/ou une transaction depuis Java pour remplacer un DELETE CASCADE sous prétexte que ce dernier appose une transaction potentiellement dangereuse, je vois pas l'intérêt : dans tous les cas, ce sera pire !

D'où l'unique solution viable selon moi, pour une grande masse d'information :
1/ flaguer les données parentes (d'une manière ou d'une autre) afin d'avoir la garantie qu'elles ne sont plus utilisée dans la couche applicative. (une unique transaction sur quelques lignes d'un seule table)
2/ supprimer les données liées sans besoin de transaction (si ça échoue, on s'en fout, puisque les données parentes -et donc toutes les données filles- ne sont plus utilisables dans l'application)
3/ delete cascade sur les données parentes, afin de s'assurer que des données filles créées par des transactions concurrentes ne viennent pas nous empêcher de faire la suppression

=> On y gagne sur tous les plans, d'autant que les étapes 2 et 3 peuvent être traitées à des moment totalement différents de la première, qui est plus rapide qu'une suppression.

[François DORIN]

Dans tous les cas, utiliser une procédure stockée et/ou une transaction depuis Java pour remplacer un DELETE CASCADE sous prétexte que ce dernier appose une transaction potentiellement dangereuse, je vois pas l'intérêt : dans tous les cas, ce sera pire !

Tout d'abord, il faut que tu m'expliques "pourquoi dans tous les cas ce sera pire" ? Et que signifie "pire" ? D'un point de vue performance ? D'un point de vue mise en place ? D'un point de vue maintenance ? Tout est relatif

Ensuite, l'intérêt que je vois, c'est la gestion de la suppression qui est plus simple (moins intrusive à mettre en oeuvre et en maintenance) avec une procédure stockée ou une transaction depuis Java qu'avec ce que tu proposes :

• tu ne modifies pas le schéma de ta bd pour pouvoir gérer la suppression ;
• tu n'as pas de trigger ;
• tu n'as pas à réaliser une routine de nettoyage la nuit ou durant les périodes creuses ;
• toute la logique de suppression est située dans un seul et même espace clos (sinon, il faut aller voir ET le schéma, ET les triggers, ET la routine de nettoyage, sans compter qu'il faut tenir compte du flag dans l'applicatif)

[StringBuilder]

Tout d'abord, il faut que tu m'expliques "pourquoi dans tous les cas ce sera pire" ? Et que signifie "pire" ? D'un point de vue performance ? D'un point de vue mise en place ? D'un point de vue maintenance ? Tout est relatif

Avec un DELETE CASCADE, tu es certain que :
1/ Les verrous ne sont posés que le strict temps nécessaire de la suppression (transaction implicite unitaire)
2/ En cas de changement du modèle des données (ajout de tables filles, etc.) ça marchera toujours à coup sûr
3/ Parallélisation possible des suppressions, contrairement à la procédure ou un programme Java qui va exécuter séquentiellement les ordres de suppression des lignes dans les tables filles (une table à la fois)

Ensuite, l'intérêt que je vois, c'est la gestion de la suppression qui est plus simple (moins intrusive à mettre en oeuvre et en maintenance) avec une procédure stockée ou une transaction depuis Java qu'avec ce que tu proposes :

• tu ne modifies pas le schéma de ta bd pour pouvoir gérer la suppression ;
• tu n'as pas de trigger ;
• tu n'as pas à réaliser une routine de nettoyage la nuit ou durant les périodes creuses ;
• toute la logique de suppression est située dans un seul et même espace clos (sinon, il faut aller voir ET le schéma, ET les triggers, ET la routine de nettoyage, sans compter qu'il faut tenir compte du flag dans l'applicatif)

• La technique proposée par SQL Pro, à savoir modifier l'ID (en le multipliant par -1 par exemple) évite justement de modifier le modèle des données
• Le DELETE CASCADE utilise les clés étrangères, il n'a pas besoin de trigger
• La routine de nettoyage pendant la nuit, dans ton programme JAVA (ou procédure stockée), tu l'as : c'est la routine que tu souhaite écrire. Il faut juste la rendre asynchrone et générale (supprimer toutes les lignes d'ID négatif par exemple), et éventuellement la déclencher à un autre moment
• Non justement : un DELETE sur une table, c'est quand même plus simple qu'une procédure stockée ou routine Java qui va devoir être mise à jour en fonction du modèle des données...

[François DORIN]

Avec un DELETE CASCADE, tu es certain que :
1/ Les verrous ne sont posés que le strict temps nécessaire de la suppression (transaction implicite unitaire)
2/ En cas de changement du modèle des données (ajout de tables filles, etc.) ça marchera toujours à coup sûr
3/ Parallélisation possible des suppressions, contrairement à la procédure ou un programme Java qui va exécuter séquentiellement les ordres de suppression des lignes dans les tables filles (une table à la fois)

Merci. Là c'est bien argumenté et je comprends beaucoup mieux ce que tu voulais dire.

• La technique proposée par SQL Pro, à savoir modifier l'ID (en le multipliant par -1 par exemple) évite justement de modifier le modèle des données
• Le DELETE CASCADE utilise les clés étrangères, il n'a pas besoin de trigger
• La routine de nettoyage pendant la nuit, dans ton programme JAVA (ou procédure stockée), tu l'as : c'est la routine que tu souhaite écrire. Il faut juste la rendre asynchrone et générale (supprimer toutes les lignes d'ID négatif par exemple), et éventuellement la déclencher à un autre moment
• Non justement : un DELETE sur une table, c'est quand même plus simple qu'une procédure stockée ou routine Java qui va devoir être mise à jour en fonction du modèle des données...

Alors, sans vouloir t'offenser, tu fais preuve d'un peu de mauvaise fois. Tu n'as pas arrêté de nous parler d'une suppression logique via un flag avec propagation possible par trigger si besoin est avant de réaliser la suppression physique des données lors d'une période creuse. Et là, tu ne te cantonnes qu'à la suppression physique des données. Dès lors que suppression logique et physique des données sont dissociées, la mise en oeuvre et la maintenance de la suppression est beaucoup plus compliquée qu'un simple DELETE (avec ou pas ON CASCADE). Il ne faut donc réserver cela que lorsque cela s'avère nécessaire.

Ton modèle, que tu utilises des flag, des valeurs par défaut ou des NULLs, etc... est forcément impacté par tes choix (tu rajoutes une colonne, tu relâches une contrainte en autorisant les NULL, etc...). Et après, ton application doit tenir compte de cela. Si les choses sont bien faites, tu peux le faire via des vues (modèle encore impacté), ou si les choses sont mal faites, dans l'application elle-même (par exemple, ajout de clause WHERE pour filtrer les lignes supprimées). Quand je réalise un schéma, il est vrai que je me pose toujours la question de savoir quelles seront les requêtes exécutées, mais requête de sélection, voire mise à jour. Je ne me pose pas la question pour la suppression car cela n'a jamais été un soucis jusqu'à présent (mais je n'ai jamais eu à gérer une suppression de masse, hors maintenance pour cause d'évolution).

Pour en revenir à ta question initial, qui était savoir quel est l'intérêt de ne pas suivre ta procédure différenciant suppression logique/physique, j'espère avoir répondu à la question. De toutes les façons, comme toujours, "LA solution miracle"n'existe pas (sinon, on ne serait pas là avec plein de boulot ) mais il y a toujours un compromis à faire entre différents besoins qui ne vont souvent pas dans le même sens...