Transactions et verrous

Chapitre 07 - Transactions et verrous

46 minutes à lire

Transactions et verrous

Objectif

La transaction est le mécanisme de base du maintien de la cohérence d’un SGBD client-serveur. Il est donc important de bien le connaître. L’outil mis en place par les transactions pour assurer cette cohérence est le verrouillage. Un verrouillage excessif heurte les performances. Il indispensable de maîtriser ce sujet et de limiter autant que possible l’étendue et la durée des transactions, donc du verrouillage, pour éviter les attentes trop longues, les blocages, et les étreintes fatales (deadlocks).

Qu’est-ce que la transaction ?

Un SGBDR est un système multi-utilisateurs, de ce point de vue, ses responsabilités sont doubles : il doit assurer un accès concurrent aux données, et assurer que celles-ci restent dans un état cohérent, par rapport aux contraintes définies par la structure de la base, aux modifications d’ensembles de données, et aux potentialités d’écritures simultanées. Cette protection est assurée par le mécanisme de la transaction. Les transactions permettent de contrôler la cohérence des données et de ne permettre une validation que lorsque celle-ci est respectée. Lors de toute modification, la base est dans un état incohérent durant un laps de temps plus ou moins long. Par exemple, si nous voulons mettre tous les noms de famille de la table Person.Contact en majuscules, nous exécutons le code suivant :

UPDATE Person.Contact
SET LastName = UPPER(LastName);

qui met à jour près de 20 000 lignes. Que se passe-t-il si une erreur se produit au milieu de cette modification, ou si, en même temps, une autre session change le nom d’un contact ? Du point du vue du SGBDR, ce sont des incohérences. Pour assurer que cela ne se produise pas, toute modification (insertion, suppression, mise à jour) de données est obligatoirement protégée par un mécanisme transactionnel qui garantit, à travers quatre critères, la consistance de cette modification. Ces critères sont nommées « l’acidité » de la transaction, par leur acronyme (ACID). Une transaction doit être :

**Atomique ** – une transaction est atomique, parce qu’elle représente une seule unité de modification, même si la mise à jour porte sur plusieurs milliers de lignes, ou plusieurs tables. Cela signifie qu’une telle modification sera totalement validée, ou totalement annulée. La base de données ne restera jamais dans un état intermédiaire, où une partie seulement des modifications est enregistrée.
**Cohérente **– elle est cohérente, parce qu’elle ne peut violer les contraintes de la base de données, ou laisser la base dans un état intermédiaire. Si dans une mise à jour de cent lignes, une ligne viole une contrainte d’intégrité référentielle (une clé étrangère), la transaction sera totalement annulée. La base passe d’un état cohérent avant la transaction, à un état cohérent après validation.
**Isolée **– la transaction s’exécute dans un contexte d’isolation des autres transactions. Les lignes touchées par une modification sont protégées contre un accès par une autre transaction, que ce soit en lecture (selon le niveau d’isolation, nous le verrons), ou surtout, en écriture.
**Durable **– lorsque la transaction est validée, elle est durable, c’est-à-dire que les modifications sont définitivement inscrites dans la base de données, sauf défaillance matérielle du disque, bien entendu.

Pour illustrer l’utilité de cette acidité, imaginez-vous une partie de billard. Lorsque c’est votre tour de jouer, vous restez à la table tant que vous placez une bille dans une poche. Quand vous avez fini, vos points sont enregistrés, on ne peut revenir en arrière. Votre tour est donc atomique et durable. Vous ne pouvez violer les règles du jeu, sinon votre action est nulle, c’est donc consistant. Et, le plus important, personne ne peut jouer en même temps que vous : lorsque c’est votre tour, personne d’autre ne touche les billes sur la table, sinon, le jeu ne signifie plus rien.

La durabilité et l’atomicité sont possibles grâce au journal de transactions, qui permet de rejouer ou d’annuler une transaction, qu’elle que soit sa durée. L’isolation, qui est l’attribut qui garantit la cohérence des modifications tout en diminuant la concurrence, est gérée à travers le mécanisme de verrouillage.

Toute instruction DML de modification de données (INSERT, UPDATE, DELETE, MERGE) est encapsulée dans une transaction. Un seul UPDATE par exemple, quelque soit le nombre de lignes qu’il affecte, est donc ACID. Le SELECT ne génère aucune transaction. Pour enrôler plusieurs instructions dans une seul transaction, et donc les rendre ACID, vous devez déclarer une transaction utilisateur explicite, à l’aide des instructions TCL (Transaction Control Language) du langage SQL :

BEGIN TRANSACTION – débute une transaction explicite ;
SAVE TRANSACTION – introduit un point de sauvegarde. On pourra ensuite valider ou annuler la partie de la transaction jusqu’à ce point ;
COMMIT TRANSACTION – valide la transaction et la termine ;
ROLLBACK TRANSACTION – annule la transaction et la termine.

Ces commandes permettent donc d’étendre les principes d’ACIDité de la transaction à plusieurs instructions DML ou DDL (en SQL Server, les instructions DDL sont transactionnelles). Dans les faits, cela transforme une suite d’ordres SQL en un seul ordre logique.

Validation automatique et annulation automatique

En SQL Server, le terme « transaction implicite » est utilisé pour décrire un fonctionnement connu sous le nom de « validation automatique » (autocommit). Par défaut, une session est en mode autocommit, c’est-à-dire qu’une instruction DML transactionnelle est automatiquement validée ou annulée (si elle provoque une exception) après exécution. Vous pouvez changer ce mode à l’aide de la commande SET IMPLICIT_TRANSACTIONS { ON | OFF }. En mode IMPLICIT_TRANSACTIONS ON, toute instruction doit valider ou annuler la transaction par un COMMIT ou un ROLLBACK. Ce mode est dangereux et en général inutile, ne l’utilisez donc pas.

