Générer ou vérifier code unique

**madric** · 30/08/2012, 09h54

Bonjour a tous,

J'utilise mysql innodb pour une application. Je debute avec mysql (avec les bases de donnees en general). Voici mon probleme:

J'ai deux connections independantes (dans des threads/processus differents) qui veulent rajouter une ligne dans une table. Pour rajouter une ligne, je dois au prealable generer un id unique sous forme de chaine de caracteres.

Avant de faire mon INSERT, je fais un select pour chercher un ID unique. Le problement est que si les 2 threads tentent de creer une nouvelle ligne en meme temps, j'ai un risque de creation du meme ID. Pour cela, je pense que je dois locker la table entiere avant de chercher le nouvel ID. Et je delock la table apres le INSERT.

Est-ce la bonne facon de faire?

Merci d'avance,

madric

**CinePhil** · 30/08/2012, 09h59

Si ton "id unique sous forme de chaine de caracteres" est la clé primaire de la table, c'est un mauvais choix !

Si c'est une clé alternative, significative pour le domaine (une référence, un code, un numéro de série structuré...), alors utilise un TRIGGER BEFORE INSERT. Ainsi, la transaction est maître de la table pour déterminer le code alphanumérique à créer et à intégrer dans la requête INSERT.

**madric** · 30/08/2012, 10h18

Merci pour ta reponse.

Je vais regarder le TRIGGER_BEFORE.
C'est effectivement une clef alternative.

J'utilise le driver ODBC pour mysql. J'utilise des transactions et je lis dans la documentation que LOCK TABLES n'est pas compatibles avec les transactions et que son utilisation principale est justement l'emulation de transactions. En quoi les transactions peuvent permettre de se passer des locks de tables? Je peux avoir deux transactions concurrentes.

**CinePhil** · 30/08/2012, 10h33

Tu vas soumettre une requête INSERT.
Le SGBD bloque la table pour traiter la requête.
Il remarque qu'il y a un TRIGGER BEFORE INSERT sur cette table et l'exécute, ce qui permet de calculer en toute sécurité la clé alternative à insérer tant que la table est bloquée pour cette transaction.
Une fois l'insertion effectuée, le SGBD libère la table pour l'autre processus qui patientait à la porte en se rongeant les ongles !

**madric** · 30/08/2012, 10h41

C'est realisable en passant avec le driver ODBC?

**CinePhil** · 30/08/2012, 10h43

Je pense que oui puisque tout se passe dans le serveur MySQL. ODBC ne fait que transmettre la requête et attendre le résultat. C'est comme une insertion normale sauf que le SGBD fait un boulot en plus qu'une simple insertion.

**madric** · 30/08/2012, 11h24

Sympa ce system de trigger.
Mais j'ai du mal a ecrire mon trigger...

Dans MySQL workbench, a ma table, dans le tab "Triggers", j'ai ecris:

Code :

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

1
2
3
4
5
6
7
-- Trigger DDL Statements
DELIMITER $$
 
USE `OSSITEDB`$$
 
CREATE TRIGGER CheckUniqueID 
BEFORE INSERT ON `PREFERENCE_SETS`

Ensuite ca se complique... Rien ne passe a la syntax. Je voudrais faire un truc du style:

Code :

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

1
2
SELECT COUNT(ID) INTO total FROM PREFERENCE_SETS WHERE ID='ID DANS LE INSERT" AND SEQSITE="SEQSITE DANS LE INSERT"
IF total > 0 THEN ANNULER LE INSERT

Je ne sais pas comment ecrire ca avec la bonne syntax, tout me semble refuser... Saurais tu comment faire? (je sais j'abuse, mais aucun des exemples que je trouve sur google ne correspond a ce que je veux).

Encore merci...

**madric** · 30/08/2012, 11h29

mumm, plus je regarde les triggers, plus je me demande si ca correspond a ce dont j'ai besoin. Peut-on vraiment annuler l'INSERT avec les triggers?

**CinePhil** · 30/08/2012, 11h34

J'ai un gros doute sur la nature de ton besoin !
Dans le premier message, tu disais ceci :

Pour rajouter une ligne, je dois au prealable generer un id unique sous forme de chaine de caracteres.

Maintenant, tu essaies de créer ce trigger :

Code :

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

CREATE TRIGGER CheckUniqueID

Tu veux créer l'identifiant ou juste vérifier qu'il n'existe pas déjà ?

mumm, plus je regarde les triggers, plus je me demande si ca correspond a ce dont j'ai besoin. Peut-on vraiment annuler l'INSERT avec les triggers?

Je ne suis pas un habitué des triggers mais effectivemetn il me semble avoir déjà rencontré ce genre de souci avec MySQL !

**madric** · 30/08/2012, 11h51

ok, je vais essayer d'expliquer plus clairement ce que je veux faire.

J'ai une table "PREFERENCES" qui a une clef primaire de type INTEGER en auto incrementation. Ensuite, les preferences sont rattachees a un utilisateur, j'ai une clef etrangere de type INTEGER qui y correspond. Ensuite, j'ai besoin d'un ID pour la preference, sous forme de chaine de caractere. Je veux que cet ID soit unique pour un utilisateur (un meme ID peut-etre utilise si les utilisateurs sont differents -> cle etrangere differente).

Ce que je fais, surement pas terrible mais en gros:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
START TRANSACTION
int index = 1; 
string id;
while (1) {
      id = "PREF%index"; (PREF1, PREF2, PREF3...)
      SELECT COUNT(ID) AS RESULT FROM PREFERENCES WHERE ID=id AND USERSEQ = x;
      if (result == 0) break;
      index++;
}
 
INSERT INTO PREFERENCES ID=id USERSEQ=x
COMMIT TRANSACTION

Voila, j'espere que c'est assez clair.
Je veux pouvoir etre sur que mon id soit unique. Si 2 threads executent ce bout de code en meme temps, je peux me retrouver avec un id identique.

L'idee avec le trigger serait alors ceci:

Code :

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

1
2
3
4
5
6
7
8
9
START TRANSACTION
int index = 1; 
string id;
while (1) {
      id = "PREF%index"; (PREF1, PREF2, PREF3...)
      if (INSERT INTO PREFERENCES ID=id USERSEQ=x) == SUCESS) break;
      index++;
}
COMMIT TRANSACTION

