Confus à propos de UPDLOCK, HOLDLOCK

Question

Alors que des recherches sur l'utilisation des indicateurs de Table, je suis tombé sur ces deux questions:

• Qui des indicateurs de verrou dois-je utiliser (T-SQL)?

• Quel est l'effet HOLDLOCK sur UPDLOCK?

Les réponses à ces deux questions, dire que lors de l'utilisation d' (UPDLOCK, HOLDLOCK), les autres processus ne sera pas en mesure de lire des données sur cette table, mais je n'ai pas vu cela. Pour tester, j'ai créé une table et a commencé à deux SSMS windows. À partir de la première fenêtre, j'ai couru une opération qui sélectionnée à partir de la table à l'aide de divers indicateurs de table. Alors que l'opération était en cours d'exécution, à partir de la deuxième fenêtre, j'ai couru diverses déclarations à voir ce qui serait bloqué.

La table de test:

CREATE TABLE [dbo].[Test](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](50) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

De SSMS Fenêtre 1:

BEGIN TRANSACTION

SELECT * FROM dbo.Test WITH (UPDLOCK, HOLDLOCK)
WAITFOR DELAY '00:00:10'

COMMIT TRANSACTION

De SSMS Fenêtre 2 (couru l'un des éléments suivants):

SELECT * FROM dbo.Test
INSERT dbo.Test(Value) VALUES ('bar')
UPDATE dbo.Test SET Value = 'baz' WHERE Value = 'bar'
DELETE dbo.Test WHERE Value= 'baz'

Effet de différents indicateurs de table sur l'exécution des instructions dans la Fenêtre 2:

           (UPDLOCK)       (HOLDLOCK)    (UPDLOCK, HOLDLOCK)    (TABLOCKX)
---------------------------------------------------------------------------
SELECT    not blocked      not blocked       not blocked         blocked
INSERT    not blocked        blocked           blocked           blocked
UPDATE      blocked          blocked           blocked           blocked
DELETE      blocked          blocked           blocked           blocked

Ai-je mal comprendre les réponses apportées à ces questions, ou faire une erreur dans mes tests? Si non, pourquoi voudriez-vous utiliser (UPDLOCK, HOLDLOCK) vs (HOLDLOCK) seul?

---

De plus amples explications de ce que je suis en train de réaliser:

Je voudrais sélectionner des lignes d'une table et d'empêcher que les données dans la table modifiée alors que je suis en traitement. Je ne suis pas la modification des données, et à autoriser les lectures se produire.

Cette réponse est clairement dit qu' (UPDLOCK, HOLDLOCK) bloquera lit (pas ce que je veux). Les commentaires sur cette réponse implique qu'il est HOLDLOCK qui empêche lit. Pour essayer de mieux comprendre les effets de la table de conseils et de voir si UPDLOCK à elle seule faire ce que je voulais, j'ai fait l'expérience ci-dessus et obtenu des résultats qui contredisent ces réponses.

Actuellement, je crois qu' (HOLDLOCK) est ce que je devrais utiliser, mais je suis préoccupé de ce que j'ai peut-être fait une erreur ou oublié quelque chose qui va revenir à la mords-moi dans l'avenir, d'où cette question.

Answer 1

Pourquoi UPDLOCK bloc sélectionne? La Compatibilité de Verrouillage de la Matrice montre clairement N pour le S/U et U/S de contention, comme dans Aucun Conflit.

Comme pour le HOLDLOCK indicateur de la documentation:

HOLDLOCK: équivalent à SERIALIZABLE. Pour plus d'informations, consultez SERIALIZABLE plus loin dans cette rubrique.

...

SERIALIZABLE: ... L'analyse est effectuée avec la même sémantique que d'une transaction en cours d'exécution au niveau d'isolation SERIALIZABLE...

et le Niveau d'Isolation de Transaction rubrique explique ce qu'est SÉRIALISABLE signifie:

Aucune autre transaction ne peut modifier les données qui ont été lues par le transaction en cours jusqu'à ce que la transaction en cours.

D'autres transactions ne peut pas insérer de nouvelles lignes avec des valeurs de clés tomber dans la gamme de clés de lecture par des instructions de l'actuel transaction jusqu'à ce que la transaction en cours.

Par conséquent, le comportement que vous voyez est parfaitement expliqué par la documentation du produit:

• UPDLOCK ne bloque pas simultanées SÉLECTIONNER ni INSÉRER, mais bloque toute mise à JOUR ou SUPPRIMER des lignes sélectionnées par T1
• HOLDLOCK signifie SERALIZABLE et permet donc de CHOISIR, mais les blocs de mise à JOUR et les SUPPRESSIONS de lignes sélectionnées par T1, ainsi que de toute INSERTION dans la gamme sélectionnée par T1 (qui est l'ensemble de la table, donc tout insert).
• (UPDLOCK, HOLDLOCK): votre expérience ne montre pas ce qui pourrait bloquer outre le cas ci-dessus, à savoir une transaction avec UPDLOCK en T2:
  SELECT * FROM dbo.Test WITH (UPDLOCK) WHERE ...
• TABLOCKX pas besoin d'explications

La vraie question est - ce que vous essayez d'atteindre? Jouer avec les indicateurs de verrou w/o absolue complet de 110% de la compréhension de la sémantique de verrouillage est la mendicité pour les problèmes...

Après l'OP edit:

Je voudrais sélectionner des lignes d'une table et d'empêcher les données de cette table d'être modifiés pendant que je suis en traitement.

Vous devez utiliser l'un de la hausse des niveaux d'isolation des transactions. REPEATABLE READ permettra d'éviter les données que vous lisez d'être modifiés. SERIALIZABLE permettra d'éviter les données que vous lisez d'être modifiées et de nouvelles données soient insérées. En utilisant des niveaux d'isolation de transaction est la bonne approche, par opposition à l'aide des indicateurs de requête. Kendra Peu a une belle affiche exlaining les niveaux d'isolement.

Answer 2

UPDLOCK est utilisé lorsque vous souhaitez verrouiller une ligne ou des lignes au cours d'une instruction select pour une future mise à jour de la déclaration. La future mise à jour pourrait être la prochaine instruction de la transaction.

D'autres sessions peuvent encore voir les données. Ils juste ne peut pas obtenir des verrous qui sont incompatiable avec le UPDLOCK et/ou HOLDLOCK.

Vous utilisez UPDLOCK lorsque vous wan pour tenir d'autres sessions de modifier les lignes que vous avez verrouillé. Il limite leur capacité à mettre à jour ou supprimer les lignes verrouillées.

Vous utilisez HOLDLOCK lorsque vous voulez garder d'autres sessions de modifier les données que vous recherchez. Il limite leur capacité à insérer, mettre à jour ou supprimer les lignes que vous avez verrouillé. Cela vous permet d'exécuter la requête à nouveau et de voir les mêmes résultats.