De même, l’option SET XACT_ABORT { ON | OFF } permet d’annuler automatiquement une transaction explicite lorsqu’une instruction provoque une exception. Elle est activé par défaut. Elle influe sur la valeur de la fonction XACT_STATE() qui peut être testée, en général dans la partie CATCH d’un bloc TRY CATCH, pour connaître l’état de la transaction. Après une exception, si XACT_ABORT est à OFF, XACT_STATE() vaudra 1 (transaction active et validable. Si elle est à ON, XACT_STATE() vaudra -1 (transaction active et en erreur). Lorsque XACT_STATE() vaut -1, seul un ROLLBACK est possible.

Dans la pratique, il est rare de vouloir gérer une validation partielle de la transaction, cela peut provoquer plus facilement de la confusion que des fonctionnalités utiles.

Le verrouillage

Le principe de la transaction qui nous intéresse plus particulièrement en regard des performances, est l’isolation. Il consiste à assurer qu’une seule transaction à la fois puisse accéder aux mêmes données. Cette isolation est réalisée à travers le mécanisme de verrouillage. Dès qu’une transaction accède à une ressource, celle-ci est verrouillée (le moteur de stockage pose un verrou sur l’objet : la ligne, la page ou la table), afin de la protéger des accès concurrents. Il existe plusieurs types de verrous (ou modes de verrouillage), qui sont compatibles ou non entre eux, et plusieurs granularités. Nous allons les passer en revue.

Les modes de verrouillage

Vous trouvez dans le tableau 7.1 une liste des types de verrous les plus fréquemment rencontrés dans SQL Server.

7.1 les modes de verrouillage

Mode Nom Description

S Shared Verrou partagé

X Exclusive Verrou exclusif : une écriture est en cours sur la ressource verrouillée.

U Update Verrou de mise à jour :

IS Intent Shared Verrou d’intention partagé. Un verrou partagé (S) est posé à une granularité plus fine.

IX Intent Exclusive Verrou d’intention exclusif. Un verrou exclusif (X) est posé à une granularité plus fine.

SIX Shared Intent
Exclusive

Sch-S Schema Shared Verrou partagé de schéma : lors de l’utilisation d’un objet, ce verrou empêche une modification de la structure de cet objet.

Les verrous ont une compatibilité entre eux, cela signifie que deux verrous compatibles peuvent être simultanément posés sur la même ressource. Les verrous incompatibles sont mutuellement exclusifs. Le tableau 7.2 présente la compatibilité des principaux modes de verrouillage.

7.2 Compatibilité des verrous

Mode S X U IS IX SIX

S oui non oui oui non non

X non non non non non non

U oui non non oui non non

IS oui non oui oui oui oui

IX non non non oui oui non

SIX non non non oui non non

Les verrous partagés (shared, S) sont des verrous de lecture. Ils sont posés lors de tout SELECT, dans les niveaux d’isolation à partir de READ COMMITTED (voir section 7.3). Comme leur nom l’indique, ils sont compatibles entre eux, on peut donc poser des verrous partagés sur des ressources qui en ont déjà, et ainsi lire en même temps les mêmes lignes. Par contre, ils permettent de protéger les opérations de lecture contre l’écriture simultanée par une autre transaction, ce qui pourrait déboucher sur des lectures sales. Ils sont incompatibles avec les verrous d’écriture (X), ce qui permet, dans l’autre sens, de prévenir une lecture sur une ressource en cours de modification.

Prenons la requête suivante, qui met à jour la table d’en-têtes de commandes pour ajouter dix jours à la date d’envoi, puis ajoute dans la même transaction dix jours à la date limite de paiement dans la table de détail de commandes :

BEGIN TRAN

BEGIN TRY

UPDATE Purchasing.PurchaseOrderHeader

SET ShipDate = DATEADD(day, 10, ShipDate)

UPDATE Purchasing.PurchaseOrderDetail

SET DueDate = DATEADD(day, 10, DueDate)

COMMIT TRAN

END TRY

BEGIN CATCH

IF (XACT_STATE()) <> 0

ROLLBACK TRAN

END CATCH

Si une lecture a lieu entre les deux instructions, elle peut trouver des dates de paiement antérieures aux dates d’envoi, ce qui est une incohérence fonctionnelle. Pour éviter cela, une lecture ne peut se faire que lorsqu’un verrou S est posé sur les ressources. Ce verrou étant incompatible avec le verrou X, il doit attendre la libération des verrous X des ressources souhaitées.

Les verrous de type exclusifs (exclusive, X) sont des verrous d’écriture, posés sur des lignes en cours de modification (INSERT, DELETE, UPDATE). Ils sont incompatibles avec tout type de verrous, y compris eux-mêmes : quelles que soient les options de la base ou de la session, il est interdit à deux transactions d’écrire simultanément les mêmes ressources. Si cela était possible, ce serait le chaos[^23]. On ne peut poser ni verrou exclusif, ni verrou partagé, etc. sur une ressource qui a déjà un verrou exclusif. On ne peut donc donc pas non plus une ligne en cours de modification, dans un niveau d’isolation qui pose un verrou partagé à la lecture.

Les verrous de mise à jour (update, U) sont posés dans les niveaux d’isolation qui maintiennent les verrous partagés toute la durée de la transaction (REPEATABLE READ et SERIALIZABLE), ils permettent d’éviter un forme courante de verrou mortel (deadlock), lorsque la lecture sert, dans une instruction suivante, aux mises à jour. Dans ce cas, le verrou partagé doit être converti en verrou exclusif. Mais, si une autre transaction maintient elle aussi des verrous partagés sur ces ressources, la première doit attendre leur libération. Lorsque la seconde transaction essaie à son tour de modifier les données, elle doit également attendre sur la première, d’où situation inextricable et deadlock. Pour éviter cela, SQL Server pose également un verrou de mise à jour. Un seul verrou U peut-être posé dans le même temps, sur la même ressource. Cela permet à la transaction de convertir le verrou S en verrou X sans attendre, si elle possède le verrou U.

Les verrous intentionnels (intent, I) permettent de protéger les ressources aux granularités supérieures, contre un verrouillage incompatible. Si une transaction pose un verrou sur une ligne, il est nécessaire d’empêcher les autres transactions de poser des verrous sur la page qui contient cette ligne, ou sur la table, afin de ne pas se retrouver avec des verrous incompatibles par une voie détournée. Les verrous d’intention permettent d’optimiser les performances de verrouillage, en évitant la recherche des verrous de granularité plus fine. Si une transaction veut verrouiller la table, elle n’a pas besoin de chercher s’il existe des verrous de ligne ou de page, il lui suffit de vérifier la présence de verrous effectifs ou d’intention, sur la table.

Prenons un exemple, exécutons une requête de modification sur une table d’AdventureWorks :

USE AdventureWorks

GO

BEGIN TRAN

UPDATE TOP (1) Sales.Currency

SET ModifiedDate = current_timestamp

Examinons les verrous :

SELECT

tl.resource_type,

tl.resource_subtype,

DB_NAME(tl.resource_database_id) as db,

tl.resource_description,

CASE tl.resource_type

WHEN 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)

ELSE COALESCE(OBJECT_NAME(p.object_id),

CAST(tl.resource_associated_entity_id as sysname))

END as obj,

tl.request_mode,

tl.request_status

FROM sys.dm_tran_locks tl

LEFT JOIN sys.partitions p

ON tl.resource_associated_entity_id = p.hobt_id

WHERE

tl.request_session_id = @@SPID AND

tl.resource_database_id = DB_ID()

ORDER BY

CASE resource_type

WHEN 'KEY' THEN 1

WHEN 'RID' THEN 1

WHEN 'PAGE' THEN 2

WHEN 'EXTENT' THEN 3

WHEN 'HOBT' THEN 4

WHEN 'ALLOCATION_UNIT' THEN 5

WHEN 'OBJECT' THEN 6

WHEN 'DATABASE' THEN 7

WHEN 'FILE' THEN 8

ELSE 9

END

Le résultat est visible sur la figure 7.1. Nous voyons que des verrous d’intention exclusifs ont été placés sur la page, et sur la table.

Fig. 7.1 – verrous

Les verrous intentionnels comportent le mode du verrou originel : IS pour Intent Shared, IX pour Intent Exclusive, etc.

Les verrous de schéma (schema, Sch-M) sont des verrous sur les structures. Il serait désagréable de voir la table dans laquelle on est en train de lire, supprimée par une autre transaction.

Les verrous d’étendue (range, RangeM-M) sont des verrous sur les ressources qui « entourent » la ressource verrouillée. Elles permettent de prévenir les lectures fantômes en niveau d’isolation SERIALIZABLE. Nous en parlerons quand nous présenterons les niveaux d’isolation.

Nous pouvons observer le verrouillage à travers plusieurs vues de gestion dynamiques que nous présenterons au long de ce chapitre. La vue principale affichant les verrous en cours est sys.dm_tran_locks.

Que sont les latches ?

Vous verrez parfois, par exemple dans les sessions en attente (en exécutant la vue sys.dm_os_waiting_tasks, notamment), passer le mot latch, par exemple avec des attentes de type PAGEIOLATCH.

Les latches, traduits en français dans la documentation par verrous internes ou verrous de ressources internes, sont des verrous de synchronisation, légers, rapides et posés habituellement durant un temps très court, destinés à protéger les structures physiques lors de leur accès par le moteur de stockage et leur transmission au moteur relationnel. Lorsqu’une ligne est lue, par exemple, SQL Server doit protéger non seulement la ligne, mais également la page (pour éviter qu’une insertion ne modifie l’entête qui indique l’offset de la ligne). Les latches ne sont maintenus que sur des pages de données présentes dans le buffer (alors que des verrous peuvent rester posés sur des pages sur le disque), car toute page en cours d’utilisation physique est placée dans le buffer. Cela aide ainsi à garantir une grande rapidité du latch.

Il existe aussi d’autres types de latches non-buffer, sur des structures internes.

Vous pouvez obtenir des statistiques sur la gestion des latches avec l’objet de compteur de performances SQL:Latches.

Les latches d’entrées/sorties sont des types particuliers de latches de buffer, utilisés pour la synchronisation entre les pages du buffer et les pages du disques, on les voit dans les attentes de type PAGEIOLATCH. Ces attentes sont souvent typiques des latences du disque. Vous pouvez utiliser une requête comme celle-ci pour détecter ces attentes :

SELECT wait_type,

waiting_tasks_count,

wait_time_ms

FROM sys.dm_os_wait_stats

WHERE wait_type like ‘PAGEIOLATCH%’

ORDER BY wait_type

La granularité de verrouillage

Les verrous sont posés sur des ressources. En ce qui concerne les verrous de données (il existe des verrous posés sur des objets de code, pour éviter leur modification durant l’exécution), les ressources sur lesquelles un verrou peut être posés sont : la ligne (ou la clé d’index), la page, la table, et la base de données. On parle de granularité pour indiquer sur quel niveau de ressource s’effectue le verrouillage. En SQL Server, la granularité par défaut est la ligne, ou la clé d’index. Cela permet de verrouiller le moins de ressources possible, cela offre donc un excellent niveau de concurrence. Par exemple, lors d’insertions, il prévient la contention sur la page qui se produit sur les tables clustered dans les SGBDR dont la granularité minimal est la page.

