Czy w tej pętli while potrzebne są jawne transakcje?

SQL Server 2014:

Mamy bardzo duży stół (100 milionów wierszy) i musimy na nim zaktualizować kilka pól.

W przypadku wysyłki kłód itp. Oczywiście chcemy również zachować transakcje wielkości kęsa.

Jeśli pozwolimy, aby poniższe polecenie działało przez chwilę, a następnie anulujemy / zakończymy zapytanie, czy wszystkie dotychczas wykonane prace zostaną zatwierdzone, czy też musimy dodać wyraźne instrukcje BEGIN TRANSACTION / END TRANSACTION, abyśmy mogli anulować w dowolnym momencie?

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 10000

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END

Indywidualne wyciągi - DML, DDL itp. - są transakcjami same w sobie. Tak więc, po każdej iteracji pętli (technicznie: po każdej instrukcji), cokolwiek ta UPDATEinstrukcja uległa zmianie, zostało zatwierdzone automatycznie.

Oczywiście zawsze istnieje wyjątek, prawda? Możliwe jest włączenie transakcji niejawnych za pośrednictwem SET IMPLICIT_TRANSACTIONS , w którym to przypadku pierwsza UPDATEinstrukcja rozpoczyna transakcję, którą musisz wykonać COMMITlub ROLLBACKna końcu. Jest to ustawienie poziomu sesji, które w większości przypadków jest domyślnie wyłączone.

czy musimy dodać wyraźne instrukcje BEGIN TRANSACTION / END TRANSACTION, abyśmy mogli anulować w dowolnym momencie?

Nie. W rzeczywistości, biorąc pod uwagę, że chcesz być w stanie zatrzymać proces i uruchomić go ponownie, dodanie jawnej transakcji (lub włączenie transakcji niejawnych) byłoby złym pomysłem, ponieważ zatrzymanie procesu może go złapać przed wykonaniem tego COMMIT. W takim przypadku musisz ręcznie wydać polecenie COMMIT(jeśli korzystasz z SSMS) lub jeśli uruchamiasz to z zadania agenta SQL, wtedy nie masz takiej możliwości i może dojść do osieroconej transakcji.

Możesz także ustawić @CHUNK_SIZEmniejszą liczbę. Eskalacja blokad zwykle ma miejsce przy 5000 blokadach uzyskanych na jednym obiekcie. W zależności od wielkości wierszy i jeśli robi to Blokowanie wierszy kontra Blokowanie stron, być może przekroczyłeś ten limit. Jeśli rozmiar wiersza jest taki, że na każdej stronie mieści się tylko 1 lub 2 wiersze, zawsze możesz to robić, nawet jeśli blokuje strony.

Jeśli tabela jest podzielona na partycje, możesz ustawić LOCK_ESCALATIONopcję (wprowadzoną w SQL Server 2008) dla tabeli, aby AUTOblokowała tylko partycję, a nie całą tabelę podczas eskalacji. Lub, dla każdego stołu, możesz ustawić tę samą opcję DISABLE, chociaż musisz być bardzo ostrożny. Szczegóły znajdziesz w ALTER TABLE .

Oto dokumentacja mówiąca o blokowaniu eskalacji i progach: Blokowanie eskalacji (mówi, że dotyczy to „SQL Server 2008 R2 i wyższych wersji”). A oto post na blogu, który dotyczy wykrywania i naprawy eskalacji blokad: Blokowanie w Microsoft SQL Server (Część 12 - Eskalacja blokad) .

Nie związane z dokładnym pytaniem, ale związane z zapytaniem zawartym w pytaniu, można wprowadzić tutaj kilka ulepszeń (a przynajmniej wydaje się, że w ten sposób po prostu patrząc na nie):

1. W przypadku pętli wykonanie WHILE (@@ROWCOUNT = @CHUNK_SIZE)jest nieco lepsze, ponieważ jeśli liczba wierszy zaktualizowanych podczas ostatniej iteracji jest mniejsza niż kwota wymagana do aktualizacji, oznacza to, że nie ma już pracy do wykonania.

2. Jeśli deletedpole jest BITtypem danych, to czy ta wartość nie zależy od tego, czy deletedDatejest 2000-01-01? Dlaczego potrzebujesz obu?

