choisir un niveau d'isolation

[Madfrix]

Bonjour,

dans l'optique de monter un petit projet perso (qui veut donc dire que j'ai le temps de réfléchir au préalable), je me mets à PostgreSQL (je commence à lâcher MySQL...). Je m'intéresse aux niveaux d'isolations des transactions. J'ai compris que le niveau de base Uncommited Read est permissif et peut provoquer des erreurs d'intégrité. En fait, à lire la doc, seul le niveau Serializable est 100% sur mais les transactions sont mises en attente "FIFO".

Basiquement, je me dis : utilise le mode Serializable dugenou sinon tu risques à terme de pourrir un peu ta base ! Mais je me doute que c'est pas aussi simple que cela

J'ai lu (en diagonale j'avoue) le papier d'SQLPro sur les niveaux d'isolations mais je ne comprends pas trop au final comment choisir tel ou tel niveau d'isolation...

En Uncommited Read, on s'expose quand même à terme à des incohérence non...?

Merci de m'éclairer un peu

[estofilo]

Pour savoir si le niveau d'isolation par défaut ne convient pas, il faut identifier un scénario précis de processus concurrents travaillant sur les mêmes données en même temps.
Ce scénario dépend complètement de l'application et de son usage en parallèle ou pas.

[Madfrix]

Bonjour et merci de la réponse.

En fait, ma bdd (non construite pour le moment), modélisera un jeu ou chaque joueur possédera des ressources. Les joueurs pouvant se faire piller, on doit considérer entre autres, le fait qu'un pillage ne doit pas retirer plus d'une certaines quantité de ressources (le joueur pillé ne peut pas avoir des ressources négatives après pillage).

Or, il faudra probablement user et abuser de transactions afin de maintenir une certaine intégrité des ressources entre les joueurs etc...

Cependant, pendant ces transactions, d'autres transactions concurrentes pourront être lancées et donc je pense créer des instabilité en cas de niveau d'isolation Uncommited Read. Pour tout dire, je ne vois pas dans quel cas ce niveau d'isolation pourrait m'être utile mais comme je n'ai jamais utilisé les niveaux d'isolation et encore mois sous pg, je me pose des questions

Le niveau Serializable provoque un ralentissement assez sensible non ?

Merci de m'éclairer un peu

[estofilo]

Il n'y a pas techniquement de raison que les transactions soient ralenties en mode sérialisé.
En revanche, elles peuvent échouer avec une erreur de sérialisation si elles veulent modifier des données qui ont été changées entre-temps par une autre transaction.
C'est-à-dire que ça ne résout pas de problème d'accès concurrent, au contraire ça provoque une erreur quand ils surviennent.
Pour gérer des accès hautement concurrents où l'on veut faire une
barrière de synchronisation, personnellement j'ai tendance à utiliser un simple update en mode d'isolation read commited normal.
Par exemple imaginons ta table des ressources avec une ligne par joueur

Code :

1
2
3
4
5
 
begin; -- debut transaction
UPDATE ressources SET derniere_modif=now() WHERE joueur = :1;
... tester les ressources pour le joueur en question et les augmenter/réduire etc...
commit; -- fin transaction

Avec cet update dès le début de la transaction, un premier process pourra entrer dans cette portion de code mais un deuxième sera automatiquement mis en attente au niveau de l'update tant que le premier n'a pas effectué le commit, la ligne sur laquelle porte l'update étant verrouillée jusqu'au commit.

[Madfrix]

Envoyé par estofilo

En revanche, elles peuvent échouer avec une erreur de sérialisation si elles veulent modifier des données qui ont été changées entre-temps par une autre transaction.

J'avais lu cela aussi il me semble sur l'aide pg.

Je croyais que les transactions en mode sérialisé mettaient en attente TOUTES les transactions de tous les users mais apparemment à lire ce que tu m'écris cela met en attente les transactions uniquement d'un utilisateur en particulier c'est cela ?

Merci de m'ôter ce gros doute ^^

[estofilo]

Je croyais que les transactions en mode sérialisé mettaient en attente TOUTES les transactions de tous les users mais apparemment à lire ce que tu m'écris cela met en attente les transactions uniquement d'un utilisateur en particulier c'est cela ?

Non, il n'y a aucune mise en attente lié au mode sérialisé.

[Madfrix]

Le niveau sérialisable est le niveau d'isolation de transaction le plus strict. Ce niveau émule l'exécution de la transaction en série, comme si les transactions avaient été exécutées l'une après l'autre, en série plutôt que parallèlement. Néanmoins, les applications utilisant ce niveau doivent être préparées à tenter de nouveau les transactions suite aux échecs de la sérialisation.

