[SQL Server] Accès concurrentiels : présentation des verrous

Ce billet présente les verrous supportés par SQL Server, et aborde également quelques notions liées au blocage.

Notions de verrouillage et de blocage

Qu’est-ce qu’un verrou et un blocage ?

Un verrou est une fonctionnalité de base de données permettant de contrôler un environnement multi-utilisateur concurrentiel. Son intérêt est de permettre d’assurer l’intégrité des données quand plusieurs utilisateurs (ou transactions) tentent d’écrire au sein de la base de données.

Un blocage est un événement caractéristique des bases de données relationnelles comme SQL Server. Cette situation intervient quand une transaction maintient un verrou sur une ressource spécifique et une autre transaction tente d’acquérir un verrou sur cette même ressource. En temps normal, le maintien d’un verrou est bref, ce qui permet à l’autre transaction d’accéder à la ressource sitôt la libération du verrou de la première transaction faite.

Cela est un comportement parfaitement normal car permet de garantir l’intégrité et la consistance des données. Et il existe diverses façons de consulter/analyser des événements liés au verrouillage : DMV (sys.dm_tran_locks, notamment), procédures stockées systèmes (sp_lock, tout particulièrement), le SQL Server Profiler,…

En principe, il n’est généralement nul besoin de contrôler explicitement les verrous, SQL Server le faisant très bien et de manière dynamique (on parle, ici, de verrouillage dynamique ou dynamic locking qui consiste à choisir la stratégie de verrouillage la moins coûteuse), et l’utilisateur pouvant simplement se contenter d’adapter (si nécessaire) le niveau d’isolation de ses transactions. Il peut néanmoins arriver, de façon exceptionnelle, que l’on soit amené à « jouer » avec les verrous si d’aventure l’on est confronté à des problèmes transactionnels (conflits intempestifs, inter-blocages,…).

Les différents indicateurs de verrouillage

