La promotion de verrou est phénomène qui se produit couramment dans le moteur. Le but de celui-ci est d'économiser des ressources aussi bien mémoire que processeur.
En pratique dès que le moteur verrouille plus de 5 000 enregistrements il va chercher à verrouiller au niveau supérieur, c'est-à-dire au niveau page, au-delà de 5 000 pages on verrouillera la table. Le contrôle de la promotion se fait tous les 1250 verrous. Il est possible que la promotion échoue par ailleurs et que la finesse de verrouillage reste à son niveau initial.
En tout cas c'est le principe documenté, et il y a des fois où il n'est pas franchement évident de comparer pratique et cette description théorique qui peuvent ne pas correspondre… Eh oui la gestion des verrous reste compliquée !
Voici un petit script pour reproduire le phénomène :
use AdventureWorks
go
-- Démarre une transaction en mode de REPEATABLE READ
-- Pour conserver les verrous après le SELECT
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
-- Etats des stats sur les verrous
select 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(), object_id('Person.Address'), null, null)
select * from Person.Address
-- Liste les verrous de la session en cours
select * from sys.dm_tran_locks where request_session_id = @@SPID
-- Etats des stats sur les verrous
select 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(), object_id('Person.Address'), null, null)
ROLLBACK TRAN – ou COMMIT, on ne fait que des lectures, donc peut importe
Idéalement, lancez SQL Profiler avant, et suivez l'événement « Lock :Escalation » situé dans « Locks ». Et si vous avez du courage « Lock :Acquired » et « Lock : Released », non pas que çà soit compliqué, le nombre d'évènement de type acquisition de verrous et libération sont très nombreux, le résultat sur des grosses tables s'appelle chercher une aiguille dans une botte de foin.
Que constates t'on ? SQL Server commence à verrouiller au niveau enregistrement (çà on le sait par la fonction dm_db_index_operational_stats car le champ row_lock_count augmente après l'exécution de la requête) et va rapidement verrouiller la table (çà on le sait par dm_tran_locks qui nous indique la présence d'un verrou S sur un objet qui est, croyez moi sur parole, notre table).
SQL Profiler a enregistré l'événement Lock Escalation et le champ index_lock_promotion_count a augmenté, signe que nous avons bien eu affaire à une promotion de verrous. En effet le moteur aurait pu opter d'emblé pour un verrou de table.
Dans certains cas le verrou de type table peut avoir des conséquences désastreuses, le moteur ayant tendance a verrouiller la totalité de la table même quand on n'accède pas à la totalité de la table mais qu'un grande partie des données sont impactée par la requête.
SQL Server 2008 apporte des solutions intéressantes :
ALTER TABLE Person.[Address] SET (LOCK_ESCALATION = xxx)
Où xxx peut avoir l'une des valeurs suivantes :
- AUTO : Autorise la promotion de verrou au niveau partition si la table est partitionnée
- DISABLE : Interdit la promotion de verrou sur cette table
- TABLE : Mode par défaut et identique au comportement de la version 2005
Dans les versions précédentes, un peu moins d'options :
Il est possible de désactiver la promotion de verrou au niveau de l'instance à l'aide des Trace Flag 1211 et 1224. Le premier désactive la promotion de verrou de manière inconditionnelle, tandis que le second l'autorise en cas de manque de mémoire.
Pour plus de détails sur ces Trace Flags : http://msdn.microsoft.com/fr-fr/library/ms188396.aspx
Il y a une solution aussi plus orientée bricolage :
BEGIN TRANSACTION
DELETE FROM Person.Address WHERE 1 = 0
On me prend généralement pour un fou lorsque je tape cette requête… =< :o)
Eh oui, il n'y ni commit, ni rollback… Et il faut conserver la connexion ouverte. Cette requête a bien pour effet d'interdire la promotion de verrou au niveau table.
Pour approfondir, l'aide en ligne à une section complète sur le sujet : http://msdn.microsoft.com/fr-fr/library/ms184286.aspx
Les questions avec les réponses cette fois ci... Félicitations aux gagnants !
Pourquoi la dernière requête permet d'elle d'éviter la promotion de verrou au niveau table ?
Un verrou de type IX est posé sur la table, et est maintenu dessus étant donné que la transaction n'est pas cloturée.
Comme le verrou IX est incompatible avec les verrous S et X le moteur de base de données ne peut plus vérrouiller le niveau table, il est ainsi obliger de verrouiller à un niveau inférieur.
La solution est tout de même moins flexible et moins sûre que celle de 2008.
Comment faire pour conserver la session ouverte, et exécuter automatiquement cette requête au démarrage de SQL Server ?
Effectivement démarrer un job dans l'agent de manière automatique au démarre de celui-ci est la méthode la plus simple.
Bons verrous…