Cette granularité comporte néanmoins un désavantage : si la commande affecte un grand nombre de lignes, cela implique de poser un grand nombre de verrous. Chaque verrou coûte en temps processeur, et en mémoire vive. Pour limiter les coûts, SQL Server évalue le nombre de lignes à traiter, et lors de l’exécution, peut convertir des verrous en granularité plus large. Pour saler le contenu de votre assiette, vous prenez une pincée. Si vous devez saler la marmite d’un régiment, vous versez à partir d’un grand sachet de sel, car le faire pincée par pincée vous prendrait des heures.

Par exemple, au lieu de poser 19 972 verrous sur la table Person.Contact pour modifier toutes les lignes, il ne va poser qu’un seul verrou, sur la table. Cette conversion s’appelle l’escalade. Elle fait l’économie d’un verrouillage intense, mais elle bloque aussi potentiellement plus de ressources que nécessaire, et donc diminue la concurrence d’accès.

L’escalade de verrous

Vous pouvez obtenir des informations précises sur verrouillage de vos index, par la fonction sys.dm_db_index_operational_stats(). Notamment, la colonne index_lock_promotion_count vous indique le nombre d’escalades :

SELECT

OBJECT_NAME(ios.object_id) as table_name,

i.name as index_name,

i.type_desc as index_type,

row_lock_count,

row_lock_wait_count,

row_lock_wait_in_ms,

page_lock_count,

page_lock_wait_count,

page_lock_wait_in_ms,

index_lock_promotion_attempt_count,

index_lock_promotion_count

FROM sys.dm_db_index_operational_stats(

DB_ID('AdventureWorks'),

OBJECT_ID('Person.Contact'), NULL, NULL) ios

JOIN sys.indexes i ON ios.object_id = i.object_id AND ios.index_id =
i.index_id

ORDER BY ios.index_id;

Parfois, vous voudrez contrôler le niveau de granularité, soit en l’augmentant pour augmenter les performances (c’est très rarement nécessaire), soit pour empêcher la pose de verrous aux granularités supérieures, qui bloquent d’autres transactions. La deuxième option est plus courante, dans des cas de mises à jour d’une grande partie ou de la totalité d’une table, par exemple. L’opération prend du temps, et si un verrou est posé sur la table, il bloque tout autre travail sur celle-ci.

Augmenter la granularité

Nous verrons dans la section 8.2.1 que nous pouvons contrôler la granularité de verrouillage par table, nous y reviendrons. Nous avons aussi la possibilité de contrôler le verrouillage de tous les accès aux tables clustered à travers les options de création d’index ALLOW_ROW_LOCKS et ALLOW_PAGE_LOCKS. Nous les avons survolés en section 6.1.3. ALLOW_ROW_LOCKS = OFF désactive la verrouillage de lignes, ALLOW_PAGE_LOCKS = OFF désactive le verrouillage de page. Appliqués sur un index clustered, ils gèrent effectivement la granularité de verrouillage de la table. Ils sont très rarement utiles. Vous pouvez les essayer avec l’exemple de code suivant, qui vous présente deux requêtes utiles : la première retourne les index dont ces options ont été modifiées, la seconde vous permet de tracer les verrous posés par un spid à l’aide des vues de gestion dynamique :

SELECT

OBJECT_NAME(object_id) as table_name,

name as index_name,

allow_row_locks,

allow_page_locks

FROM sys.indexes

WHERE allow_row_locks & allow_page_locks = 0

ORDER BY table_name, index_name;

ALTER INDEX PK_Contact_ContactID

ON Person.Contact

SET (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF);

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION

SELECT LastName, FirstName, EmailAddress

FROM Person.Contact

WHERE LastName = 'Adams';

SELECT

CASE es.transaction_isolation_level

WHEN 0 THEN 'non spécifié'

WHEN 1 THEN 'READ UNCOMMITTED'

WHEN 2 THEN 'READ COMMITTED'

WHEN 3 THEN 'REPEATABLE'

WHEN 4 THEN 'SERIALIZABLE'

WHEN 5 THEN 'SNAPSHOT'

END as transaction_isolation_level,

tl.request_session_id as spid, tl.resource_type, tl.request_mode,

tl.request_type,

CASE

WHEN tl.resource_type = 'object' THEN

OBJECT_NAME(tl.resource_associated_entity_id)

WHEN tl.resource_type = 'database' THEN

DB_NAME(tl.resource_associated_entity_id)

WHEN tl.resource_type IN ('key','page') THEN

(SELECT object_name(i.object_id) + '.' + i.name

FROM sys.partitions p

JOIN sys.indexes i ON p.object_id = i.object_id

AND p.index_id = i.index_id

WHERE p.hobt_id = tl.resource_associated_entity_id)

ELSE CAST(tl.resource_associated_entity_id as varchar(20))

END as objet

FROM sys.dm_tran_locks tl

LEFT JOIN sys.dm_exec_sessions es

ON tl.request_session_id = es.session_id

WHERE request_session_id = @@spid

ORDER BY

CASE resource_type

WHEN 'DATABASE' THEN 10

WHEN 'METADATA' THEN 20

WHEN 'OBJECT' THEN 30

WHEN 'PAGE' THEN 40

WHEN 'KEY' THEN 50

ELSE 100

END

ROLLBACK

ALTER INDEX PK_Contact_ContactID

ON Person.Contact

SET (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

En modifiant les options d’index, vous observerez les différences de verrouillage.

Diminuer la granularité

Vous pourriez parfois avoir besoin d’empêcher l’escalade de verrous, notamment sur les requêtes de modification qui touchent une grand partie de la table. Vous avez pour cela deux moyens. Le plus propre est de découper votre requête de modification en étapes, pour ne toucher qu’une partie de la table à chaque fois. À l’aide de la requête listée plus haut, vous pouvez voir la différence de verrouillage de ces deux instructions :

BEGIN TRAN

UPDATE Person.Contact

SET LastName = UPPER(LastName);

UPDATE TOP (1000) Person.Contact

SET LastName = UPPER(LastName);

ROLLBACK

La première pose un verrou exclusif sur la table, et un UPDATE Person.Contact WITH (ROWLOCK) n’y change rien, car il n’affecte que le plan d’exécution, pas le comportement d’escalade lors de l’exécution même. La seconde, par contre, pose des verrous sur les lignes (chaque clé de l’index clustered. Ainsi, vous pourriez créer une boucle WHILE de ce type :

DECLARE @incr int, @rowcnt int

SET @incr = 1
SET @rowcnt = 1

WHILE @rowcnt > 0 BEGIN

    UPDATE TOP (1000) Person.Contact
    SET LastName = UPPER(LastName)
    WHERE ContactID >= @incr

    SET @rowcnt = @@ROWCOUNT
    SET @incr = @incr + 1000

END

Une seconde solution, plus de genre bricolage, existe. Elle se base sur le fait qu’un verrou d’intention posé à un niveau de granularité supérieur n’empêche pas les verrous de granularité inférieure, et prévient bien sûr la pose de verrous incompatibles. Vous pouvez poser un verrou d’intention exclusif sur la table, par exemple de cette manière :

BEGIN TRAN

SELECT TOP (0) * FROM Person.Contact WITH (UPDLOCK, HOLDLOCK)

Le verrou reste posé jusqu’au ROLLBACK ou COMMIT de la transaction. Vous pourriez créer un travail de l’agent qui lance la commande, avec un WAITFOR pour limiter la durée de la transaction (attention au grossissement du journal de transactions !). Tant que cette transaction est ouverte, les autres transactions ne pourront pas escalader leurs verrous sur la table. Elles essaieront régulièrement durant l’exécution, et vous verrez leur essais dans la colonne index_lock_promotion_attempt_count de la fonction sys.dm_db_index_operational_stats().

Les lectures et écritures se feront sans problème.

Vous pouvez totalement désactiver l’escalade sur votre serveur à l’aide du drapeau de trace 1211 : DBCC TRACEON (1211, -1) ou -T1211 au démarrage de l’instance. C’est une solution vraiment déconseillée, vous verrez sans aucun doute une baisse générale de performances dûe à un nombre de verrous excessif.

Vous retrouvez des conseils précis dans l’article de la base de connaissance 323630 : http://support.microsoft.com/kb/323630

Configurer l’escalade de verrous sur les table partitionnées, en SQL Server 2008

En SQL Server 2005, il n’y a pas de granularité de verrou par partition. L’escalade ne peut se faire que de la page à la table, même si la requête n’a besoin d’accéder qu’à une seule partition. EN SQL Server 2008, la partition devient une granularité de verrouillage possible. Vous pouvez gérer finement cette possibilité table par table si vous le souhaitez. La commande ALTER TABLE a été enrichie de l’option : SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } ). Voici un exemple de syntaxe :