Les opérations de modification (INSERT, UPDATE, DELETE) sont toujours accompagnées de verrous exclusifs afin de garantir l’intégrité transactionnelle des données. Toutefois,
SQL Server offre différents indicateurs (hints) de verrouillage pouvant accompagner des requêtes de sélection (SELECT)
pour des besoins spécifiques :

  • READCOMMITTED à hint qui utilise le comportement par défaut de SQL Server du point de vue des accès concurrentiels. Comparable au fonctionnement d’une transaction utilisant le niveau d’isolation READ COMMITTED permettant de n’accéder qu’aux données validées.
  • NOLOCK (ou READUNCOMMITTEDà hint permettant de lire des données ayant été verrouillées pour un usage exclusif. Ce type d’indicateur est sujet aux lectures « sales » (c’est-à-dire inconsistantes).
  • READPAST à hint permettant de lire des lignes d’enregistrement en ignorant celles verrouillées.
  • REPEATABLEREAD à hint permettant de forcer la non-libération de verrous lancés par la requête (ou transaction) tant que cette dernière n’est pas terminée. Ainsi, aucune modification (UPDATE, DELETE) de données lues par la requête ne sera possible tant qu’elle ne sera pas complétée. En revanche, cela n’empêche pas les insertions. Cela est comparable à une transaction exécutée avec le niveau d’isolation REPEATABLE READ.
  • HOLDLOCK (ou SERIALIZABLEà hint permettant de maintenir un verrou. Par exemple, quand on lance une instruction SELECT, des verrous partagés sont posés puis libérés au fur et à mesure que chaque ligne ou page est lue. Or, en cas d’utilisation de HOLDLOCK, lesdits verrous seront maintenus jusqu’à la fin de la transaction.
  • UPDLOCK à hint (inhabituel) permettant de forcer l’utilisation d’un verrou de mise-à-jour au détriment d’un verrou partagé.
  • ROWLOCK à hint permettant de forcer l’utilisation d’un verrou partagé au niveau ligne.
  • PAGLOCK à hint permettant de forcer l’utilisation d’un verrou partagé au niveau page.
  • TABLOCK à hint permettant de forcer l’utilisation d’un verrou partagé au niveau table.
  • TABLOCKX à hint permettant de forcer l’utilisation d’un verrou exclusif au niveau table. Pas de consultation possible par les transactions tierces de la table durant toute la durée de vie dudit verrou.
  • XLOCK à hint permettant de forcer l’utilisation de verrous exclusifs sur toutes les lignes de données traitées par la requête, et leur maintien jusqu’à la fin de celle-ci.

Les notions de verrous partagés et exclusifs seront abordés plus loin.

Voici ci-dessous un exemple de syntaxce d’utilisation d’un indicateur :

SELECT * FROM MaTable WITH (hint)
GO

hint est un indicateur spécifique (NOLOCK, TABLOCKX,…).

Les niveaux de granularité et notion d’escalade de verrou (lock escalation)

SQL Server supporte différents niveaux de granularité pour le verrouillage d’objets :

  • Verrouillage de ligne/clé (row ou RID lock/key lock) à il s’agit du niveau de granularité par défaut. Le verrou est posé au niveau de la ligne ou de la clé (dans le dernier cas, c’est surtout dans un index utilisé pour le traitement de plage de clés par des transactions susceptibles d’être sérialisées. Voir sous-section Les verrous de (plages de) clé(s), plus loin)).
  • Verrouillage de page (page lock).
  • Verrouillage de table (table lock), avec possibilité de l’interdire à partir de SQL Server 2008.
  • Verrouillage d’extent (extent lock).
  • Verrouillage de base de données (database lock).

D’autres niveaux de verrouillage existent (HoBT pour une table en Heap ou une structure d’index BTree, fichier, application, métadonnée,…). Plus de détails ici : http://msdn.microsoft.com/fr-fr/library/ms189849.aspx.

A partir de SQL Server 2008, il est possible d’effectuer un contrôle de l’escalade de verrou (lock escalation) au niveau table ou partition en ligne de commande SQL : ALTER TABLE MaTable SET (LOCK_ESCALATION =
[TABLE | AUTO |DISABLE]), où TABLE permet une escalade niveau table ; AUTO, une escalade niveau table ou partition (si la table est partitionnée) ; DISABLE, interdit l’escalade pour la table cible.

En outre, il existe également des niveaux de granularité dédiés aux indexes (possible à partir de SQL Server 2005). Citons :

  • ALLOW_ROW_LOCK à lors du traitement d’un index, permet à SQL Server d’effectuer un verrouillage au niveau des lignes.
  • ALLOW_PAGE_LOCK à lors du traitement d’un index, permet à SQL Server d’effectuer un verrouillage au niveau des pages. Cette option de granularité de verrouillage est requise si une réorganisation d’index est envisagée.

Si les 2 options ne sont pas activées (c’est-à-dire à OFF), un verrouillage au niveau table sera utilisé. Et si les 2 sont activées sont même temps, SQL Server décidera en fonction de la quantité de lignes à traiter et des ressources disponibles.

La modification de ces options peut se faire soit via sp_indexoption, soit (et cela est plus pratique) en utilisant la clause ALTER INDEX :

ALTER INDEX MonIndex ON MaTable
 SET (ALLOW_PAGE_LOCKS=ON)
GO

Vous pouvez remplacer ALLOW_PAGE_LOCKS par ALLOW_ROW_LOCKS et/ou substituer la valeur ON par OFF.

Notion de délai d’attente de verrouillage (lock timeout)

SQL Server permet de contrôler les délais d’attente de verrouillage. Cela est possible en spécifiant, via SET LOCK_TIMEOUT, une valeur (en millisecondes) en guise de temps d’attente qu’une instruction doit respecter avant de libérer un verrou.

Par défaut, la valeur est à -1, ce qui indique un temps d’attente illimité, ce qui signifie que toute transaction en attente le restera théoriquement pour toujours, tant que le verrou qui le bloque ne sera pas libéré.

La requête suivante change le délai d’attente de verrouillage à 5 secondes :

SET LOCK_TIMEOUT 5000
GO
A noter qu’en cas de délai d’attente écoulé, l’erreur 1222 est levée : « Lock request timeout period expired. » ou « Le délai d’attente est écoulé . ». Si cela se produit trop fréquemment, vous pouvez envisager l’idée de modifier la valeur LOCK_TIMEOUT afin que le verrouillage soit maintenu moins longtemps (5 secondes, par exemple). Dans tous les cas, jetez un coup d’œil à la transaction incriminée (via les DMV sys.dm_os_waiting_tasks et/ou sys.dm_tran_locks, que nous aborderons dans un autre billet) afin de l’optimiser, si possible.

Description des principaux types de verrous

Les grands modes de verrouillage traditionnels

Les modes de verrouillage (ou types de verrous) traditionnels les plus importants sont :

  • Les verrous partagés ou shared locks (S) à utilisés pour les lectures durant les opérations de consultation (SELECT).
  • Les verrous exclusifs ou exclusive locks (X) à utilisés pour les écritures durant les opérations de modification de données (INSERT, UPDATE, DELETE). Ils permettent d’éviter qu’un objet soit modifié par une session tierce durant son traitement. Ils sont toujours acquis et maintenus jusqu’à la fin d’une transaction d’écriture.
  • Les verrous de mise-à-jour ou update locks (U) à utilisés quand des modifications sont à effectuer. Il s’agit d’un mix entre les verrous partagés et les verrous exclusifs. Concrètement, quand SQL Server souhaite mettre à jour une ligne de données, par exemple, il pose un verrou de mise-à-jour sur toutes les lignes de la table à laquelle appartient la ligne à traiter, puis les balaye une par une jusqu’à tomber sur la ligne qui correspond à la requête. Et à ce moment-là, quand la ligne est trouvée, le verrou de mise-à-jour se transforme en verrou exclusif (X).

Les verrous intentionnels

A ces principaux types de verrous se rajoutent également les verrous intentionnels ou intent locks qui permettent d’indiquer la présence de verrous au niveau des objets enfants. L’intérêt d’un verrou intentionnel repose sur l’amélioration de performances. En effet, quand il n’y a pas de verrou intentionnel, SQL Server est obligé de vérifier la présence ou non de verrous au niveau de la table, des extents, des pages, des lignes et des clés, ce qui peut lui prendre du temps.

Les verrous intentionnels permettent également de réduire les risques de contention au niveau des verrous partagés (S). Lorsqu’une transaction T1 pose un verrou partagé sur un objet (une ligne, par exemple), une autre transaction T2 s’apprêtant à y accéder ne peut pas poser de verrou exclusif sur l’objet. Or, qu’adviendrait-il si la transaction T1 pose un verrou partagé et le garde relativement longtemps ? Un risque de blocage permanent pour la transaction T2, cette dernière étant dans l’incapacité de poser un verrou exclusif pour réaliser ses modifications. En revanche, si la transaction T2 se dote d’un verrou intentionnel exclusif (IX), aucune autre transaction ne peut s’approprier un verrou partagé.

Il existe plusieurs types de verrous intentionnels, dont en voici quelques-uns :

  • Les verrous intentionnels de partage…
    • … ou intent shared locks (IS).
    • … exclusif ou shared with intent exclusive locks (SIX).
  • Les verrous intentionnels exclusifs ou intent exclusive locks (IX).
  • Les verrous intentionnels de modification…
    • … ou intent update locks (IU).
    • … partagée et modification simultanée ou shared with intent update locks (SIU).
    • … exclusive (UIX).

Les verrous de (plages de) clé(s)

Les verrous de clé ou key(-range) locks sont utilisés pour protéger une plage de valeurs de clé (cas des key-range locks) ou une simple valeur de clé (cas des key locks) de table ou d’index lues dans un jeu de résultats d’une requête, quand le niveau d’isolation SERIALIZABLE est adopté. Concrètement, les verrous de clé permettent d’interdire à des transactions tierces d’insérer de nouvelles lignes de données dont les clés existent déjà dans la plage de clés lues par la transaction sérialisée. Cela permet de garantir un jeu de lignes équivalent pour toute requête exécutée lors de ladite transaction, empêchant ainsi les lectures « fantômes » (nous en discuterons en détail dans des billets indépendants sur les niveaux d’isolation et les anomalies transactionnelles).

Il existe 4 modes de verrouillage de plage de clés :

  • Mode de verrouillage de ressource partagé avec plage partagée (RangeS-S) à pour l’analyse sérialisée.
  • Mode de verrouillage de ressource exclusif avec plage exclusive (RangeX-X) à pour la mise-à-jour d’une clé dans une plage.
  • Mode de verrouillage de mise-à-jour de ressource avec plage partagée (RangeS-U) à utilisé sur une plage de valeurs de clés spécifiques à mettre à jour.
  • Mode de verrouillage de ressource NULL avec plage d’insertion (RangeI-N)
    à pour le contrôle des valeurs de la plage avant insertion.

Pour finir, l’utilisation d’un verrou de (plages de) clé(s) est principalement soumis aux conditions suivantes :

  1. Utilisation du niveau d’isolation SERIALIZABLE.
  2. Utilisation d’un index.
  3. Filtrage de la requête cible avec une clause WHERE permettant le renvoie d’une plage de valeurs spécifiques.

Plus de détails ici : http://msdn.microsoft.com/en-us/library/ms191272.aspx.

Les verrous de mise-à-jour de données en bloc

Les verrous de mise-à-jour en bloc ou bulk-update locks (BU) sont utilisés durant la copie en bloc de données (BULK INSERT ; INSERT INTO … SELECT * FROM… ; commande bcp) par exemple) dans une table avec l’hint
TABLOCK ou l’option table lock on bulk load de sp_tableoption activée. Ils permettent à plusieurs threads concurrents de charger des données en bloc au sein d’une même table tout en empêchant toute autre transaction qui ne participe pas à ces opérations d’accéder à ladite table.

