SQL Server : Gestion des transactions, verrous et concurrence d’accès aux données
S’il y a un point où les améliorations sont visibles dans toutes les versions de SQL Server, c’est bien l’isolation des données, et la gestion de la concurrence d’accès aux données. Les quelques points clefs des différentes versions depuis SQL Server 7 :
- Dans toutes les versions depuis SQL Server 7 et peut être même un peu avant pour certaines fonctionnalités
- Support des niveaux d’isolation de la norme ISO SQL : niveaux d’isolation dit « pessimistes », à base de verrous
- Support du verrouillage au niveau « Page » et « Table »
- Possibilité de lister les verrous et les demandes de verrous
- Résolution automatique des Interblocages (DeadLock)
- Choix automatique de la granularité du verrou en fonction des données impactées par la requête ==> Limite la consommation des ressources serveur
- Escalade de verrou (Lock Escalation) automatique ==> Limite la consommation des ressources serveur
- Contrôle du Timeout sur une demande verrou SET LOCK_TIMEOUT
- SQL Server 7
- SQL Server 2000
- Contrôle de l’escalade de verrou au niveau instance par TraceFlag (1224 / 1211)
- SQL Server 2005
- SQL Server 2008
- Introduction du verrouillage au niveau « partition »
- Possibilité d’interdire les verrous de niveau « table »
- Contrôle de l’escalade de verrou au niveau table par commande SQL
De plus d’une manière générale le système d’isolation de SQL Server, n’interdit en aucun cas l’exécution simultanée de 2 requêtes, y compris dans le mode d’isolation SERIALIZABLE. Les blocages ne se produisent qu’en cas de verrouillage de la même ressource avec des verrous incompatibles.
Exemples de blocages :
- 2 Sessions mettent à jour simultanément le même enregistrement.
La seconde restera bloquée tant que la première n’a pas terminée sa transaction par une COMMIT ou ROLLBACK
- 1 Session met à jour un enregistrement, tandis qu’une autre effectue une lecture de la table.
La lecture se mettre en attente de la fin de l’écriture pour avoir une vue consistante des données. Les niveaux d’isolation SNAPSHOT de 2005 permettent de lire l’état précédent en ne bloquant pas la lecture, avec les autres versions le mot clef NOLOCK permet la lecture, mais avec risque d’inconsistance des données (données fausses). http://blogs.codes-sources.com/christian/archive/2007/03/08/sql-server-les-verrous-et-l-utilisation-de-nolock.aspx
Exemples où il n’y aura pas de blocage :
- 2 Sessions effectuent une lecture complète de table.
En effet les lectures ne bloquent que les écritures et uniquement dans le cadre des niveaux d’isolation pessimistes (verrous).
- 2 Sessions effectuent la mise à jour d’un enregistrement différent.
Pas de blocage car le choix du moteur se portera sur un verrou d’enregistrement (Il y a de rares exceptions, liés par exemple à la taille très réduite de la table), les ressources verrouillés n’étant pas les mêmes, aucun blocage.
Bonne gestion de l’isolation…
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
