Les dangers du NOLOCK

Un grand nombre de développeurs utilisent de manière stupide le tag « NOLOCK », ayant entendu dire que SQL Server était bloquant et que la solution magique consistait à mettre du NOLOCK partout.

L’utilisation du tag NOLOCK est souvent mal comprise, utilisé à tort et à travers, et peut générer des données incohérentes ! Nous allons voir une démonstration de l’absurdité de l’utilisation du NOLOCK conduisant à corrompre les données de la base…

Le principe des transactions

Tout d’abord notons que toute requête, qu’elle soit en lecture ou en écriture pose des verrous pour assurer la consistance des données manipulées. C’est la base et l’essence même des SGBD relationnel dont l’apanage consiste à réaliser des transactions pour les écritures, comme pour les lectures. Toute requête SQL est une transaction à part entière (même un CREATE ou un ALTER…) et on ne peut pas soustraire les manipulation des données effectuées dans une base relationnelles sans passer par le mécanisme de gestion des transactions… En effet une transaction est une unité de traitement qui doit être effectuée en tout ou rien, c’est à dire que, soit toutes les opérations sont réalisées (COMMIT) soit aucune (ROLLBACK), ce qui revient dans ce dernier cas à défaire le travail entrepris. Même une simple lecture est transactionnelle !

Dans tous les cas, le SGBDR doit garantir que les données ne soient pas modifiées en cours de lecture. Que se passerait-il si madame Michu mettant à 12h45 une côtelette à un prix de 13,57 € le kilo, revenait au rayon boucherie quelques secondes plus tard pour en prendre une deuxième dont le prix aurait évolué entre temps ? Afin de garantir que madame Michu ait un prix stable sur les achats de même nature, un verrou est posé sur tous les objets de son panier jusqu’au moment ou elle passe à la caisse. Les verrous de lecture ont donc la particularité d’être partagé.
Par conséquent, lors des lectures, les verrous partagés garantissent la stabilité des informations. Les informations que l’on lit ne doivent pas être modifiées tant que dure la transaction.
Pour les écritures, on doit s’assurer qu’un seul utilisateur modifie les informations à la fois. Des verrous exclusifs sont posés pendant les écritures afin de garantir qu’aucun autre utilisateur ne va lire les données ou effectuer une autre modification sur les données en cours de changement.

Pour gérer cela, les chercheurs Gray et Bernstein ont proposé au début des années 1970, le principe de journalisation des transactions, qui, associé à un mécanisme de pose des verrous permet de garantir la cohérences des données manipulées. L’algorithme généralement utilisé pour piloter le journal des transaction est baptisé ARIES pour Algorithm for Recovery and Isolation Exploiting Semantics, et figure dans tous les bons SGBD Relationnels.
À lire :
A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging
Repeating History Beyond ARIES

Anomalies transactionnelles

Dans le principe, la garantie absolue qu’aucune interférence entre une transaction et une autre, par exemple entre une modification des données d’une table de la part d’un utilisateur X et une lecture de la même table par un utilisateur Y, nécessite une isolation maximale de la table en cours d’utilisation, ce qui conduit à :

  • verrouiller de manière exclusive la table pour l’utilisateur X afin d’empêcher toute lecture concurrente
  • ou bien de de verrouiller de manière partagée la table pour l’utilisateur Y afin d’empêcher toute écriture concurrente

Et le seul moyen de garantir dans l’absolu toute anomalie transactionnelle est de verrouiller l’intégralité de la table…
Cependant, il est possible de diminuer l’intensité du verrouillage en fonction de la nature des traitements qui y sont effectuées. Pour cela, les SGBD Relationnels distinguent différents modes d’isolation des transactions, permettant de garantir que certaines anomalies transactionnelles soient évitées ou bien ne portent pas à conséquence…