Les verrous de schéma

Les verrous de schémas sont utilisés pour protéger des schémas de bases de données. Il en existe 2 types :

  • Verrous de stabilité de schéma ou schema stability locks (Sch-S) à utilisés pour assurer la stabilité d’un schéma en interdisant toute modification DDL (Data Definition Language).
  • Verrous de modification de schéma ou schema modification locks à utilisés quand SQL Server souhaite apporter des modifications sur le(s) schéma(s). Par exemple, si SQL Server en train de modifier physiquement une table (via l’ajout d’une colonne, par exemple), un verrou de type Sh-M est posé pour empêcher toute autre transaction d’accéder aux données durant l’opération de modification DDL.

Compatibilités inter-verrous

Le tableau ci-dessous présente les compatibilités entre verrous en répondant à la question « Le verrou 2 est-il compatible avec le verrou 1 préexistant ? » :

Verrou 1 S Verrou 1 X Verrou 1 U Verrou 1 RangeS-S Verrou 1 RangeX-X Verrou 1 RangeS-U Verrou 1 RangeI-N
Verrou 2 S Oui. Non. Oui. Oui. Non. Oui. Deviennent RangeI-S.
Verrou 2 X Non. Non. Non. Non. Non. Non. Deviennent RangeI-X.
Verrou 2 U Oui. Non. Non. Non. Non. Non. Deviennent RangeI-U.
Verrou 2 RangeS-S Oui. Non. Oui. Oui. Non. Oui. Non.
Verrou 2 RangeX-X Non. Non. Non. Non. Non. Non. Non.
Verrou 2 RangeS-U Oui. Non. Non. Non. Non. Non. Non.
Verrou 2 RangeI-N Oui. Oui. Oui. Deviennent RangeX-S. Non. Deviennent RangeX-U. Oui.

