Just another tech site

SQL Server NOLOCK


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

voici le post:

SQL Server : Les verrous et l’utilisation de NOLOCK
A force d’entendre les gens en parler et de voir les gens l’utiliser, je pense qu’il y a besoin d’une petite explication sur le sujet.

NOLOCK est ce qu’on appelle un « hint » il se place derrière le nom de la table dans la requête en vue de changer le comportement du moteur au moment de l’exécution de la requête.


SELECT * FROM MaTable WITH(NOLOCK)

Il permet de préciser ce qu’on appelle le niveau d’isolation de la transaction pour la table qu’il suit. Il est strictement équivalent à :


SELECT * FROM MaTable WITH(READUNCOMMITTED)

Et si la requête ne comporte qu’une seule table, ou si toutes les tables étaient suivies de NOLOCK à :


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM MaTable

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Petit rappel sur les verrous… Ils permettent d’isoler une transaction, pour éviter que celle-ci ne soit faussée ou qu’elle ne fausse une autre transaction.

Pour qu’une requête s’exécute sur SQL Server (c’est le cas aussi sur les autres moteurs de base de données) elle demande un verrou sur toutes les tables dont elle a besoin. Le type du verrou est fonction de l’opération qu’elle s’apprête à exécuter (lecture ou écriture). Si elle réussi à obtenir tous les verrous qu’elle a demandée la requête s’exécute, sinon elle se met en attente de l’obtention des verrous manquant.

Prenons la requête qu’il y a plus haut sans NOLOCK :


SELECT * FROM MaTable

Quand elle s’apprête à s’exécuter elle demande un verrou S (lecture) sur la table MaTable. Si une écriture (verrou X) est en cours sur cette même table il sera impossible d’obtenir le verrou S tant que la modification n’est pas terminée. Si par contre il s’agissait d’une autre lecture sur MaTable, la requête pourra s’exécuter.

L’idée générique étant qu’une lecture ne bloque jamais une autre lecture, mais que par contre une écriture bloque une autre écriture, une lecture bloque une écriture et une écriture bloque une lecture.

Que ce passe t’il quand on force le niveau d’isolation READ UNCOMMITED (avec NOLOCK entre autres). C’est en fait le niveau d’isolation le plus bas (voir ci-dessous les niveaux du moins verrouillant au plus verrouillant, le niveau par défaut du moteur est en gras).


READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
(+ SNAPSHOT et READ_COMMITED_SNAPSHOT qui sont un peu hors classement)

Dans ce niveau, le moteur ne demande pas à obtenir un verrou avant d’exécuter la requête (uniquement en SELECT). Ce qu’il fait qu’il n’est pas totalement bloqué par les écritures. Par contre si je faisais une écriture dans ce mode, le moteur fera, comme dans tous les autres niveaux, une demande de verrou X (écriture) avant son exécution.

En sachant çà on en conclu que dans ce niveau d’isolation

Les écritures bloquent toujours les écritures
Les écritures sont toujours bloquées par les lectures
Les écritures ne bloquent plus les lectures
Ce dernier point n’est que partiellement vrai. Il existe un autre mécanisme qui bloque la lecture d’une donnée en cours de modification. Mais contrairement au verrou qui est maintenu jusqu’au COMMIT ou ROLLBACK, ce dispositif est maintenu très peu de temps (quelques millisecondes la plupart du temps).

Le gros problème avec ce niveau, c’est essentiellement le manque de protection de la transaction. En regardant plus près l’exemple qui suit nous allons nous en apercevoir. Mettons que les 2 comptes soient à 2000 au début de la transaction.


BEGIN TRANSACTION 
-- A 
UPDATE solde = solde - 1000 FROM Compte WHERE cpt_id = 123456 
-- B 
UPDATE solde = solde + 1000 FROM Compte WHERE cpt_id = 654321 
-- C 
COMMIT 
-- D

En parallèle j’exécute cette requête :


SELECT solde FROM Compte WITH (NOLOCK)

Que se passe-t-il ? Ma deuxième requête n’est pas bloquée par la première à cause du hint NOLOCK. Par contre voici le résultat du solde des comptes 123456 et 654321 quand la 2ème requête se trouve exécuter au point A, B, C ou D (On distingue D après un COMMIT (transaction validée) ou après un ROLLBACK (transaction annulée)).

123456

654321

A

2000

2000

B

1000

2000

C

1000

3000

D / COMMIT

1000

3000

D / ROLLBACK

2000

2000

Résultat si la requête est validée (COMMIT) et que ma seconde requête s’est exécutée en B, le résultat qu’elle a obtenu est faux. Si la requête a été annulée (ROLLACK), le résultat obtenu en B et en C est faux. Seules les valeurs en A et D sont toujours justes.

Le résultat, c’est que la seconde requête va travailler avec des valeurs fausses, et c’est de cette manière que vous vous retrouverez avec 1000 euros de moins sur votre compte sans aucunes raisons. Je considère que le mode NOLOCK est dangereux et que beaucoup en abuse. Il ne faut pas le mettre partout au risque d’avoir de gros problèmes dans vos données. Le pire c’est que vous ne vous en rendrez pas compte de suite, mais peut être juste en fin d’année lorsqu’au bilan de votre société vous aurez un énorme trou dans les comptes totalement inexplicable.

Les alternatives à NOLOCK pour limiter l’impact des lectures / écritures concurrentes existent :

Les modes SNAPSHOT de SQL Server 2005
Limiter le nombre d’enregistrements renvoyés/modifier par les requêtes
Séparer les données dans 2 tables et les synchroniser par une réplication par exemple. Les lectures seront réalisées sur l’une et les écritures sur l’autre.
Et d’autres…
Bref j’espère que vous ne verrez plus le NOLOCK de la même manière.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Tag Cloud

%d bloggers like this: