Re: Oui mais Oracle n'est pas un moteur SQL...

Sommaire

• MVCC mon amour
• MVCC et journaux
• MVCC et update contre update
• MVCC et triggers

MVCC mon amour

nous essayons actuellement de remplacer Oracle par HSQLDB […] car HSQLDB dispose d'un mode de compatibilité avec Oracle (syntaxe, mais je ne suis pas sur pour l’exécution)

Pour l'exécution, n'oublie pas le comportement MVCC d'Oracle et son implémentation : que je sache, Postgres ou HSQLDB s'appuient aussi sur du MVCC mais tu devrais vérifier que c'est suffisant pour t'éviter des effets de bord indésirables et potentiellement ardus à détecter et reproduire.

Premier écueil (mais que tu ne devrais peut-être pas avoir avec HSQLDB ?) : en résumé, un update ne bloque jamais un select.

• Sur un moteur X non MVCC, tu peux avoir du code qui se retrouve bloqué par un select sur des données updatées dans une autre session : ce blocage est peut-être voulu, mais il peut aussi être fortuit. Dans les deux cas, il s'agit ni plus ni moins que d'un comportement classique de synchronisation d'exécution comme un mutex, un sémaphore, … sauf que ça passe par la base de données et non pas par codage explicite dans l'application.
• Dès lors que tu migres sur un autre moteur Y qui fonctionne en MVCC, il y a toutes les chances pour que cette synchronisation n'existe plus : le code qui était auparavant bloqué sur le select va dorénavant s'exécuter comme si de rien n'était.
• Dans le meilleur des cas, l'application continue à fonctionner correctement après changement du moteur SQL et va même peut-être plus vite (car "les verrous c'est pas bien", c'est un tue-l'amour en termes de performances et de scalabilité).
• Dans le pire des cas, l'application ne se comporte plus de manière correcte (génération de faux résultats) et il faut espérer qu'on le détectera avant passage en production car c'est un truc ultra-vicieux : le bug dépend de la dynamique d'exécution et du comportement multi-threads (ou multi-clients ou multi-sessions) de l'application. Cela peut être un véritable calvaire que de reproduire le bug.
• Facteur aggravant : je ne sais pas ce qui est enseigné dans les IUT / BTS / écoles d'ingénieurs informatiques / … ces dernières années, mais je constate que 100% des développeurs avec qui j'ai eu l'occasion de travailler sont convaincus que un update bloque obligatoirement un select sur la même donnée : il y a donc toutes les chances de trouver du code qui s'appuie sur ce comportement pour implémenter une logique métier.

MVCC et journaux

Second écueil : la "durée de vie"

• L'implémentation Oracle du MVCC s'appuie sur les journaux.
• Si je passe sur les détails : une session démarrée à l'instant T travaillera toujours sur l'état complet de la base de données telle qu'elle était exactement à cet instant précis. On peut donc, par exemple, avoir un "batch" quelconque qui mouline durant 2 heures. Dans ce scénario, le batch n'est jamais bloqué (voir point précédent) et à tout instant, il "voit" l'intégralité de la base de données telle qu'elle était à l'instant T même si des millions d'update / commit ont été faits par d'autres sessions sur les tables manipulées par le batch.
• Le volume de "millions d'update" ne dépend que de la taille allouée aux journaux d'undo / redo : tant que le journal qui contient la version initiale de la donnée demandée par le batch (et qui a été modifiée x fois par d'autres sessions) existe, le batch fonctionne sans souci (et sinon, on obtient la fameuse erreur "ora-01555" : le moteur ne peut plus s'appuyer sur les undo pour retrouver la valeur de la donnée telle qu'elle était il y a 2 heures).
• Ce comportement est "out of the box", permanent. Je crois que d'autres moteurs ont des notions de "snapshot" et ce genre de choses. Sur Oracle, le MVCC s'appuie toujours sur les journaux : c'est le seul et unique comportement, il n'y a pas d'alternative.

Je ne connais pas HSQLDB mais tu devrais peut-être vérifier que ton application ne s'appuie pas (peut-être sans que ce soit volontaire) sur ce comportement Oracle.

MVCC et update contre update