ALTER TABLE dbo.matable SET (LOCK_ESCALATION = AUTO)

AUTO – permet la sélection automatique de la granularité des verrous. Sur une table partitionnée, l’escalade pourra se produire sur la partition. Sur une table non partitionnée, l’escalade se produira sur la table ;

TABLE – l’escalade se fera sur la table, qu’elle soit partitionnée ou non. Il s’agit de la valeur par défaut, et correspondant au comportement de SQL Server 2005 ;

DISABLE – prévient l’escalade dans la plupart des cas. Le verrouillage de table n’est pas complètement désactivé.

les niveaux d’isolation de la transaction

Nous l’avons vu, l’isolation de la transaction, à travers le mécanisme de verrouillage, a une forte influence sur la cohérence des données. Comme toute application multi-utilisateurs, SQL Server doit équilibrer son comportement entre le maintien de cette cohérence, et le besoin de permettre des accès concurrents. Ce sont deux exigences opposées. Plus la cohérence est garantie, moins est grand le nombre possible d’accès concurrents, et vice-versa. Cela a donc fort un impact sur les performances. Afin de contrôler cet équilibre, nous pouvons agir sur l’isolation, en modifiant le niveau d’isolation de la transaction, dans le contexte de la session, ou par table requêtée.

La norme SQL définit quatre niveaux d’isolation, qui répondent à quatre problématiques connues de la cohérence transactionnelle : les mises à jour perdues, les lectures sales, les lectures non renouvelables, et les lectures fantômes.

La mise à jour perdue (lost update) se produit lorsque deux transactions lisent puis modifient simultanément les mêmes données (les mêmes lignes) à partir de cette lecture. La dernière mise à jour étant celle qui est prise en compte, des modifications appliquées par les transactions précédentes peuvent être écrasées.

La lecture sale (dirty read) décrit le cas où une ligne est lue, alors qu’elle est en cours de modification par une autre transaction, soit parce que l’instruction est en cours d’exécution, soit parce que la transaction qui a modifié la ligne n’est pas encore validée (ce qui selon le principe de l’atomicité de la transaction est strictement équivalent). Le risque encouru est donc d’obtenir des informations incomplètes ou non valides.

La lecture non renouvelable (non-repeatable read) indique la possibilité, pour une transaction, de trouver des valeurs différentes dans les lignes lues à deux moments de la transaction, parce qu’une autre transaction aura modifié les données entre les deux lectures. C’est un danger pour les transactions qui comptent sur la cohérence de ces données dans la durée de vie de la transaction pour leur traitement.

La lecture fantôme (phantom read) est forme aggravée de la lecture non répétable. Elle décrit l’apparition de nouvelles lignes lors de nouvelles lectures, lorsque dans la même transaction, un deuxième SELECT portant sur les mêmes lignes (la même clause WHERE), voit apparaître de nouvelles lignes, parce qu’une autre transaction aura inséré des données répondant à la clause de filtre.

Répondre à ces différents problèmes implique, pour une transaction, de choisir différentes stratégies de verrouillage. Ces stratégies sont dictées par le niveau d’isolation de la transaction. La norme SQL décrit quatre niveaux d’isolation, qui répondent chacun à un problème transactionnel. Ce sont, dans l’ordre croissant de cohérence :

READ UNCOMMITTED ;
READ COMMITTED ;
REPEATABLE READ ;
SERIALIZABLE.

SQL Server, à partir de la version 2005, ajoute un niveau d’isolation propriétaire, basé sur le row versioning, appelé SNAPSHOT.

Ces niveaux d’isolation permettent de choisir un équilibre entre la cohérence des données et la concurrence d’accès : plus la cohérence transactionnelle est maintenu par un verrouillage fort, plus les données sont isolées des autres transaction, et donc moins la concurrence d’accès simultanée entre sessions est possible.

Le niveau d’isolation peut être changé pour la session à l’aide de l’instruction SET TRANCATION ISOLATION LEVEL comme ne le verrons ci-dessous. Il peut aussi être configuré dans une même requête, table par table, à l’aide d’un indicateur de table, tout comme on peut choisir une granularité de verrou :

SELECT * FROM Person.Contact WITH (READUNCOMMITTED);

– ou

SELECT * FROM Person.Contact WITH (NOLOCK);

Les deux syntaxes ci-dessus sont strictement équivalentes. NOLOCK est simplement un alias propre à SQL Server pour indiquer un niveau READ UNCOMMITTED. Mais voyons ces niveaux.

Le niveau d’isolation READ UNCOMMITTED est le niveau le plus permissif, posant le moins de verrou, et permettant donc l’apparition du plus grand nombre d’erreurs transactionnelles. Dans ce niveau, les lectures sales (dirty reads) sont possibles : une transaction peut lire des lignes qui sont en train d’être modifiées par une autre transaction, ou modifier des lignes en cours de lecture. L’extraction de données non validées (« sales ») est donc possible. Dans ce niveau, SQL Server continue à poser des verrous exclusifs (X) lors de modifications : il est toujours impossible d’écrire en même temps les mêmes lignes. Par contre, en lecture, aucun verrou n’est posé. Seul un verrou de schéma (Sch-S) est maintenu, empêchant la modification de la structure des tables lues. De même, les verrous exclusifs ne sont pas honorés par la lecture. Ce niveau est donc très favorable aux performances, en diminuant les opérations de verrouillage, et les attentes de libération de verrous.

Vous entendrez certainement des mises en garde contre le niveau d’isolation READ UNCOMMITTED. La lecture sales inquiètent certaines personnes. Dans la pratique, pour des générations de rapport, de l’extraction de données ou du simple affichage, il est rare de se préoccuper d’obtenir une ligne validée ou non. À un centième de secondes près, vous obtenez une valeur différente, quelle importance ? Compte tenu du gain de performances apporté par ce mode, nous ne pouvons que vous le conseiller pour des requêtes de lecture. La vraie contre-indication se présente dans un environnement où des transactions modifient plusieurs lignes en rapport les unes avec les autres, dans des tables différentes ou dans la même table. Exemple classique, si vous gérez une application bancaire et que vous effectuez par SELECT un total des avoirs d’un client, vous risquez, en niveau READ UNCOMMITTED, de lire des totaux erronés si en même temps, une transaction retire un montant d’un compte courant pour le poser sur un compte d’épargne. Si le SELECT intervient lorsque la ligne est supprimée du compte courant, mais pas encore ajoutée au compte d’épargne, le total sera inférieur au crédit du client. Dans ce genre de cas, le niveau d’isolation SNAPSHOT peut permettre une plus grande concurrence, sans ralentir les requêtes par des attentes de verrous. Lorsque ce risque n’est pas présent, un SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED en début de procédure stockée dédiée à la lecture ou au reporting, est une façon simple d’augmenter la concurrence et les performances. N’oubliez pas de laisser la session dans son niveau par défaut à la fin de la procédure, avec un SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

Le niveau d’isolation READ COMMITTED est le niveau par défaut d’une session SQL Server. Il protège la transaction contre les lectures sales, en ne permettant des lectures que sur les lignes validées. Pour cela, SQL Server pose un verrou partagé sur les données en cours de lecture. Les verrous partagés sont compatibles entre eux (ce qui veut dire qu’on peut en placer plusieurs sur la même ressource), mais incompatibles avec les verrous exclusifs. Un verrou S ne pourra donc pas être placé sur un ligne ayant déjà un verrou X. La lecture d’une ligne en cours de modification devra donc attendre. Réciproquement, une tentative d’écriture devra attendre la libération de tous les verrous partagés sur la ressource. Ce niveau est un peu plus contraignant que le premier, et limite un peu plus les performances, en générant des verrous partagés, et des attentes en lecture. Par contre, il ne protège pas des lectures non répétables, car les verrous partagés sont libérés dès la fin de l’instruction de lecture, et ne persistent pas jusqu’à la fin de la transaction.

Le niveau d’isolation REPEATABLE READ, comme son nom l’indique, permet les lectures répétables, en maintenant les verrous partagés jusqu’à la fin de la transaction. Faisons le test :

USE AdventureWorks

GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT TOP 10 * FROM Sales.Currency

En exécutant notre requête sur la vue sys.dm_tran_locks, nous trouvons le résultat de la figure 7.2.

Fig. 7.2 – verrous partagés en REPEATABLE READ

Des verrous partagés (S) sont maintenus sur chaque clé de l’index clustered (KEY), c’est-à-dire sur chaque ligne impactée dans la requête. Ces lignes ne pourront donc pas être modifiée tant que la transaction est encore active. Nous voyons également que des verrous d’intention partagés sont maintenus sur la page, et sur la table (OBJECT).

