[TOUS] Isolation des transactions : Optimisation

Bonjour,


Nous utilisons Sql Server avec des pages asp.net (dans le cadre d'accès multi-utilisateur).
Nous encapsulons toutes les requêtes contenues dans une page dans une transaction READ.COMMITED.
En cas d'erreur dans une des requêtes, un rollback est effectué.

Parfois une page peut mettre plusieurs minutes à se charger.

Pendant toute la durée de la transaction (qui peut durée plusieurs minutes voire plus rarement + d'une heure), des verrous sont ils posés sur les lignes retournées par les requêtes SELECT, tout comme pour les requêtes INSERT/UPDATE ?

Si oui, je pense que les verrous sur les requêtes SELECT sont inutiles et que donc, on pourrait diminuer l'isolation de ces requêtes SELECT, via l'option snapshot ON de READCOMMITED.
(Cela augmenterait nos performances qui deviennent critiques).

J'aurai besoin de l'avis de personnes plus expertes dans ce domaine..
Mais aussi tous conseils de personnes aussi avisés que moi seront les bienvenus..

D'avance merci beaucoup..



---
P.S. : si des verrous sont posés sur les requêtes SELECT; dans le cas d'une longue transaction, si une autre transaction tente d'accèder aux même données, elle sera obligé d'attendre la fin de la première transaction, et dans ce cas, sa page ne pourra s'afficher que lorsque la première transaction sera terminée..
Or j'ai testé, et ça ne le fais pas : j'arrive à afficher rapidement une page qui accède aux mêmes données qu'une autre page que j'ai lancé avant et dont la transaction dure très longtemps (qui n'est pas terminée alors que ma deuxième page (lancée en deuxième) a pu s'afficher avant).

Bonjour,

En niveau d'isolation READ COMMITTED, les verrous partagés posés pour un SELECT ne sont pas maintenus durant la durée de la transaction, seulement le temps de l'instruction.

Pour quelle raison maintiens-tu une transaction durant l'exécution de la page ASP, est-ce vraiment nécessaire ? Ceci dit, une exécution de plusieurs minutes, c'est très long, il y aurait vraiment de l'amélioration de performance à affectuer.

Peux-tu nous dire pourquoi tu fais des transactions, et quelles sont, en gros les opérations effectuées ?

Bonjour Rudib,

merci pour votre réponse.

Nous avons opté pour une transaction dans la durée de vie d'une page, car cela nous facilite le code (on ne gère pas les transactions car elles se gèrent toutes seules dans le page_init et le page_unload [pour faire simple])

Pendant toute la durée d'une page, on a
- des lecture/ajout/modification via des formulaires html de saisies.
- des lectures pour l'affichage de données en tableaux
- des lectures pour des parcourts d'arbres etc.. (les traitements peuvent être assez long voire très long en fonction des données que l'utilisateur demande). Sur le parcourt d'arbre on a effectivement qulques optimisation qu'on peut faire (nombre de requêtes etc..)

Pour le fait qu'on maintient la transaction sur toute la page, cela est plus sécurisant pour le programmeur (il est déjà dans une transaction, aucun risque pour l'intégrité des données).

Par contre nous réfléchissons au fait de diminuer l'isolation de la transaction en cours de transaction (j'ai vu que c'est possible dans la msdn).
Et dans ce cas, nous cherchons à savoir quelles sont les pages ou bien à quel endroit de la page on peut le faire.. A priori j'aurai dis les SELECT mais comme vous me dîtes que les verrous sur les SELECT ne sont maintenus que le temps de l'instruction, alors ce n'est pas la peine.. et donc on est déjà optimisé au mieux..

qu'en pensez vous ?

(merci)

Connaissez vous le lien sur la doc MSDN qui explique comment sont traités les verrous sur les différents SELECT/INSERT/UPDATE d'un READ COMMITED ?