De plus, ce que je viens de décrire a également des conséquences surprenantes sur le comportement des update : ce comportement est évidemment correct d'un point de vue transactionnel mais le MVCC et l'implémentation Oracle font que cela n'est pas toujours conforme à notre intuition. Donc, danger.

Si tu veux approfondir ce genre de sujets, je ne peux que te recommander un seul bouquin, celui de Tom Kyte : Expert Oracle Database Architecture. Je sais qu'il existe en version PDF.

• Il traite notamment du MVCC, en lecture et en écriture. Dans ma version (9i + 10g seulement), le chapitre est "Seeing a Restart". Si tu ne dois lire qu'un bouquin sur Oracle (même avant d'en sortir), prends celui-là. C'est écrit par un des meilleurs spécialistes d'Oracle.
• Point capital : il prend soin de faire en sorte que tout ce qu'il explique soit démontré par l'expérience et reproductible. Son credo est "ne croyez pas les experts, pas même moi, vérifiez toujours avec les scripts que je fournis que vous avez le même résultat".

L'exemple présenté au chapitre "Seeing a Restart" évoqué plus haut commence ainsi :

• Une session fait Update t set y = 10 where y = 5
• Une autre fait Update t Set x = x+1 where y = 5 ; elle est évidemment bloquée. Remarquer que la 1ère requête modifie la colonne qui est utilisée dans le where de cette seconde requête.
• La première session réalise un commit.
• La seconde session peut alors continuer.
• La question est : que doit-il se passer lorsque la 1ère session aura fait son commit ? Cela doit évidemment être conforme aux principes ACID.
• Et la réponse n'est pas aussi immédiate qu'il y paraît !
  • On ne peut pas prétendre, dans la seconde session, que "where y=5" ne retourne rien : la première session n'a pas encore été commitée. C'est d'ailleurs pour cela et en vertu du principe ACID que la seconde session est bloquée !
  • Mais, une fois que la première session a été commitée, que faire ? La donnée existait, a bloqué la seconde session, puis quand elle se débloque, la donnée n'existe plus !
  • Pour Oracle, la réponse est, sauf en isolation SERIALIZABLE : l'update de la seconde session est rollbacké de manière transparente, le moteur réalise alors un équivalent logique à "select from t where y = 5 for update" pour poser des verrous explicites puis relance l'update (c'est rollbacké car il y a peut-être des milions de lignes qui répondent à la clause where - voir plus bas).
  • Ceci évite de se retrouver dans le cas de figure initial.
• Et le bouquin continue, car le scénario n'est pas terminé.

Tu as peut-être du code qui fonctionne correctement du fait de ce comportement. Comment le détecter ? Qu'en sera-t-il après portage ?

Pour les longues soirées d'hiver : que doit-il se passer si "where y = 5" correspond à des millions de lignes ? Là, l'asynchronisme prend de l'importance.

• En effet, rien ne garantit dans le standard SQL que les deux sessions vont traiter ces lignes dans le même ordre, ni à la même vitesse.
• On peut rendre la chose plus explicite en modifiant un peu les requêtes, par exemple la 1ère session fait "where y > 1000" et l'autre "where y > 10", qui est un sur-ensemble de "y > 10" : si ça se trouve, la 2nde requête aura déjà fait des milliers d'update avant d'être bloquée par la première requête qui lui a "tiré le tapis sous les pieds".
• Quel est le comportement attendu du moteur (n'importe lequel) ?

MVCC et triggers

Troisième écueil : les triggers

• C'est un effet de bord du MVCC en update : si tu as des triggers en update sur une table, le moteur Oracle est susceptible de les déclencher plusieurs fois.
• Pour les détails : voir bouquin mentionné.
• Là encore, tu as peut-être du code qui fonctionne correctement avec le comportement Oracle : je ne sais pas dire si ce comportement est, d'un point de vue formel, par rapport au standard, une bizarrerie ou un truc normal.
• Perso, là encore, je trouve que c'est en tout cas assez étrange pour s'interroger.
  • Est-ce propre à la manière dont Oracle implémente le MVCC ? Donc danger.
  • Ou est-ce une conséquence inévitable du MVCC, indépendamment du moteur ?

Au plaisir.