Re: SQL, transactions et isolation

Le Wednesday 10 December 2008 17:08:26 Stephane Bortzmeyer, vous avez écrit :
> Je vais me permettre de poser une question qui ne concerne pas
> réellement PostgreSQL. Car, avec PostgreSQL, tout marche bien mais je
> voulais savoir ce qu'il en était d'autres SGBD.
>
> La doc' de PostgreSQL prétend
> <http://www.postgresql.org/docs/current/interactive/mvcc.html> et
> l'expérience semble confirmer, que chaque transaction voit un
> "snapshot" des données et que "reading never blocks writing and
> writing never blocks reading".
>
> Avec le niveau d'isolation par défaut, READ COMMITTED, une transaction
> "de lecture" voit les commits des autres transactions (et ne peut donc
> pas compter sur une vision cohérente des données).
>
> Avec le niveau d'isolation SERIALIZABLE, par contre, une transaction
> "de lecture" ne voit absolument pas les commits des autres
> transactions (que ce soit pour des INSERT, des DELETE et des
> UPDATE). Elle n'est pas bloquée pour autant, elle peut faire des
> SELECT tant qu'elle veut et ils se terminent, rendant les données qui
> existaient au début de la transaction (ce qui est bien le but des
> transactions sérialisables). C'est parfait, c'est juste ce que je
> voulais.
>
> Mais ma question est :
>
> Ce comportement de "snapshot" de PostgreSQL est-il imposé par la norme
> SQL ? Pas sûr, on peut sans doute être compatible avec la norme en
> mettant des simples verrous qui bloquent la transaction de lecture
> tant que des écritures sont en cours. Si ce n'est pas standard, quel
> SGBD a les mêmes capacités que PostgreSQL ? Tous ? Aucun ?

En tant que personne ayant souffert avec un certain nombre de moteurs, je peux
répondre à qq questions sur le sujet :)

Déjà pour commencer, une liste assez à jour des moteurs MVCC :
http://en.wikipedia.org/wiki/Multiversion_concurrency_control

(Non, DB2 n'est pas MVCC, comme je l'ai vu dans une des réponses)

* Pour ce qui est des ordres d'implémentation du MVCC entre les moteurs, je ne
sais pas qui a dégainé le premier.

* Oracle ne fait pas le MVCC via ses redo logs mais ses segments d'undo (ou de
rollback jusqu'à la 8i), qui stockent les images avant modification des
blocks

* SQL Server peut faire du MVCC, mais ce n'est pas par défaut. Il faut
l'activer par base, et c'est plus ou moins déconseillé pour des raisons de
perfs apparemment (j'ai pas d'info précise là dessus)

* InnoDB, BerkeleyDB et le (futur ?) moteur falcon sont MVCC aussi, chacun
avec son implémentation

* Pour ce qui est des moteurs non MVCC (SQL Server par défaut, et toutes les
versions antérieures à 2005), DB2, Progress (je crois), Sybase et quelques
autres, il y a 2 entrées de blogs de msdn qui expliquent assez bien comment
ça marche avec SQL Server. C'est à très peu de choses pareil sur les autres
moteurs non MVCC (au moins DB2 et Sybase...)
http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx
et si vous voulez avoir encore plus mal :
http://blogs.msdn.com/craigfr/archive/2007/05/02/query-plans-and-read-committed-isolation-level.aspx
(de l'influence du plan d'exécution sur le résultat d'une requête...)

* Les moteurs MVCC ne sont pas vraiment majoritaires dans les gros SGBD (à
part Oracle et un peu SQL Server maintenant). C'est un des gros points forts
d'Oracle sur la concurrence, et un sujet d'amusement de leurs experts
d'ailleurs quand ils parlent de la concurrence.

* Les snapshots ne sont pas imposés par la norme (comme expliqué dans les
entrées de blog au dessus). Par contre, honnêtement, une appli
transactionnelle est très pénible à développer sans. Dans beaucoup de
développements que j'ai vu sur le sujet, ça se termine soit par l'utilisation
de read uncommitted (ni implémenté par postgresql ni par oracle, et pour
cause, il n'y en a pas besoin), soit par un énorme travail pour éviter que
les écrivains ne bloquent les lecteurs. C'est aussi beaucoup plus facile
d'avoir des deadlocks dans l'appli, vu que les verrous sont plus nombreux...
Le read uncommitted est vraiment beaucoup plus fréquent qu'on ne se l'imagine
chez les utilisateurs moins fortunés de moteurs non MVCC

* Dans les moteurs non MVCC il y a aussi des promotions de verrous et autres
mécanismes pour éviter que les verrous mémoire ne deviennent ingérables : un
update géant sur une table, qui va verrouiller 10 millions d'enregistrements
aurait 10 millions de verrous mémoire sinon. Donc on se retrouve à remplacer
des verrous d'enregistrement par des verrous de blocs a partir d'une certaine
limite, voire carrément des verrous de table ...

Bref, MVCC, c'est une des choses que je mets systématiquement en avant pour
justifier l'utilisation de PostgreSQL ou Oracle pour une appli
transactionnelle... ça coûte un peu en performances, mais ça évite beaucoup
d'ennuis.
Pour un infocentre, c'est moins important par contre.

En espérant avoir à peu près fait le tour de la question ...