Comme on peut le noter, certains modes de verrouillage de type key-range locking peuvent interagir avec les modes de verrouillages traditionnels (S, X, U…) et donner lieu à une fusion (ou conversion) de verrous. Une fusion est également possible entre certains modes de verrouillages key-range locking. En revanche, cela n’est pas possible, ni compatible avec un verrou intentionnel.

Le tableau ci-dessous aborde, justement, les compatibilités entre les verrous intentionnels et ceux traditionnels :

Verrou 1 S Verrou 1 X Verrou 1 U Verrou 1 IS Verrou 1 IX Verrou 1 IU Verrou 1 SIX Verrou 1 SIU Verrou 1 SIU
Verrou 2 S Oui. Non. Oui. Oui. Non. Oui. Non. Oui. Non.
Verrou 2 X Non. Non. Non. Non. Non. Non. Non. Non. Non.
Verrou 2 U Oui. Non. Non. Oui. Non. Non. Non. Non. Non.
Verrou 2 IS Oui. Non. Oui. Oui. Non. Oui. Oui. Oui. Oui.
Verrou 2 IX Oui. Non. Non. Non. Oui. Oui. Non. Non. Non.
Verrou 2 IU Oui. Non. Non. Oui. Oui. Oui. Oui. Non. Non.
Verrou 2 SIX Non. Non. Non. Oui. Non. Oui. Non. Non. Non.
Verrou 2 SIU Oui. Non. Non. Oui. Non. Non. Non. Oui. Non.
Verrou 2 UIX Non. Non. Non. Oui. Non. Non. Non. Non. Non.

Pour ce qui est des verrous…

  • … de mise-à-jour en bloc (BU) : il n’y aucun verrou compatible, hormis ceux de type BU ou Sch-M ou NOLOCK.
  • … de schémas :
  • Sch-S est compatible avec n’importe quel verrou, sauf ceux intentionnels et Sch-M.
  • Sch-M n’est compatible avec aucun verrou, hormis NOLOCK.

En guise d’informations complémentaires et détaillées sur les compatibilités inter-verrous, vous pouvez jeter un coup d’œil ici : http://msdn.microsoft.com/en-us/library/ms186396.aspx.

Pour aller plus loin…

Ce billet a été l’occasion de présenter les principaux concepts théoriques régissant les verrous. Pour consulter d’autres billets relatifs aux accès concurrentiels, gardez un oeil ici.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s