3. Jeśli te dwa pola są nowe i dodałeś je tak, NULLaby mogło to być działanie online / nieblokujące i chcesz teraz zaktualizować je do wartości „domyślnej”, to nie było to konieczne. Począwszy od SQL Server 2012 (tylko wersja Enterprise), dodawanie NOT NULLkolumn z ograniczeniem DOMYŚLNYM jest operacją nieblokującą, o ile wartość DOMYŚLNA jest stała. Więc jeśli jeszcze nie korzystasz z pól, po prostu upuść i dodaj ponownie jako NOT NULLz domyślnym ograniczeniem.

4. Jeśli żaden inny proces nie aktualizuje tych pól podczas wykonywania tej aktualizacji, byłoby szybsze, jeśli ustawisz w kolejce rekordy, które chcesz zaktualizować, a następnie po prostu obejdziesz tę kolejkę. W obecnej metodzie występuje pogorszenie wydajności, ponieważ za każdym razem trzeba ponownie sprawdzać tabelę, aby uzyskać zestaw, który należy zmienić. Zamiast tego możesz wykonać następujące czynności, które skanują tabelę tylko raz na tych dwóch polach, a następnie wydają tylko bardzo ukierunkowane instrukcje UPDATE. Nie ma również kary za zatrzymanie procesu w dowolnym momencie i rozpoczęcie go później, ponieważ początkowa populacja kolejki po prostu znajdzie rekordy pozostające do aktualizacji.

   1. Utwórz tabelę tymczasową (#FullSet), która zawiera tylko pola kluczy z indeksu klastrowego.
   2. Utwórz drugą tabelę tymczasową (#CurrentSet) o tej samej strukturze.

   3. wstaw do #FullSet przez SELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

      TOP(n)Jest tam ze względu na wielkość stołu. Mając 100 milionów wierszy w tabeli, tak naprawdę nie musisz wypełniać tabeli kolejek całym zestawem kluczy, szczególnie jeśli planujesz co jakiś czas zatrzymywać proces i ponownie go uruchamiać później. Więc może ustawić nna 1 milion i pozwolić, aby przebiegło to do końca. Zawsze możesz zaplanować to w zadaniu agenta SQL, które uruchamia zestaw 1 miliona (a może nawet mniej), a następnie czeka na następny zaplanowany czas, aby ponownie odebrać. Następnie można zaplanować uruchamianie co 20 minut, aby między zestawami było wymuszone oddychanie n, ale cały proces zostanie zakończony bez nadzoru. Następnie po prostu usuń zadanie, gdy nie będzie już nic więcej do zrobienia :-).

   4. w pętli wykonaj:
      1. Wypełnij bieżącą partię za pomocą czegoś podobnego DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
      2. IF (@@ROWCOUNT = 0) BREAK;
      3. Wykonaj AKTUALIZACJĘ, używając czegoś takiego: UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;
      4. Wyczyść bieżący zestaw: TRUNCATE TABLE #CurrentSet;
5. W niektórych przypadkach pomocne jest dodanie indeksu filtrowanego, aby ułatwić SELECTpobieranie danych do #FullSettabeli tymczasowej. Oto kilka uwag związanych z dodawaniem takiego indeksu:
   1. Zatem warunek WHERE powinien pasować do warunku WHERE zapytania WHERE deleted is null or deletedDate is null
   2. Na początku procesu większość wierszy będzie pasować do warunku GDZIE, więc indeks nie jest zbyt pomocny. Przed dodaniem tego możesz poczekać do około 50%. Oczywiście, ile to pomaga i kiedy najlepiej dodać indeks, różnią się z powodu kilku czynników, więc jest to trochę prób i błędów.
   3. Może być konieczne ręczne UAKTUALNIANIE STATYSTÓW i / lub ODBUDOWANIE indeksu, aby był aktualny, ponieważ dane podstawowe zmieniają się dość często
   4. Pamiętaj, że indeks, pomagając SELECT, zaszkodzi, UPDATEponieważ jest to kolejny obiekt, który należy zaktualizować podczas tej operacji, a więc więcej operacji we / wy. Odgrywa to zarówno zastosowanie indeksu filtrowanego (który zmniejsza się, gdy aktualizujesz wiersze, ponieważ mniej wierszy pasuje do filtra), jak i czekanie przez chwilę, aby dodać indeks (jeśli nie będzie to bardzo pomocne na początku, to nie ma powodu, aby go ponosić dodatkowe wejścia / wyjścia).

AKTUALIZACJA: Zapoznaj się z moją odpowiedzią na pytanie związane z tym pytaniem, aby uzyskać pełną implementację sugestii powyżej, w tym mechanizm śledzenia statusu i czystego anulowania: serwer SQL: aktualizowanie pól na dużym stole w małych porcjach: jak uzyskać stan postępu?