Ainsi, aucune autre session ne pourra modifier ces lignes jusqu’à la fin de la transaction. À cause du verrouillage plus important, n’utilisez ce mode que lorsque vous souhaitez, dans une transaction, utiliser le résultat de votre lecture pour des mises à jour dans les instructions suivantes. Éviter également d’écrire du code transactionnel qui s’exécute dans une logique inverse (par exemple SELECT puis UPDATE dans une transaction, UPDATE puis SELECT dans une autre), car vous vous exposeriez à un risque important de deadlock. Lorsque vous le faites dans le même ordre, SQL Server pose des verrous de mise à jour potentielle (update, U) pour éviter le problème.

Le niveau d’isolation SERIALIZABLE représente la protection maximale contre les erreurs transactionnelles, mais la concurrence d’accès minimale.

Sur une table sans index clustered, et dont les colonnes de la clause WHERE ne sont pas indexées, SQL Server sera obligé de poser un verrou de table. Démonstration :

USE tempdb

GO

CREATE TABLE dbo.testSerializable

(nombre int, texte varchar(50) COLLATE French_BIN)

GO

INSERT INTO dbo.testSerializable VALUES (1, 'un')

INSERT INTO dbo.testSerializable VALUES (2, 'deux')

INSERT INTO dbo.testSerializable VALUES (3, 'trois')

INSERT INTO dbo.testSerializable VALUES (4, 'quatre')

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

-- sans index

UPDATE dbo.testSerializable

SET texte = 'DEUX'

WHERE nombre = 2

-- requête sur sys.dm_tran_locks

ROLLBACK

GO

CREATE NONCLUSTERED INDEX nix$testSerializable$nombre

ON dbo.testSerializable (nombre)

GO

BEGIN TRAN

-- avec index

UPDATE dbo.testSerializable

SET texte = 'DEUX'

WHERE nombre = 2

-- requête sur sys.dm_tran_locks

ROLLBACK

GO

Les deux requêtes sur la vue sys.dm_tran_locks ne sont pas incluses dans le code pour des raisons de place, vous les connaissez. Leur résultat est visible dans la figure 7.3 pour le premier cas (heap sans index), et dans la figure 7.4 pour le second cas (heap avec index nonclustered).

Fig. 7.3 – verrous sur un heap

Fig 7.4 – verrous sur une table clustered

Vous pouvez voir que dans le premier cas, SQL Server n’a d’autre choix que de poser un verrou exclusif sur la table. Par contre, lorsqu’un index existe, il peut poser des verrous d’étendues sur les clés de l’index qui devraient être accédées si se produit un ajout de ligne répondant à la clause nombre = 2. Les verrous sont des RangeS-U : partagé sur l’étendue, mise à jour sur la ressource. Un Intent Update est posé sur la page de l’index.

N’utilisez le niveau SERIALIZABLE qu’en cas d’absolue nécessité (changement ou calcul de clé, par exemple), et n’y restez que le temps nécessaire. Le verrouillage important entraîne une réelle baisse de concurrence.

Le niveau d’isolation SNAPSHOT existe depuis SQL Server 2005. Il permet de diminuer le verrouillage, tout en offrant une cohérence de lecture dans la transaction. Alors que dans le niveau READ UNCOMMITTED, la possibilité existe de lectures sales, le niveau SNAPSHOT permet de lire les données en cours de modification, dans l’état cohérent dans lequelle elles se trouvaient avant modification. Pour cela, SQL server utilise une fonctionnalité interne appelée version de lignes (row versioning). Pour pouvoir l’utiliser, le support du niveau d’isolation SNAPSHOT doit avoir été activé dans la base de données. Exemple :

ALTER DATABASE sandbox SET ALLOW_SNAPSHOT_ISOLATION ON

Quand ceci est fait, toutes les transactions qui modifient des données dans cette base, maintiennent une copie (une version) de ces données avant modification, dans tempdb, quel que soit son niveau d’isolation. Si une autre session en niveau d’isolation SNAPSHOT essaie de lire ces lignes, elle lira la copie des lignes, et non pas les lignes en cours de modification, ce qui lui garantit une lecture cohérente de l’état de ces données, avant modification. Expérimentons-le :

USE sandbox;

GO

CREATE TABLE dbo.testSnapshot

(nombre int, texte varchar(50) COLLATE French_BIN);

GO

INSERT INTO dbo.testSnapshot VALUES (1, 'un');

INSERT INTO dbo.testSnapshot VALUES (2, 'deux');

INSERT INTO dbo.testSnapshot VALUES (3, 'trois');

INSERT INTO dbo.testSnapshot VALUES (4, 'quatre');

GO

-- session 1

BEGIN TRAN;

UPDATE dbo.testSnapshot

SET texte = 'DEUX'

WHERE nombre = 2;

-- session 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SELECT *

FROM dbo.testSnapshot

WHERE nombre = 2;

La deuxième session lit sans problème la ligne. La valeur retournée pour la colonne texte est ‘deux’, et non ‘DEUX’, comme ce serait le cas dans une lecture en niveau d’isolation READ UNCOMMITTED : la valeur lue est la version de la ligne précédent la modification, par la ligne en cours de modification.

Vous pouvez voir les versions de lignes en cours à l’aide des vues de gestion dynamique sys.dm_tran_active_snapshot_database_transactions et sys.dm_tran_version_store. sys.dm_tran_active_snapshot_database_transactions vous donne le spid (session_id) et la durée en secondes depuis la création de la version (elapsed_time_seconds), sys.dm_tran_version_store vous retourne une ligne par ligne versionnée, avec l’indication de la base de données (database_id) source. Attention aux requêtes sur sys.dm_tran_version_store : elles peuvent impacter les performances, compte tenu de l’importante quantité de résultats qu’elles peuvent retourner.

Une lecture dans ce niveau, génère aussi une version :

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

SELECT *

FROM dbo.testSnapshot

WHERE nombre = 2;

SELECT session_id, transaction_id, elapsed_time_seconds

FROM sys.dm_tran_active_snapshot_database_transactions;

ROLLBACK;

Pourquoi cela ? Afin de permettre, dans la même transaction, d’éviter les lectures non répétables et les lectures fantômes. De fait, ce niveau d’isolation correspond à un niveau SERIALIZABLE, sans le verrouillage. Une requête dans sys.dm_tran_locks ne montre aucun verrou maintenu. Les autres transactions pourront donc modifier la ligne WHERE nombre = 2 à loisir. Durant tout la durée de la transaction, notre session en isolation SNAPSHOT verra la version de ligne équivalant à la première lecture.

Mais que se passera-t-il si nous cherchons à modifier ensuite la ligne dans notre transaction ? Imaginons qu’après avoir exécuté le SELECT ci-dessus, une autre session a mise à jour la ligne de dbo.testSnapshot, WHERE nombre = 2. Pourrons-nous à notre tour effectuer un UPDATE, basé sur une version de ligne précédant la mise à jour externe ? Non. Au moment de l’UPDATE dans notre transaction, un conflit sera détecté, et une erreur sera retournée : la cohérence des mises à jour est toujours garantie, pour nous protéger contre les lost updates. Le mode d’isolation SNAPSHOT est donc à proprement parler un mode de verrouillage optimiste : les données lues sont conservées sans verrouillage, avec la supposition qu’il y a peu de probabilité qu’un conflit apparaisse.

Compteurs de performance : ces compteurs vous donnent une visibilités sur les statitistiques du niveau d’isolation snapshot : MSSQL:Transactions\Longest Transaction Running Time, MSSQL:Transactions\Snapshot Transactions, MSSQL:Transactions\Update conflict ratio.

Quel est mon niveau d’isolation ?

Vous pourriez avoir envie de savoir dans quel niveau d’isolation se trouve votre session, ou une autre session en cours d’exécution. En SQL Server 2000, la commande DBCC USEROPTIONS était votre seul salut. Cette commande retourne un jeu de résultats comportant deux colonnes : [Set Option] et Value. Pour la manipuler en programmation, vous deviez insérer le résultat dans une table temporaire, en l’appelant dans du SQL dynamique. Par exemple :

CREATE TABLE #setoption (so varchar(64), val varchar(64))

INSERT INTO #setoption (so, val)

EXEC ('DBCC USEROPTIONS')

SELECT val

FROM #setoption

WHERE so = 'isolation level'

Pour connaître le niveau d’isolation d’une autre session que la vôtre, vous deviez utiliser une commande DBCC non documentée : DBCC PSS, dont la fonction est de produire des informations de bas niveau sur les utilisateurs connectés, et les sessions. Depuis 2005, vous avez à disposition la vue de gestion dynamique sys.dm_exec_sessions. Par exemple, pour connaître le niveau d’isolation de votre propre session :

