Discussion :

[vmolines]

Bonjour,

suite à des recherches pour trouver un sgbd qui me conviendrait, je suis tombé sur quelquechose qui m'a paru étrange dans le manuel de PostgreSQL. Ceci concerne le verouillage de prédicat lors d'une transaction serialisable.

Il est dit que ce n'est pas implémenté dans pg et dans aucun autre SGBD. Pourtant il me semble bien que MySQL (InnoDB) peut verouiller par prédicat et empécher l'insertion de lignes qui correspondraient à une sélection verouillante pour le dit prédicat.

Comme je vais surement expliquer tout ça moins bien que le manuel, j'ai extrait la partie qui mentionne le problème.


Extrait de l'aide (rubrique 12.2.2.1. Isolation sérialisable contre vraie sérialisation ) :

Code :

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

1
2
3
4
5
6
classe | valeur
--------+-------
1 | 10
1 | 20
2 | 100
2 | 200

Supposons que la transaction sérialisable A traite
SELECT SUM(valeur) FROM ma_table WHERE classe = 1;
puis insère le résultat (30) comme valeur dans une nouvelle ligne avec classe = 2. En concurrence, la transaction serialisable
B traite
SELECT SUM(valeur) FROM ma_table WHERE classe = 2;
et obtient le résultat 300, qu'il insère dans une nouvelle ligne avec classe = 1. Donc, les deux transactions valident. Aucun des
comportements indiqués comme non désirables n'est survenu, pourtant nous avons un résultat qui n'aurait pû arriver dans tout
ordre sériel. Si A a été exécuté avant B, B aurait trouvé la somme 330, et non pas 300. De façon similaire, l'autre ordre aurait eu
comme résultat une somme différente pour le calcul par A.
Pour garantir une vraie sérialisation mathématique, il est nécessaire que le système de bases de données force le verrouillage du
prédicat, ce qui signifie qu'une transaction ne peut pas insérer ou modifier une ligne qui aurait correspondue à la condition WHERE
d'une requête dans une autre transaction concurrente. Par exemple, une fois que la transaction A a exécuté la requête SELECT
... WHERE classe = 1, un système à verrouillage de prédicat aurait interdit l'insertion de toute ligne de classe 1 par la transaction
B jusqu'à la validation de la transaction A. 1 Un tel système de verrous est complexe à implémenter et extrêmement coûteux
à l'exécution car toutes les sessions doivent être conscientes des détails de chaque requête exécutée par chaque transaction
concurrente. Et cette grande dépense est pratiquement complètement perdue car, en pratique, la plupart des applications ne posent
pas ce genre de problèmes (l'exemple ci-dessus est assez petit et a peu de chance de représenter de vrais logiciels). Du coup, PostgreSQL
™ n'implémente pas le verrouillage de prédicat et, autant que nous le sachions, aucun DBMS en production ne le gère.

Et j'en profite pour vous citer aussi une partie du manuel de MySQL qui traite aussi du pb :

15.11.5. Verrou de clé suivante : éviter le problème des
lignes fantômes
Avec le verrouillage de ligne, InnoDB utilise un algorithme appelé le verrouillage de la clé suivante.
InnoDB fait un verrouillage de telle sorte que lorsqu'il fait une recherche ou un scan d'index, il pose des
verrous partagés ou exclusifs sur les lignes d'index qu'il rencontre. Par conséquent, les verrous de lignes
sont plus exactement appelés des verrous d'index.
Les verrous que InnoDB posent affectent aussi l'espace qui le sépare de la ligne suivante. Si un
utilisateur a un verrou partagé ou exclusif sur une ligne L dans un index, alors un autre utilisateur ne
peut faire d'insertion immédiatement avant la ligne L, dans l'ordre de l'index. Ce verrouillage est fait
pour éviter le problème de la ligne fantôme. Supposons que je veuille lire et verrouiller tous les enfants
ayant un identifiant supérieur à 100 dans la table CHILD, puis modifier certains champs des lignes ainsi
identifiées :
SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;
Supposons qu'il y ait un index sur la table CHILD, sur la colonne ID. Notre requête va scanner l'index à
partir de la première ligne où ID est plus grand que 100. Maintenant, si le verrou posé sur les lignes
d'index n'empêche pas l'utilisation des intervalles entre les lignes d'index, un nouvel enfant peut être
inséré dans la table durant la lecture. Et maintenant, si ma transaction exécute la commande :
SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;
je vais trouver un nouvel enfant dans le résultat de ma requête. Ceci va à l'encontre du principe
d'isolation des transactions : une transaction doit être capable de s'exécuter sans que les lectures soient
affectées durant ce temps. Si vous considérons un intervalle de données, alors la nouvelle ligne 'fantôme'
va casser le principe d'isolation.
Lorsque InnoDB scanne un index, il va aussi verrouille l'espace après la dernière ligne de l'index. C'est
ce qui est arrivé dans l'exemple ci-dessus : le verrou posé par InnoDB va éviter qu'une insertion
n'intervienne dans la table où ID serait plus grand que 100.
Vous pouvez utiliser le verrouillage de la clé suivant pour implémenter des vérifications d'unicité dans
votre application : si vous lisez des données en mode partagé, et que vous ne voyez pas de duplicata de
la ligne que vous allez insérer, alors vous pouvez l'insérer sans problème, en sachant que le verrou de clé
suivante va vous garantir que durant ce temps, personne ne pourra insérer de ligne, qui déboucherait sur
un duplicata de la votre. Le verrou de clé suivante permet de verrouiller aussi la non-existence de ligne
dans votre table.

Est ce que j'ai mal compris quelque chose ? que pensez vous de l'implémentation de Postgre et qu'il est dit qu'aucun SGBD ne gère ceci ?

[Fabien Celaia]

Ai-je mal compris ?

Dans votre cas, les 2 transactions catchent en même temps la valeur de l'autre classe

Au temps t, A catche 30, B catche 300
Au temps t+1, A insère 30 dans l'autre série (et donc modifie la série de B, mais B l'a catché AVANT, donc c'est sa valeur qui vaut), et B insère 300 (idem)...

Ou est le problème ?? La notion de temps est primordiale dans le traitement transactionnel et je ne vois pas en quoi l'exemple donné est problématique

Mais que je le sache, la plupart des bases permettent de verrouiller les enregistrement que l'on veut en forçant l'isolation. D'où parfois l'exécution d'un select holdlock/noholdlock/serialisable/... ou qqch de ce genre avant certains updates ou inserts.

Donc, en faisant un holdlock sur le select du prédicat avant l'insertion, on éviterait votre problème...