Cette publication ne fait pas partie de la bibliothèque YouScribe
Elle est disponible uniquement à l'achat (la librairie de YouScribe)
Achetez pour : 5,00 € Lire un extrait

Lecture en ligne + Téléchargement

Format(s) : PDF

sans DRM

SQL Server gérer la concurrence et les locks

De
129 pages
La gestion de la concurrence est un des problèmes subis par les développeurs qui utilisent SQL Server.
Ce livre va leur apprendre comment le système de locks fonctionne dans un premier temps, puis à mettre en œuvre des stratégies pour éviter les attentes et autres deadlocks.
Des solutions avancées comme le partitionnement de données ou le mode RCS seront aussi étudiées.
Voir plus Voir moins



SQL Server
2008 R2 à
2016
Gérer la concurrence et les locks
Dominique Verrière
Dialogue Formation Informatique
SQL Server 2008 R2 à 2016




















SQL Server 2008R2-2016 : Gérer la concurrence et les locks par Dominique Verrière 2 Pourquoi cet ouvrage ?


J’ai récemment animé une session de la « Master class pour SQL Server » chez ORSYS.
Cette session n’a pu que confirmer ce que je constate en entreprise tous les jours en tant que consultant : les
développeurs sont de plus en plus éloignés de la base de données (notamment au travers de frameworks tels
Entity Framework) et subissent plus qu’ils ne maitrisent le moteur de bases de données.
La gestion du verrouillage étant l’un des sujets les plus subis par les développeurs, j’ai décidé de mettre tout
cela au clair afin d’aider les développeurs et les DBA (dont je fais partie) à ne pas se mettre dans des situations
impossibles.
Dans les missions de consulting que je mène, il n’est pas rare de voir que ces problèmes peuvent entrainer la
‘mort’ du système, les solutions matérielles étant impuissantes à résoudre les conséquences des locks et autres
deadlocks.
Je remercie ici mes fidèles relecteurs, Rudi Bruchez (!), David, Mika, qui m’ont aidé à améliorer ce document.

Dominique Verrière
Juin 2017


Table des matières
I : Notions de base ....................................................................................................... 9
Des locks en lecture? ........... 10
Comportement par défaut .............................. 10
Notion de transaction .......................................................................................................................................... 12
Transactions explicites et implicites ................ 12
Transactions imbriquées ................................................................................................................................. 13
Le niveau d’isolation ou isolation level ................................................................................................................ 15
Niveau read uncommitted ............................... 15
Niveau read committed ................................................................................................................................... 16
Niveau repeatable read ... 18
Le niveau serializable ....... 20
Vérifier le niveau d’isolation des sessions en cours ........................................................................................ 21
Définir un niveau d’isolation ............................................................... 22
Sous Transact SQL ............................................................................................................ 22
Transaction et niveau d’isolation avec ADO.Net ............................................................................................. 23
Transaction et niveau d’isolation avec Entity Framework .............. 24
Gérer les transactions distribuées ................................................................................................................... 26
Transactions distribuées avec Entity Framework ............................ 29
Transactions distribuées avec Transact SQL .................................................................................................... 31
Conclusions .......................................................................................... 35
II : Surveillance et outils ............................................................. 37
Surveiller les locks ............................................................................................................... 38
Utiliser la procédure stockée sp_lock .............. 38
Créer une procédure personnalisée de surveillance des locks ....................................................................... 39
Utiliser le moniteur de ressources de Management Studio................ 41
Les tâches en attente :..................................................................... 41
Les attentes de ressource ................................................................ 42
Utiliser les événements étendus ......................................................................................... 42
Utiliser le profiler ................................................................................. 44
Lancer des tâches concurrentes .......................... 45
III : Stratégies de verrouillage ..................................................................................... 48

SQL Server 2008R2-2016 : Gérer la concurrence et les locks par Dominique Verrière 5 Sommaire

