Quel est l'effet HOLDLOCK sur UPDLOCK?

J'ai vu de nombreux exemples de l'indicateur HOLDLOCK être utilisé en combinaison avec UPDLOCK (comme ceci). Cependant La documentation de Microsoft pour ces conseils faire paraître comme HOLDLOCK devrait être redondante, puisque UPDLOCK déjà persiste le verrou jusqu'à la fin de la transaction. (Aussi, il semble dire que HOLDLOCK s'applique uniquement aux verrous partagés de toute façon.)

Comment HOLDLOCK affecter la requête, le cas échéant?

InformationsquelleAutor marijne | 2009-10-06

61

Il a un grand impact.

Le verrou de mise à Jour prend un verrou de mise à Jour sur la ligne, à l'Intention de mise à jour sur la page et un verrou partagé sur la table /base de données.

Cela n'empêche pas d'autres requêtes d'accès aux données dans le tableau, car les verrous sur la page /base de données sont purement part de serrures. Ils juste ne peut pas clash serrures à l'encontre de la personne de lignes /page /table en essayant d'effectuer une opération qui serait en contradiction avec les verrous. Si qui s'est déroulée à la demande dans la file d'attente derrière les verrous en cours et attendre qu'il vienne disponibles avant de pouvoir continuer.

En utilisant holdlock, la requête est contraint à être sérialisé, le verrouillage de la table exclusivement jusqu'à ce que l'action est terminée. Cela empêche tout le monde de la lecture de la table, sauf si l'indicateur nolock est utilisé, permettant potentiellement un sale lire.

Pour voir l'effet, de générer un exemple de tableau 'foo' et de mettre la poubelle des données.
```
begin tran

select * from foo with (updlock)
where tableid = 1
-- notice there is no commit tran
```
Ouvrir une autre fenêtre et essayez:
```
select * from foo
```
Les lignes de revenir, maintenant commettre l'original de la requête de transaction. Exécutez de nouveau modifié pour utiliser holdlock ainsi:
```
begin tran

select * from foo with (updlock, holdlock)
where tableid = 1
```
Revenir à l'autre fenêtre et essayez de sélectionner à nouveau les données, la requête ne retourne pas de valeurs car il est bloqué par le verrou exclusif. Valider la transaction sur la première fenêtre, et les résultats de la deuxième requête apparaîtra, car il n'est plus bloqué.

Test Final est d'utiliser le nolock, exécutez de nouveau la transaction à l'aide de updlock et holdlock. ensuite, exécutez les opérations suivantes dans la deuxième fenêtre:
```
select * from foo (nolock)
```
Les résultats reviendront automatiquement, puisque vous avez accepté de prendre le risque d'une sale de lecture (read).

Sorte qu'il est considéré avoir un grand impact, qui vous obligent les actions contre la table pour être sérialisé qui pourrait être ce que vous voulez (en fonction de la mise à jour à été faite) ou créera un très grand goulot d'étranglement sur la table. Si tout le monde a fait qu'à une table occupée avec de longues transactions en cours d'exécution, alors il serait la cause des retards importants au sein d'une application.

Comme avec toutes les fonctions SQL, lorsqu'il est utilisé correctement, ils peuvent être puissants, mais une mauvaise utilisation d'une fonction /indicateur peut causer des problèmes importants. Je préfère utiliser des indicateurs comme un dernier recours pour quand je dois remplacer le moteur - non pas comme une approche par défaut.

Modifier comme Demandé : Testé dans SQL 2005, 2008, 2008R2 (Entreprise) - tous installés sur à peu près les paramètres par défaut, test de la base de données créée à l'aide de tous les paramètres par défaut (juste entré le nom de la DB seulement).
- une grande réponse, merci
- belle explication, ça aide beaucoup!
- J'ai roulé la modification que vous avez fait, aucun verrou n'est pas la valeur par défaut et la deuxième sélection ne devrait pas prendre un verrou de mise à jour.
- Votre tout droit NOLOCK est pas par défaut, mais s'il vous plaît tester le code que vous avez mis au-dessus, sans NOLOCK...vous verrez que l'SÉLECTIONNEZ exécute indépendamment du fait que HOLDLOCK est ajouté dans la première fenêtre.
- J'ai retesté et il reste correcte pour SQL Server 2005 / 2008 / 2008R2 - ce qui était tout ce qui était disponible au moment de cette question en 2009.
- Je l'ai testé (2008R2 et 2012) et j'obtiens le comportement que je décris dans ma réponse...étrange, je suggère d'autres test pour eux-mêmes.
- Andrew, @Darren: "En utilisant holdlock, la requête...empêche quiconque de la lecture de la table, sauf si l'indicateur nolock est utilisé..." Où je pense que vous êtes malentendu, c'est que, il bloque uniquement les si la connexion avec le verrou modifie les données demandées. Seulement sale lectures sont bloqués. Vous pouvez le voir en action en suivant les étapes décrites dans ce pastie. Notez comment les sélectionner à l'Étape 4 (après le verrouillage, avant modification) ne bloque pas, mais le sélectionner à l'Étape 6 (après modification) t.
- André, @Darren: Et c'est probablement la peine de souligner que la lecture de connexion peut éviter le blocage en utilisant set transaction isolation level read uncommitted. Mais bien sûr, ça voudrait dire que la lecture des données qui pourrait bien ne jamais "réellement" dans la base de données.
- réglage au niveau de la connexion, est en effet en disant NOLOCK sur l'indicateur. Avec NOLOCK, vous pouvez non seulement lire des données qui n'est pas engagé, vous pouvez en de rares occasions, de lire deux fois le même disque.
- Il y a un écart énorme entre le @Andrew et @Darren réponses, et je ne peux pas voir quoi que ce soit dans le manuel de l'expliquer. Pour ce que ça vaut, je ne vois pas de blocage avec ma "un autre" fenêtre de requête en cours d' select * from foo mais je ne vois blocage avec ma ", une nouvelle fenêtre de requête" comme select * from foo with (updlock). Le type qui a du sens, si vous vous souciez de serialising des requêtes de prise de décisions sur l'opportunité de mettre à jour les lignes qu'ils lisent, ils devraient avoir updlock là quelque part. Disclaimer: je ne suis pas assaisonné à SQL Server de verrouillage.
- De plus la question de savoir si ou de ne pas HOLDLOCK bloque effectivement sélectionne: stackoverflow.com/q/7843733/12484
InformationsquelleAutor Andrew
13

