Discussion :

[switchON]

Bonjour à tous, je poste ce sujet ici, mais j'aurais surement pu le créer plutôt dans la forum postgreSQL ? Excusez moi d'avance si je suis un peu hors sujet.

Je n'ai vraiment pas réussi à trouver une réponse définitive à mon problème.
Je développe dans le cadre de mon stage une application Python, destinée à tourner comme un service windows, qui interroge et remplit une base de données postgreSQL.

J'ai déjà développé plusieurs outils en Python, et je tiens à faire les choses bien (respect des normes PEP8, utilisation de classes organisées, aussi, si vous voyez un problème, n'hésitez pas à me faire vos remarques).

Je me demandais donc comment gérer des accès à la base de données de multiples clients simultanés (cas que je ne rencontre pas vraiment, mais je veux quelque chose de propre et évolutif), et ce dans un cas bien particulier.

Je dispose de plusieurs methodes sur ce modèle :

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
 
 def add_message(self, message):
        try:
            self.cursor.execute("""SELECT * 
                FROM messages 
                WHERE content = %s""", (message,))
 
            rows = self.cursor.fetchall()
            if len(rows) == 0:
                self.cursor.execute("""INSERT INTO messages (content) 
                    VALUES (%s) returning ID""", (message,))
                id_message = self.cursor.fetchone()[0]
            else:
                id_message = rows[0][0]
 
            if len(rows) > 1:
                logging.warning("Found several messages occurrences in "
                                "the dataBase for : " + message)
 
            return id_message
        except psycopg2.ProgrammingError as e1:
            raise DAOProgrammingError("addMessage : SQL Trace " + str(e1))
        except psycopg2.Error as e2:
            raise DAOError("Can't add message the syslog event : " + str(e2))

Cette méthode fonctionne très bien, elle permet de vérifier si le message existe, dans le cas contraire l'insère, et dans tous les cas renvoie l'id du message ajouté (la table comporte 2colonnes avec une clé auto incrémentée : id et message tout bêtement). Elle permet également de signaler dans les logs les éventuelles "incohérences".

Problème : J'envisage le cas où deux clients utilisant cette méthode seraient exécutés en même temps, et qui pour une raison X ou Y auraient le comportement suivant, chronologiquement :

___Client 1 :

Code :

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

SELECT * FROM messages WHERE content = 'nouveau message 1';

=> Le client voit que ce message n'existe pas

___Client 2

Code :

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

SELECT * FROM messages WHERE content = 'nouveau message 1';

=> Le client voit que ce message n'existe pas

___Client 1 continue son exécution :
Le message n'existe pas, donc je l'insère

Code :

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

INSERT INTO messages (content) VALUES ('nouveau message 1') returning ID

___Client 2 continue son exécution:
Le message n'existe pas, donc je l'insère

Code :

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

INSERT INTO messages (content) VALUES ('nouveau message 1') returning ID

Comme vous pouvez vous en douter, les deux clients au même moment se rendent compte que la valeur n'existe pas et donc la créés. La question peut paraître basique, mais comment empêcher suite à ce traitement d'obtenir :

id + message
1 | 'nouveau message 1'
2 | 'nouveau message 1'

Et donc un doublon...

En soit, je pourrais me débrouiller pour utiliser un "INSERT" avec une close "WHERE NOT" qui aurait le même effet, et qui se ferait donc en une seule exécution requête, supprimant ainsi l'étape intermédiaire qui pause problème, mais cette solution ne me satisfait pas (en effet, peut être qu'un jour, je n'aurai pas le choix...)

J'ai vu également qu'il était possible de mettre le niveau d'isolation des requêtes sur "Serialized" (il est ici en autocommit), mais ceci semble générer des exceptions en cas d'échec, ce qui complexifie le programme...

Ma question est donc, quelle est la "Best Practice" dans ce genre de cas ?

A noter que j'utilise la librairie psycopg2 pour mes requêtes ( http://initd.org/psycopg/docs/ ) avec python 2.7.5.