SELECT transaction_isolation_level,

CASE transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'ReadUncomitted'

WHEN 2 THEN 'Readcomitted'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

END AS isolation_level_description

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID

Mais l’intérêt dans le code est limité. Si vous voulez forcer votre niveau d’isolation, exécutez simplement un SET TRANSACTION ISOLATION LEVEL …

Les attentes

Comme l’isolation de la transaction empêche les accès concurrents non compatibles, sur les mêmes ressources de verrouillage, cela veut dire qu’une transaction qui veut accéder à une ressource utilisée par une autre transaction doit attendre la libération des verrous non compatibles.

Par défaut, une transaction attend indéfiniment la libération de verrous. Vous pouvez limiter cette attente par l’option de session LOCK_TIMEOUT :

SET LOCK_TIMEOUT 1000;

SELECT @@LOCK_TIMEOUT as lock_timeout;

La valeur est exprimée en millisecondes. Le défaut est -1, c’est-à-dire sans limite. Vous pouvez aussi utiliser l’indicateur de table READPAST (voir la section 8.2.1).

Les attentes ne sont pas seulement dûes au verrouillage, elles peuvent être provoquées par les entrées/sorties, la mise en attente du processus par l’ordonnanceur, etc. Nous verrons ci-dessous les principaux types d’attentes.

Une attente peut aussi se produire sur l’attribution de mémoire pour la requête. Dans des situations de pression sur la mémoire, cela peut même provoquer des timeouts

Vous obtenez des informations sur les attentes à travers les vues de gestion dynamique sys.dm_tran_locks, sys.dm_os_wait_stats, sys.dm_os_waiting_tasks et sys.dm_exec_requests.

La DMV sys.dm_os_wait_stats accumule les statistiques sur les attentes (waits). Pour chaque type d’attente (l’indication de la raison de l’attente d’un processus SQL Server), cette vue indique principalement le nombre d’attentes et leur durée totale en millisecondes. Les valeurs s’incrémentent au fur et à mesure de l’activité de l’instance, et elles sont remises à zéro – comme pour toutes les DMV – lors d’un redémarrage de celle-ci. Dans ce cas, vous pouvez aussi remettre explicitement les compteurs à zéro, par exemple avant de lancer une lot de requêtes test, à l’aide de cette commande DBCC :

dbcc sqlperf(‘sys.dm_os_wait_stats’, clear)

Voyons en pratique une attente. Exécutons ces deux requêtes dans deux sessions différentes :

-- dans la session 1

BEGIN TRAN

UPDATE Sales.Currency

SET Name = 'Franc Francais'

WHERE CurrencyCode = 'EUR'

-- dans la session 2

BEGIN TRAN

SELECT *

FROM Sales.Currency WITH (TABLOCK)

WHERE CurrencyCode = 'CHF'

Que voyons-nous avec une requête sur sys.dm_tran_locks (légèrement modifiée pour afficher les deux spid en présence, vous trouverez cette requête sur le site d’accompagnement du livre) ? Réponse dans la figure 7.5.

Fig. 7.5 – attentes

Nous voyons que le spid 53 cherche à poser un verrou partagé sur la table Currency. Le request_status est WAIT, ce qui indique que le verrou est en attente. Nous avons introduit ici, pour l’exemple, une petite perversion : la deuxième requête ne serait pas en attente si l’indicateur de table WITH (TABLOCK) ne forçait un verrou à la granularité de table. Comme la transaction du spid 52 a posé un verrou d’intention exclusif sur la table Currency, le spid 53 ne peut poser de verrou partagé sur cet objet.

Au passage, nous voyons aussi, sur la figure 7.5, que deux pages sont verrouillées par le spid 52. Pourquoi deux pages, alors que nous devons modifier une seule ligne ? Vérifions :

DBCC TRACEON (3604)

DBCC PAGE (AdventureWorks, 1, 295, 0)

DBCC PAGE (AdventureWorks, 1, 2101, 0)

L’entête de la page 295 nous montre ceci : Metadata: ObjectId = 597577167 …

Les types d’attentes

Pour référence, nous listons ici les types d’attentes que vous rencontrerez le plus souvent, et qui peuvent vous aider à détecter des problèmes. Ces attentes sont visibles dans la colonne wait_type des vues de gestion dynamique sys.dm_os_wait_stats et sys.dm_os_waiting_tasks.

Une liste exhaustive est donnée dans l’excellent document « SQL Server 2005 Waits and Queues » : http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

**ASYNC_NETWORK_IO **– « bufférisation » d’une session TCP, souvent à cause d’un client qui met du temps à traiter l’envoi de SQL Server, ou qui maintient ouvert un jeu de résultat. Le compteur de performance MSSQL:Wait statisticsNetwork IO waits est aussi utile pour ce type de diagnostic ;
**CXPACKET **– échange entre des threads parallélisaés sur la même requête. Peut indiquer un problème lié au parallélisme. Si vous avez beaucoup d’attentes de ce type, désactivez le parallélisme ;
LCK_… - attentes sur des verrous, voir l’encadré pour une surveillance par compteurs de performances ;
LOGBUFFER et WRITELOG – écriture et lecture des journaux de transaction ;
LOGMGR_RESERVE_APPEND – attente de troncature pour vérifier si la libération d’espace dans le journal sera suffisante pour écrire l’enregistrement actuel. C’est un signe de lenteur de disque, sur des journaux en mode de récupération simple ;
PAGEIOLATCH – attentes sur des synchronisatrions entre les pages du buffer et les pages du disques, bon indicateur de contention disque ;
SOS_SCHEDULER_YIELD – se produit lorsqu’une tâche s’interrompt volontairement pour redonner sa place, dans le modèle non-prémptif de l’ordonnanceur SQLOS. Voir cette entrée de blog pour plus de détails : http://blogs.msdn.com/sqlcat/archive/2005/09/05/461199.aspx

Surveiller les attentes de verrous

Le compteur MSSQL:Wait statistics\Lock waits donne des informations importantes sur les attentes en cours sur le système. sur les instances :

Average wait time (ms) – Temps d’attente moyen par type.

Cumulative wait time (ms) per second – temps total d’attente dans la seconde ;

Waits in progress – nombre de processus en cours d’attente.

Waits started per second – nombre d’attentes d émarrées par seconde.

les blocages et les deadlocks

Nous avons présenté les attentes de façon générale, et nous avons vu qu’il y a plusieurs types d’attentes. Les attentes spécifiques sur la libération de verrous par une autre transaction, sont appelés des blocages : l’attente est involontaire, et dépend d’une autre transaction. La transaction en attente détecte bien entendu cette situation, et elle se considère comme bloquée. Cette information de blocage est visible dans les vues de gestion dynamique sys.dm_os_waiting_tasks et sys.dm_exec_requests (la colonne blocking_session_id y indique le sipd qui bloque la requête en cours).

Voici deux exemples de requêtes pour détecter les blocages :

SELECT session_id, wait_duration_ms, wait_type,

blocking_session_id, resource_description

FROM sys.dm_os_waiting_tasks

WHERE blocking_session_id IS NOT NULL;

SELECT blocked.session_id, st.text as blocked_sql,

blocker.session_id as blocker_session_id, blocker.last_request_end_time

FROM sys.dm_exec_requests blocked

JOIN sys.dm_exec_sessions blocker

ON blocked.blocking_session_id = blocker.session_id

CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) st;

Bien sûr, une session qui est bloquée par une première, peut elle aussi en bloquer une suivante, et ainsi de suite. Lorsque les verrous sont maintenus trop longtemps sur des ressources, il peut se produire ainsi des chaînes de blocage qui finissent par paralyser le serveur. Il est donc important de surveiller ces situations.

Détecter les blocages par notification d’événements

Avant SQL Server 2005, le seul moyen pour surveiller et être averti de blocages survenant sur le serveur, était de planifier une vérification régulière de la table système sysprocesses. Cette approche est toujours possible. Vous pouvez placer dans un travail de l’agent SQL planifié pour s’exécuter toutes les quelques minutes, une requête comme celle-ci (adaptée à SQL Server 2005-2008) :

SELECT

es2.session_id as spid_blocking,

es1.session_id as spid_blocked,

er1.start_time as blocked_start,

er1.row_count,

CASE er1.transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'ReadUncommitted'

WHEN 2 THEN 'Readcommitted'

WHEN 3 THEN 'RepeatableRead'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

END as transaction_isolation_level,

DB_NAME(er1.database_id) as db,

est1.text as sql_command_blocked,