Les anomalies transactionnelles les plus courantes sont au nombre de 4 :

  • la lecture sale (dirty read), appelée aussi lecture impropre ou lecture dans le chaos;
  • la lecture non répétable;
  • la lecture de données fantôme;
  • la perte de mise à jour.

Dans les faits il existe bien d’autres anomalies transactionnelles, mais celles-ci sont les plus courantes.

Vérrouillage et anomalies transactionnelles

  • Si l’on ne pose aucun verrou, toutes les anomalies de lecture sont possible.
  • Si l’on pose un verrou sur les lignes en cours de manipulation (lecture ou écriture), et juste le temps de la requête la lecture sale est éradiquée.
  • Si l’on pose un verrou sur les lignes en cours de manipulation (lecture ou écriture), le temps de la transaction, lecture sale et lecture non répétable sont éradiquées.
  • Si l’on pose un verrou sur la table en cours de manipulation (lecture ou écriture), le temps de la transaction, toutes les anomalies de lecture (lecture sale, lecture non répétable et lecture de données fantômes) sont éradiquées.

Concernant l’anomalie de perte de mise à jour, elle est susceptible de survenir en fonction de la nature des verrous posés.
Si la nature des verrous est :

  • optimiste, alors l’anomalie de perte de mise à jour est possible
  • pessimiste, l’anomalie de perte de mise à jour est impossible

Nous reparlerons de la différence entre verrous pessimiste et optimiste plus loin dans cet article, car leur utilisation permet justement de s’affranchir de l’utilisation imbécile du NOLOCK…

Pour combattre les anomalies transactionnelles, le langage SQL propose une instruction :

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Les tableaux ci-dessous résume les différents niveaux d’isolation des transactions, et les anomalies évitées, en fonction du mode de verrouillage :

Les différents niveaux d’isolation des transactions et les anomalies transactionnelles évitées

NOLOCK

La plupart des développeurs croient naïvement que le tag NOLOCK ne pose pas de verrou. Ce qui est faux. En effet, dans le cadre d’une mise à jour (INSERT, UPDATE, DELETE…), le fait de préciser le tag NOLOCK n’empêchera pas le système de poser des verrous exclusif le temps d’effectuer la mise à jour. Par défaut il est d’ailleurs impossible de poser un tag NOLOCK sur les commandes INSERT, UPDATE, DELETE, MERGE… L’erreur lancée est alors :
Msg 1065, Niveau 15, État 1, Ligne 15
Les indicateurs de verrou NOLOCK et READUNCOMMITTED ne sont pas autorisés pour les tables cibles des instructions INSERT, UPDATE DELETE ou MERGE.

À noter : NOLOCK ou READ UNCOMMITTED (niveau d’isolation) sont deux mots désignant la même chose. Mais le scope en est différent : NOLOCK s’applique au niveau table tandis que READ UNCOMMITED doit être utilisé conjointement à la commande SET TRANSACTION ISOLATION LEVEL et porte globalement sur la transaction.

Par exemple, ces deux scripts SQL sont équivalents :

SELECT COUNT(*) AS NB_FAC, COUNT(DISTINCT C.CLI_ID) AS NB_CLI_FAC
FROM   T_CLIENT AS C WITH (NOLOCK)
       JOIN T_FACTURE AS F  WITH (NOLOCK)
	      ON C.CLI_ID = F.CLI_ID;

SELECT COUNT(*) AS NB_CLI
FROM   T_CLIENT WITH (NOLOCK);
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT COUNT(*) AS NB_FAC, COUNT(DISTINCT C.CLI_ID) AS NB_CLI_FAC
FROM   T_CLIENT AS C
       JOIN T_FACTURE AS F
	      ON C.CLI_ID = F.CLI_ID;

SELECT COUNT(*) AS NB_CLI
FROM   T_CLIENT;

Même en essayant de « truander » le système, le tag NOLOCK d’une lecture de sous requête pour la mise à jour d’une table est ignoré.

Démonstration :

NOLOCK et mise à jour