Merci beaucoup d'avance pour vous réponses.

EDIT : J'ai réussi à éviter l'erreur via :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
BEGIN;
LOCK TABLE messages IN ACCESS EXCLUSIVE MODE;
INSERT INTO messages (content)
SELECT  'nouveau message 1'
WHERE NOT EXISTS (
    SELECT content
    FROM messages
    WHERE content = 'nouveau message 1'
)
returning id;
COMMIT;

Je me garde cette solution sous le coude, est ce la seule à votre avis?

[wiztricks]

Comme vous pouvez vous en douter, les deux clients au même moment se rendent compte que la valeur n'existe pas et donc la créés. La question peut paraître basique, mais comment empêcher suite à ce traitement d'obtenir :

id + message
1 | 'nouveau message 1'
2 | 'nouveau message 1'

Et donc un doublon...

Du point de vue SGDB, il suffit de déclarer la colonne messages comme "primary key" ou comme "unique".
Le SGDB retournera une erreur au client qui essaiera d'insérer "après".

Les notions de clés primaires/candidates,... sont des notions de base de tout SGDB relationnel.
Si vous ne les connaissez pas, vous allez vous prendre le chou à inventer des solutions plutôt que chercher à comment les mettre en œuvre avec un SGDB particulier.
Et ces concepts ne sont pas documentés dans la documentation du SGDB: c'est quelque chose que vous devez avoir acquis "avant".

Sur developpez, vous avez un rubrique SGDB dans laquelle vous trouverez ce genre de tuto.

Bon courage.
- W

[switchON]

Bonjour, merci pour votre réponse.

Il semblerait que l'exemple que j'ai donné soit trop simple.

Je me doute que je peux résoudre le problème ainsi, mais je cherchais à mettre en valeur la simultanéité de l'insertion, c'est donc le même problème qu'une application avec plusieurs threads (sauf qu'il n'y a pas de mutex).
Les threads doivent agir de façon à ne pas attaquer la base en même temps.

Je trouve que mettre toute la colonne en unique n'est "pas propre" (enfin si, c'est l'erreur générée qui n'est pas propre), étant donné qu'on ne fait que balancer une requête en espérant qu'elle ne renvoie pas d'erreur, tout en sachant pertinemment qu'il y a peut être 30 clients qui sont en train d'effectuer la même requête en parallèle (et donc qui généreront 30 fois l'erreur ...!)

N'est il pas possible de prévenir cette erreur ? Si le programme est bien conçu, les erreurs de la base ne devraient elles pas se limiter à des problèmes de connexion par exemple ?

Après comme vous vous en doutez, je n'ai pas d'expérience de ce type, peut être que la gestion de l'erreur est la meilleure solution et que la question ne se pose pas.

[wiztricks]

"clé primaire" ou "unique" sont des façons simples de donner au SGDB la responsabilité de l'unicité de ...
i.e. de préserver l'intégrité de la base de donnée.

Si vous ne voulez pas confier cette responsabilité au SGDB, il faudra définir un protocole distribué entre les
différents clients pour que la création de cette ligne soit effectuée par un et un seul d'entre eux.

Ce qui suppose (très succinctement):

1. qu'ils sachent qu'ils existent,
2. d'élire et de convenir du client qui effectuera l'opération,
3. de mettre en place une mécanique de reprise au cas ou l'élu se plante en cours de route,...
4. ...

Je me doute que je peux résoudre le problème ainsi, mais je cherchais à mettre en valeur la simultanéité de l'insertion, c'est donc le même problème qu'une application avec plusieurs threads (sauf qu'il n'y a pas de mutex).
Les threads doivent agir de façon à ne pas attaquer la base en même temps.

Ben avec un SGDB, le mutex est réalisé par le SGDB.
Comme vous utilisez PostgreSQL, vous devriez lire la documentation sur son MVCC. Ca explique comment il sérialise les transactions sans mutex.

- W