Zdezorientowany UPDLOCK, HOLDLOCK


89

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

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.

Odpowiedzi:


102

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 .


+1 i dziękuję za szczegółową odpowiedź. Zaktualizuję moje pytanie, aby dodać szczegóły dotyczące mojego celu.
Jeff Ogata,

1
@Remus Rusanu czy mógłbyś wyjaśnić, dlaczego właściwym podejściem jest stosowanie poziomów izolacji, a nie stosowanie wskazówek dotyczących zapytań? Mam procedurę, w której muszę zablokować tylko dwie tabele przed modyfikacją i używam TABLOCK, HOLDLOCK, czy naprawdę powinienem zmienić poziom izolacji i zablokować wszystkie tabele w mojej transakcji?
Steve

Chciałbym wyjaśnić TABLOCKX :)
niico

Uwaga: link do wpisu na blogu Kendra Little zwraca 404. Nie mogę znaleźć żadnego wpisu z 2 lutego 2011 r., Jak sugeruje link.
Bacon Bits

22

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.


1
Dziękuję, ale nie sądzę, że naprawdę odpowiedziałeś na moje pytanie: czy odpowiedzi na te pytania były błędne, stwierdzając, że (UPDLOCK,HOLDLOCK)blok jest czytany, i czy jest powód, aby używać (UPDLOCK,HOLDLOCK)zamiast po prostu (HOLDLOCK)?
Jeff Ogata,

Moje drugie stwierdzenie odpowiada na twoje pytanie, są błędne. Inne sesje mogą nadal odczytywać dane.
Scott Bruns

Updlock, Holdlock to nie to samo co Holdlock. Updlock, holdlock blokuje wiersze do aktualizacji i serializuje transakcję. Sam Holdlock po prostu serializuje transakcję. Nie blokuje wybranych wierszy w celu dalszego dostępu.
Scott Bruns

„UPDLOCK jest używany, gdy chcesz zablokować wiersz lub wiersze podczas instrukcji select dla przyszłej instrukcji aktualizacji”. Uwielbiam to, ponieważ XLOCK może czasami nie działać
Yiping
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.