Comment interpréter alors cette phrase et ce qui est en gras particulièrement ?

Moi j'ai compris comme ceci : les fonctions lancées en concurrence sont exécutées les unes à la suite des autres dans leur ordre d'apparition. D'où ma question précédente qui consistait en fait à savoir si dans un tel cas de figure, les fonctions sont mises en attente "globalement" ou pour chaque utilisateur ? En fait si pg va construire un stack pour chaque utilisateur ou un stack global ?

Mais apparemment d'après ce que tu me dis, j'ai rien compris ?

[estofilo]

Le texte dit que ce niveau émule l'exécution de la transaction en série, pas qu'il met effectivement en série les transactions. Ce qui se passe dans ce mode, c'est simplement que si T1 et T2 sont 2 transactions en parallèle avec T1 qui démarre en premier, T1 ne voit pas les modifs de T2, y compris après que T2 ait commité si T2 finit avant T1.
Le rapport avec le terme de sérialisation est que du point de vue T1, c'est comme si T2 n'avait pas encore commençé.

[Madfrix]

Ok merci c'est déjà un peu plus clair

Mais prenons un exemple concret, imaginons un joueur A possédant 1000 en ressources. 2 joueurs B et C lancent une attaque sur lui et arrivent dans la même seconde mais pas dans le même dixième.