er1.wait_type,

es1.host_name as host_name_blocked,

es1.program_name as program_name_blocked,

es1.login_name as login_name_blocked,

es2.host_name as host_name_blocking,

es2.program_name as program_name_blocking,

es2.login_name as login_name_blocking

FROM sys.dm_exec_requests er1

JOIN sys.dm_exec_sessions es1

ON er1.session_id = es1.session_id

CROSS APPLY sys.dm_exec_sql_text(er1.sql_handle) est1

JOIN sys.dm_exec_sessions es2

ON er1.blocking_session_id = es2.session_id

ORDER BY spid_blocking

qui envoie son résultat, s’il y en a un, par e-mail, à l’aide de Database Mail.

Il existe maintenant une méthode intégrée qui utilise la notification d’événements (event notification). Cette technologie est basée sur les événements DDL (Data Definition Language) disponibles depuis SQL Server 2005 notamment pour écrire des déclencheurs DDL, ainsi que sur Service Broker (voir ci-dessous). Elle permet de répondre à des événements en mode asynchrone, et d’envoyer ces événements aussi bien sur le serveur SQL local que sur une machine distante. L’événement que nous allons utiliser s’appelle BLOCKED_PROCESS_REPORT.

Cet événement doit être activé dans les options du serveur, pour être déclenché lorsqu’un processus est bloqué depuis un certain temps.

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO

EXEC sp_configure 'blocked process threshold', 30

RECONFIGURE

GO

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

GO

la valeur de l’option « blocked process threshold » est exprimée en secondes. Elle indique à partir de quel durée de blocage un événement sera déclenché, dans l’exemple ci-dessus, après 30 secondes, et chaque 30 secondes suivantes, jusqu’à ce que le blocage soit libéré.

En général, un blocage en provoque d’autres à son tour, produisant une chaîne de processus bloqués les uns par les autres. L’événement BLOCKED_PROCESS_REPORT ne montre pas cette chaîne, il se produit pour chaque processus bloqué, individuellement.

