Czy NOLOCK jest zawsze zły?

Jestem programistą raportów, który chce, aby moje zapytania były jak najbardziej wydajne. Pracowałem z DBA, który powiedział mi - myślę, że zawsze miałem do czynienia z raportami na serwerze produkcyjnym - do użycia NOLOCKw każdym zapytaniu.

Teraz współpracuję z DBA, który zbanował NOLOCKw każdych okolicznościach - nawet gdy mój raport (z powodu znacznego braku indeksów w kilku tabelach) zatrzymuje replikację i aktualizacje systemu. Moim zdaniem w tym przypadku NOLOCKbyłoby dobrze.

Ponieważ większość moich szkoleń SQL dotyczy różnych DBA z bardzo różnymi opiniami, chciałem zapytać o to szeroką gamę DBA.

Jeśli Twój raport blokuje aktualizacje, że Twój DBA ma rację: absolutnie nie powinieneś go używać NOLOCK. Sam fakt, że tam są konflikty jest wyraźne wskazanie, że jeśli byłoby użyć brudne czyta dostaniemy niepoprawne raporty.

Moim zdaniem zawsze są lepsze alternatywy niż NOLOCK:

• Czy Twoje tabele produkcyjne są tylko do odczytu i nigdy nie są modyfikowane? Zaznacz bazę danych tylko do odczytu!
• Skany tabel powodują konflikty blokad? Indeksuj odpowiednio tabele, korzyści są liczne.
• Nie możesz zmodyfikować / nie wiem, jak odpowiednio indeksować? Użyj SNAPSHOT ISOLATION .
• Nie można zmienić aplikacji na migawkę? Włącz odczyt zatwierdzoną migawkę !
• Zmierzyłeś wpływ wersjonowania wierszy i masz dowody, że wpływa to na wydajność? Nie możesz indeksować danych? i jesteś w porządku z nieprawidłowymi raportami ? Więc przynajmniej zrób sobie przysługę i skorzystaj SET TRANSACTION ISOLATION LEVEL, a nie podpowiedź do zapytania. Łatwiej będzie później naprawić poziom izolacji zamiast modyfikować każde zapytanie.

Nie zawsze jest źle.

Oczywiście pozwala to na odczytanie niezaangażowanych wartości (które mogą zostać wycofane, a zatem nigdy nie istniały logicznie), a także pozwala na takie zjawiska, jak wielokrotne odczytywanie wartości lub wcale.

Jedynymi poziomami izolacji, które gwarantują, że nie napotkasz takich anomalii, jest możliwość serializacji / migawki. W przypadku powtarzalnych wartości odczytu można pominąć, jeśli wiersz zostanie przeniesiony (z powodu aktualizacji klucza), zanim skan osiągnie ten wiersz, w przypadku odczytu zatwierdzonych wartości można odczytać dwukrotnie, jeśli aktualizacja klucza spowoduje przejście do poprzednio odczytanego wiersza.

Prawdopodobieństwo wystąpienia tych problemów jest mniejsze nolock, ponieważ domyślnie na tym poziomie izolacji użyje skanowania z przydzielonym przydziałem, gdy szacuje, że można odczytać więcej niż 64 strony . Oprócz kategorii problemów, które pojawiają się, gdy wiersze przemieszczają się między stronami z powodu aktualizacji klucza indeksu, te uporządkowane skany alokacji są również podatne na problemy z podziałami stron (gdzie wiersze można pominąć, jeśli nowo przydzielona strona znajduje się wcześniej w pliku niż punkt już zeskanowane lub przeczytane dwukrotnie, jeśli już zeskanowana strona jest podzielona na późniejszą stronę w pliku).

Przynajmniej w przypadku prostych zapytań (pojedynczej tabeli) można zniechęcić do korzystania z tych skanów i uzyskać skanowanie z poleceniem klucza nolockpo prostu poprzez dodanie ORDER BY index_keyzapytania do zapytania, tak aby Orderedwłaściwość IndexScanbyła true.

Ale jeśli twoja aplikacja do raportowania nie potrzebuje absolutnie dokładnych liczb i może tolerować większe prawdopodobieństwo takich niespójności, może być do przyjęcia.

Ale z pewnością nie powinieneś odrzucać wszystkich zapytań w nadziei, że będzie to magiczny przycisk „turbo”. Oprócz większego prawdopodobieństwa napotkania nieprawidłowych wyników na tym poziomie izolacji lub braku wyników (błąd „Nie można kontynuować skanowania za pomocą NOLOCK z powodu przenoszenia danych”) istnieją nawet przypadki, w których wydajność nolock może być znacznie gorsza .

Czy Twoi klienci tolerują niespójne wyniki w raportach? Jeśli odpowiedź brzmi „nie”, nie powinieneś używać NOLOCK - możesz uzyskać błędne wyniki w ramach współbieżności. Napisałem kilka przykładów tutaj , tutaj i tutaj . Te przykłady pokazują niespójne wyniki w CZYTANIE ZOBOWIĄZANIA i POWTARZANE CZYTANIE, ale możesz je ulepszyć i uzyskać błędne wyniki również z NOLOCK.

Większość raportów, które tworzę, nie są uruchamiane na bieżących danych. Większość klientów generuje raporty z wczorajszych danych. Czy Twoja odpowiedź zmieniłaby się, gdyby tak było?

W takim przypadku masz jeszcze jedną możliwą opcję:
Zamiast uruchamiać zapytania w produkcyjnej bazie danych i bawić się blokadami NOLOCK, możesz uruchomić raporty z kopii produkcyjnej bazy danych.

Możesz go skonfigurować, aby co noc był automatycznie przywracany z kopii zapasowej .
Najwyraźniej twoje raporty działają na serwerach w witrynach klientów, więc nie wiem, czy skonfigurowanie tego byłoby dla Ciebie realnym rozwiązaniem.
(ale z drugiej strony ... i tak powinny mieć kopie zapasowe, więc wystarczy trochę miejsca na serwerze, aby je przywrócić)

Jestem programistą wewnętrznym, więc jest to dla mnie łatwiejsze, ponieważ mam pełną kontrolę nad serwerami i bazami danych.

Możesz to zrobić przynajmniej w przypadku raportów, które potrzebują tylko danych z wczoraj i starszych. Być może niektóre raporty będą musiały pozostać w produkcyjnej bazie danych, ale przynajmniej przeniesiesz część obciążenia do innej bazy danych (lub jeszcze lepiej innego serwera).

Mam tę samą sytuację w pracy:
używamy takiej produkcyjnej kopii bazy danych do prawie wszystkich raportów, ale istnieje kilka zapytań, które wymagają dzisiejszych danych.