SQL Server : Quand SQL Server ne sait plus compter (utilisation de NOLOCK)…
J'ai fait un début d'explication sur le sujet, voici un complément sur les méfaits que peut produire l'utilisation de NOLOCK à l'intérieur de requêtes sur vos bases de données.
Le sujet précédent que vous pourrez trouver ici :
http://blogs.codes-sources.com/christian/archive/2007/03/08/sql-server-les-verrous-et-l-utilisation-de-nolock.aspx
Ici nous allons voir que le décompte d'enregistrements n'est pas chose si aisé pour notre moteur de base de données avec l'utilisation de NOLOCK.
Pour rappel : Que fait NOLOCK ?
C'est l'équivalent du niveau d'isolation « READ UNCOMMITTED », mais appliqué à une seule table, la syntaxe sera la suivante dans le cas d'un COUNT sur une table :
select count(*) from TableTest WITH ( NOLOCK )
Ce mode ne demande pas à acquérir de verrous (verrou Shared (S)) lors de la lecture des données, si la table est actuellement en cours d'écriture (elle serait bloquée par un verrou Exclusive (X)) la lecture ne sera pas bloquée. Mais les risques sont élevés, pensez à l'utilisation de Thread multiples accédant à un objet non ThreadSafe, et bien ici c'est pareil.
Nos allons voir, ce qui se passe lors d'un COUNT avec NOLOCK quand la table est actuellement en cours de modification. Le script d'exemple complet est en pièce jointe de ce billet.
On alimente une table avec environ 100 000 enregistrements, pour que l'opération soit visible parfaitement. L'opération sera longue donc le phénomène d'autant plus visible et reproductible, mais soyez sure que çà se produit sur des jeux de données bien plus petite que celui utilisé ici.
On lance une mise à jour :
update TableTest set alea = newid()
Celle-ci à pour effet de fragmenter volontairement les données et de les déplacer physiquement du début de la table vers la fin ou l'inverse. La aussi on amplifie volontairement le phénomène avec un GUID.
Pendant ce temps on réalise une boucle qui compte les enregistrements de la table :
select count(*) from TableTest WITH ( NOLOCK )
On notera (des résultats différents des miens) des écarts au niveau des COUNT :
Nombre d'enregistrements dans la table : 131056 soit une différence de : -16
Nombre d'enregistrements dans la table : 131107 soit une différence de : 35
Nombre d'enregistrements dans la table : 131084 soit une différence de : 12
Comment cela se fait ? Regardons le schéma ci dessous :
La lecture se fait pendant la mise à jour, la compte se déroule, l'enregistrement 3 est compté avant d'être mis à jour à 11. Le décompte se poursuit, l'enregistrement a été déplacé car mis à jour en présence d'un index (Clustered ici, qui a forcé le déplacement physique de l'enregistrement). La lecture réalisée par le NOLOCK continue et trouve notre ancien enregistrement déplacé et le compte une seconde fois, on aura ainsi compté un enregistrement 2 fois, le COUNT(*) est donc faux.
Ce phénomène pourrait aussi se produire dans l'autre sens, si l'enregistrement migrait au début de la table, dans ce cas on compterait moins d'enregistrements qu'il n'en existe réellement dans la table.
Le NOLOCK provoque donc l'apparition de doublons ou la disparition d'enregistrements dans la table lors de requête avec ce niveau d'accès. Encore une fois méfiez vous de ce type d'accès qui vous expose à de grave erreurs, préférez lui des niveaux d'isolation type READ_COMMITTED_SNAPHOt qui a fait son apparition dans SQL Server 2005.
Bon tests…
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