Pour les INSERT et UPDATE, le verrou est bien maintenu durant toute la transaction ? (enfin j'espère)

(merci)

Dernière remarque :

Sachant que Read.Commited est une isolation qui a pour but de prévenir les "lectures incorrectes",

Si dans un read.Commited, les verrous ne sont maintenus que le temps de l'instruction, alors on a des possibilités de "lectures incorrectes" si une transaction concurrente modifie une donnée de l'arbre (qu'on est en train de parcourir récursivement via plusieurs SELECT séparés dans le temps) ?

ce ne sont pas des lectures incorrectes d'une seule ligne , mais une lecture incorrecte global de tout l'arbre récupéré....

J'espère ne pas trop vous embrouiller

Bon, le READ COMMITTED permet de garantir des lectures propres, cad qu'il prévient la lecture de lignes qui sont en train d'être modifiées, donc la lecture exactement en même temps que l'exécution d'un UPDATE.
Si l'UPDATE est dans une transaction, la lecture à partir d'une autre session devra attendre la fin de la transaction ...
Chez vous, ça peut faire pas mal de temps.

Franchement, faire systématiquement des longues transactions va tuer vos performances, pour la raison expliquée ci-dessus. Il faudrait vraiment réfléchir à l'utilité de ça ...

Pour les SELECT, tu peux les faire en READ UNCOMMITTED ( WITH (NOLOCK) ), qui n'honore pas les verrous exclusifs. En d'autres terme, permet les lectures sales.

De plus le fait de maintenir des verrous durant une longue période provoque des bloquages sur d'autres requêtes. Donc si la base de données est utilisée par plusieurs application ou plusieurs instance de la même application, il y a de fort risque d'échecs des tentatives de mise à jour des tables.

Merci rudib..
(et madinico)

Cependant, les longues transactions sont essentiellement dues à des lectures d'arbres dans la base de données.

Donc d'après ce que vous m'avez dit à propos des SELECT qui ne verouillent que le temps de l'exécution et pas pendant toute la transaction comme le fait les INSERT et UPDATE, dans ce cas, ça ne pose pas de problème au niveau des performances d'après ce que je comprends.

Sinon pour les longues transactions qui contiennent des INSERT et UPDATE, je pense que ce n'est pas incorrect d'encapsuler toute la page dans une transaction car cela permet de faire un roll back sur tous ces INSERT et UPDATE (qui ont d'ailleurs été verouillé et tant mieux.. car on fait un roll back et elles disparaissent).

Si vous pensez que je me trompe n'hésitez pas à me le dire.

donc :
1) les selects ne se verouillent pas donc pas de soucis à ce niveau
2) les insert/update se vérouillent et ça nous arrange qu'ils se verouillent durant toute la page, car en cas de problème (n'importe lequel dans la construction de la page) -> rollback.. et les données des transactions concurrentes sont intègres car elles n'avaient pas pu accèder aux insert/update.

Là ou j'ai peur par contre c'est pour la récupération d'arbre dans la base de données..
Les select n'étant pas vérouillés, on peut récupérer un arbre à moitié intègre (il suffit q'une de ses branches ait été modifié par une autre transaction, pendant qu'il récupérait l'arbre à coup de select)..

petite remarque : notre application permet à des utilisateur d'accèder à leurs propres données. Donc pas de risque de verrous sachant que les utilisateurs ne verouillent que leurs propres données.

Par contre, si plusieurs pages sont demandées en même temps par le même utilisateur (ex: il clique sur Actualiser 3 fois de suite).. il y aurait peut être là des verrous sur lui même..?!

1) si vos lectures d'arbres sont longues, optez pour la représentation en mode intervallaire. Lisez l'articler que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/arborescence/

2) le mode READ COMMITTED n'est pas approprié à votre gestion transactionnelle interpages. Il faudrait pour cela faire du vérouillage pesismite (ce qui n'est pas natif dans SQL Server) ou alors travailler en version de ligne (SNAPSHOT ISOLATION) valable à partir se SS 2005.

En effet en mode read commited, le scénario suivant peut se passer :
a) debut de la transaction T1 pour USER 1
b) USER 1 lit la ligne 55 de la table X
c) debut de la transaction T2 pour USER 2
d) USER 1 affiche les données de la ligne 55 de la table X
e) USER 2 supprime la ligne 55 de la table X
f) USER 1 modifie une donné dans le formulaire
g) fin de la transaction par COMMIT pour USER 2
h) USER 1 clique sur le bonton OK pour forcer l'ordre UPDATE
i) fin de la transaction par COMMIT pour USER 1

Aucune erreur n'est remontée et USER 1 à perdu la ligne sur laquelle il travaillait.

Bref, il semble que vous ayez de grandes lacunes sur la gestion des transactions.
Si votre but est de tuer votre serveur en multi utilisateur... Vous êtes sur la bonne voie !

A +

Citation:

---

Aucune erreur n'est remontée et USER 1 à perdu la ligne sur laquelle il travaillait

---

Bonjour SqlPro,

Merci pour vos conseils.

- Concernant les arbres par intervalles, nous avions lu votre article mais nous avions déjà trop avancé dans le développement et avons remis cela à plus tard lorsque nous aurions plus de temps. merci.

- Pour l'exemple que vous donnez, je pense plutôt que USER 1 va avoir un message d'erreur car la transaction ne va pas pouvoir faire un UPDATE (h) vu que la donnée n'existera plus dans la base de données. (Nous gérons les exceptions via try catch et affichons un message d'erreur).
Dîtes moi si je me trompe ?

-- fenêtre 0

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

CREATE TABLE T_toto (col INT)   INSERT INTO T_toto VALUES (33)       -- fenêtre transactionnelle 1   SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION   SELECT * FROM T_toto WHERE col = 33   WAITFOR DELAY '00:00:30'   --> demarrez ici l'autre transaction (fenêtre 2)   UPDATE T_toto SET col = 44 WHERE col = 33   COMMIT TRANSACTION     -- fenêtre transactionnelle 2   SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION   DELETE FROM T_toto WHERE col = 33   COMMIT TRANSACTION

