Discussion :

[madric]

Bonjour!

Je suis debutant dans les bases de donnees et je n'arrive pas vraiment a comprendre quel est le meilleur moyen de synchroniser les ecritures dans une base de donnees.

Je pars sur un cas simple avec deux tables:
- la table "GROUPS" qui definit des groupes d'utilisateurs
- la table "ROLES" qui definit les roles des utilisateur.

A un groupe peut etre assigne un role ou aucun role.
J'ai une clef etrangere dans la table "ROLES" qui pointe sur la table GROUPS.
Les deux tables possedent une clef primaire de type integer auto-incrementee.

Voila la situation que je ne sais comment gerer correctement:

J'ai deux transactions concurrente en cours, la premiere modifie le groupe "groupe1" en lui assignant le role "administrateur" et la deuxieme detruit le role "administrateur".

Ce que j'ai ecrit et qui m'amene un dead lock potentiel:

Transaction 1:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
BEGIN TRANSACTION
SELECT FROM GROUPS WHERE SEQ=SEQGROUP1 FOR UPDATE => Exclusive lock sur le groupe vise
SELECT FROM ROLE WHERE SEQ=SEQADMIN FOR SHARE => Share lock sur le role
UPDATE GROUPS SET ROLESEQ=SEQADMIN WHERE SEQ=SEQGROUP1
COMMIT

Transaction 2:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
BEGIN TRANSACTION
SELECT FROM ROLES WHERE SEQ=SEQADMIN FOR UPDATE => Exclusive lock sur le role
UPDATE GROUPS SET ROLESEQ=NULL WHERE ROLESEQ=SEQADMIN => Exclusive lock
DELETE FROM ROLES WHERE SEQ=SEQADMIN
COMMIT

Je prefere ne pas utiliser de table lock ni utiliser le mode d'isolation SERIALIZABLE des transactions.
Quelle est la bonne methode pour synchroniser ce ca simple?

Merci d'avance,

madric

[CinePhil]

A un groupe peut etre assigne un role ou aucun role.

La règle de gestion est incomplète.
En létat, elle permet de dessiner ce morceau de MCD où il manque les cardinalités du côté "role" :
groupe -0,1----assigner----?,?- role

=> Combien de groupe(s) un rôle peut-il assigner ?

J'ai une clef etrangere dans la table "ROLES" qui pointe sur la table GROUPS.

Ceci veut dire qu'un role assigne un groupe et un seul et qu'on a donc le MCD suivant :
groupe -0,1----assigner----1,1- role

Est-ce juste ?
J'en doute quand je vois vos requêtes puisque vous mettez à jour la table GROUPS et non pas la table ROLE.

Donnez la structure exacte de vos tables et précisez bien quelle est la règle de gestion de cette association entre groupe et rôle avant que nous puissions répondre à votre question.

J'ai deux transactions concurrente en cours, la premiere modifie le groupe "groupe1" en lui assignant le role "administrateur" et la deuxieme detruit le role "administrateur".

Il y en a forcément une qui démarre avant l'autre et prend la priorité.

Associées aux clés étrangères existent les contraintes d'intégrité, et notamment ON DELETE CASCADE ou RESTRICT (qui à mes yeux devraient être les seules utilisées).

[madric]

Merci de votre retour.

Un role peut-etre assigne a zero, un ou plusieurs groupes.

Voici via un script mysql la creation des tables (j'utilise ces memes tables sur postgresql et MSSQL):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
 
CREATE  TABLE IF NOT EXISTS `OSSITEDB`.`ROLES` (
  `SEQ` INT NOT NULL AUTO_INCREMENT ,
  `ID` VARCHAR(45) NOT NULL ,
  `NAME` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`SEQ`) ,
  UNIQUE INDEX `Seq_UNIQUE` (`SEQ` ASC) ,
  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) ,
ENGINE = InnoDB;
 
