|
4 - L'isolation des données - suite
Les mécanismes de transaction et d’isolation des données tels que décrit ci-dessus peuvent aussi engendrer des situations de blocages. Plus on cherchera à assurer la consistance des données, plus la probabilité d’apparition d’un problème de ce type est probable. Les blocages Un processus est bloqué par un autre qui utilise une ressource dont le premier à besoin. Les ressources sont généralement des données qui sont protégées par des verrous. D‘autres blocages sont possibles via des latchs, des compilations, du parallélisme, etc. Le diagnostique des blocages au sein du moteur de base de données, n’est pas chose aisée, il est possible assez facilement de quantifier le problème, mais certaines vues et fonctions dynamiques nous y aide. Cette requête listera les connexions actuellement bloquées par d’autres : select session_id, blocking_session_id from sys.dm_exec_requests where blocking_session_id != 0 Les problèmes relatifs aux blocages ne sont souvent que la partie immergée de l’iceberg. En effet un problème de lenteur d’accès disque ralentira les écritures dans le journal de transaction, qui va lui-même ralentir le traitement des écritures de données réalisées dans des transactions. Si bien que la durée de la transaction se trouvera allongé ainsi que la durée de vie du verrou exclusif associé. Le problème semblera être une attente sur des verrous, alors qu’en réalité le système nécessite un système de disque plus efficace, ce qui fera disparaître tout problème. Blocked Process Report Depuis SQL Server 2005, une option est disponible au niveau du serveur pour vous permettre de détecter plus facilement et de manière plus proactive ces problèmes de blocages. Il suffit pour activer cette fonctionnalité de configurer les options serveur de la manière suivante : -- Affiche les options avancées exec sp_configure 'show advanced options', 1 reconfigure -- Indique le temps d'attente maximum de 10 secondes exec sp_configure 'blocked process threshold', 10 reconfigure -- Masque les options avancée exec sp_configure 'show advanced options', 0 reconfigure J’ai ici mis de manière arbitraire un temps d’attente de 10 secondes, vous pouvez l’ajuster à votre guise, évitez de descendre sous les 5 secondes, cela aurait des conséquences négatives importes sur les performances de votre serveur. Le moteur de base de données vérifiera régulièrement sur l’un de ses processus interne n’effectue pas d’activités que celui-ci n’est pas bloqué plus que le temps indiqué plus haut. Ce mécanisme ne grève que très peu les performances du serveur, car on s’appuie sur les mêmes bases que pour la détection des inter-blocages que l’on verra dans la partie suivante. Pour extraire les informations du serveur, nous avons 2 options principales, l’outil Générateur de Profils (SQL Profiler) ou les évènements « Errors and Warnings » > « Blocked Process Report »
Détection de blocages avec le Générateur de Profils Figure 23-1. Le résultat est renvoyé en XML, on identifiera assez aisément les principales caractéristiques du blocage, tels que les sessions incriminée et les requêtes associées. Les inter-blocages Un inter-blocage appelé aussi deadlock est une situation où plusieurs processus sont bloqués de manière a ce que la situation ne puisse être résolue, même en attendant très longtemps. SQL Server prend alors la décision d’arrêter un ou plusieurs processus bloqués. Ce phénomène se produit la plupart du temps avec des verrous quand 2 processus réclament des verrous sur des objets déjà verrouillés par l’autre processus. D’autres types peuvent d’inter blocages peuvent aussi se produire. L’inter blocage se traduit par l’arrêt d’une ou de plusieurs transactions qui reçoivent alors le message suivant : Msg 1205, Level 13, State 51, Line 10 La transaction (ID de processus 59) a été bloquée sur les ressources verrou par un autre processus et a été choisie comme victime. Réexécutez la transaction. Idéalement l’application devra journaliser ce type d’erreur pour faciliter le dépannage de telle erreurs. Cependant il existe d’autres outils pour diagnostiquer ce problème. La détection des inter-blocages De la même manière que pour les blocages, le Générateur de Profils de SQL Server va nous être très utile. Depuis SQL Server 2005 il intègre la possibilité de lister les inter-blocages et de représenter le problème de manière graphique, facilitant le diagnostique de ces derniers. L’évènement « Locks » > « DeadLock Graph » est suivit et permet d’afficher le résultat graphique tel vous pouvez le voir dans la figure 23-2
Détection d’inter-blocages avec le Générateur de Profils Figure 23-2. Il est possible d’intercepter les évènements d’inter blocage en utilisant des Traceflag, cette méthode est d’ailleurs la seule réellement envisageable pour connaître le détail de l’erreur avec SQL Server 2000. DBCC TRACEON (1204, 3604, -1) Ces 2 méthodes ont un point commun : le seul historique renvoyé par le moteur est le dernier lot qui lui été soumis dans la connexion. Ce qui est très insuffisant pour réaliser un diagnostique correct. Il est nécessaire en parallèle se suivre l’exécutions de toutes les lots pour être capable de remonter l’historique de l’exécution de ces derniers sur une connexion particulière et trouver les requêtes ayant initialement entamé l’inter blocage. Comment résoudre blocages et inter-blocages ? Ré exécuter la même transaction Comme le message d’erreur le suppose lors de la rencontre avec un inter blocage, le fait de ré exécuter la transaction une seconde fois permettra de l’exécuter avec succès dans plus de 90% des cas. Dans l’application, réaliser l’interception de l’erreur 1205 qui est renvoyée en cas de transaction arrêtée pour cause d’inter blocage. Ré exécutez tant que cette erreur apparait en attendant quelques millisecondes entre chaque exécution. Dans la mesure du possible limiter le nombre d’itérations. Cette solution permet d’augmenter la probabilité d’exécution du code impliqué dans l’inter blocage et rend transparent l’inter-blocage pour l’utilisateur. En contrepartie cette exécution sera plus longue si le code soit être ré exécuté. Cela ne vous affranchie pas de trouver une solution définitive aux inter-blocages et journaliser ces derniers pour les résoudre. Cette solution ne s’applique pas aux blocages. Ordonner le code T-SQL Cette solution permet de limiter exclusivement les problèmes d’inter blocage et ne vous sera d’aucuns secours dans le cas de blocages. L’ordre d’accès aux données en lecture et surtout en écriture doit être cohérent d’un morceau de code à un autre. Prenez, par exemple, comme convention pour l’ordre d’accès aux objets le nom de l’objet. Dans ces 2 requêtes exécutées simultanément : begin transaction
update person.person set firstname = 'robert'
waitfor delay '00:00:10'
update person.address set city = 'boulogne'
rollback transaction Et dans une seconde connexion : begin transaction
update person.address set city = 'paris'
waitfor delay '00:00:10'
update person.person set firstname = 'christian'
rollback transaction L’ordre ici est inversé entre les 2 transactions ce qui provoque l’obtention d’une ressource nécessaire à la seconde connexion dans les 2 cas, provoquant l’inter blocage. La résolution du problème est extrêmement trivial l’insertion de l’accès aux tables de l’une ou l’autre supprimera définitivement le problème. Limiter la durée des transactions Les transactions doivent être les plus courtes possibles. Bannissez les interactions avec l’utilisateur dans celle-ci et l’utilisation de procédures stockées étendues doit être limitée. Les lectures peuvent être exclues de la transaction dans le cas de l’utilisation du niveau d’isolation par défaut. Finalement seules devraient se trouver les modifications de données dans des cas courant. L’usage de déclencheur sur les données prolongeant la durée d’exécution des transactions, eux aussi doivent être évités. C’est une bonne pratique à incorporer en amont dans le développement de code SQL de l’application. Utiliser un niveau d’isolation à base de versionnement Les niveaux d’isolation à base de versionnement READ COMMITTED SNAPSHOT et SNAPSHOT sont des solutions assez élégantes aux problèmes de blocages et même inter blocage. Le versionnement empêchant le blocage des écritures par les lectures et réciproquement cela apporte un gain significatif dans de multiples situation. Cependant, assurez-vous que cette solution ne vous provoque pas de problème de performance disque. De plus, les niveaux d’isolation à base de versionnement ne modifieront en rien les blocages entre les écritures via des verrous exclusifs. Certains inter-blocages pourront toujours se produire y compris à ces niveaux d’isolation. Utilisation de la directive UPDLOCK et d’autres Le cas exposé dans une section précédente de la réservation de voyage, nécessite de lire les données avant de les modifier. Pour garantir la préservation des données lues il est nécessaire de monter le niveau d’isolation à REPEATABLE READ. Mais cela nous amène un autre problème, un inter blocage se produit lors de l’exécution des commandes suivantes : set transaction isolation level repeatable read
begin transaction
select * from person.person where businessentityid = 14
waitfor delay '00:00:10'
update person.person set firstname = 'christian', lastname = 'robert' where businessentityid = 14
rollback transaction Dans une seconde connexion exécutée simultanément : set transaction isolation level repeatable read
begin transaction
select * from dbo. where businessentityid = 14
waitfor delay '00:00:10'
update person.person set firstname = 'christian', lastname = 'robert' where businessentityid = 14
rollback transaction Un inter blocage s’est produit : les 2 sessions ont acquises un verrou partagé sur la même ressource, et demandent toutes 2 à le convertir en verrou exclusif. Or le verrou exclusif est incompatible avec le verrou partagé que les 2 sessions possède. La solution à ce problème consiste en l’utilisation d’un verrou de mise à jour dès la première lecture, signifiant notre intention aux autres transactions : set transaction isolation level repeatable read
begin transaction
select * from dbo. With(updlock) where businessentityid = 14 Indexer correctement les données et limiter l’usage des curseurs Le type d’accès aux données est aussi important au niveau blocages et inter-blocages. Le moteur peut accéder à une données via un index ou en lisant la table directement. Le fait que 2 chemins d’accès existent vers la même donnée amplifie le risque de blocage. Pour cette raison l’indexation correcte des données diminue fortement ces risques, tout en augmentant les performances générales de lectures des données. La remarque s’applique aussi aux accès à base de curseur qui s’apparentent à une lecture complète de table accéder enregistrement par enregistrement. L’utilisation des curseurs augmente ainsi de la même façon les risques.
|