Vous devez mettre en place la notification. La fonctionnalité d’event notification est basée sur Service Broker, un système orienté service d’échanges de messages intégré à SQL Server (un concept parfois nommé SODA : Service Oriented Database Architecture). La description de Service Broker étant hors du cadre de cet ouvrage, nous nous contenterons de vous montrer comment mettre pratiquement en œuvre une notification d’événement. Vous devez, dans l’ordre, mettre en place une file d’attente Service Broker, un service, éventuellement une route pour indiquer où les messages seront dirigés. Dans l’exemple ci-dessous, les noms d’objet (queue, service, …

CREATE QUEUE NotifyQueue ;

GO

CREATE SERVICE NotifyService

ON QUEUE NotifyQueue

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

GO

CREATE ROUTE NotifyRoute

WITH SERVICE_NAME = 'NotifyService',

ADDRESS = 'LOCAL';

GO

CREATE EVENT NOTIFICATION BlockedProcessReport

ON SERVER

WITH fan_in

FOR BLOCKED_PROCESS_REPORT

TO SERVICE 'NotifyService', 'current database';

GO

L’information du blocage sera transmise par Service Broker sous forme d’un document XML qui sera posé dans la file d’attente NotifyQueue. Testons-le en créant une table puis en insérant une ligne dans un session après avoir démarré une transaction explicite. Dans une autre session, essayons de lire la table (dans un niveau d’isolation au moins équivalant à READ COMMITTED) :

USE sandbox

GO

CREATE TABLE dbo.testblocage ( id int )

BEGIN TRAN

INSERT dbo.testblocage ( id ) VALUES( 1 )

GO

-- dans une autre session

SELECT * FROM sandbox.dbo.testblocage

Le SELECT est mis en attente de libération du verrou exclusif posé par la transaction d’insertion. Au bout du temps déterminé par l’option ‘blocked process threshold’, la notification d’événement posera une ligne dans la file d’attente NotifyQueue. Nous pouvons simplement faire un SELECT sur cette queue :

SELECT CAST(message_body as XML) as msg

FROM NotifyQueue;

résultat :

<EVENT_INSTANCE>

<EventType>BLOCKED_PROCESS_REPORT</EventType>

<PostTime>2008-05-15T13:16:40.797</PostTime>

<SPID>4</SPID>

<TextData>

<blocked-process-report monitorLoop="22162">

<blocked-process>

<process id="process9a8c58" taskpriority="0" logused="0"
waitresource="RID: 7:1:204:0" waittime="13843" ownerId="289041"
transactionname="SELECT" lasttranstarted="2008-05-15T13:16:26.950"
XDES="0x102331d0" lockMode="S" schedulerid="2" kpid="4224"
status="suspended" spid="54" sbid="0" ecid="0" priority="0"
transcount="0" lastbatchstarted="2008-05-15T13:16:26.950"
lastbatchcompleted="2008-05-15T13:11:12.077"
lastattention="2008-05-15T13:11:12.077" clientapp="Microsoft SQL
Server Management Studio - Query" hostname="BABALUGA-XPS"
hostpid="1804" loginname="BABALUGA-XPS\rudi" isolationlevel="read
committed (2)" xactid="289041" currentdb="1"
lockTimeout="4294967295" clientoption1="671090784"
clientoption2="390200">

<executionStack>

<frame line="1"
sqlhandle="0x0200000077178b24288485b9f690b6810f187eb9cd7ba294" />

</executionStack>

<inputbuf>

SELECT * FROM sandbox.dbo.testblocage </inputbuf>

</process>

</blocked-process>

<blocking-process>

<process status="sleeping" spid="53" sbid="0" ecid="0"
priority="0" transcount="1"
lastbatchstarted="2008-05-15T13:16:19.700"
lastbatchcompleted="2008-05-15T13:16:19.700" clientapp="Microsoft SQL
Server Management Studio - Query" hostname="BABALUGA-XPS"
hostpid="1804" loginname="BABALUGA-XPS\rudi" isolationlevel="read
committed (2)" xactid="289008" currentdb="7"
lockTimeout="4294967295" clientoption1="671090784"
clientoption2="390200">

<executionStack />

<inputbuf>

CREATE TABLE dbo.testblocage ( id int )

BEGIN TRAN

INSERT dbo.testblocage ( id ) VALUES( 1 )

</inputbuf>

</process>

</blocking-process>

</blocked-process-report>

</TextData>

<DatabaseID>7</DatabaseID>

<TransactionID>289041</TransactionID>

<Duration>13843000</Duration>

<EndTime>2008-05-15T13:16:40.793</EndTime>

<ObjectID>0</ObjectID>

<IndexID>0</IndexID>

<ServerName>BABALUGA-XPS\SQL2005</ServerName>

<Mode>3</Mode>

<LoginSid>AQ==</LoginSid>

<EventSequence>1134</EventSequence>

<IsSystem>1</IsSystem>

<SessionLoginName />

</EVENT_INSTANCE>

Vous pouvez voir que le rapport inclut les informations des deux sessions, la bloquante () et la bloquée (), avec les input buffers (les caches de la dernière instruction de la session) de chacune.

La façon correcte de recevoir l’événement est d’utiliser l’instruction RECEIVE, qui permet de lire une file d’attente Service Broker et de la vider des lignes récupérées. RECEIVE peut insérer à son tour le résultat dans… une variable de type table seulement. C’est pour cette raison que nous en utilisons-une dans le code suivant. Nous plaçons l’extraction de la queue dans une transaction, afin d’annuler la suppression générée par RECEIVE en cas de problème. L’exemple démontre aussi l’utilisation de XQuery pour l’extraction des informations dans la structure XML, à l’aide des « méthodes » value et query.

CREATE TABLE dbo.BlockedProcesses (

message_body xml NOT NULL,

report_time datetime NOT NULL,

database_id int NOT NULL,

process xml NOT NULL

)

GO

BEGIN TRY

BEGIN TRAN

DECLARE @BlockedProcesses TABLE (

message_body xml NOT NULL,

report_time datetime NOT NULL,

database_id int NOT NULL,

process xml NOT NULL

);

DECLARE @rowcount int;

RECEIVE cast( message_body as xml ) as message_body,

cast( message_body as xml ).value( '(/EVENT_INSTANCE/PostTime)[1]',
'datetime' ) as report_time,

cast( message_body as xml ).value(
'(/EVENT_INSTANCE/DatabaseID)[1]', 'int' ) as database_id,

cast( message_body as xml ).query(
'/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process'
) as process

FROM NotifyQueue

INTO @BlockedProcesses;

SET @rowcount = @@ROWCOUNT

INSERT INTO dbo.BlockedProcesses

SELECT * FROM @BlockedProcesses;

IF (@rowcount <> @@ROWCOUNT)

ROLlBACK

ELSE

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN

END CATCH

Vous pouvez bien sûr utiliser Database Mail (sp_send_dbmail) pour vous notifier.

Comment éviter les blocages : Les blocages très courts sont inévitables en environnement concurrentiel, ils ne posent en général pas de problème sérieux. Par contre, nous l’avons vu, il faut autant que possible éviter les blocages longs. Pour cela, il faut optimiser le code SQL et créer de bons index pour diminuer la durée des transactions. Il faut aussi savoir jouer sur le niveau d’isolation des transaction. Les niveaux READ UNCOMMITTED et SNAPSHOT (ou READ COMMITTED SNAPSHOT) sont précieux.

Les verrous mortels

Les verrous mortels, ou deadlocks, aussi poétiquement appelés étreintes fatales, sont des animaux particuliers qu’on rencontre occasionnellement dans les buissons des applications transactionnelles. Ils ressemblent un peu aux scénarios des westerns qui respectent l’unité de lieu, comme Rio Bravo. Dans la dernière partie de ce film d’Howard Hawks, les bons, menés par le shérif John T. Chance, détiennent en otage le frère du chef des méchants, Nathan Burdette. Et les méchants se sont emparés de l’adjoint du shérif, Dude. Burdette ne libérera Dude que si Chance lui rend son frère, et Chance ne libèrera le frère que si Burdette rend à Dude sa liberté. Nous savons que dans ce genre de situation, il n’y a qu’une échappatoire : un des deux chefs doit mourir (mauvais exemple ici, car Burdette ne meurt pas).

SQL Server fonctionne de la même façon : un deadlock se produit lorsqu’une transaction attend la libération d’un verrou de la part d’une autre transaction, qui elle-même attend la libération d’un verrou de la première transaction. La situation est sans solution, et SQL Server le détecte à l’aide d’un thread de surveillance dédié, qui lance une recherche à travers toutes les sessions ouvertes[^24]. Lorsqu’il rencontre une sitation de deadlock, il choisit une victime (la transaction qui a effectué le moins de modification de données), et annule (ROLLBACK) cette transaction. Une erreur 1205 est envoyée à la session choisie comme victime, avec ce message :

Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Dans des applications de bases de données correctement implémentées, un deadlock devrait être une situation rare. Elle ne peut se produire que dans le cas d’une transaction explicite, dans un contexte particulier. Illustrons-le par un exemple de code :

**-- dans la session 1**

BEGIN TRAN

UPDATE HumanResources.Employee SET MaritalStatus = 'S'

WHERE Gender = 'F'

UPDATE c SET Title = 'Miss'

FROM Person.Contact c

JOIN HumanResources.Employee e WITH (READUNCOMMITTED)

ON c.ContactID = e.ContactID

WHERE e.Gender = 'F'

COMMIT TRAN

**-- dans la session 2**

BEGIN TRAN

UPDATE c SET Suffix = 'Mrs'

FROM Person.Contact c

JOIN HumanResources.Employee e WITH (READUNCOMMITTED)

ON c.ContactID = e.ContactID

WHERE e.Gender = 'F'

UPDATE HumanResources.Employee SET MaritalStatus = 'M'

WHERE Gender = 'F'

COMMIT TRAN

Comme vous le voyez, les deux transactions effectuent des opérations similaires dans un ordre inversé. C’est la porte ouverte à un deadlock. Il suffit que les transactions se retrouvent au même moment entre leur deux instructions, pour que les instructions suivantes soient fatales. Il est aisé, à partir de cet exemple, de comprendre comment minimiser le risque de deadlock : réduire autant que possible la durée de ses transactions, et éviter d’effectuer les mêmes opérations dans des ordres différents (pour cela, il suffit de centraliser la logique des modifications dans une seule procédure stockée).

Malgré tout, une situation de deadlock est toujours possible, à plus forte raison si vous devez maintenir des bases de données dont vous n’avez pas écrit les procédures. Il est donc prudent de mettre en place une détection de deadlocks. C’est aisé en créant une alerte de l’agent SQL qui vous notifie au déclenchement de l’erreur 1205. Si vous commençez à rencontrer des deadlocks, vous avez quelques outils qui vous permettent de remonter à la source du problème.

Analyser les verrous mortels

les drapeaux de trace 1204 et 1222 activent un mode de rapport détaillé de deadlock. Ils peuvent être utilisés séparément ou ensemble, pour afficher les informations de deux façons différentes. Il n’est possible de de les activer que globalement[^25], soit en les ajoutant en paramètres de lancement de SQL Server : -T1204 ou -T1222, dans le gestionnaire de configuration, comme illustré sur la figure 7.6.

Fig. 7.6 – paramètres de démarrage

soit à l’aide de DBCC TRACEON, en indiquant -1 en second paramètre, ce qui indique l’activation d’un drapeau global :

DBCC TRACEON(1204, -1)

Après activation, le moniteur de deadlocks inscrira des informations sur la chaîne de deadlocks dans le journal d’erreurs de SQL Server (le fichier ERRORLOG, situé dans le sous-répertoire LOG du répertoire où SQL Server stocke ses données). Voici le résultat d’une journalisation réalisée après activation du drapeau 1204 :

Deadlock encountered .... Printing deadlock information

Wait-for graph

Node:1

KEY: 5:72057594044153856 (07005a186c43) CleanCnt:3 Mode:X Flags: 0x0

Grant List 0:

Owner:0x1016D080 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:52 ECID:0
XactLockInfo: 0x11EBAF64

SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1

Input Buf: Language Event: UPDATE c

SET Title = 'Miss'

FROM Person.Contact c

JOIN HumanResources.Employee e WITH (READUNCOMMITTED)

ON c.ContactID = e.ContactID

WHERE e.Gender = 'F'

Requested By:

ResType:LockOwner Stype:'OR'Xdes:0x10687A80 Mode: U SPID:51 BatchID:0
ECID:0 TaskProxy:(0x1090A378) Value:0x1016b6c0 Cost:(0/19956)

Node:2

KEY: 5:72057594043236352 (2e0032505f99) CleanCnt:2 Mode:X Flags: 0x0

Grant List 0:

Owner:0x1016B420 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:0
XactLockInfo: 0x10687AA4

SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 1

Input Buf: Language Event: UPDATE HumanResources.Employee

SET MaritalStatus = 'M'

WHERE Gender = 'F'

Requested By:

ResType:LockOwner Stype:'OR'Xdes:0x11EBAF40 Mode: U SPID:52 BatchID:0
ECID:0 TaskProxy:(0x11BEA378) Value:0x1016b480 Cost:(0/13864)

Victim Resource Owner:

ResType:LockOwner Stype:'OR'Xdes:0x11EBAF40 Mode: U SPID:52 BatchID:0
ECID:0 TaskProxy:(0x11BEA378) Value:0x1016b480 Cost:(0/13864)

Comme vous le voyez, vous avez les informations de base nécessaire à comprendre la source du deadlock : les spids, et la dernière commande exécutée dans chaque spid (l’inputbuffer). La section « Victim Resource Owner » indique qui a été choisi comme victime. Le drapeau 1222 affiche les informations dans un ordre différents, et donne plus d’informations sur les deux sessions concernées par le deadlock.

Vous pouvez également tracer les deadlocks avec le profiler, et obtenir une vue graphique de la chaîne. Les événements Lock:Deadlock, Lock:Deadlock Chain et Lock:Deadlock Graph vous indiquent l’apparition d’une deadlock, la chaîne de résolution, et un détail en format XML. Vous trouverez en figure 7.7 les événements lors d’un deadlock.

Fig. 7.7 – deadlock graph

Vous voyez que la chaîne de deadlock et le graph, sont des événements déclenchés par le système, sur des SPID inférieurs à 50 (donc des spids système). La colonne EventSequence vous permet d’ordrer correctement la séquence d’événements. Un certain nombre de colonnes ajoutent des informations. Nous voyons ici IntegerData, qui retourne un numéro de deadlock – SQL Server leur attribue un numéro incrémentiel – et Type indique sur quelle ressource de verrouillage il s’est produit. L’événement Deadlock Graph s’affiche graphiquement dans le profiler. Vous voyez les ressources en jeu, les types de verrous posés, sur quelques objets. La victime est marquée d’une croix. En laissant votre souris sur un des processus, vous obtenez l’inputbuffer en tooltip..

Maintenant que vous avez tous les éléments en main, vous pouvez dire à vos deadlock, comme John T. Chance : « Sorry don’t get it done, Dude. That’s the second time you hit me. Don’t ever do it again ».