CREATE  TABLE IF NOT EXISTS `OSSITEDB`.`GROUPS` (
  `Seq` INT NOT NULL AUTO_INCREMENT ,
  `ID` VARCHAR(45) NOT NULL ,
  `Name` VARCHAR(45) NOT NULL ,
  `SeqRole` INT NULL ,
  PRIMARY KEY (`Seq`) ,
  UNIQUE INDEX `Seq_UNIQUE` (`Seq` ASC) ,
  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) ,
  INDEX `fk_GROUPS_ROLES1_idx` (`SeqRole` ASC) ,
  CONSTRAINT `fk_GROUPS_ROLES1`
    FOREIGN KEY (`SeqRole` )
    REFERENCES `OSSITEDB`.`ROLES` (`SEQ` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
ENGINE = InnoDB;

A un groupe peut-etre assigne zero ou un seul role.
Un meme role peut etre assigne a zero, un ou plusieurs groupe.
Lorsqu'un groupe est detruit, son role eventuel ne doit pas l'etre.
Si un role est detruit, les groupes le possedant ne doivent pas l'etre.

Je ne pense pas avoir de problemes mes tables.

Merci encore.

[CinePhil]

Il me semblait bien que la clé étrangère ne devait pas être dans la table ROLES !

Un role peut-etre assigne a zero, un ou plusieurs groupes.
A un groupe peut-etre assigne zero ou un seul role.

MCD qui en découle :
role -0,n----assigner----0,1- groupe

Comme je l'explique dans mon blog, une telle association devrait engendrer la création d'une table associative pour éviter la prolifération du bonhomme NULL.
Une clé étrangère nulle, c'est anti-relationnel et contre-performant.
Bref...

Lorsqu'un groupe est detruit, son role eventuel ne doit pas l'etre.
Si un role est detruit, les groupes le possedant ne doivent pas l'etre.

La contrainte ON DELETE NO ACTION (c'est vrai que je l'avais oubliée celle-là !) va bloquer la suppression du rôle si celui-ci est encore affecté à un groupe.

Donc en gros le modèle est bon mais pourquoi faire un truc aussi compliqué pour affecter un rôle à un groupe ?

la premiere modifie le groupe "groupe1" en lui assignant le role "administrateur"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
UPDATE GROUPS
SET SeqRole =
(
	SELECT SEQ
	FROM ROLES
	WHERE NAME = 'Administrateur'
)
WHERE Seq = 1

la deuxieme detruit le role "administrateur"

Comme dit plus haut, la contrainte ON DELETE NO ACTION va bloquer cette suppression.

[madric]

Plutot d'accord avec la table associative. Je vais revoir mon design.

En attendant, je voudrais bien comprendre ce qui se passe en utilisant le design actuel:

La premiere transaction doit assigner le role "administrateur" (clef primaire 5) au groupe 1 (clef primaire 1).

La deuxieme transaction doit supprimer le role "administrateur" (clef primaire 5).

Les transactions peuvent donc s'ecrire:

Transaction 1:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT SEQ FROM ROLES WHERE ID = 'ADMINISTRATEUR' FOR SHARE
UPDATE GROUPS SET SEQROLE = PREVIOUS_RESULT WHERE SEQ = 1

Transaction 2:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
UPDATE GROUPS SET SEQROLE = NULL WHERE SEQROLE = 5
DELETE FROM ROLES WHERE SEQROLE=5;

Pour la transaction 1, je prefere le faire en deux lignes car dans certains cas, je dois faire un recherche par ID, dans d'autre j'ai deja la valeur de la clef.
Le SELECT FOR SHARE me garantie que le role "Administrateur" ne sera pas detruit par une autre transaction avant que je ne l'affecte au groupe.

Si je ne m'abuse, j'ai bien ici un risque de dead lock non? Si oui, comment faire ca proprement. J'aimerais bien aussi (je ne l'ai pas ecris) qu'il n'y ait pas de risque dans la transaction 1 que le groupe soit detruit par une autre transaction et dans la transaction 2 que le meme role ne soit pas detruit dans une autre transaction concurrente.


Sinon pour revenir sur le bon design (rajouter une table associative), j'imagine que la table associative comportera deux colonnes (GROUPSEQ, ROLESEQ). Est-ce que les deux colonnes doivent etre definies comme des cles etrangeres?