Points à considérer pour ces stratégies ............................................................................................................... 49
Granularité de verrouillage ............................. 49
Mode de verrouillage ...................................................................................................................................... 50
Compatibilité des locks .... 50
Durée de vie ..................................................................................................................................................... 51
Etude des verrouillages standards ...................... 51
Les updates bloquent les select ? .................... 51
Les inserts bloquent les select ? ...................................................................................................................... 53
Les delete bloquent les select ? ....................... 54
Escalade de locks : lock escalation ...................... 56
Lock escalation en select ? .............................................................................................................................. 56
Requête sans escalade de lock ........................ 56
Requête avec escalade de locks ...................................................................................................................... 58
Quelle est la valeur du seuil ? .......................... 59
Que se passe-t-il si deux processus veulent escalader ? ................. 59
Que faire si on détecte beaucoup de ces escalades ? ..................................................................................... 60
Influence de la méthode d’accès aux données ............................... 61
Concurrence optimiste versus pessimiste ........................................................................................................... 64
Technique des old values : ................................ 66
Gestion des old values avec ADO.Net ............. 66
Gestion des old values avec Entity Framework ............................................................................................... 66
Technique du timestamping ............................................................ 69
Gestion du timestamping avec Entity Framework .......................... 69
Conclusion sur la concurrence optimiste ........................................................................................................ 70
Utiliser les lock time out ...................................... 71
Comprendre et gérer les deadlocks .................................................................................................................... 72
Notions théoriques .......... 72
Quel processus est tué ? .................................................................................................................................. 73
Affinage au moyen du profiler......................... 74
Etude d’une capture au moyen d’une session d’événements étendus .......................................................... 75
Paramétrage d’une session de surveillance des deadlocks (2012 et+) ........................... 77
Comment éviter les deadlocks ? ...................................................................................... 79
Se déclarer en victime de deadlock avec Transact SQL ................................................... 81

SQL Server 2008R2-2016 : Gérer la concurrence et les locks par Dominique Verrière 6
Se déclarer victime de deadlock avec Entity Framework ................................................................................ 82
Conclusion sur la concurrence des lectures et mises à jour 84
IV : Modes de gestion avancée ................................................................................... 85
Partitionnement physique des tables ................................................. 86
Introduction ..................................................... 86
Que faire ? ....................................................................................... 86
Mise en œuvre simple avec SQL Server 2016 ................................................................. 87
Mise en œuvre de partitionnement par script ................................ 91
Et si nous revenions à nos locks ? .................................................................................... 96
Exemple complet de partitionnement avec escalade de locks ....................................... 97
Utilisation pratique du partitionnement ....................................................................... 102
Mise en œuvre du read committed snaphot ..... 103
Définition ....................................................................................................................................................... 103
Paramétrage de la base en mode read committed snapshot (RCS) .............................. 103
Lectures non bloquées par les mises à jour .................................................................................................. 104
Prix à payer pour la gestion des versions ...... 105
Aspects fonctionnels...................................... 108
Lectures en mode serializable ....................................................................................................................... 109
Mise à jour bloque mise à jour ? ................... 110
Votre application est-elle prête ? .................. 110
Mise en œuvre du snapshot .............................................................................................................................. 112
Mises à jour non bloquantes ......................... 112
Nombre de versions en SI 114
Coût de ce paramétrage ................................................................................................................................ 115
Mises à jour impossibles 119
Un petit tour dans les internals ......................................................................................................................... 120
Conclusions .................................................... 125
Contacter l’auteur : .................................................................................................. 128
Présentation de cet ouvrage .................... 129




SQL Server 2008R2-2016 : Gérer la concurrence et les locks par Dominique Verrière 7 Sommaire




SQL Server 2008R2-2016 : Gérer la concurrence et les locks par Dominique Verrière 8

Un pour Un
Permettre à tous d'accéder à la lecture
Pour chaque accès à la bibliothèque, YouScribe donne un accès à une personne dans le besoin