A moins que je ne me trompe completement, c'est comme ca que je voyais les choses.

**CinePhil** · 30/08/2012, 11h58

Si j'ai bien compris, tu veux que le couple {identifiant_utilisateur, id_preference} soit unique ?

La table "PREFERENCES" est-elle issue d'une association du MCD ? Autrement dit, est-ce une table associative ?
Si oui : fais du couple la clé primaire de la table.
Si non : ajoute simplement un index unique sur le couple de colonnes.

On peut avoir la structure de la table "PREFERENCES" et la description de l'association entre utilisateur et préférence ?

Quelques billets à lire :
- pour bien écrire les règles de gestion des données ;
- pour savoir s'il faut faire une table associative et comment la faire.

**madric** · 30/08/2012, 12h16

Je ne sais pas ce qu'est une table associative. Je vais lire tes liens.

Le truc en plus, c'est que l'ID peut-etre propose par le client, je ne peux donc pas me satisfaire d'une generation automatique.
Dans les deux propositions que tu me fais, il me semble que ca assure que la bdd refusera la creation d'un couple (user,id) si il existe deja. Mon probleme se trouve en amont, au niveau de la creation de l'id. A moins que tu veuilles me dire qu'il suffit de laisser la bdd le soin de tester ca a ma place. Ce qui reviendrait a un truc du syle:

Code :

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

1
2
3
4
5
6
7
8
9
START TRANSACTION
int INDEX = 1; 
string id;
while (1) {
      id = "PREF%index"; (PREF1, PREF2, PREF3...)
      IF (INSERT INTO PREFERENCES ID=id USERSEQ=x) == SUCESS) break;
      INDEX++;
}
COMMIT TRANSACTION

et donc la, pas besoin de trigger si je fais du couple (user,id) la clef primaire.

**madric** · 30/08/2012, 12h30

ok, j'ai compris ce qu'etait une table associative (enfin je crois).
Ici, ce n'est pas le cas.

Un utilisateur possede 0 a n preferences.
Une preference n'est detenu que par un seul utilisateur.

**madric** · 30/08/2012, 12h58

bon, je viens de tester avec un index unique sur les deux champs et ca marche. Mais ca m'embete quand meme de rajouter un index. Ca ne risque pas de ralentir les insertions? Parce que la, ce nouvel index ne me sert que pour assurer l'unicite du couple (user, id). Le trigger me paraissait plus sympa, si il y avait moyen d'annuler l'insertion sous condition...

**CinePhil** · 30/08/2012, 13h55

Les index sont au contraire se qui fait accélérer les requêtes !

**Fred_34** · 30/08/2012, 14h03

Je n'ai pas vraiment compris ce que tu souhaitais faire, et l'utilisation d'un index est probablement une meilleure solution, mais voici quand même un exemple de trigger qui aurait pu faire plus ou moins ce que tu voulais :

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
 
DELIMITER $$
 
USE `OSSITEDB`$$
 
CREATE TRIGGER `CheckUniqueID`
BEFORE INSERT ON `PREFERENCE_SETS`
FOR EACH ROW
BEGIN
    DECLARE RESULT INT;
	SET RESULT = (SELECT COUNT(ID)  FROM PREFERENCE_SETS WHERE ID=NEW.ID);
 
	IF (RESULT<>0) THEN
        SIGNAL sqlstate '45000' SET message_text = 'blah blah';
    END IF; 
END$$

**FSiebert** · 30/08/2012, 15h52

Envoyé par madric

Mais ca m'embete quand meme de rajouter un index. Ca ne risque pas de ralentir les insertions? Parce que la, ce nouvel index ne me sert que pour assurer l'unicite du couple (user, id). Le trigger me paraissait plus sympa, si il y avait moyen d'annuler l'insertion sous condition...

Ça sera probablement toujours plus rapide qu'un trigger qui va locker la table et faire une requête dessus

**ericd69** · 30/08/2012, 18h15

salut,

les trigger font partis de la transaction (voir le test) même si leur corps est défini aec un begin au début... par contre un begin dans le corps poserait un problème en innodb...

pour signal dans un trigger ça n'est autorisé que pour mysql 5.1.6

à tester donc

**madric** · 31/08/2012, 12h46

Un grand merci pour toutes vos reponses. J'ai appris pas mal de choses interessante.

J'essaye maintenant une autre methode pour arriver a mes fins. Celle que j'ai trouve sur la documentation de MySql ici:

http://dev.mysql.com/doc/refman/5.0/...ing-reads.html

Je fais des tests avec la commande "SELECT FOR UPDATE" et bizarrement MySQL me lock la table entierement plutot que de me locker juste la ligne selectionnee. J'utilise le moteur innodb et d'apres la documentation, ca ne devrait pas etre le cas. J'ai vu d'autres personnes sur differents forums soulever le meme probleme, sans trouver aucune explication ou solution. Ca me parait trop gros pour etre un bug. Auriez vous des infos la dessus?

Merci encore pour toutes vos reponses.