215
7.2 Verrouillage
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.
Escalade de verrous
Vous pouvez obtenir des informations précises sur le verrouillage de vos index par la
fonction sys.dm_db_index_operational_stats(). Notamment, la colonne
index_lock_promotion_count 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.
Augmentation de la granularité
Nous verrons dans la section 8.2.1 que nous pouvons contrôler la granularité de
verrouillage par table. 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 le 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 présente deux requêtes utiles :
la première retourne les index dont ces options ont été modifiées, la seconde permet
de tracer les verrous posés par un spid à l’aide des vues de gestion dynamique :
7.2 Verrouillage
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.
Escalade de verrous
Vous pouvez obtenir des informations précises sur le verrouillage de vos index par la
fonction sys.dm_db_index_operational_stats(). Notamment, la colonne
index_lock_promotion_count 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.
Augmentation de la granularité
Nous verrons dans la section 8.2.1 que nous pouvons contrôler la granularité de
verrouillage par table. 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 le 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 présente deux requêtes utiles :
la première retourne les index dont ces options ont été modifiées, la seconde permet
de tracer les verrous posés par un spid à l’aide des vues de gestion dynamique :