Chez un de mes clients on est venu me dire la chose suivante : visiblement lorsque j’utilise le hint NOLOCK ou un niveau de transaction READ UNCOMMITED lorsque j’exécute une requête je vois quand même des verrous posés sur mes tables alors que je croyais le contraire et du coup ma requête en lecture doit bloquer les mises à jour … A cela j’ai répondu : la vérité est ailleurs 
Plus sérieusement, la bonne question à se poser est la suivante : est-ce que le fait d’utiliser un hint NOLOCK ou un niveau de transaction permettant la lecture sale veut dire que aucun verrou n’est posé ?
Pour répondre prenons un exemple simple avec une requête dans la base de données AdventureWorksDW2012 :
FROM dbo.FactInternetSales AS f
CROSS JOIN dbo.FactInternetSales AS f2
Voyons les verrous posées par cette requête à l’aide de la DMV sys.dm_tran_locks :
resource_database_id,
resource_type,
resource_subtype,
request_mode,
request_type,
request_status,
request_owner_type
FROM sys.dm_tran_locks
WHERE request_session_id = "session"
…
Avec un niveau d’isolation de transaction en read committed (niveau d’isolation par défaut) :
Avec un niveau d’isolation de transaction en read uncommitted :
On voit d’abord que le niveau d’isolation de transaction en mode read uncommitted ne veut pas dire qu’il n’existe plus de verrou. Le 1er verrou (de type SHARED_TRANSACTION_WORKSPACE) nous intéresse moins ici car c’est un verrou posé lorsqu’il existe une connexion sur une base de données (en l’occurence database_id = 6 ici). Cela permet de protéger la base de données contre les suppressions accidentelles par exemple lorsqu’il existe une connexion sur cette dernière.
Les verrous suivants sont plus intéressants dans notre cas. On voit que la différence notoire est qu’il existe des verrous de type IS et type S dans un cas (read committed) et plus de verrou de ce type dans l’autre cas (read uncommitted). Le niveau d’isolation de transaction par défaut utilisé par SQL Server garantit une lecture propre des données c’est la raison pour laquelle il existe des verrous de type S et IS à différents niveaux (OBJECT et PAGE) . Avec le niveau d’isolation permettant de la lecture sale ces verrous n’existent évidemment plus mais d’autres verrous existent cependant comme celui que l’on voit dans la dernière image : verrou de type Sch-S. Ce verrou protège la table contre les modifications pendant qu’une lecture est effectuée même si celle-ci n’est pas propre.
Faisons le même test avec une table HEAP (sans index cluster) avec la requête suivante :
FROM dbo.DatabaseLog AS f
CROSS JOIN dbo.DatabaseLog AS f2
… et voyons les verrous posées :
Avec un niveau d’isolation de transaction en read committed (niveau d’isolation par défaut) :
Avec un niveau d’isolation de transaction en read uncommitted :
La aussi on peut voir qu’avec les verrous de type S ne sont plus présents sur les objets de type PAGE ou OBJET avec un niveau d’isolation de transaction en read uncommitted. On a néanmoins un type de verrou supplémentaire que l’on n’avait pas avec une table possédant un index cluster à la ligne 2 (resource_type = HOBT et resource_subtype = BULK_OPERATION). Ce type de verrou n’est présent que sur les tables HEAP pour protéger les lectures contre les pages potentiellement mal formatés via des opérations BULK (merci à Paul Randal pour la précision).
On vient donc de voir que la lecture sale sur SQL Server engendrait certains types de verrous. Cependant on peut pousser le raisonnement plus loin. Qui dit verrou veut dire aussi latch pour accéder aux structures physiques en mémoire … on peut essayer de voir si une requête avec un niveau d’isolation en read uncommitted produit des latchs. Pour cela on peut utiliser la DMV sys.dm_os_waiting_tasks comme ceci :
SELECT TOP 1 *
INTO #waiting_tasks
FROM sys.dm_os_waiting_tasks;
GO
TRUNCATE TABLE #waiting_tasks;
GO
WHILE 1 = 1
BEGIN
INSERT #waiting_tasks
SELECT *
FROM sys.dm_os_waiting_tasks
WHERE SESSION = "session"
END
En parallèle la requête suivante est lancée :
FROM dbo.DatabaseLog AS f
CROSS JOIN dbo.DatabaseLog AS f2
Après avoir lancé une des requêtes avec un niveau d’isolation de transaction en read uncommitted on peut constater que cette dernière a effectivement générée des latchs de type SH dans notre cas :
On peut regarder à quel objet appartient la page 154 par exemple :
GO
DBCC PAGE(6, 1, 154) WITH TABLERESULTS;
GO
…
Pour conclure l’idée que l’utilisation de la lecture sale avec SQL Server ne génère pas de verrous est faussée. Certains les verrous de type S pouvant gêner les écritures sont absents mais on a pu constater au cours de ce billet que d’autres types de verrous étaient et bien présents ainsi que des latchs pouvant même aboutir à une potentielle contention !!
Bonne utilisation de lecture sale
David BARBARIN (Mikedavem)
MVP SQL Server
. Comme vous le savez sans doute lorsqu’un index créé des statistiques lui sont également associées. Ces dernières servent à l’optimisateur de requêtes afin de déterminer un plan optimal pour retrouver les données initiés par une requête. Autrement dit en fonction des cardinalités ou de la sélectivité des données sous jacentes celui-ci va déterminer quelle(s) méthode(s) il devra pour utiliser pour les retrouver avec un minimum de coût. Cependant que se passe-t-il lorsqu’une requête composée d’un prédicat ne concerne pas un index ? He bien celui-ci va créer des statistiques sur la ou les colonnes concernées pour pouvoir estimer la cardinalité des données à retourner. Ces statistiques se retrouvent sous la forme _WA_Sys_00000004_0F382DC6 par exemple. Comment interpréter ce nom plutôt abscons à première vue. C’est ce que nous allons voir dans la suite de billet.