Zdezorientowany UPDLOCK, HOLDLOCK

Badając zastosowanie podpowiedzi do tabeli , natknąłem się na te dwa pytania:

• Jakich wskazówek dotyczących blokad powinienem używać (T-SQL)?

• Jaki wpływ ma HOLDLOCK na UPDLOCK?

Odpowiedzi na oba pytania mówią, że podczas używania (UPDLOCK, HOLDLOCK)inne procesy nie będą w stanie odczytać danych z tej tabeli, ale tego nie widziałem. Aby przetestować, stworzyłem tabelę i uruchomiłem dwa okna SSMS. W pierwszym oknie przeprowadziłem transakcję, która została wybrana z tabeli przy użyciu różnych wskazówek dotyczących tabeli. Podczas trwania transakcji w drugim oknie uruchomiłem różne instrukcje, aby zobaczyć, które zostaną zablokowane.

Tabela testowa:

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]

Z okna 1 programu SSMS:

BEGIN TRANSACTION

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

COMMIT TRANSACTION

Z okna SSMS 2 (uruchomiono jedną z następujących czynności):

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'

Wpływ różnych wskazówek dotyczących tabeli na instrukcje uruchamiane w oknie 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

Czy źle zrozumiałem odpowiedzi udzielone w tych pytaniach lub popełniłem błąd podczas testu? Jeśli nie, dlaczego miałbyś używać (UPDLOCK, HOLDLOCK)vs. (HOLDLOCK)sam?

Dalsze wyjaśnienie tego, co próbuję osiągnąć:

Chciałbym wybrać wiersze z tabeli i zapobiec modyfikowaniu danych w tej tabeli podczas jej przetwarzania. Nie modyfikuję tych danych i chciałbym zezwolić na odczytywanie.

Ta odpowiedź wyraźnie mówi, że (UPDLOCK, HOLDLOCK)zablokuje odczyty (nie to, czego chcę). Komentarze do tej odpowiedzi sugerują, że ona HOLDLOCKuniemożliwia odczytanie. Aby lepiej zrozumieć skutki podpowiedzi tabeli i zobaczyć, czy UPDLOCKsam zrobi to, co chcę, przeprowadziłem powyższy eksperyment i otrzymałem wyniki, które są sprzeczne z tymi odpowiedziami.

Obecnie uważam, że (HOLDLOCK)to jest to, czego powinienem użyć, ale obawiam się, że mogłem popełnić błąd lub przeoczyć coś, co w przyszłości będzie mnie gryźć, stąd to pytanie.

Dlaczego miałoby wybierać blokowe UPDLOCK? Blokada Compatibility Matrix wyraźnie wskazuje Nna S / U oraz U / S niezgody, co w żaden konflikt .

Jeśli chodzi o wskazówkę HOLDLOCK w dokumentacji jest napisane :

HOLDLOCK: jest równoważne SERIALIZABLE. Aby uzyskać więcej informacji, zobacz SERIALIZOWALNE w dalszej części tego tematu.

...

SERIALIZABLE: ... Skanowanie jest wykonywane z taką samą semantyką, jak transakcja działająca na poziomie izolacji SERIALIZABLE ...

a temat Poziom izolacji transakcji wyjaśnia, co oznacza SERIALIZOWALNY:

Żadne inne transakcje nie mogą modyfikować danych, które zostały odczytane przez bieżącą transakcję, dopóki bieżąca transakcja nie zostanie zakończona.

Inne transakcje nie mogą wstawiać nowych wierszy z wartościami kluczy, które należałyby do zakresu kluczy odczytywanych przez jakiekolwiek instrukcje w bieżącej transakcji, dopóki bieżąca transakcja nie zostanie zakończona.

Dlatego zachowanie, które widzisz, jest doskonale wyjaśnione w dokumentacji produktu:

• UPDLOCK nie blokuje współbieżnego SELECT ani INSERT, ale blokuje wszelkie UPDATE lub DELETE wierszy wybranych przez T1
• HOLDLOCK oznacza SERALIZOWALNY i dlatego zezwala na WYBÓR, ale blokuje AKTUALIZACJĘ i USUNIĘCIE wierszy wybranych przez T1, a także dowolne WSTAWIENIE w zakresie wybranym przez T1 (czyli całą tabelę, a więc każdą wstawkę).
• (UPDLOCK, HOLDLOCK): Twój eksperyment nie pokazuje, co blokowałoby się poza powyższym przypadkiem, a mianowicie kolejna transakcja z UPDLOCK w T2 :
  SELECT * FROM dbo.Test WITH (UPDLOCK) WHERE ...
• TABLOCKX nie ma potrzeby wyjaśniania

Prawdziwe pytanie brzmi: co próbujesz osiągnąć ? Granie ze wskazówkami dotyczącymi blokowania bez absolutnie pełnego zrozumienia na poziomie 110% semantyki blokowania jest prośbą o kłopoty ...

Po edycji OP:

Chciałbym wybrać wiersze z tabeli i zapobiec modyfikowaniu danych w tej tabeli podczas jej przetwarzania.

Należy użyć jednego z wyższych poziomów izolacji transakcji. REPEATABLE READ zapobiega modyfikowaniu odczytywanych danych. SERIALIZABLE uniemożliwi dane odczytane przed modyfikacją i nowe dane przed włożona. Używanie poziomów izolacji transakcji jest właściwym podejściem w przeciwieństwie do wskazówek dotyczących zapytań. Kendra Little ma ładny plakat wyjaśniający poziomy izolacji .

UPDLOCK jest używany, gdy chcesz zablokować wiersz lub wiersze podczas instrukcji select dla przyszłej instrukcji aktualizacji. Przyszła aktualizacja może być kolejnym oświadczeniem w transakcji.

Inne sesje nadal mogą wyświetlać dane. Po prostu nie mogą uzyskać blokad, które są niekompatybilne z UPDLOCK i / lub HOLDLOCK.

Używasz UPDLOCK, gdy chcesz uniemożliwić innym sesjom zmianę zablokowanych wierszy. Ogranicza ich możliwość aktualizowania lub usuwania zablokowanych wierszy.

Używasz HOLDLOCK, gdy chcesz uniemożliwić innym sesjom zmianę jakichkolwiek danych, na które patrzysz. Ogranicza ich możliwość wstawiania, aktualizowania lub usuwania zablokowanych wierszy. Dzięki temu możesz ponownie uruchomić zapytanie i zobaczyć te same wyniki.