Encore merci.

[CinePhil]

Je trouve vraiment dangereux de faire ainsi, vis à vis des utilisateurs.

Imaginons que dans le backend de l'application qui utilise la BDD, seuls les administrateurs peuvent attribuer des rôles aux groupes.
Vous supprimez le rôle administrateur en BDD via votre procédure transactionnelle en deux phase (SET NULL sur la clé étrangère puis suppression du rôle) et plus aucun utilisateur de l'appli ne peut gérer les droits !

En plus, cela pollue la BDD avec NULL. Si fsmrel passe par là, il va tirer à vue ! En plus, la chasse vient d'ouvrir !

Je préfère largement que l'appli prévoie la gestion de l'erreur retournée par le SGBD en cas de viol de la contrainte d'intégrité. Ainsi, si l'utilisateur ou un DBA peu consciencieux ou une procédure lancée par un développeur pour test... tente de supprimer le rôle et qu'il y a encore au moins un groupe auquel est assigné ce rôle, le SGBD dit "Halte là !" et c'est à l'appli ou à l'utilisateur indélicat du SGBD de gérer l'erreur... ou de se rendre compte qu'il est en train de faire une connerie !

On ne transige pas avec la qualité des données !

Si je ne m'abuse, j'ai bien ici un risque de dead lock non?

À mon avis non puisque vous gérez ça dans une transaction du SGBD.

Sinon pour revenir sur le bon design (rajouter une table associative), j'imagine que la table associative comportera deux colonnes (GROUPSEQ, ROLESEQ). Est-ce que les deux colonnes doivent etre definies comme des cles etrangeres?

Tu n'as pas lu mon billet de blog jusqu'au bout !

Cas 03) A -0,1—-associer—-0,n- B
Aucune des deux entités n’est systématiquement associée à l’autre (cardinalités minimales à 0). Il faut donc une table associative dont la clé primaire sera, pour la même raison que dans le cas 04, la clé étrangère référençant A.
...
03) et 04) Là aussi, la clé primaire n’est constituée que d’une seule des deux clés étrangères : celle référençant A. L’autre est une clé étrangère « ordinaire ».

[madric]

J'ai peur de ne pas tout comprendre.

Restons sur le design actuel et passons sur la pollution des NULL.
Si un administrateur veut detuire un rôle, il faut bien que tous les groupes qui ont ce rôle soit modifies et mettre leur rôle a NULL. Puis detruire le rôle.

Si on ne fait pas ça en 2 étapes, comment le faire en une seule? Que le rôle de l'utilisateur actuel ne puisse pas être détruit, c'est un autre problème je pense.

Pour le deadlock, j'ai fait des tests et j'arrive bien a un deadlock en synchronisant bien les transactions.

C'est qui fsmrel? Pour les NULL je suis d'accord, c'est pas top mais une table en plus pour éviter les nulls, c'est pas moins performant?

[madric]

Bon, apres mure reflexion, je pense qu'on est sur deux strategies differentes.

A moins de me tromper (et je m'en excuse alors d'avance):

Vous considerez qu'il est preferable de ne rien locker soit meme et de laisser le SGBD generer des erreurs et de traiter ces erreurs par la suite. Le point positif est que c'est simple a gerer, quasi rien a faire. Mais le point negatif est le traitement de l'erreur dont le code peut probablement varier d'un SGBD a l'autre peut etre penible a decripter si on veut renvoyer une erreur lisible a l'utilisateur (pas un message du type "la cle etrangere bla bla bla").

Ma strategie est differente, je prefere locker mes donnees a l'avance pour etre sur que mes UPDATE ou DELETE se passeront correctement. L'avantage est que je peux savoir le type d'erreur avant que le SGBD ne m'en revoit une (sauf celle de pb de connection ou autre), donc traitement facile et surtout independant du SGBD. En fait, cette facon de faire revient a assurer l'integrite des donnees a la place du SGBD et on pourrait meme se passer alors des cles etrangeres.

Mais dans les deux cas, il faut faire attention a l'ordre des operations pour eviter les risques de dead locks.