218
Chapitre 7. Transactions et verrous
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 à la taille 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
leurs 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 due à 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 tables partitionnées, en SQL Server
2008
En SQL Server 2005, il n’existe 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é.
7.3 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 multiutilisateurs, 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
Chapitre 7. Transactions et verrous
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 à la taille 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
leurs 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 due à 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 tables partitionnées, en SQL Server
2008
En SQL Server 2005, il n’existe 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é.
7.3 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 multiutilisateurs, 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