Comprendre les LOCKS du serveur SQL sur les requêtes SELECT

Question

Je me demande quel est l'avantage d'utiliser SELECT WITH (NOLOCK) sur une table si les seules autres requêtes affectant cette table sont SELECT des requêtes.

Comment cela est-il géré par SQL Server ? Est-ce qu'un SELECT bloc de requête autre SELECT une requête ?

J'utilise SQL Server 2012 et un programme Linq-to-SQL. DataContext .

(EDIT)

À propos de la performance :

• Est-ce qu'un 2e SELECT doivent attendre une 1ère SELECT pour terminer si vous utilisez une SELECT ?
• Versus a SELECT WITH (NOLOCK) ?

Answer 1

A SELECT dans SQL Server placera un verrou partagé sur une ligne du tableau - et une deuxième SELECT nécessiterait également un verrou partagé, et ceux-ci sont compatibles entre eux.

Donc non - un SELECT ne peut pas bloquer un autre SELECT .

Qu'est-ce que le WITH (NOLOCK) query hint est utilisé pour pouvoir lire des données qui sont en train d'être insérées (par une autre connexion) et qui n'ont pas encore été validées.

Sans cet indice de requête, un SELECT peut être bloqué dans la lecture d'une table par une INSERT (ou UPDATE ) qui place un exclusif verrouiller des lignes (ou éventuellement une table entière), jusqu'à ce que la transaction de cette opération ait été validée (ou annulée).

Problème de la WITH (NOLOCK) L'astuce est la suivante : il se peut que vous lisiez des lignes de données qui ne seront pas insérées du tout, en fin de compte (si le fichier INSERT La transaction est annulée), de sorte que votre rapport, par exemple, peut afficher des données qui n'ont jamais vraiment été enregistrées dans la base de données.

Il y a un autre indice d'interrogation qui pourrait être utile - WITH (READPAST) . Ceci indique au SELECT pour sauter toutes les lignes qu'elle tente de lire et qui sont verrouillées exclusivement. L'adresse SELECT ne bloquera pas, et ne lira pas de données "sales" non engagées - mais il pourrait sauter certaines lignes, par exemple ne pas afficher toutes vos lignes dans la table.

Answer 2

Sur la performance, vous continuez à vous concentrer sur la sélection.
Le partage ne bloque pas les lectures.
Mise à jour des blocs de verrouillage partagés.
Si vous avez des centaines de verrous partagés, il faudra un certain temps à une mise à jour pour obtenir un verrou exclusif, car elle doit attendre que les verrous partagés soient levés.

Par défaut, une sélection (lecture) prend un verrou partagé.
Les verrous partagés (S) permettent aux transactions concurrentes de lire (SELECT) une ressource.
Un verrou partagé n'a aucun effet sur les autres sélections (1 ou 1000).

La différence réside dans la manière dont le verrou non partagé ou le verrou partagé affecte l'opération de mise à jour ou d'insertion.

Aucune autre transaction ne peut modifier les données tant que des verrous partagés (S) existent sur la ressource.

Un verrou partagé bloque une mise à jour !
Mais nolock ne bloque pas une mise à jour.

Cela peut avoir un impact considérable sur les performances des mises à jour. Cela a également un impact sur les insertions.

La lecture sale (nolock) semble juste sale. Vous n'obtiendrez jamais de données partielles. Si une mise à jour change John en Sally, vous n'obtiendrez jamais Jolly.

J'utilise beaucoup les verrous partagés pour la concurrence. Les données sont périmées dès qu'elles sont lues. Une lecture de John qui se transforme en Sally la milliseconde suivante est une donnée périmée. Une lecture de Sally qui est retournée à John la milliseconde suivante est une donnée périmée. Cela se passe au niveau de la milliseconde. J'ai un dataloader qui prend 20 heures pour fonctionner si les utilisateurs prennent des verrous partagés et 4 heures pour fonctionner si les utilisateurs ne prennent aucun verrou. Dans ce cas, les verrous partagés font que les données sont périmées depuis 16 heures.

N'utilisez pas mal les nolocks. Mais elles ont leur place. Si vous devez effectuer un contrôle lorsqu'un octet est à 1, puis à 2 lorsque le contrôle est effectué, ce n'est pas le moment d'utiliser un nolock.

Answer 3

Je dois ajouter un commentaire important. Tout le monde mentionne que NOLOCK ne lit que les données sales. Ce n'est pas précis. Il est également possible que vous obteniez deux fois la même ligne ou que la ligne entière soit sautée pendant votre lecture. La raison en est que vous pourriez demander certaines données au même moment où le serveur SQL rééquilibre le b-tree.

Vérifiez un autre fil de discussion

https://stackoverflow.com/a/5469238/2108874

http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx )

Avec l'indication NOLOCK (ou en définissant le niveau d'isolement de la session sur READ UNCOMMITTED), vous indiquez au serveur SQL que vous n'attendez pas de cohérence, et qu'il n'y a donc aucune garantie. Gardez cependant à l'esprit que les "données incohérentes" ne signifient pas seulement que vous pouvez voir des modifications non engagées qui ont été annulées ultérieurement, ou des modifications de données dans un état intermédiaire de la transaction. Cela signifie également que dans une simple requête qui balaie toutes les données de la table ou de l'index, le serveur SQL peut perdre la position de balayage, ou vous pouvez obtenir deux fois la même ligne.

Answer 4

À mon travail, nous avons un très gros système qui fonctionne sur de nombreux PC en même temps, avec de très grandes tables comportant des centaines de milliers de lignes, et parfois plusieurs millions de lignes.

Lorsque vous effectuez un SELECT sur une très grande table, disons que vous voulez connaître toutes les transactions effectuées par un utilisateur au cours des dix dernières années, et que la clé primaire de la table n'est pas construite de manière efficace, l'exécution de la requête peut prendre plusieurs minutes.

Ensuite, notre application pourrait fonctionner sur plusieurs PC d'utilisateurs en même temps, en accédant à la même base de données. Ainsi, si quelqu'un essaie d'insérer dans la table que l'autre SELECT est en train de lire (dans les pages que SQL essaie de lire), alors un LOCK peut se produire et les deux transactions se bloquent mutuellement.

Nous avons dû ajouter un "NO LOCK" à notre instruction SELECT, car il s'agissait d'un énorme SELECT sur une table qui est utilisée par beaucoup d'utilisateurs en même temps et nous avions des LOCKS tout le temps.

Je ne sais pas si mon exemple est suffisamment clair ? Il s'agit d'un exemple concret.

Answer 5

En SELECT WITH (NOLOCK) permet la lecture de données non engagées, ce qui équivaut à avoir la fonction READ UNCOMMITTED le niveau d'isolation défini sur votre base de données. Le site NOLOCK permet un contrôle plus fin que la définition du niveau d'isolation de l'ensemble de la base de données.

Wikipedia propose un article utile : Wikipedia : Isolement (systèmes de bases de données)

Il est également abordé en détail dans d'autres articles de stackoverflow.