Alors … Oui et non.
Quand on lance une requête sur SQL Server, le comportement par défaut d’une transaction, qu’elle soit implicite ou explicite, est le READ COMMITTED. En d’autres termes, vous effectuez une lecture de données validées, propres. Cela permet d’éviter de lire des données non validées, possiblement incorrectes et incohérentes.
Là où le moteur Oracle possède un tablespace UNDO pour les lectures consistances, SQL Server n’utilise pas de segments UNDO.
Bien que le mode de lecture READ COMMITTED garantisse la validité des données lues, il réduit fortement la concurrence.
Sur cet exemple, nous mettons en évidence que la lecture est bloquée par une autre opération de modification :
Et inversement, un autre cas de figure où la modification est bloquée par la lecture :
Pour comprendre ce comportement, il faut faire la distinction entre les différents types de verrous qui existent sur SQL Server.
Un simple SELECT acquière un lock de type SHARED (partagé, autrement appelé verrou de stabilité du schéma), et peut donc bloquer une autre session souhaitant acquérir un verrou non compatible avec le verrou SHARED ; par exemple un DELETE ou un UPDATE qui détiennent des verrous dits exclusifs (autrement appelé verrou de modification du schéma).
Vous l’aurez compris, deux SELECT ne se bloquent pas l’un l’autre, mais il peut bloquer un INSERT, UPDATE, DELETE ou bien un DDL sur la dite table.
Une fois qu’on a compris ce principe, comment implémenter le niveau d’isolation et limiter au mieux la concurrence des sessions dans un système aussi transactionnel que SQL Server.
Deux philosophies existent.
La première. Le métier accepte des données dites dirty (le point négatif, c’est qu’on lit des données non cohérentes, non committées, transitoires). Dans ce cas, il est possible de contourner les blocages via l’un de ses deux procédés :
- Utilisation du tag NOLOCK dans la requête TSQL ou la procédure stockée
- Utilisation du niveau d’isolation READ UNCOMMITTED à l’ouverture de la session
Mais ces procédés ne sont pas sans risque. En effet, des anomalies de transaction telles que les lectures fantômes peuvent se produire. De plus que le tag NOLOCK verrouille toute la table (via un verrou partagé), et non les quelques lignes consultées, ce qui peut avoir une incidence d’autant plus importante sur les blocages.
La seconde. Le métier n’accepte que des données validées. Ce qui devrait toujours être le cas !
- Dans ce cas de figure, l’implémentation d’un système de vertionning des enregistrements est donc obligatoires pour garantir des lectures consistances sans verrou.
La méthode d’accès aux données via l’option dite snaphot_isolation utile un système de vertionning des enregistrements qui permet de lire une donnée sans poser de lock (ce versionning appelé version store utilise la base tempdb pour stocker ces valeurs). Voyez le comme l’équivalent d’un tablespace UNDO sur Oracle.
L’option d’isolation d’instantanés combine les deux méthodes ci-dessus et fournie donc un accès concurrentiel (sans poser de lock) et propre (lecture de données commitées). Cette méthode réduit drastiquement les contentions de verrouillage.
Ok. Mais comment mettre en place ce type d’accès, sans toutefois que cela devienne ingérable ?
La première étape consiste à activer la fonctionnalité de lecture par snapshot :
- On vérifie que le niveau est bien désactivé avant
SELECT DB_NAME(database_id), snapshot_isolation_state_desc FROM sys.databases
- Activation pour la base de données en question
ALTER DATABASE AxiansDM SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE AxiansDM SET ALLOW_SNAPSHOT_ISOLATION ON
GO- On vérifie que le niveau est bien activé après
SELECT DB_NAME(database_id), snapshot_isolation_state_desc FROM sys.databasesAinsi, plus de blocage généré par de « simples » requêtes SELECT.
Un point de vigilance toutefois, il faudrait surveiller la taille de la base tempdb si nous souhaitons implémenter ce mode d’accès.
Des erreurs du type : « Insufficient space in tempdb to hold row versions. Need to shrink the version store to free up some space in tempdb.» peuvent affecter les requêtes de la base nécessitant un espace temporaire (pour un tri, group by ou autre).
Il se peut que des traitements résiduels persistent à verrouiller des lignes, même après avoir fait ce changement. C’est normal. Certaines de vos chaînes de connexion doivent sans doute continuer à ouvrir des sessions avec un niveau d’isolation personnalisé (UNCOMMITED, REPEATABLE ou SERIALIZABLE), et ne prennent donc pas en compte le niveau SNAPCHOT.
N’ayez crainte. Tentez d’identifier ces traitements/requêtes et corrigez les.
Nous pouvons vous aider en menant un audit complet de votre instance SQL Server. Contactez Axians Data Management.