Le tag NOLOCK est ignoré en cas de mise à jour de la table

Dans ces scripts nous créons une table avec quelques valeurs (requête SQLQuery1.sql).
Dans la fenêtre centrale (requête SQLQuery2.sql) nous mettons à jour la table en faisant une lecture WITH (NOLOCK) de la table. Nous attendons 30 secondes et validons la transaction.
Avant la limite des 30 secondes, nous lançons la troisième requête (requête SQLQuery3.sql). Vous constaterez que cette dernière est bloquée le temps de finaliser la transaction de la deuxième requête…
Le texte de ces trois requêtes est disponible ici :
SQLQuery1.sql
SQLQuery2.sql
SQLQuery3.sql

En fait, l’utilisation du tag NOLOCK induit la pose de plusieurs verrous, notamment :

  • un verrou partagé de schéma sur la table (TAB:Sch-S)
  • un verrou partagé de métadonnées dans la base (MD:Sch-S)
  • un verrou partagé sur la base (DB:S)

Mais le NOLOCK est bien plus pervers… En effet, il ignore les verrous posés par d’autres transactions, conduisant ainsi à :

  • lire plusieurs fois les mêmes lignes
  • omettre la lecture de certaines lignes

si des lignes sont déplacées par des mises à jour concurrentes.
Le résultat est que les informations retournées par l’usage du NOLOCK sont incohérentes !

Démonstration…
Commençons par créer une base de données et deux tables. L’une de nom T_DIRTY comportera un peut plus d’un million de ligne, l’autre T_COUNT sera destinée à insérer le résultat du comptage des lignes. Voici le script de création de ces lignes : (requête Q1.txt)

CREATE DATABASE DB_TEST;
GO
USE DB_TEST;
GO
CREATE TABLE T_DIRTY 
(ID     INT IDENTITY PRIMARY KEY,
 STRING VARCHAR(8000));
GO
INSERT INTO T_DIRTY VALUES (NULL)
GO 1000
INSERT INTO T_DIRTY
SELECT NULL
FROM   T_DIRTY AS T1
       CROSS JOIN T_DIRTY AS T2
GO
CREATE TABLE T_COUNT 
(COUNT_ROW INT, DT DATETIME2)
GO

À ce stade, la table T_DIRTY compte 1 001 000 lignes.

Pour notre test nous allons jouer deux requêtes en parallèle :

NOLOCK et lecture

Le tag NOLOCK provoque des calculs faux lors des lectures

Comme il n’y a aucun ajout de ligne, ni suppression, le résultat doit être constant et toujours égal au nombre de ligne soit 1 001 000.

À la fin du traitement, la lecture de la table T_COUNT, offre quelques surprises :

lecture sale avec NOLOCK

Incohérence des données liées à l’utilisation du tag NOLOCK (SQL Server). Cet indicateur de table est à proscrire…

Comme indiqués les résultats sont incohérents, tantôt il y a plus de lignes, tantôt moins…

Voila ce que vous risquez en utilisant l’indicateur de table NOLOCK !

Il existe une solution pour éviter de bloquer et éviter les incohérences de lectures… Utiliser le verrouillage optimiste au lieu du verrouillage pessimiste. SQL Server est le seul SGBDR à permettre les deux modes de verrouillage. par défaut il fait du verrouillage optimiste car cela permet d’économiser pas mal de ressources. Pour passer du mode pessimiste au mode optimiste, il y a juste un paramètre à changer dans vos bases, mais cela est une autre histoire !

Suppléments…
De nombreux articles approximatif sur ce sujet ont été écrit et montre que, même, ces auteurs ignorent le réel danger que présente le NOLOCK :
Using WITH (NOLOCK)
De l’utilisation de NOLOCK


Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Ce contenu a été publié dans SQL Server, avec comme mot(s)-clé(s) , , . Vous pouvez le mettre en favoris avec ce permalien.