Andrew réponse est correcte conformément à la documentation MSDN, cependant j'ai testé contre 2008R2 et 2012, et je ne vois pas ce comportement, de sorte s'il vous plaît TESTER vous-même

Le comportement que je vois est comme ci-dessous:

De la première exécution de ce une pièce de la base de données.
```
CREATE TABLE [dbo].[foo](
    [tableid] [int] IDENTITY(1,1) NOT NULL,
    [Col2] [varchar](100) NOT NULL,
    CONSTRAINT [PK_foo] PRIMARY KEY CLUSTERED 
    (
        [tableid] ASC
    )
)
```
...et de mettre quelques lignes dans.

Maintenant coller ce code dans les deux onglets requête (changer l'onglet " texte dans l'onglet de deux):
```
begin tran

select * from foo with (UPDLOCK, HOLDLOCK)
where tableid = 1

UPDATE foo SET Col2 = 'tab one'
where tableid = 1

commit tran
```
Et le mettre dans un autre tab 3:
```
select * from foo
where tableid = 1
```
1. Assurez-vous que votre pointage de votre jeu de données où la table est.
2. Mettre en évidence tout AVANT l'instruction de mise à jour dans onglet 1 et de l'exécuter.
3. De faire de même dans tab 2 vous trouverez tab 2 ne se termine PAS et est toujours en cours d'exécution.
4. Maintenant exécuter le simple SÉLECTIONNEZ dans tab 3 dans mon environnement, c'est terminé.
5. Mettre en évidence l'instruction de mise à jour dans onglet 1 et de l'exécuter (ne PAS faire le commit encore), vous verrez tab 2 est TOUJOURS en cours d'exécution.
6. Aller de l'avant et d'exécuter l'engagement dans onglet 1...tab 2 allons maintenant procéder à la sélection...vous pouvez exécuter le reste.
- Qui version de SQL Server a été cette course contre? si 2012, puis le comportement de changé, mais en 2005 / 2008 / 2008R2 holdlock serait vigueur dans serializable et en mode bloc à autre chose de prendre un verrou partagé - qui est ce que la "simple" sélectionnez tentera d'émettre.
- Une autre possibilité, vous avez des versions de ligne en fonction de l'isolement activé - lorsque versioning de base de l'isolation est en place, le "simple" select n'est pas question d'un verrou partagé, juste un verrou Sch-S - qui l'empêcherait d'être bloqué, bien que le niveau d'isolement n'est pas la valeur par défaut en 2008. Sera intéressant d'identifier la différence dans les tests.
- et Darren: merci d'éditer vos messages et note les versions de SQL Server que vous avez test vos solutions sur. Liens décrivant tous les changements dans le comportement par défaut (ou tous les réglages que vous avez fait) serait également apprécié =)
- fait, 3 versions, les résultats qu'ils l'étaient il y a 3 ans. Je ne doute pas de Darren résultats ou qu'il a testé, c'est trop complexe d'un problème de le faire à l'aveugle. Un peu surprenant pour une question de 3 ans pour obtenir autant d'attention.
- merci! Nécessaires à la recherche des verrous de la semaine dernière, c'est parfait =)
- J'ai testé contre 2012. C'était une nouvelle nouvelle base de données de test, de sorte réglages par défaut... TRANSACTION_ISOLATION_LEVEL est Readcomitted. snapshot_isolation_state est ÉTEINT. Je n'Ai pas testé contre 2008/2005 encore.
- Fonctionne comme je le décris contre 2008R2 ainsi. Proposer d'autres test pour eux-mêmes.
- Il se comporte de même pour moi en 2008R2 comme @Darren décrit; si il y a une transaction en attente, une deuxième sélection réussit toujours si elle n'utilise pas toutes les allusions.
- C'est un vieux post, mais je pense que c'est encore juste pour ajouter à. J'ai eu la même chose que Darren sur une installation propre de la 2008 R2
InformationsquelleAutor Darren

Vous devez vous connecter pour publier un commentaire.