La transaction T1 effectuera un ensemble de traitements qui durera 5s (j'exagère bien sur) et qui commencera légèrement donc avant T2.

Dans les instructions de T1, il y a un transfert de ressources en totalité de A à B plus d'autres instructions quelconques.

Un problème se pose si les 2 joueurs B et C retirent chacun 1000 de ressources à A => il aura -1000 de ressources au final.

Je voudrais un comportement transactionnel qui empêche T2 de piller A puisque T1 l'a déjà fait 1 dixième de seconde avant et ce malgré le fait que T1 n'a pas fini de s'exécuter.

Certes, d'autres modes de transactions permettent de "voir" en direct les modifications d'autres transactions en cours, mais si on reprend l'exemple ci dessus, T1 pille A puis T2 ne pille pas A puisque T1 vient de le faire. Or, pour une raison quelconque la transaction T1 rollback. Ok pour T1 mais T2 finalement n'a pas pillé A et il reste 1000 de ressources à A malgré 2 attaques.

Je cherche donc un moyen vraiment efficace (mais existe t-il...) afin de ne pas être confronté à ce genre de situation. C'est pourquoi je cherchais à exécuter toutes les transactions à la suite indépendamment les unes des autres, mais est ce possible...?

Merci de tes explications en tout cas

[estofilo]

Je voudrais un comportement transactionnel qui empêche T2 de piller A puisque T1 l'a déjà fait 1 dixième de seconde avant et ce malgré le fait que T1 n'a pas fini de s'exécuter.

Ce n'est vraiment pas un problème. Ce code le fait:

Code :

1
2
3
4
5
6
7
8
9
 
FUNCTION pillage(joueur_a_piller, quantite_a_piller)
DECLARE q AS int;
BEGIN
  SELECT quantite INTO q FROM ressources WHERE joueur=joueur_a_piller FOR UPDATE;
  IF (q >= quantite_a_piller) THEN
    UPDATE ressources SET quantite=quantite-quantite_a_piller WHERE joueur=joueur_a_piller
  END IF;
END;

La clause FOR UPDATE va en effet mettre un verrou sur la ligne de sorte qu'une éventuelle autre exécution parallèle de ce code pour le même joueur à piller va se retrouver bloquée jusqu'à que la première ait fini sa transaction, quel que soit le temps que ça met.

[SQLpro]

Envoyé par estofilo

Il n'y a pas techniquement de raison que les transactions soient ralenties en mode sérialisé.

Là mon cher ami vous dites une grosse connerie.

Le mode SERIALIZABLE oblige à un verrou exclusif sur TOUTE LA TABLE, maintenu jusqu'à la fin de la transaction !!!!!!

Alors que le niveau REPEATABLE READ nécessite un verrou exclusif que sur les lignes manipulées et maintenu jusqu'à la fin de la transaction

Enfin le niveau READ COMMITTED nécessite un verrou exclusif sur les lignes manipulée, uniquement le temps de faire l'ordre SQL et non pas toute la transaction.

Bref, mode SERIALIZABLE => aucun accès autre que la transaction en cours sur les tables et index manipulées dans la transaction.

Si c'est pas un goulet d'étranglement pour les performances, alors je démissionne !!!!

A +

[Madfrix]

Merci bien pour toutes ces informations.

@estofilo : merci pour la clause FOR UPDATE, j'étais passé un peu vite dessus dans la doc sans comprendre vraiment son intérêt, je vais potasser cela de nouveau.

@SQLpro : merci pour ces explications. A ce que je comprends, le niveau REPEATABLE READ équivaut à poser des clauses FOR UPDATE partout implicitement où cela est nécessaire ? Si oui, ceci me chagrine (extrait de la doc off) :

Dans PostgreSQL™, vous pouvez demander un des quatre niveaux standards d'isolation de transaction. Mais, en interne, il existe seulement deux niveaux distincts d'isolation, qui correspondent aux niveaux Read Committed et Serializable. Lorsque vous sélectionnez le niveau Read Uncommitted, vous obtenez réellement Read Committed, et quand vous sélectionnez Repeatable Read, vous obtenez réellement Serializable, donc le niveau d'isolation actuel pourrait être plus strict que ce que vous sélectionnez.

est-ce donc possible d'obtenir un niveau REPEATABLE READ "réel" ou doit on passer à chaque fois par des clauses FOR...?

Merci

[SQLpro]

Ni l'un ni l'autre. Le niveau REPETABLE READ n'étant pas implémenté le FOR UPDATE correspond au niveau READ COMMITTED et les verrous ne sont pas maintenus durant toute la transaction.

C'est pour cela qu'il existe des SGBDR commerciaux, comme SQL Server, qui implémente les 4 niveaux d'isolation, plus certains niveaux hors norme comme le niveau SNAPSHOT.

Les gens ont du mal à comprendre que pour un bon SGBDR comme Oracle ou SQL Server il faut pas loin de 1000 années hommes de R&D. PostGreSQL, si estimable qu'il soit en est encore loin :

• pas de gestion des niveaux d'isolation
• pas de multithreading des requêtes
• pas de gestion fine des espaces de stockage

et quelques centaines d'autres fonctionnalités non encore implémentées...

A +

[estofilo]

Envoyé par SQLpro

Le mode SERIALIZABLE oblige à un verrou exclusif sur TOUTE LA TABLE, maintenu jusqu'à la fin de la transaction !!!!!!

Absolument pas. Tu n'as qu'à tester pour t'en rendre compte.

[SQLpro]

Mais si c'est le principe même !

Sinon aucune garantie ne peut être apportée en mode SERIALIZABLE.

Exécute les requêtes données dans mon cours sur ce sujet pour t'en convaincre :
http://sqlpro.developpez.com/isolation-transaction/

En sus : http://en.wikipedia.org/wiki/Isolati...ase_systems%29

A +:

[Madfrix]

Hmm plus je vous lis et plus je m'embrouille

En fait, le mode SERIALIZABLE serait parfait...s'il ne créait pas un goulet d'étranglement puisqu'il lock toute la table nous sommes d'accord ?

Je cherche seulement à verrouiller que certaines lignes de la table (comme dans le cas de figure évoqué plus haut) donc est ce que je peux m'en tirer à coup sur avec le mode Read committed par défaut ainsi qu'avec les clauses FOR UPDATE/SHARE ?

Merci encore

[estofilo]

En sus : http://en.wikipedia.org/wiki/Isolati...ase_systems%29

Oui, justement. Je cite et je mets en gras

With a lock-based concurrency control DBMS implementation, serializability requires that range locks are acquired when a query uses a ranged WHERE clause. When using non-lock concurrency control, no lock is acquired; however, if the system detects a concurrent transaction in progress which may be modified by some other transaction when it commits. In this isolation level, read locks are acquired on selected data but they are released immediately whereas write locks are released at the end of the transaction.

Postgresql est concerné par la méthode "non-lock concurrency control" puisqu'il utilise un simili-timestamp pour trouver la version des données qui date du début de la transaction.

[SQLpro]

Mias vous pouvez toujours lire les données au niveau d'ISOLATION READ UNCOMMITTED auquel PostGreSQL descent sans le dire à personne dans ce cas de figure.
C'est cela que veut dire le passage.

En fait comme il ne sait pas gérer tous les niveaux d'isolation il faity un peu n'importe quoi !

Mais en aucun cas vous ne pourrez accéder aux vrais valeurs de la table tant que le mode SERIALIZABLE est posé, dans une transaction concurrente...

A +

[Madfrix]

Je ferais comme SQLpro, je simulerai un scénario avec l'équivalent WAITFOR pg afin de comprendre et de déterminer quel niveau d'isolation je dois appliquer.

Le problème est donc réglé pour moi mais je ne tag pas en si vous voulez encore débattre ^^

Merci en tout cas

[SQLpro]

utilise pg_sleep pour le scénario donné ! Je voulais le faire, mais pas le temps !!!!

A +