ReadCommited, ReadUncommited, NoLock

Je Viens de tomber (encore) sur un mauvais cas d’utilisation du NoLock.

Malheureusement, et comme dans 90% des cas, l’utilisation du NoLock est faite “à défaut” pour palier à un problème de blocage…

Ceci n’engage que moi, mais le NoLock, c’est le mal ! (enfin pas que moi dis donc )

Mais revenons tout d’abord aux bases :

NoLock correspond à l’alias de ReadUncommitted qui est un Niveau d’Isolation.
Qu’est ce qu’un niveau d’isolation (ou “Verrou”) : C’est l’isolation d’une transaction en cours, pour éviter qu’une autre transaction ne lise des données non validées (Un – Commit)

Il existe plusieurs niveaux d’isolation :

1. ReadUncommitted
2. ReadCommitted (niveau par défaut)
3. RepeatableRead
4. Serializable

Par défaut SQL SERVER utilise sur toutes ses transactions le niveau ReadCommitted

Il existe deux niveaux particuliers disponibles depuis SQL SERVER 2005

• Read_Committed_Snapshot
• Snapshot

Ces deux niveaux s’appliquent sur la base de données complète. Le premier est relativement proche de ReadCommited, le second plutôt proche du niveau Serializable. Ces deux niveaux, globaux, sont basés sur les versions de lignes.

Snapshot est le niveau par défaut sur Oracle.

Attention, le mode Snapshot a tendance à créer beaucoup d’activité sur la base TempDB, qu’il vous faut donc surveiller et tailler en conséquence

Pourquoi utiliser des verrous ?

Pour bien comprendre le mécanisme, il faut imaginer l’exécution en parallèle de deux requêtes :

1. La première met à jour des données en enchainant les opérations d’Update, ou Delete, le tout dans une transaction.
2. La deuxième lit en parallèle ces même données, dans une transaction.

Il existe 3 cas de lecture de données qui sont très vite problématiques, si on ne fait rien contre :

Cas 1  : Dirty Read (Lecture incorrecte)

Vous lisez une donnée, écrite par une autre transaction qui n’est pas encore validée (imaginez que la transaction qui écrit cette donnée ne soit pas validée (Rollback), vous avez lu une donnée non valide !!)

Cas 2 : NonReapableRead (Lecture non reproductible)

Une transaction lit une donnée une première fois, puis dans la même transaction; essaie de relire cette donnée.. Celle ci a évolué, dû à une autre transaction validée qui l’a modifiée entre temps.

Cas 3 : Phantom Read (lecture fantôme)

Sur ce coup là une transaction lit un ensemble de données, puis dans la même transaction essaie de relire ce même ensemble de lignes, mais trouve un nombre de lignes différents. Une des lignes a été supprimée par une autre transaction validée.

Que peut on faire suivant le niveau d’utilisation ?

C’est ici qu’intervient le niveau d’isolation. Il s’agit d’empêcher qu’une transaction ne lise des données non valides. Le verrou appliquée va bloquer la transaction de lecture en la mettant en attente de déverrouillage. Une fois le verrou levé, la transaction de lecture effectue sa lecture cohérente.

Voici un tableau récapitulatif des comportements des différents niveaux d’isolations suivant les cas de lecture :

: Vous avez un niveau d’isolation qui permet de lire une donnée dans cet état.
: Vous avez une donnée dans un état ne pouvant pas être lu dans ce niveau d’isolation.

Quel niveau pour quel cas ?

Au final le plus sécurisant, si vous voulez éviter de lire des données non transactionnellement stable, c’est le niveau Serializable.
Alors pourquoi ne pas utiliser de base, ce niveau ?

Lorsqu’une transaction débute, elle applique un verrou sur l’ensemble des données qui sont modifiées lors de cette transaction.

Si vous décidez de lire cette donnée avec un niveau d’isolation très haut (genre Serializable), SQL SERVER n’autorisera sa lecture qu’une fois que la première transaction sera terminée.

La transaction se bloque, dans l'attente de la libération du verrou existant.

Par défaut, il n'existe pas de délai d'expiration obligatoire, vous avez donc un risque de blocage infini.
Note :

1. Utilisez la vue sys.dm_os_waiting_tasks pour déterminer si un processus est bloqué
2. Déterminez le temps limite (par défaut illimité) via l’instruction Set Lock_Timeout 

Exemple d’utilisation

Les niveaux d’isolation peuvent être considérés comme un “hint” à placer à la suite de la requête :

Select * from dbo.Client With(NoLock)

Comme nous avons précisé que NoLock = ReadUncommited, on peut donc écrire :

Select * from dbo.Client With(ReadUncommitted)

Effectuer un lot de requêtes sur un niveau particulier:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION
SELECT * FROM Client
SELECT * FROM Employe

COMMIT TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Des ressources !

Msdn biensur : http://msdn.microsoft.com/fr-fr/library/tcbchxcb(VS.80).aspx

Mon pote JP : http://blog.djeepy1.net/post/2008/04/24/De-lutilisation-de-NOLOCK

Christian Robert, MVP SQL SERVER : http://blogs.codes-sources.com/christian/archive/2007/03/08/sql-server-les-verrous-et-l-utilisation-de-nolock.aspx

Craig Freedman : http://blogs.msdn.com/craigfr/archive/tags/Isolation+Levels/default.aspx