---

Voici le scénario de test que je vous propose. Mettez ceci dans 3 fenêtres différentes et lancez cela en parrallèle. Dites m'en des nouvelles...

Vous constaterez qu'aucune erreur n'a lie dans aucune fenêtre et que votre update à porté sur 0 lignes.

Au plaisir de vous revoir !

A +

Effectivement vous avez raison.
les DELETE et UPDATE ne genereront pas d'erreur puisqu'ils ne trouveront pas de col=33 (clause where)..

Je réfléchissais au mode SNAPSHOT qui est soit une isolation à part entière soit une option de l'isolation Read.Commited..

Je pencherait pour la deuxième : option SNAPSHOT du Read.Commited.

Mais dans l'exemple que vous m'avez donné, que va t'il se passer pour l'UPDATE ?

1) Un select de T1 prend une donnée X et l'affiche
2) Un delete de T2 supprime la donnée X
3) Un update de T1 est effectuée sur la donnée X

Malgré le snapshot par versioning sur la donnée A, elle n'existe plus dans la base de données. Le update ne va pas générer de message d'erreur, et aura porté sur 0 lignes..
que faire?

En fait, j'ai mal lu, vous me conseillé de prendre SNAPSHOT ISOLATION et donc pas l'option de Read.Commited.

C'est surement l'isolation qu'il nous faut.

Mais les performances de notre serveur ne seront t'il pas diminués ?

Je pensais que plus les transactions étaient restrictives (élevé en terme de niveaux d'isolation), plus les performances subissaient ?

d'avance merci

Tout à fait et en SNAPSHOT pensez à avoir beaucoup de RAM et des disques très performants en plaçant la base tempdb en RAID 1 sur une grappe à part, car ce mode va enfler de manière très spectaculaire votre volume des données (donc consommer du disque et de la RAM.
Mais je pense que vous êtes dans la mauvaise voie, pour ne pas dire dans la pire. C'est encore au niveau fonctionnel qu'il faut revoir votre copie. Vous vous enferrez dans votre façon de faire qui date du cobol et des fichiers plats. On en est aux SGBDR client/server et au transactionnel. Tout ce que vous ferez pour contourner ce mode vous conduira à des performances de plus en plus lamentables, tant en volumétrie des données qu'en volumétrie transactionnelle (nombreux utilisateurs). Votre solution sera à terme intenable et ne supportera pas la montée en charge...

A +

Merci..

Par contre, je n'ai pas bien compris quelle est donc la meilleure solution que vous nous conseillé..

Je ne souhaite pas m'enfermer dans une mauvaise voie, mais quelle est la bonne alors ?

La bonne solution consiste à ne pas gérer de transaction dans laquelle figure une intervention humaine et de reporter toutes vos modif dans des appels de procédures stockées au final.

Bref, revoir entièrement votre modèle cinématique et fonctionnel. C'est un boulot gigantesque, hélas, mais nécessaire. Vous avez pris le contrepied du fonctionnement d'un SGBDR C/S. Il sera donc lent et faiblement concurrent (peu d'utilisateurs simultané) si vous laissez votre application en l'état.

De quoi avez vous peur si une donné est modifiée ou supprimée pendant que votre utilisateur traveile ?

En principe on ne doit JAMAIS avoir d'interaction utilisateur dans une transaction.
Une transaction doit se faire dans le temps le plus court (quelques millisecondes). Plus une transaction dure, plus la probabilité d'obtenir un verrou mortel augmente et ce de façon exponentielle par rapport à la montée en charge de la concurrence des utilisateurs.
La solution du SNAPSHOT à outrance n'est qu'un paliatif, comme celle du verrou pessismiste. Un peu comme si vous vouliez conduire un 38 tonnes pour faire le rally de monté carlo en pensant concurrencer sébastien loeb !!!

A +

SqlPro,

merci d'avoir pris la peine de me donner vos bons conseils. Cela est bénéfique.

Par contre, concernant l'intervention utilisateur dans la transaction, nous n'en avons pas. En effet, c'est la construction de la page asp.net que nous avons encapsuler dans une transaction (= durée de vie de la page.. pageInit:debut_transaction, page_unload:fin_transaction).
Donc si l'utilisateur valide sa saisie dans le formulaire html, c'est une nouvelle transaction.. Au pire comme vous disiez, l'utilisateur tentera de modifier une donnée qui n'existe plus (car affichée sur la page 1 mais n'existant plus après appuie sur le bouton de validation).

Concernant les verrous mortels, je pense que nous n'en aurons pas tant qu'un utilisateur travaille sur ses propres données (ce qui est majoritairement le cas pour notre application mais effectivement on devrait mieux y réfléchir, car ça peut peut être se produire).

Dans tous les cas, merci de votre aide.

Je met ce post en résolu, même si on devra encore travailler sur certains points.