Przewodnik dotyczący blokowania transakcji i wersjonowania wierszy

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w usłudze Microsoft Fabric

W każdej bazie danych niewłaściwe zarządzanie transakcjami często prowadzi do problemów z rywalizacją i wydajnością w systemach, które mają wielu użytkowników. Wraz ze wzrostem liczby użytkowników uzyskujących dostęp do danych, ważne staje się posiadanie aplikacji, które efektywnie wykorzystują transakcje. W tym przewodniku opisano mechanizmy blokowania i przechowywania wersji wierszy używane przez aparat bazy danych w celu zapewnienia integralności każdej transakcji i zawiera informacje na temat efektywnego kontrolowania transakcji przez aplikacje.

Notatka

Zoptymalizowane blokowanie to funkcja silnika bazy danych wprowadzona w 2023 r., która znacząco zmniejsza pamięć zajmowaną przez blokady oraz liczbę blokad wymaganych do współbieżnych zapisów. Ten artykuł został zaktualizowany, aby opisać zachowanie silnika bazy danych z oraz bez zoptymalizowanego blokowania.

• Aby uzyskać więcej informacji i dowiedzieć się, gdzie jest dostępne zoptymalizowane blokowanie, zobacz Zoptymalizowane blokowanie.
• Aby określić, czy zoptymalizowane blokowanie jest włączone w bazie danych, zobacz Czy włączono zoptymalizowane blokowanie?

Zoptymalizowane blokowanie wprowadza istotne zmiany w niektórych sekcjach tego artykułu, w tym:

• blokowanie w mechanizmie bazy danych
• Operacja usuwania
• Operacja wstawiania
• eskalacja blokady
• Zmniejsz blokowanie i eskalację
• zachowanie podczas modyfikowania danych
• zachowanie w podsumowaniu
• wskazówki dotyczące blokowania

Podstawy transakcji

Transakcja to sekwencja operacji wykonywanych jako pojedyncza jednostka logiczna pracy. Jednostka logiczna pracy musi zawierać cztery właściwości, nazywane atomowością, spójnością, izolacją i trwałością (ACID), aby mogła być zakwalifikowana jako transakcja.

niepodzielność
Transakcja musi być niepodzielnym elementem pracy; wszystkie jej modyfikacje danych są wykonywane, albo żadna z nich nie jest wykonywana.

spójność
Po zakończeniu transakcja musi pozostawić wszystkie dane w stanie spójnym. W relacyjnej bazie danych wszystkie reguły muszą być stosowane do modyfikacji transakcji w celu zachowania całej integralności danych. Wszystkie wewnętrzne struktury danych, takie jak indeksy drzewa B lub podwójnie połączone listy, muszą być poprawne na końcu transakcji.

Notatka

W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach rowstore silnik bazy danych implementuje drzewo B+. Nie dotyczy to indeksów magazynu kolumn ani indeksów w tabelach zoptymalizowanych pod kątem pamięci. Aby uzyskać więcej informacji, zobacz przewodnik dotyczący architektury i projektowania indeksów w SQL Server i Azure SQL.

Izolacja
Modyfikacje wprowadzone przez transakcje współbieżne muszą być odizolowane od modyfikacji wprowadzonych przez inne współbieżne transakcje. Transakcja rozpoznaje dane w stanie, w których znajdowała się przed zmodyfikowaniem innej współbieżnej transakcji, lub rozpoznaje dane po zakończeniu drugiej transakcji, ale nie rozpoznaje stanu pośredniego. Jest to określane jako seryjność, ponieważ umożliwia ponowne załadowanie danych początkowych i odtworzenie serii transakcji, aby doprowadzić dane do tego samego stanu, w którym były po wykonaniu oryginalnych transakcji.

trwałość
Po zakończeniu w pełni trwałej transakcji jego efekty są trwale wprowadzone w systemie. Modyfikacje są utrwalane nawet w przypadku awarii systemu. SQL Server 2014 (12.x) i nowsze umożliwiają opóźnione trwałe transakcje. Zatwierdzenie opóźnionych trwałych transakcji następuje, zanim rekord dziennika transakcji zostanie zapisany na dysku. Aby uzyskać więcej informacji na temat opóźnionej trwałości transakcji, zobacz artykuł "Trwałość Transakcji Kontrolnych".

Aplikacje są odpowiedzialne za uruchamianie i kończenie transakcji w punktach, które wymuszają logiczną spójność danych. Aplikacja musi zdefiniować sekwencję modyfikacji danych, które pozostawiają dane w spójnym stanie względem reguł biznesowych organizacji. Aplikacja wykonuje te modyfikacje w jednej transakcji, aby aparat bazy danych mógł wymusić integralność transakcji.

Jest to odpowiedzialność systemu bazy danych przedsiębiorstwa, takiego jak instancja silnika bazy danych, aby dostarczać mechanizmy gwarantujące integralność każdej transakcji. Aparat bazy danych zapewnia:

• Mechanizmy blokujące, które zachowują izolację transakcji.

• Mechanizmy rejestrowania w celu zapewnienia trwałości transakcji. W przypadku w pełni trwałych transakcji, rekord dziennika jest zapisywany na dysku przed zatwierdzeniem transakcji. W związku z tym, nawet jeśli sprzęt serwera, system operacyjny lub wystąpienie aparatu bazy danych ulegnie awarii, system używa dzienników transakcji do automatycznego wycofania wszystkich niekompletnych transakcji do momentu awarii systemu podczas ponownego uruchomienia. Transakcje trwałe są zatwierdzane z opóźnieniem, zanim rekord dziennika transakcji zostanie zapisany na dysku. Takie transakcje mogą zostać utracone, jeśli wystąpi awaria systemu, zanim rekord dziennika zostanie wzmocniony na dysku. Aby uzyskać więcej informacji na temat opóźnionej trwałości transakcji, zobacz artykuł "Trwałość Transakcji Kontrolnych".

• Funkcje zarządzania transakcjami, które wymuszają niepodzielność transakcji i spójność. Po rozpoczęciu transakcji musi zostać pomyślnie ukończona (zatwierdzona) lub aparat bazy danych cofa wszystkie modyfikacje danych wprowadzone przez transakcję od momentu rozpoczęcia transakcji. Ta operacja jest określana jako wycofywanie transakcji, ponieważ zwraca dane do stanu sprzed tych zmian.

Kontrolowanie transakcji

Aplikacje kontrolują transakcje głównie przez określenie, kiedy transakcja rozpoczyna się i kończy. Można to określić przy użyciu instrukcji Transact-SQL lub funkcji interfejsu API (Database Application Programming Interface). System musi być również w stanie poprawnie obsługiwać błędy, które kończą transakcję przed jego ukończeniem. Aby uzyskać więcej informacji, zobacz Transakcje, Wykonywanie transakcji w ODBC, i Transakcje w SQL Server Native Client.

Domyślnie transakcje są zarządzane na poziomie połączenia. Po uruchomieniu transakcji na połączeniu wszystkie instrukcje Transact-SQL wykonywane na tym połączeniu są częścią transakcji do momentu zakończenia transakcji. Jednak w ramach wielu aktywnych sesji zestawu wyników (MARS), transakcja jawna lub niejawna Transact-SQL staje się transakcją o zakresie wsadowym, która jest zarządzana na poziomie wsadu. Po zakończeniu przetwarzania wsadowego, jeśli transakcja o zakresie wsadowym nie zostanie zatwierdzona ani wycofana, silnik bazy danych automatycznie ją wycofa. Aby uzyskać więcej informacji, zobacz Korzystanie z wielu aktywnych zestawów wyników (MARS).

Rozpoczynanie transakcji

Korzystając z funkcji interfejsu API i instrukcji Transact-SQL, można uruchamiać transakcje jako jawne, automatyczne zatwierdzanie lub niejawne transakcje.

jawne transakcje

Jawna transakcja to transakcja, w której jawnie definiujesz zarówno początek, jak i koniec transakcji za pośrednictwem funkcji interfejsu API lub wydając instrukcje Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTIONlub ROLLBACK WORK Transact-SQL. Po zakończeniu transakcji połączenie powraca do trybu transakcji, w którym nastąpiło przed rozpoczęciem jawnej transakcji, co może być trybem niejawnego lub automatycznego zatwierdzania.

Można użyć wszystkich instrukcji Transact-SQL w jawnej transakcji, z wyjątkiem następujących instrukcji:

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• Procedury składowane systemu pełnotekstowego
• sp_dboption ustawić opcje bazy danych lub dowolną procedurę systemową, która modyfikuje bazę danych master w ramach jawnych lub niejawnych transakcji.

Notatka

UPDATE STATISTICS można używać wewnątrz jawnej transakcji. Jednak UPDATE STATISTICS zatwierdza się niezależnie od transakcji, którą otacza, i nie można go wycofać.

automatyczne zatwierdzanie transakcji

Tryb autozatwierdzania jest domyślnym trybem zarządzania transakcjami w aparacie bazy danych. Każde polecenie Transact-SQL jest zatwierdzane lub cofane po zakończeniu. Jeśli instrukcja zakończy się pomyślnie, zostanie zatwierdzona; jeśli wystąpi jakikolwiek błąd, zostanie wycofana. Połączenie z wystąpieniem silnika bazy danych działa w trybie automatycznego zatwierdzania, kiedy ten tryb domyślny nie został zmieniony przez jawne lub niejawne transakcje. Tryb autozatwierdzania jest również trybem domyślnym dla SqlClient, ADO, OLE DB i ODBC.

Niejawnych Transakcji

Gdy połączenie działa w trybie niejawnych transakcji, instancja Silnika Bazy Danych automatycznie rozpoczyna nową transakcję po zatwierdzeniu lub wycofaniu bieżącej transakcji. Nie należy określać początku transakcji; zatwierdzasz lub cofasz tylko każdą transakcję. Niejawny tryb transakcji generuje ciągły łańcuch transakcji. Ustaw niejawny tryb transakcji za pomocą funkcji interfejsu API lub instrukcji Transact-SQL SET IMPLICIT_TRANSACTIONS ON. Ten tryb jest również znany jako Autocommit OFF, zobacz setAutoCommit Method (SQLServerConnection).

Po włączeniu niejawnego trybu transakcji dla połączenia instancja silnika bazy danych automatycznie rozpoczyna transakcję przy pierwszym wykonaniu którejkolwiek z tych instrukcji.

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

Transakcje o zasięgu wsadowym

Dotyczy tylko wielokrotnych aktywnych zestawów wyników (MARS), transakcja Transact-SQL jawna lub niejawna, która rozpoczyna się w ramach sesji MARS, staje się transakcją o zakresie wsadowym. Transakcja o zakresie wsadowym, która nie jest zatwierdzona lub wycofana, gdy wsad zostanie zakończony, jest automatycznie cofana przez mechanizm bazy danych.

Transakcje rozproszone

Transakcje rozproszone obejmują co najmniej dwa serwery znane jako zarządcy zasobów. Zarządzanie transakcją musi być koordynowane między menedżerami zasobów przez składnik serwera nazywany menedżerem transakcji. Każde wystąpienie silnika bazy danych może działać jako menedżer zasobów w transakcjach rozproszonych, koordynowanych przez menedżerów transakcji, takich jak Koordynator Transakcji Rozproszonych firmy Microsoft (MS DTC), lub innych menedżerów transakcji obsługujących specyfikację XA Grupy Open na potrzeby rozproszonego przetwarzania transakcji. Aby uzyskać więcej informacji, zobacz dokumentację usługi MS DTC.

Transakcja w ramach jednego wystąpienia silnika bazy danych, które obejmuje dwie lub więcej baz danych, jest transakcją rozproszoną. Wystąpienie zarządza transakcją rozproszoną wewnętrznie; dla użytkownika, działa jako transakcja lokalna.

W aplikacji transakcja rozproszona jest zarządzana tak samo jak transakcja lokalna. Na końcu transakcji aplikacja żąda zatwierdzenia lub wycofania transakcji. Zatwierdzenie rozproszone musi być zarządzane inaczej przez menedżera transakcji, aby zminimalizować ryzyko, że awaria sieci może doprowadzić do tego, że niektórzy zarządcy zasobów pomyślnie zatwierdzą transakcję, podczas gdy inni ją wycofają. Jest to osiągane przez zarządzanie procesem zatwierdzania w dwóch fazach (faza przygotowania i faza zatwierdzania), która jest nazywana zatwierdzeniem dwufazowym.

• Faza przygotowawcza

  Gdy menedżer transakcji odbiera żądanie zatwierdzenia, wysyła polecenie prepare do wszystkich menedżerów zasobów zaangażowanych w transakcję. Następnie każdy menedżer zasobów wykonuje wszystko, co jest wymagane, aby transakcja była trwała, a wszystkie bufory dziennika transakcji zostają zapisane na dysku. Gdy każdy menedżer zasobów ukończy fazę przygotowania, informuje menedżera transakcji o powodzeniu lub niepowodzeniu tej fazy. Program SQL Server 2014 (12.x) wprowadził opóźnioną trwałość transakcji. Transakcje opóźnione trwałe są zatwierdzane przed opróżnieniem buforów dziennika transakcji na dysk w każdym menedżerze zasobów. Aby uzyskać więcej informacji na temat opóźnionej trwałości transakcji, zobacz artykuł "Trwałość Transakcji Kontrolnych".

• faza zatwierdzania

  Jeśli menedżer transakcji otrzyma potwierdzenia przygotowania ze wszystkich menedżerów zasobów, wysyła polecenia zatwierdzenia transakcji do każdego menedżera zasobów. Menedżerowie zasobów mogą następnie ukończyć zatwierdzenie. Jeśli wszyscy menedżerowie zasobów zgłaszają pomyślne zatwierdzenie, menedżer transakcji wysyła powiadomienie o powodzeniu do aplikacji. Jeśli jakikolwiek menedżer zasobów zgłosił niepowodzenie przygotowania, menedżer transakcji wysyła polecenie wycofania do każdego menedżera zasobów i wskazuje niepowodzenie zatwierdzenia w aplikacji.

  Aplikacje aparatu bazy danych mogą zarządzać transakcjami rozproszonymi za pośrednictwem Transact-SQL lub za pośrednictwem interfejsu API bazy danych. Aby uzyskać więcej informacji, zobacz BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Transakcje końcowe

Transakcje można zakończyć za pomocą instrukcji COMMIT lub ROLLBACK albo za pomocą odpowiedniej funkcji interfejsu API.

• Zatwierdzenie

  Jeśli transakcja zakończy się pomyślnie, zatwierdź ją. Instrukcja COMMIT gwarantuje, że wszystkie modyfikacje transakcji zostaną wprowadzone w stałej części bazy danych. Zatwierdzenie zwalnia również zasoby, takie jak blokady, używane przez transakcję.

• Wycofaj

  Jeśli w transakcji wystąpi błąd lub jeśli użytkownik zdecyduje się anulować transakcję, wycofaj transakcję. Instrukcja ROLLBACK zwraca wszystkie modyfikacje wprowadzone w transakcji, zwracając dane do stanu, w którym znajdowała się na początku transakcji. Cofnięcie transakcji zwalnia zasoby przechowywane przez transakcję.

Notatka

W przypadku wielu aktywnych sesji zestawów wyników (MARS) jawna transakcja uruchomiona za pośrednictwem funkcji interfejsu API nie może zostać zatwierdzona, gdy istnieją oczekujące żądania wykonania. Każda próba zatwierdzenia tego typu transakcji podczas wykonywania żądań powoduje wystąpienie błędu.

Błędy podczas przetwarzania transakcji

Jeśli błąd uniemożliwia pomyślne zakończenie transakcji, aparat bazy danych automatycznie wycofa transakcję i zwalnia wszystkie zasoby przechowywane przez transakcję. Jeśli połączenie sieciowe klienta z instancją Silnika bazy danych zostanie przerwane, wszystkie oczekujące transakcje dla tego połączenia zostaną wycofane, gdy sieć powiadomi instancję o zerwaniu połączenia. Jeśli aplikacja kliencka ulegnie awarii lub komputer kliencki zostanie wyłączony albo uruchomiony ponownie, spowoduje to przerwanie połączenia, a silnik bazy danych wycofa wszelkie niezakończone transakcje, gdy sieć powiadomi go o zerwaniu połączenia. Jeśli klient rozłączy się z aparatem bazy danych, wszystkie zaległe transakcje zostaną wycofane.

Jeśli w partii wystąpi błąd w czasie wykonywania instrukcji (na przykład naruszenie ograniczenia), domyślne działanie silnika bazy danych polega na wycofaniu tylko instrukcji, która wygenerowała błąd. To zachowanie można zmienić przy użyciu instrukcji SET XACT_ABORT ON. Po wykonaniu SET XACT_ABORT ON każdy błąd instrukcji czasu wykonywania powoduje automatyczne wycofanie bieżącej transakcji. Błędy kompilacji, takie jak błędy składni, nie mają wpływu na SET XACT_ABORT. Aby uzyskać więcej informacji, zobacz SET XACT_ABORT (Transact-SQL).

W przypadku wystąpienia błędów należy uwzględnić odpowiednią akcję (COMMIT lub ROLLBACK) w kodzie aplikacji. Jednym z skutecznych narzędzi do obsługi błędów, w tym tych w transakcjach, jest konstrukcja Transact-SQL TRY...CATCH. Aby uzyskać więcej informacji o przykładach obejmujących transakcje, zobacz TRY...CATCH (Transact-SQL). Począwszy od programu SQL Server 2012 (11.x), można użyć instrukcji THROW, aby zgłosić wyjątek i przekazać wykonanie do bloku CATCH konstrukcji TRY...CATCH. Aby uzyskać więcej informacji, zobacz THROW (Transact-SQL).

Błędy kompilowania i uruchamiania w trybie autopoleceń

W trybie automatycznego zatwierdzania czasami wydaje się, że wystąpienie silnika bazy danych anulowało całą transakcję, zamiast wycofać tylko jedną instrukcję SQL. Dzieje się tak, jeśli napotkany błąd jest błędem kompilacji, a nie błędem czasu wykonywania. Błąd kompilacji uniemożliwia aparatowi bazy danych utworzenie planu wykonywania, dlatego nie można wykonać niczego w partii. Chociaż wydaje się, że wszystkie instrukcje przed wygenerowaniem błędu zostały wycofane, błąd uniemożliwił wykonanie czegokolwiek w partii. W poniższym przykładzie żadna z instrukcji INSERT w trzeciej partii nie jest wykonywana z powodu błędu kompilacji. Wydaje się, że pierwsze dwie instrukcje INSERT zostają cofnięte, ponieważ nigdy nie są wykonywane.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

W poniższym przykładzie trzecia instrukcja INSERT generuje błąd zduplikowanego klucza podstawowego w czasie wykonywania. Pierwsze dwie instrukcje INSERT wykonują się pomyślnie i są zatwierdzone, więc pozostają po błędzie czasu wykonywania.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Aparat bazy danych używa odroczonego rozpoznawania nazw, gdzie nazwy obiektów są rozpoznawane w czasie wykonywania, a nie w czasie kompilacji. W poniższym przykładzie pierwsze dwie instrukcje INSERT są wykonywane i zatwierdzane, a te dwa wiersze pozostają w tabeli TestBatch, po tym jak trzecia instrukcja INSERT generuje błąd czasu wykonywania, odwołując się do tabeli, która nie istnieje.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Podstawy blokowania i przechowywania wersji wierszy

Aparat bazy danych używa następujących mechanizmów, aby zapewnić integralność transakcji i zachować spójność baz danych, gdy wielu użytkowników uzyskuje dostęp do danych w tym samym czasie:

• blokowanie

  Każde żądanie transakcji blokuje różne typy zasobów, takich jak wiersze, strony lub tabele, na których zależy transakcja. Blokady blokują modyfikowanie zasobów w inny sposób, co spowodowałoby problemy z transakcją żądającą blokady. Każda transakcja zwalnia blokady, gdy nie ma już zależności od zablokowanych zasobów.

• wersjonowanie wierszy

  Gdy używany jest poziom izolacji opartej na wersjach wierszy, aparat bazy danych obsługuje wersje każdego wiersza, który jest modyfikowany. Aplikacje mogą określać, że transakcja używa wersji wierszy do przeglądania danych na początku transakcji lub zapytania, zamiast chronić wszystkie odczyty za pomocą blokad. Dzięki zastosowaniu wersjonowania wierszy znacznie zmniejsza się prawdopodobieństwo, że operacja odczytu zablokuje inne transakcje.

Blokowanie i przechowywanie wersji wierszy uniemożliwia użytkownikom odczytywanie niezatwierdzonych danych i uniemożliwianie wielu użytkownikom jednoczesnej próby zmiany tych samych danych. Bez blokowania lub przechowywania wersji wierszy zapytania wykonywane względem tych danych mogą powodować nieoczekiwane wyniki, zwracając dane, które nie zostały jeszcze zatwierdzone w bazie danych.

Aplikacje mogą wybierać poziomy izolacji transakcji, które definiują poziom ochrony transakcji od modyfikacji wprowadzonych przez inne transakcje. Wskazówki na poziomie tabeli można określić dla poszczególnych instrukcji Transact-SQL w celu dalszego dostosowania zachowania do wymagań aplikacji.

Zarządzanie dostępem współbieżnych danych

Użytkownicy, którzy uzyskują dostęp do zasobu w tym samym czasie, uzyskują dostęp do zasobu równocześnie. Współbieżny dostęp do danych wymaga mechanizmów, aby zapobiec negatywnym skutkom, gdy wielu użytkowników próbuje modyfikować zasoby, z których aktywnie korzystają inni użytkownicy.

Efekty współbieżności

Użytkownicy modyfikujący dane mogą mieć wpływ na innych użytkowników, którzy odczytują lub modyfikują te same dane w tym samym czasie. Mówi się, że użytkownicy uzyskują jednocześnie dostęp do danych. Jeśli baza danych nie ma kontroli współbieżności, użytkownicy mogą zobaczyć następujące skutki uboczne:

• utracone aktualizacje

  Utracone aktualizacje występują, gdy co najmniej dwie transakcje wybierają ten sam wiersz, a następnie aktualizują wiersz na podstawie wartości, którą wybrano na początku. Każda transakcja nie jest świadoma innych transakcji. Ostatnia aktualizacja zastępuje aktualizacje wprowadzone przez inne transakcje, co powoduje utratę danych.

  Na przykład dwa edytory tworzą elektroniczną kopię tego samego dokumentu. Każdy edytor zmienia kopię niezależnie, a następnie zapisuje zmienioną kopię, zastępując w ten sposób oryginalny dokument. Edytor, który zapisuje zmienioną kopię jako ostatni, nadpisuje zmiany wprowadzone przez innego edytora. Ten problem można uniknąć, jeśli jeden edytor nie może uzyskać dostępu do pliku do czasu zakończenia i zatwierdzenia transakcji przez drugi edytor.

• niezatwierdzona zależność (brudny odczyt)

  Niezatwierdzona zależność występuje, gdy druga transakcja odczytuje wiersz, który jest aktualizowany przez inną transakcję. Druga transakcja odczytuje dane, które nie zostały jeszcze zatwierdzone i mogą zostać zmienione przez transakcję aktualizującą wiersz.

  Na przykład edytor wprowadza zmiany w dokumencie elektronicznym. Podczas wprowadzania zmian drugi edytor pobiera kopię dokumentu zawierającego wszystkie zmiany wprowadzone do tej pory i dystrybuuje dokument do zamierzonej grupy odbiorców. Następnie pierwszy edytor decyduje, że zmiany wprowadzone do tej pory są nieprawidłowe i usuwa zmiany i zapisuje dokument. Dokument rozproszony zawiera edycje, które już nie istnieją i powinny być traktowane tak, jakby nigdy nie istniały. Ten problem można uniknąć, jeśli nikt nie może odczytać zmienionego dokumentu, dopóki pierwszy edytor nie wykona ostatecznego zapisu modyfikacji i zatwierdzi transakcję.

• niespójna analiza (niewykonywalne powtórne odczyty)

  Niespójna analiza występuje, gdy druga transakcja uzyskuje dostęp do tego samego wiersza kilka razy i odczytuje różne dane za każdym razem. Niespójna analiza jest podobna do niezatwierdzonej zależności w tym, że inna transakcja zmienia dane odczytywane przez drugą transakcję. Jednak w niespójnej analizie dane odczytane przez drugą transakcję zostały zatwierdzone przez transakcję, która dokonała zmiany. Ponadto niespójna analiza obejmuje wiele operacji odczytu (dwie lub więcej) tego samego wiersza, przy czym za każdym razem informacje są zmieniane przez inną transakcję; stąd termin niepowtarzalny odczyt.

  Na przykład edytor odczytuje ten sam dokument dwa razy, ale między każdym z czytań autor przepisuje dokument. Gdy edytor odczytuje dokument po raz drugi, zmienił się. Oryginalny odczyt nie był powtarzalny. Można byłoby uniknąć tego problemu, gdyby autor nie mógł zmieniać dokumentu, dopóki redaktor nie przeczyta go po raz ostatni.

• Phantom odczytuje

  Odczyt phantom jest sytuacją, która występuje, gdy są wykonywane dwa identyczne zapytania, a zestaw wierszy zwracanych przez drugie zapytanie jest inny. W poniższym przykładzie pokazano, jak może się to zdarzyć. Załóżmy, że dwie transakcje są wykonywane w tym samym czasie. Dwie instrukcje SELECT w pierwszej transakcji mogą zwracać różne wyniki, ponieważ instrukcja INSERT w drugiej transakcji zmienia dane używane przez obie te transakcje.

  --Transaction 1
  BEGIN TRAN;
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 AND ID < 10;
  
  --The INSERT statement from the second transaction occurs here.
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee (Id, Name)
  VALUES(6 ,'New');
  
  COMMIT;
  

• Brakujące i podwójne odczyty spowodowane aktualizacjami wierszy

  • Brak zaktualizowanego wiersza lub wielokrotne wyświetlanie zaktualizowanego wiersza

    Transakcje działające na poziomie READ UNCOMMITTED (lub instrukcje korzystające ze wskazówki tabeli NOLOCK) nie przydzielają blokad współdzielonych, w celu uniemożliwienia innym transakcjom modyfikowania danych odczytanych przez bieżącą transakcję. Transakcje uruchomione na poziomie READ COMMITTED powodują problemy z blokadami udostępnionymi, ale blokady wiersza lub strony są zwalniane po odczytaniu wiersza. W obu przypadkach podczas skanowania indeksu, jeśli inny użytkownik zmieni kolumnę klucza indeksu wiersza podczas odczytu, wiersz może pojawić się ponownie, jeśli zmiana klucza przeniosła wiersz na pozycję przed skanowaniem. Podobnie wiersz może nie być w ogóle odczytany, jeśli zmiana klucza przeniosła wiersz do pozycji w indeksie, która została już odczytana. Aby tego uniknąć, użyj wskazówki SERIALIZABLE lub HOLDLOCK albo wersjonowania wierszy. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące tabel (Transact-SQL).

  • Brak co najmniej jednego wiersza, który nie był celem aktualizacji

    Podczas gdy używasz READ UNCOMMITTED, jeśli twoje zapytanie odczytuje wiersze przy użyciu skanowania kolejności alokacji (przy użyciu stron IAM), możesz przegapić wiersze, jeśli inna transakcja powoduje podział strony. Nie występuje to w przypadku korzystania z poziomu izolacji READ COMMITTED.

Typy współbieżności

Gdy wiele transakcji próbuje zmodyfikować dane w bazie danych w tym samym czasie, należy zaimplementować system kontroli, aby modyfikacje wprowadzone przez jedną transakcję nie miały negatywnego wpływu na te z innej transakcji. Jest to nazywane kontrolką współbieżności.

Teoria kontroli współbieżności ma dwie klasyfikacje metod instalowania kontroli współbieżności:

• pesymistyczne sterowanie współbieżnością

  System blokad uniemożliwia transakcjom modyfikowanie danych w sposób, który wpływa na inne transakcje. Po wykonaniu przez transakcję akcji, która powoduje zastosowanie blokady, inne transakcje nie mogą wykonywać akcji powodujących konflikt z blokadą, dopóki właściciel go nie zwolni. Jest to nazywane pesymistyczną kontrolą, ponieważ jest zwykle używana w systemach, w których występuje duża rywalizacja o dane, gdzie koszt ochrony danych za pomocą blokad jest mniejszy niż koszt wycofywania transakcji, jeśli wystąpią konflikty współbieżności.

• Optymistyczna kontrola współbieżności

  W przypadku optymistycznej kontroli współbieżności transakcje nie blokują danych podczas ich odczytywania. Jednak gdy transakcja aktualizuje dane, system sprawdza, czy inna transakcja zmieniła dane po jego odczytaniu. Jeśli inna transakcja zaktualizowała dane, zostanie zgłoszony błąd. Zazwyczaj transakcja, która napotyka błąd, cofa się i rozpoczyna od nowa. Jest to nazywane optymistycznym, ponieważ jest zwykle używane w systemach, w których występuje niskie współzawodnictwo o dane, a koszt od czasu do czasu wycofywania transakcji jest niższy niż koszt blokowania danych w momencie odczytu.

Aparat bazy danych obsługuje obie metody sterowania współbieżnością. Użytkownicy określają typ kontrolki współbieżności, wybierając poziomy izolacji transakcji dla połączeń lub opcji współbieżności na kursorach. Te atrybuty można zdefiniować przy użyciu instrukcji Transact-SQL lub za pomocą właściwości i atrybutów interfejsów programowania aplikacji bazy danych (API), takich jak ADO, ADO.NET, OLE DB i ODBC.

Poziomy izolacji w aparacie bazy danych

Transakcje określają poziom izolacji, który definiuje stopień, w jakim jedna transakcja musi być odizolowana od modyfikacji zasobów lub danych dokonanych przez inne transakcje. Poziomy izolacji są opisane pod kątem skutków ubocznych współbieżności, takich jak na przykład brudne odczyty czy odczyty fantomowe, które mogą być dopuszczalne.

Kontrola poziomów izolacji transakcji:

• Czy blokady są uzyskiwane podczas odczytywania danych oraz jaki typ blokad jest żądany.
• Jak długo są przechowywane blokady odczytu.
• Czy operacja odczytu, która odwołuje się do wierszy zmodyfikowanych przez inną transakcję, jest dopuszczalna?
  • Blokuje blokadę do momentu zwolnienia blokady na wyłączność w wierszu.
  • Pobiera zatwierdzoną wersję wiersza, która istniała w momencie uruchomienia instrukcji lub transakcji.
  • Odczytuje niezatwierdzone modyfikacje danych.

Ważny

Wybranie poziomu izolacji transakcji nie ma wpływu na blokady uzyskane w celu ochrony modyfikacji danych. Transakcja zawsze przechowuje wyłączną blokadę w celu przeprowadzenia modyfikacji danych i przechowuje tę blokadę do momentu zakończenia transakcji, niezależnie od poziomu izolacji ustawionego dla tej transakcji. W przypadku operacji odczytu poziomy izolacji transakcji definiują przede wszystkim poziom ochrony przed skutkami modyfikacji wprowadzonych przez inne transakcje.

Niższy poziom izolacji zwiększa zdolność wielu transakcji do jednoczesnego dostępu do danych, ale także zwiększa liczbę efektów współbieżności, takich jak brudne odczyty lub utracone aktualizacje, które transakcje mogą napotkać. Z drugiej strony wyższy poziom izolacji zmniejsza typy skutków współbieżności, które mogą napotkać transakcje, ale wymaga więcej zasobów systemowych i zwiększa prawdopodobieństwo, że jedna transakcja blokuje inną. Wybór odpowiedniego poziomu izolacji zależy od równoważenia wymagań dotyczących integralności danych aplikacji na poziomie obciążenia poszczególnych poziomów izolacji. Najwyższy poziom izolacji, SERIALIZABLE, gwarantuje, że transakcja pobiera dokładnie te same dane za każdym razem, gdy powtarza operację odczytu, ale robi to, wykonując poziom blokowania, który może mieć wpływ na inne transakcje w systemach wielu użytkowników. Najniższy poziom izolacji, READ UNCOMMITTED, może pobierać dane zmodyfikowane, ale niezatwierdzone przez inne transakcje. Wszystkie skutki uboczne współbieżności mogą wystąpić w READ UNCOMMITTED, ale nie ma blokady odczytu ani przechowywania wersji, więc obciążenie jest zminimalizowane.

Poziomy izolacji aparatu bazy danych

Standard ISO definiuje następujące poziomy izolacji, z których wszystkie są obsługiwane przez aparat bazy danych:

Poziom izolacji	Definicja
READ UNCOMMITTED	Najniższy poziom izolacji, na którym transakcje są izolowane tylko na tyle, aby upewnić się, że dane fizycznie niespójne nie są odczytywane. Na tym poziomie dozwolone są brudne odczyty, więc jedna transakcja może zobaczyć niezatwierdzone zmiany wprowadzone przez inne transakcje.
READ COMMITTED	Umożliwia transakcji odczytywanie danych wcześniej odczytanych (niezmodyfikowanych) przez inną transakcję bez oczekiwania na ukończenie pierwszej transakcji. Aparat bazy danych przechowuje blokady zapisu (nabyte na wybranych danych) do końca transakcji, ale blokady odczytu są zwalniane natychmiast po wykonaniu operacji odczytu. Jest to domyślny poziom silnika bazy danych.
REPEATABLE READ	Silnik bazy danych przechowuje blokady odczytu i zapisu, które są nałożone na wybrane dane do końca transakcji. Jednak ze względu na to, że blokady zakresu nie są zarządzane, mogą wystąpić odczyty widmowe.
SERIALIZABLE	Najwyższy poziom, na którym transakcje są całkowicie odizolowane od siebie. Aparat bazy danych przechowuje blokady odczytu i zapisu pozyskane na wybranych danych do końca transakcji. Blokady zakresu są uzyskiwane, gdy operacja SELECT używa klauzuli WHERE zakresu, aby uniknąć odczytów fantomowych. Uwaga: operacje DDL i transakcje w replikowanych tabelach mogą zakończyć się niepowodzeniem, gdy wymaga się poziomu izolacji SERIALIZABLE. Dzieje się tak, ponieważ zapytania replikacji używają wskazówek, które mogą być niezgodne z poziomem izolacji SERIALIZABLE.

Silnik bazy danych obsługuje również dwa dodatkowe poziomy izolacji transakcji, które używają wersjonowania wierszy. Jedną z nich jest implementacja poziomu izolacji READ COMMITTED, a drugi to SNAPSHOT poziom izolacji transakcji.

Poziom izolacji wersjonowania wierszy	Definicja
Read Committed Snapshot (RCSI)	Po ustawieniu opcji bazy danych READ_COMMITTED_SNAPSHOT na ON(domyślne ustawienie w usłudze Azure SQL Database), poziom izolacji READ COMMITTED używa wersjonowania wierszy w celu zapewnienia spójności odczytu na poziomie instrukcji. Operacje odczytu wymagają tylko blokad na poziomie tabeli zapewniających stabilność schematu (Sch-S) i bez blokad stron ani wierszy. Oznacza to, że silnik bazy danych używa wersjonowania wierszy, aby przedstawić każdą instrukcję w postaci transakcyjnie spójnej migawki danych, jaką istniała na początku tej instrukcji. Blokady nie są używane do ochrony danych przed aktualizacjami przez inne transakcje. Funkcja zdefiniowana przez użytkownika może zwracać dane zatwierdzone po rozpoczęciu instrukcji zawierającej funkcję UDF. Gdy opcja READ_COMMITTED_SNAPSHOT bazy danych jest skonfigurowana na OFF, co jest ustawieniem domyślnym w programie SQL Server i usłudze Azure SQL Managed Instance, izolacja READ COMMITTED używa udostępnionych blokad, aby zapobiec modyfikowaniu wierszy przez inne transakcje, podczas gdy bieżąca transakcja uruchamia operację odczytu. Udostępnione blokady blokują również instrukcję odczytu wierszy zmodyfikowanych przez inne transakcje do momentu zakończenia innej transakcji. Obie implementacje spełniają definicję ISO izolacji READ COMMITTED.
SNAPSHOT	Poziom izolacji migawki używa wersjonowania wierszy, aby zapewnić spójność odczytu na poziomie transakcji. Operacje odczytu nie uzyskują blokad stron ani blokad wierszy; uzyskiwane są jedynie blokady tabel związane z stabilnością schematuSch-S. Podczas odczytywania wierszy zmodyfikowanych przez inną transakcję operacje odczytu pobierają wersję wiersza, która istniała podczas uruchamiania transakcji. Izolację SNAPSHOT można używać tylko wtedy, gdy opcja bazy danych ALLOW_SNAPSHOT_ISOLATION jest ustawiona na wartość ON. Domyślnie ta opcja jest ustawiona na OFF dla baz danych użytkowników w programie SQL Server i usłudze Azure SQL Managed Instance oraz ustawiono wartość ON dla baz danych w usłudze Azure SQL Database. Uwaga: Aparat bazy danych nie obsługuje przechowywania wersji metadanych. Z tego powodu istnieją ograniczenia co do operacji DDL, które można przeprowadzać w jawnej transakcji działającej pod izolacją migawkową. Następujące instrukcje DDL nie są dozwolone w trybie izolacji migawkowej po instrukcji BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEMEani w żadnej instrukcji DDL uruchamianej przez środowisko CLR (Common Language Runtime). Instrukcje tego rodzaju są dozwolone w przypadku korzystania z izolacji migawki w niejawnych transakcjach. Zgodnie z definicją, niejawna transakcja to pojedyncza instrukcja umożliwiająca wymuszenie semantyki izolacji migawkowej, nawet przy użyciu instrukcji DDL. Naruszenia tej zasady mogą powodować błąd 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

W poniższej tabeli przedstawiono efekty uboczne współbieżności uaktywniane przez różne poziomy izolacji.

Poziom izolacji	Odczyt zanieczyszczony	Niepowtarzalny odczyt	Fantom
READ UNCOMMITTED	Tak	Tak	Tak
READ COMMITTED	Nie	Tak	Tak
REPEATABLE READ	Nie	Nie	Tak
SNAPSHOT	Nie	Nie	Nie
SERIALIZABLE	Nie	Nie	Nie

Aby uzyskać więcej informacji na temat określonych typów blokowania lub wersjonowania wierszy kontrolowanych przez każdy poziom izolacji transakcji, zobacz SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Poziomy izolacji transakcji można ustawić przy użyciu Transact-SQL lub interfejsu API bazy danych.

Transact-SQL
Transact-SQL skrypty używają instrukcji SET TRANSACTION ISOLATION LEVEL.

ADO
Aplikacje ADO ustawiają właściwość IsolationLevel obiektu Connection na adXactReadUncommitted, adXactReadCommitted, adXactRepeatableReadlub adXactReadSerializable.

ADO.NET
Aplikacje ADO.NET korzystające z zarządzanej przestrzeni nazw System.Data.SqlClient mogą wywoływać metodę SqlConnection.BeginTransaction, ustawiając opcję IsolationLevel na Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializablelub Snapshot.

OLE DB
Podczas inicjowania transakcji aplikacje korzystające z OLE DB wywołują ITransactionLocal::StartTransaction z isoLevel ustawionym na ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOTlub ISOLATIONLEVEL_SERIALIZABLE.

Podczas określania poziomu izolacji transakcji w trybie automatycznego zatwierdzania aplikacje OLE DB mogą ustawić DBPROPSET_SESSION właściwości DBPROP_SESS_AUTOCOMMITISOLEVELS na DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATEDlub DBPROPVAL_TI_SNAPSHOT.

ODBC
Aplikacje ODBC wywołują SQLSetConnectAttr, mając Attribute ustawione na SQL_ATTR_TXN_ISOLATION oraz ValuePtr ustawione na SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READlub SQL_TXN_SERIALIZABLE.

W przypadku transakcji migawek aplikacje wywołają SQLSetConnectAttr z atrybutem ustawionym na SQL_COPT_SS_TXN_ISOLATION i ValuePtr ustawionym na wartość SQL_TXN_SS_SNAPSHOT. Transakcję migawki można pobrać przy użyciu SQL_COPT_SS_TXN_ISOLATION lub SQL_ATTR_TXN_ISOLATION.

Blokowanie silnika bazy danych

Blokowanie jest mechanizmem używanym przez aparat bazy danych do synchronizowania dostępu wielu użytkowników z tym samym elementem danych w tym samym czasie.

Zanim transakcja uzyska zależność od bieżącego stanu danych, takich jak odczytywanie lub modyfikowanie danych, musi chronić się przed skutkami innej transakcji modyfikujące te same dane. Transakcja robi to, żądając blokady na fragmencie danych. Blokady mają różne tryby, takie jak współużytkowany (S) lub wyłączny (X). Tryb blokady definiuje poziom zależności transakcji na danych. Nie można przyznać żadnej transakcji blokady, która powoduje konflikt z trybem blokady już przyznanej na tych samych danych innej transakcji. Jeśli transakcja żąda trybu blokady, który powoduje konflikt z blokadą, która została już udzielona na tych samych danych, aparat bazy danych wstrzyma żądającą transakcję do momentu wydania pierwszej blokady.

Gdy transakcja modyfikuje fragment danych, przechowuje pewne blokady chroniące modyfikację do końca transakcji. Czas przechowywania blokad nabytych w celu zabezpieczenia operacji odczytu zależy od ustawienia poziomu izolacji transakcji i tego, czy funkcja zoptymalizowanego blokowania jest włączona.

• Gdy zoptymalizowane blokowanie nie jest włączone, blokady wierszy i stron niezbędne do zapisu są przechowywane do końca transakcji.

• Po włączeniu zoptymalizowanego blokowania trzymana jest tylko blokada identyfikatora transakcji (TID) do końca transakcji. W ramach domyślnego poziomu izolacji READ COMMITTED transakcje nie będą przechowywać blokad wierszy i stron niezbędnych do zapisu do końca transakcji. Zmniejsza to wymaganą pamięć blokady i zmniejsza potrzebę eskalacji blokady. Ponadto po włączeniu zoptymalizowanego blokowania blokada po kwalifikacjach (LAQ) optymalizacji ocenia predykaty zapytania w najnowszej zatwierdzonej wersji wiersza bez uzyskiwania blokady, poprawiając współbieżność.

Wszystkie blokady przechowywane przez transakcję są zwalniane po zakończeniu transakcji (zatwierdzenia lub wycofania).

Aplikacje zwykle nie żądają blokad bezpośrednio. Blokady są zarządzane wewnętrznie przez część modułu bazy danych nazywaną menedżerem blokad. Gdy instancja aparatu bazy danych przetwarza instrukcję Transact-SQL, procesor zapytań aparatu bazy danych określa, które zasoby mają być uzyskane. Procesor zapytań określa, jakie typy blokad są wymagane do ochrony każdego zasobu na podstawie typu dostępu i ustawienia poziomu izolacji transakcji. Następnie procesor zapytań żąda odpowiednich blokad u menedżera blokad. Menedżer blokad przyznaje blokady, jeśli nie ma żadnych konfliktowych blokad przechowywanych przez inne transakcje.

Blokowanie stopnia szczegółowości i hierarchii

Silnik bazy danych ma wielogranularne blokady, które umożliwiają zablokowanie przez transakcję różnych typów zasobów. Aby zminimalizować koszt blokowania, aparat bazy danych automatycznie blokuje zasoby na poziomie odpowiednim dla zadania. Blokowanie przy mniejszej szczegółowości, takiej jak wiersze, zwiększa współbieżność, ale ma większe obciążenie, ponieważ więcej blokad musi być przechowywanych, jeśli wiele wierszy jest zablokowanych. Blokowanie większego stopnia szczegółowości, takiego jak tabele, jest kosztowne pod względem współbieżności, ponieważ blokowanie całej tabeli ogranicza dostęp do dowolnej części tabeli przez inne transakcje. Jednak ma mniejsze obciążenie, ponieważ jest utrzymywana mniejsza liczba blokad.

Silnik bazy danych często musi uzyskać blokady na różnych poziomach, aby w pełni chronić zasób. Ta grupa blokad na wielu poziomach szczegółowości jest nazywana hierarchią blokady. Na przykład aby w pełni chronić odczyt indeksu, wystąpienie aparatu bazy danych może wymagać uzyskania blokad współdzielonych na wierszach i blokad współdzielonych intencji na stronach i tabeli.

W poniższej tabeli przedstawiono zasoby, które aparat bazy danych może zablokować.

Zasób	Opis
RID	Identyfikator wiersza używany do blokowania pojedynczego wiersza w stercie danych.
KEY	Blokada wiersza w celu zablokowania pojedynczego wiersza w indeksie drzewa B.
PAGE	Strona 8 kilobajtów (KB) w bazie danych, taka jak dane lub strony indeksu.
EXTENT	Ciągła grupa ośmiu stron, takich jak dane lub strony indeksu.
HoBT 1	Sterta lub drzewo B. Blokada chroniąca drzewo B (indeks) lub strony danych stert w tabeli, która nie ma indeksu klastrowanego.
TABLE 1	Cała tabela, w tym wszystkie dane i indeksy.
FILE	Plik bazy danych.
APPLICATION	Zasób określony przez aplikację.
METADATA	Blokady metadanych.
ALLOCATION_UNIT	Jednostka alokacji.
DATABASE	Cała baza danych.
XACT 2	Blokada identyfikatora transakcji (TID) używana w Zoptymalizowane blokowanie. Aby uzyskać więcej informacji, zobacz blokowanie identyfikatorów transakcji (TID) .

¹HoBT i TABLE zamki mogą być wpływane przez opcję LOCK_ESCALATIONALTER TABLE.

² Dodatkowe zasoby blokujące są dostępne dla zasobów blokady XACT, zobacz Dodatki diagnostyczne w celu zoptymalizowanego blokowania.

Tryby blokowania

Silnik bazy danych blokuje zasoby przy użyciu różnych trybów blokowania, które określają, jak zasoby mogą być używane przez współbieżne transakcje.

W poniższej tabeli przedstawiono tryby blokowania zasobów używane przez aparat bazy danych.

Tryb blokady	Opis
udostępnione (S)	Służy do operacji odczytu, które nie zmieniają ani nie aktualizują danych, takich jak instrukcja SELECT.
Aktualizacja (U)	Używane w zasobach, które można zaktualizować. Zapobiega typowej formie zakleszczenia, która występuje, gdy wiele sesji odczytuje, blokuje i potencjalnie aktualizuje zasoby później.
ekskluzywne (X)	Służy do operacji modyfikacji danych, takich jak INSERT, UPDATElub DELETE. Gwarantuje, że w tym samym czasie nie można wprowadzić wielu aktualizacji do tego samego zasobu.
intencja	Służy do ustanawiania hierarchii blokady. Typy blokad intencji to: intencja wspólna (IS), intencja wyłączna (IX) i wspólna z intencją wyłączną (SIX).
Schema	Używane, gdy operacja zależna od schematu tabeli jest wykonywana. Typy blokad schematu to: modyfikacja schematu (Sch-M) i stabilność schematu (Sch-S).
Aktualizacja zbiorcza (BU)	Używane podczas zbiorczego kopiowania danych do tabeli za pomocą wskazówki TABLOCK.
zakres kluczy	Chroni zakres wierszy odczytywanych przez zapytanie podczas korzystania z poziomu izolacji transakcji SERIALIZABLE. Zapewnia, że inne transakcje nie mogą wstawiać wierszy, które kwalifikowałyby się do zapytań transakcji SERIALIZABLE, gdyby zapytania te zostały wykonane ponownie.

Blokady dzielone

Udostępnione (S) blokady umożliwiają równoczesnym transakcjom odczytywanie zasobu w ramach pesymistycznej kontroli współbieżności. Żadne inne transakcje nie mogą modyfikować danych, gdy na zasobie istnieją współdzielone blokady (S). Udostępnione (S) blokady na zasobie są zwalniane natychmiast po zakończeniu operacji odczytu, chyba że poziom izolacji transakcji jest ustawiony na REPEATABLE READ lub wyżej, albo użyta jest wskazówka blokady w celu zachowania udostępnionych (S) blokad na czas trwania transakcji.

Blokady aktualizacji

Silnik bazy danych umieszcza blokady aktualizacji (U) podczas przygotowywania się do wykonania aktualizacji. U blokady są zgodne z blokadami S, ale tylko jedna transakcja może przechowywać blokadę U jednocześnie dla danego zasobu. Jest to kluczowe — wiele współbieżnych transakcji może trzymać blokady S, ale tylko jedna transakcja może utrzymywać blokadę U na zasobie. Blokady aktualizacji (U) z czasem zostają zamienione na blokady na wyłączność (X), aby zaktualizować wiersz.

Blokady aktualizacji (U) mogą być również wykonywane przez instrukcje inne niż UPDATE, gdy w instrukcji jest określona wskazówka tabeli UPDLOCK.

• Niektóre aplikacje używają wzorca "wybierz wiersz, a następnie zaktualizuj wiersz", w którym odczyt i zapis są jawnie oddzielone w ramach transakcji. W takim przypadku, jeśli poziom izolacji jest REPEATABLE READ lub SERIALIZABLE, współbieżne aktualizacje mogą spowodować zakleszczenie w następujący sposób:

  Transakcja odczytuje dane, uzyskuje dzieloną (S) blokadę zasobu, a następnie modyfikuje dane, co wymaga konwersji blokady na wyłączną (X) blokadę. Jeśli dwie transakcje uzyskają współużytkowane (S) blokady zasobu, a następnie spróbują zaktualizować dane jednocześnie, jedna z transakcji podejmie próbę przekształcenia blokady we wyłączną (X) blokadę. Przekształcenie blokady udostępnionej na blokadę wyłączności musi czekać, ponieważ blokada wyłączności (X) dla jednej transakcji nie jest zgodna z blokadą udostępnioną (S) innej transakcji; występuje czekanie na blokadę. Druga transakcja próbuje uzyskać wyłączną blokadę (X) dla jej aktualizacji. Ponieważ obie transakcje przekształcają się w blokady na wyłączność (X) i każda z nich czeka, aż druga transakcja zwolni swoją współdzieloną blokadę (S), występuje zakleszczenie.

  W domyślnym READ COMMITTED poziomie izolacji S blokady trwają krótko, zwalniane natychmiast po ich użyciu. Chociaż zakleszczenie opisane powyżej jest nadal możliwe, jest znacznie mniej prawdopodobne przy blokadach o krótkim czasie trwania.

  Aby uniknąć tego typu zakleszczenia, aplikacje mogą postępować zgodnie z wzorcem "wybierz wiersz ze wskazówką UPDLOCK, a następnie zaktualizuj wiersz".

• Jeśli wskazówka UPDLOCK jest używana w zapisie, gdy izolacja SNAPSHOT jest używana, transakcja musi mieć dostęp do najnowszej wersji wiersza. Jeśli najnowsza wersja nie jest już widoczna, można odbierać Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict. Aby zapoznać się z przykładem, zobacz Work with snapshot isolation (Praca z izolacją migawki).

Zamki wyłącznościowe

Blokady na wyłączność (X) uniemożliwiają dostęp do zasobu za pomocą transakcji współbieżnych. Z wyłączną blokadą (X) żadne inne transakcje nie mogą modyfikować danych chronionych przez blokadę; Operacje odczytu mogą odbywać się tylko przy użyciu wskazówki NOLOCK lub poziomu izolacji READ UNCOMMITTED.

Instrukcje modyfikacji danych, takie jak INSERT, UPDATEi DELETE łączą operacje odczytu i modyfikacji. Instrukcja najpierw wykonuje operacje odczytu w celu uzyskania danych przed wykonaniem wymaganych operacji modyfikacji. W związku z tym instrukcje modyfikacji danych zwykle żądają blokad udostępnionych i blokad wyłącznych. Na przykład instrukcja UPDATE może modyfikować wiersze w jednej tabeli na podstawie sprzężenia z inną tabelą. W tym przypadku instrukcja UPDATE żąda udostępnionych blokad w wierszach odczytanych w tabeli sprzężeń oprócz żądania blokad wyłącznych w zaktualizowanych wierszach.

Blokady intencji

Silnik bazy danych używa blokad intencji w celu ochrony umieszczenia blokady współdzielonej (S) lub blokady wyłącznej (X) na zasobie na niższym poziomie hierarchii blokady. Blokady intencji mają nazwę "blokady intencji", ponieważ są uzyskiwane przed blokadą na niższym poziomie i sygnalizują zamiar umieszczenia blokad na niższym poziomie.

Blokady intencji służą dwóm celom:

• Aby uniemożliwić innym transakcjom modyfikowanie zasobu wyższego poziomu w sposób, który unieważniłby blokadę na niższym poziomie.
• Aby zwiększyć efektywność silnika bazy danych w wykrywaniu konfliktów blokad na wyższym poziomie szczegółowości.

Na przykład blokada intencji współdzielonej jest żądana na poziomie tabeli, zanim blokady wspólne (S) zostaną zażądane na stronach lub wierszach w tej tabeli. Ustawienie blokady intencji na poziomie tabeli uniemożliwia kolejnemu transakcji późniejsze uzyskanie wyłącznej blokady (X) w tabeli zawierającej tę stronę. Blokady intencji zwiększają wydajność, ponieważ aparat bazy danych analizuje blokady intencji tylko na poziomie tabeli, aby określić, czy transakcja może bezpiecznie uzyskać blokadę w tej tabeli. Spowoduje to usunięcie wymogu zbadania każdego wiersza lub blokady strony w tabeli w celu określenia, czy transakcja może zablokować całą tabelę.

blokady intencji obejmują blokadę dzielonej intencji (IS), blokadę wyłącznej intencji (IX) i dzielone z wyłączną intencją (SIX).

Tryb blokady	Opis
Intencja udostępniona (IS)	Zabezpiecza żądane lub pozyskane blokady udostępnione dla niektórych (ale nie wszystkich) zasobów na niższych poziomach hierarchii.
Intent exclusive (IX)	Chroni żądane lub pozyskane blokady na wyłączność w niektórych (ale nie wszystkich) zasobach niższych w hierarchii. IX jest nadzbiorem IS, a także chroni żądania udostępnionych blokad na zasobach niższego poziomu.
Udostępnione z zamiarem wyłączności (SIX)	Chroni żądane lub pozyskane blokady współdzielone na wszystkich zasobach niższych w hierarchii oraz intencjonalne blokady wyłączne na niektórych (ale nie wszystkich) zasobach niższego poziomu. Na najwyższym poziomie zasobów dozwolone są współbieżne blokady IS. Na przykład uzyskanie blokady SIX w tabeli uzyskuje również blokady intencjonalne wyłączności na stronach, które są modyfikowane, oraz blokady wyłączające na zmodyfikowanych wierszach. Jednocześnie na dany zasób może istnieć tylko jedna blokada SIX, co uniemożliwia innym transakcjom jego aktualizację. Jednak inne transakcje mogą odczytywać zasoby niższe w hierarchii, uzyskując blokady IS na poziomie tabeli.
aktualizacja intencji (IU)	Chroni żądane lub pozyskane blokady aktualizacji dla wszystkich zasobów niższych w hierarchii. blokady IU są używane tylko w zasobach strony internetowej. IU blokady są konwertowane na blokady IX w przypadku przeprowadzenia operacji aktualizacji.
aktualizacja intencji współdzielonej (SIU)	Połączenie blokad S i IU jest rezultatem uzyskania tych blokad oddzielnie i jednoczesnego trzymania obydwóch. Na przykład transakcja wykonuje zapytanie z wskazówką PAGLOCK, a następnie wykonuje operację aktualizacji. Zapytanie ze wskazówką PAGLOCK uzyskuje blokadę S, a operacja aktualizacji uzyskuje blokadę IU.
Zamiar aktualizacji wyłączny (UIX)	Połączenie blokad U i IX jest rezultatem uzyskania tych blokad oddzielnie i jednoczesnego trzymania obydwóch.

Blokady schematu

Silnik bazy danych używa blokad modyfikacji schematu (Sch-M) podczas operacji języka definicji danych tabeli (DDL), takich jak dodanie kolumny lub usunięcie tabeli. W czasie, gdy jest on przechowywany, blokada Sch-M uniemożliwia współbieżny dostęp do tabeli. Oznacza to, że blokada Sch-M blokuje wszystkie operacje zewnętrzne do momentu zwolnienia blokady.

Niektóre operacje języka manipulowania danymi (DML), takie jak truncowanie tabeli, używają Sch-M blokad, aby zapobiec dostępowi do tabel objętych operacjami współbieżnymi.

Silnik bazy danych używa blokad stabilności schematu (Sch-S) podczas kompilowania i wykonywania zapytań. Sch-S zamki nie blokują żadnych blokad transakcyjnych, w tym blokad wyłącznych (X). W związku z tym inne transakcje, w tym z blokadami X na tabeli, nadal działają podczas kompilowania zapytania. Jednak współbieżne operacje DDL i współbieżne operacje DML, które uzyskują blokady Sch-M, są blokowane przez blokady Sch-S.

Blokady aktualizacji zbiorczej

Blokady zbiorczych aktualizacji (BU) umożliwiają wielu wątkom współbieżne ładowanie danych do tej samej tabeli, uniemożliwiając jednocześnie innym procesom, które nie ładują zbiorczo danych, dostęp do tabeli. Silnik bazy danych używa blokad aktualizacji zbiorczej (BU), gdy oba poniższe warunki są spełnione.

• Używasz instrukcji Transact-SQL BULK INSERT lub funkcji OPENROWSET(BULK) lub jednego z poleceń interfejsu API wstawiania zbiorczego, takich jak .NET SqlBulkCopy, interfejsy API szybkiego ładowania OLEDB lub interfejsy API kopiowania zbiorczego ODBC, aby zbiorczo skopiować dane do tabeli.
• Określono wskazówkę TABLOCK lub ustawiono opcję tabeli table lock on bulk load przy użyciu sp_tableoption.

Napiwek

W przeciwieństwie do instrukcji BULK INSERT, która utrzymuje mniej restrykcyjną blokadę aktualizacji zbiorczej (BU), INSERT INTO...SELECT ze wskazówką TABLOCK utrzymuje blokadę zamierzonej wyłączności (IX) na tabeli. Oznacza to, że nie można wstawić wierszy przy użyciu operacji wstawiania równoległego.

Blokady zakresu kluczy

Blokady zakresu kluczy chronią zakres wierszy niejawnie uwzględnionych w zestawie rekordów odczytywanych przez instrukcję Transact-SQL podczas korzystania z poziomu izolacji transakcji SERIALIZABLE. Blokowanie zakresu kluczy uniemożliwia odczyty phantom. Dzięki ochronie zakresów kluczy między wierszami zapobiega również wstawianiu lub usuwaniu zjaw w zestawie rekordów, do których uzyskuje dostęp transakcja.

Zablokuj zgodność

Zgodność blokad określa, czy wiele transakcji może jednocześnie uzyskiwać blokady na tym samym zasobie. Jeśli zasób jest już zablokowany przez inną transakcję, można udzielić nowego żądania blokady tylko wtedy, gdy tryb żądanej blokady jest zgodny z trybem istniejącej blokady. Jeśli tryb żądanej blokady nie jest zgodny z istniejącą blokadą, transakcja żądająca nowej blokady czeka na zwolnienie istniejącej blokady lub wygaśnięcie czasu oczekiwania na blokadę. Na przykład żadne tryby blokady nie są zgodne z blokadami wyłącznymi. Gdy blokada wyłączna (X) jest utrzymywana, żadna inna transakcja nie może uzyskać blokady żadnego rodzaju (udostępnionej, aktualizacyjnej lub wyłącznej) na tym zasobie, dopóki blokada wyłączna (X) nie zostanie zwolniona. Z drugiej strony, jeśli blokada współdzielona (S) została zastosowana do zasobu, inne transakcje mogą również uzyskać blokadę współdzieloną lub blokadę aktualizacyjną (U) na ten zasób, nawet jeśli pierwsza transakcja nie została ukończona. Jednak inne transakcje nie mogą uzyskać wyłącznej blokady, dopóki blokada współdzielona nie zostanie zwolniona.

Poniższa tabela przedstawia zgodność najczęściej spotykanych trybów blokowania.

Istniejący tryb udzielony	IS	S	U	IX	SIX	X
żądany tryb
Intencja udostępniona (IS)	Tak	Tak	Tak	Tak	Tak	Nie
udostępnione (S)	Tak	Tak	Tak	Nie	Nie	Nie
Aktualizacja (U)	Tak	Tak	Nie	Nie	Nie	Nie
Intent exclusive (IX)	Tak	Nie	Nie	Tak	Nie	Nie
Udostępnione z zamiarem wyłączności (SIX)	Tak	Nie	Nie	Nie	Nie	Nie
ekskluzywne (X)	Nie	Nie	Nie	Nie	Nie	Nie

Notatka

Blokada zamiaru ekskluzywnego (IX) jest zgodna z trybem blokady IX, ponieważ IX oznacza, że zamiarem jest zaktualizowanie tylko niektórych wierszy, a nie wszystkich. Inne transakcje, które próbują odczytać lub zaktualizować niektóre wiersze, są również dozwolone, o ile nie są to same wiersze aktualizowane przez inne transakcje. Ponadto, jeśli dwie transakcje próbują zaktualizować ten sam wiersz, obie transakcje otrzymują blokadę IX na poziomie tabeli i strony. Jednak jedna transakcja otrzymuje blokadę X na poziomie wiersza. Druga transakcja musi czekać, aż zostanie usunięta blokada na poziomie wiersza.

Użyj poniższej tabeli, aby określić zgodność wszystkich trybów blokady dostępnych w silniku bazy danych.

Klucz	Opis
N	Brak konfliktu
Ja	Nielegalny
C	Konflikt
NL	Brak blokady
SCH-S	Blokada stabilności schematu
SCH-M	Blokada modyfikacji schematu
S	Udostępnione
U	Aktualizacja
X	Wyłączny
JEST	Zamiar udostępniony
J.M.	Aktualizacja intencji
IX	Wyłączność intencji
SIU	Udostępnij z aktualizacją intencji
SZEŚĆ	Udostępnianie z intencją na wyłączność
UIX	Aktualizacja z wyłącznym zamiarem
BU	Aktualizacja zbiorcza
RS-S	Współużytkowany zakres udostępniony
RS-U	Aktualizacja zakresu udostępnionego
RI-N	Wstaw zakres pusty
RI-S	Wstaw współużytkowany zakres
RI-U	Wstaw aktualizację zakresu
RI-X	Wstaw wyłączność zakresu
RX-S	Wyłączny zakres współdzielony
RX-U	Ekskluzywna aktualizacja zakresu
RX-X	Wyłączny zakres wyłączny

Blokowanie zakresu kluczy

Blokady zakresu kluczy chronią zakres wierszy niejawnie uwzględnionych w zestawie rekordów odczytywanych przez instrukcję Transact-SQL podczas korzystania z poziomu izolacji transakcji SERIALIZABLE. Poziom izolacji SERIALIZABLE wymaga, aby każde zapytanie wykonywane podczas transakcji musiało uzyskać ten sam zestaw wierszy za każdym razem, gdy jest wykonywany podczas transakcji. Blokada zakresu kluczy spełnia to wymaganie, uniemożliwiając innym transakcjom wstawianie nowych wierszy, których klucze mieszczą się w zakresie kluczy odczytanych przez transakcję SERIALIZABLE.

Blokowanie zakresu kluczy uniemożliwia odczyty phantom. Dzięki ochronie zakresów kluczy między wierszami uniemożliwia również wstawienia widmowe do zestawu rekordów, do których uzyskuje dostęp transakcja.

W indeksie umieszczana jest blokada zakresu kluczy, która określa początkową i końcową wartość klucza. Ta blokada blokuje wszelkie próby wstawiania, aktualizowania lub usuwania dowolnego wiersza z wartością klucza, która mieści się w zakresie, ponieważ te operacje musiałyby najpierw uzyskać blokadę indeksu. Na przykład transakcja SERIALIZABLE może wydać instrukcję SELECT, która odczytuje wszystkie wiersze, których wartości klucza pasują do warunku BETWEEN 'AAA' AND 'CZZ'. Blokada zakresu kluczy dla wartości kluczy w zakresie od 'AAA' do 'CZZ' uniemożliwia innym transakcjom wstawianie wierszy z wartościami klucza w dowolnym miejscu w tym zakresie, takich jak 'ADG', 'BBD', lub 'CAL'.

Tryby blokady zakresu kluczy

Blokady zakresu kluczy obejmują zarówno zakres, jak i składnik wiersza określony w formacie wiersz-zakres.

• Zakres reprezentuje tryb blokady chroniący zakres między dwoma kolejnymi wpisami indeksu.
• Wiersz reprezentuje tryb blokady, który chroni wpis indeksowy.
• Tryb reprezentuje używany tryb połączonej blokady. Tryby blokady zakresu kluczy składają się z dwóch części. Pierwszy reprezentuje typ blokady używany do blokowania zakresu indeksów (zakresT), a drugi reprezentuje typ blokady używany do blokowania określonego klucza (K). Dwie części są połączone łącznikiem (-), takim jak RangeT-K.

Zakres	Szereg	Tryb	Opis
RangeS	S	RangeS-S	Zakres udostępniony, blokada współużytkowanego zasobu; SERIALIZABLE skanowanie zakresu.
RangeS	U	RangeS-U	Zakres udostępniony, zaktualizuj blokadę zasobów; SERIALIZABLE aktualizacja skanu.
RangeI	Null	RangeI-N	Wstaw zakres, blokada zasobu o wartości null; używana do testowania zakresów przed wstawieniem nowego klucza do indeksu.
RangeX	X	RangeX-X	Wyłączny zakres, wyłączna blokada zasobów; używany podczas aktualizowania klucza w zakresie.

Notatka

Wewnętrzny tryb blokady Null jest zgodny ze wszystkimi innymi trybami blokady.

Tryby blokady zakresu kluczy mają macierz zgodności, która pokazuje, które blokady są zgodne z innymi blokadami uzyskanymi na nakładających się kluczach i zakresach.

Istniejący tryb udzielony	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
żądany tryb
udostępnione (S)	Tak	Tak	Nie	Tak	Tak	Tak	Nie
Aktualizacja (U)	Tak	Nie	Nie	Tak	Nie	Tak	Nie
ekskluzywne (X)	Nie	Nie	Nie	Nie	Nie	Tak	Nie
RangeS-S	Tak	Tak	Nie	Tak	Tak	Nie	Nie
RangeS-U	Tak	Nie	Nie	Tak	Nie	Nie	Nie
RangeI-N	Tak	Tak	Tak	Nie	Nie	Tak	Nie
RangeX-X	Nie	Nie	Nie	Nie	Nie	Nie	Nie

Blokady konwersji

Blokady konwersji są tworzone, gdy blokada zakresu kluczy nakłada się na kolejną blokadę.

Blokada 1	Zamek 2	Blokada konwersji
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

Blokady konwersji można zaobserwować przez krótki czas w różnych złożonych okolicznościach, czasami podczas uruchamiania procesów współbieżnych.

Seryjne skanowanie zakresu, jednoelementowe pobieranie, usuwanie i wstawianie

Blokowanie zakresu kluczy gwarantuje, że następujące operacje można serializować:

• Zapytanie zakresowego skanowania
• Jednotonowe pobieranie nieistniejących wierszy
• Operacja usuwania
• Operacja wstawiania

Przed wystąpieniem blokady zakresu kluczy należy spełnić następujące warunki:

• Poziom izolacji transakcji musi być ustawiony na wartość SERIALIZABLE.
• Procesor zapytań musi używać indeksu, aby zaimplementować predykat filtru zakresu. Na przykład klauzula WHERE w zdaniu SELECT może ustanowić warunek zakresu z tym predykatem: ColumnX BETWEEN N'AAA' AND N'CZZ'. Blokadę zakresu kluczy można uzyskać tylko wtedy, gdy ColumnX jest objęta kluczem indeksu.

Przykłady

Poniższa tabela i indeks są używane jako podstawa przykładów blokowania zakresu kluczy, które następują.

Zapytanie zakresowego skanowania

Aby upewnić się, że zapytanie skanowania zakresu jest serializowalne, to samo zapytanie powinno zwrócić te same wyniki za każdym razem, gdy jest wykonywane w ramach tej samej transakcji. Nowe wiersze nie mogą być wstawione w zapytaniu skanowania zakresu przez inne transakcje; w przeciwnym razie stają się to wstawkami phantom. Na przykład poniższe zapytanie używa tabeli i indeksu na poprzedniej ilustracji:

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

Blokady zakresu kluczy są umieszczane w wpisach indeksu odpowiadających zakresowi wierszy, w których nazwa znajduje się między wartościami Adam i Dale, co uniemożliwia dodanie lub usunięcie nowych wierszy kwalifikujących się w poprzednim zapytaniu. Mimo że pierwszą nazwą w tym zakresie jest Adam, blokada zakresu kluczy trybu RangeS-S dla tego wpisu indeksu gwarantuje, że nie można dodać nowych nazw rozpoczynających się od litery A przed Adam, takich jak Abigail. Podobnie blokada zakresu kluczy RangeS-S we wpisie indeksu dla Dale gwarantuje, że po Cnie można dodać nowych nazw rozpoczynających się od litery Carlos, takich jak Clive.

Notatka

Liczba blokad RangeS-S posiadanych to n+1, gdzie n to liczba wierszy spełniających zapytanie.

Singletonowe pobieranie nieistniejących danych

Jeśli zapytanie w ramach transakcji próbuje wybrać wiersz, który nie istnieje, wysłanie zapytania w późniejszym punkcie w ramach tej samej transakcji musi zwrócić ten sam wynik. Żadna inna transakcja nie może wstawić tego nieistniejącego wiersza. Na przykład biorąc pod uwagę to zapytanie:

SELECT name
FROM mytable
WHERE name = 'Bill';

Blokada zakresu kluczy jest umieszczana we wpisie indeksu odpowiadającym zakresowi nazw od Ben do Bing, ponieważ nazwa Bill zostanie wstawiona między tymi dwoma sąsiednimi wpisami indeksu. Na wpisie indeksu RangeS-Sumieszczona jest blokada zakresu kluczy trybu Bing. Zapobiega to sytuacji, w której żadna inna transakcja nie może wstawić wartości, takich jak Bill, między wpisami indeksu Ben a Bing.

Operacja usuwania bez zoptymalizowanego blokowania

Podczas usuwania wiersza w ramach transakcji zakres, w którym znajduje się wiersz, nie musi być zablokowany przez cały czas trwania operacji usuwania w ramach transakcji. Zablokowanie usuniętej wartości klucza do końca transakcji jest wystarczające, aby zachować możliwość serializacji. Na przykład biorąc pod uwagę tę instrukcję DELETE:

DELETE mytable
WHERE name = 'Bob';

Blokada wyłączności (X) jest umieszczana we wpisie indeksu odpowiadającym nazwie Bob. Inne transakcje mogą wstawiać lub usuwać wartości przed lub po wierszu z wartością Bob, która jest usuwana. Jednak każda transakcja, która próbuje odczytywać, wstawiać lub usuwać wiersze pasujące do wartości Bob, zostanie zablokowana do momentu, gdy transakcja usuwająca albo zostanie zatwierdzona, albo cofnięta. (Opcja bazy danych READ_COMMITTED_SNAPSHOT i poziom izolacji SNAPSHOT umożliwiają także odczyty z wersji wcześniej zatwierdzonego wiersza stanu).

Usuwanie zakresu można wykonać przy użyciu trzech podstawowych trybów blokowania: wiersza, strony lub blokady tabeli. Strategia blokowania wierszy, strony lub tabeli jest określana przez optymalizator zapytań lub może być określona przez użytkownika za pomocą wskazówek optymalizatora zapytań, takich jak ROWLOCK, PAGLOCKlub TABLOCK. Gdy jest używana PAGLOCK lub TABLOCK, aparat bazy danych natychmiast cofa przydział strony indeksu, jeśli wszystkie wiersze zostaną usunięte z tej strony. Natomiast w przypadku użycia ROWLOCK wszystkie usunięte wiersze są oznaczone jako usunięte; później zostaną one usunięte ze strony indeksu przy użyciu zadania w tle.

Operacja usuwania z zoptymalizowanym blokowaniem

Podczas usuwania wiersza w ramach transakcji blokady wierszy i stron są uzyskiwane i zwalniane przyrostowo, a nie przechowywane przez czas trwania transakcji. Na przykład, biorąc pod uwagę następującą instrukcję DELETE:

DELETE mytable
WHERE name = 'Bob';

Blokada TID jest umieszczana we wszystkich zmodyfikowanych wierszach przez czas trwania transakcji. Blokada jest pobierana na TID odpowiadający wierszom indeksu związanym z wartością Bob. W przypadku zoptymalizowanego blokowania blokady stron i wierszy są nadal uzyskiwane dla aktualizacji, ale każda blokada strony i wiersza jest zwalniana natychmiast po tym, jak każdy wiersz zostanie zaktualizowany. Blokada TID chroni wiersze przed zaktualizowaniem do momentu zakończenia transakcji. Każda transakcja, która próbuje odczytywać, wstawiać lub usuwać wiersze z wartością Bob, jest blokowana do momentu, gdy transakcja usuwająca albo zostanie zatwierdzona, albo wycofana. (Opcja bazy danych READ_COMMITTED_SNAPSHOT i poziom izolacji SNAPSHOT umożliwiają także odczyty z wersji wcześniej zatwierdzonego wiersza stanu).

W przeciwnym razie mechanika blokowania operacji usuwania jest taka sama jak bez zoptymalizowanego blokowania.

Operacja wstawiania bez zoptymalizowanego blokowania

Podczas wstawiania wiersza w ramach transakcji zakres, do który wchodzi wiersz, nie musi być zablokowany przez czas trwania transakcji wykonującej operację wstawiania. Zablokowanie wstawionej wartości klucza do końca transakcji jest wystarczające, aby zachować możliwość serializacji. Na przykład, biorąc pod uwagę następującą instrukcję INSERT:

INSERT mytable VALUES ('Dan');

Blokada zakresu kluczy trybu RangeI-N jest umieszczana w wierszu indeksu odpowiadającym nazwie David do testowania zakresu. Jeśli blokada zostanie udzielona, zostanie wstawiony wiersz z wartością Dan, a na wstawionym wierszu zostanie umieszczona blokada na wyłączność (X). Blokada zakresu kluczy trybu RangeI-N jest niezbędna tylko do testowania zakresu i nie jest przechowywana przez czas trwania transakcji wykonującej operację wstawiania. Inne transakcje mogą wstawiać lub usuwać wartości przed lub po wstawionym wierszu z wartością Dan. Jednak każda transakcja próbująca odczytać, wstawić lub usunąć wiersz z wartością Dan zostanie zablokowana, dopóki transakcja wstawiająca nie zostanie zatwierdzona lub cofnięta.

Operacja wstawiania z zoptymalizowanym blokowaniem

Podczas wstawiania wiersza w ramach transakcji zakres, do który wchodzi wiersz, nie musi być zablokowany przez czas trwania transakcji wykonującej operację wstawiania. Blokady wierszy i stron są rzadko uzyskiwane, tylko wtedy, gdy trwa ponowne kompilowanie indeksu online lub gdy istnieją równoczesne transakcje SERIALIZABLE. Jeśli zastosowano blokady wierszy i stron, są one szybko zwalniane i nie są przetrzymywane przez czas trwania transakcji. Umieszczenie wyłącznej blokady TID na wstawionej wartości klucza do końca transakcji jest wystarczające, aby zachować serializację. Na przykład biorąc pod uwagę tę instrukcję INSERT:

INSERT mytable VALUES ('Dan');

W przypadku zoptymalizowanego blokowania blokada RangeI-N jest uzyskiwana tylko wtedy, gdy w wystąpieniu jest używana co najmniej jedna transakcja korzystająca z poziomu izolacji SERIALIZABLE. Blokada zakresu kluczy trybu RangeI-N jest umieszczana w wierszu indeksu odpowiadającym nazwie David do testowania zakresu. Jeśli blokada zostanie udzielona, zostanie wstawiony wiersz z wartością Dan, a na wstawionym wierszu zostanie umieszczona blokada na wyłączność (X). Blokada zakresu kluczy trybu RangeI-N jest niezbędna tylko do testowania zakresu i nie jest przechowywana przez czas trwania transakcji wykonującej operację wstawiania. Inne transakcje mogą wstawiać lub usuwać wartości przed lub po wstawionym wierszu z wartością Dan. Jednak każda transakcja próbująca odczytać, wstawić lub usunąć wiersz z wartością Dan zostanie zablokowana, dopóki transakcja wstawiająca nie zostanie zatwierdzona lub cofnięta.

Eskalacja blokad

Eskalacja blokady to proces przekształcania wielu precyzyjnych blokad na mniej gruboziarnistych, zmniejszając obciążenie systemu przy jednoczesnym zwiększaniu prawdopodobieństwa współbieżnego konfliktu.

Eskalacja blokady działa inaczej w zależności od tego, czy zoptymalizowane blokowanie jest włączone.

Eskalacja blokowania bez optymalizacji blokad

Silnik bazy danych, uzyskując blokady niskiego poziomu, umieszcza również blokady intencji na obiektach, które zawierają te obiekty.

• Podczas blokowania wierszy lub zakresów kluczy indeksu silnik bazy danych umieszcza blokadę intencji na stronach zawierających wiersze lub klucze.
• Podczas blokowania stron aparat bazy danych umieszcza blokadę intencji na obiektach wyższego poziomu, które zawierają strony. Oprócz blokady intencji na obiekcie, blokady strony z intencją są wymagane dla następujących obiektów:
  • Strony na poziomie liścia nieklastrowanych indeksów
  • Strony danych klastrowanych indeksów
  • Strony danych stert

Silnik bazy danych może stosować zarówno blokowanie na poziomie wiersza, jak i strony dla tej samej instrukcji, aby zminimalizować liczbę blokad i zmniejszyć prawdopodobieństwo konieczności eskalacji blokad. Na przykład, silnik bazy danych może nałożyć blokady stron na indeks nieklastrowany (jeśli w węźle indeksu wybrano wystarczającą liczbę sąsiadujących kluczy, aby spełnić zapytanie) oraz blokady wierszy na indeks klastrowany lub stertę.

Aby zwiększyć poziom blokad, aparat bazy danych próbuje zmienić blokadę zamiarową w tabeli na odpowiadającą pełną blokadę, na przykład zmieniając blokadę zamiaru na wyłączność (IX) na blokadę wyłączną (X) lub blokadę zamiaru współdzielonego (IS) na blokadę współdzieloną (S). Jeśli próba eskalacji blokady powiedzie się i zostanie uzyskana pełna blokada tabeli, wówczas wszystkie blokady HoBT, strony (PAGE) lub na poziomie wiersza (RID, KEY), które transakcja posiada na stercie lub indeksie, zostaną zwolnione. Jeśli nie można uzyskać pełnej blokady, w tym czasie nie nastąpi eskalacja blokady, a aparat bazy danych będzie nadal uzyskiwać blokady wiersza, klucza lub strony.

Silnik bazy danych nie eskaluje blokad wierszy ani zakresów kluczy do blokad stron, lecz eskaluje je bezpośrednio do blokad tabeli. Podobnie blokady stron są zawsze eskalowane do blokad tabeli. Blokowanie tabel partycjonowanych może eskalować do poziomu HoBT dla skojarzonej partycji zamiast do blokady tabeli. Blokada na poziomie HoBT niekoniecznie blokuje wyrównane HoBTy dla partycji.

Notatka

Blokady na poziomie HoBT zwykle zwiększają współbieżność, ale wprowadzają możliwość zakleszczenia, gdy transakcje, które blokują różne partycje, każda z nich chce rozszerzyć swoje wyłączne blokady na inne partycje. W rzadkich przypadkach granulacja blokowania TABLE może działać lepiej.

Jeśli próba eskalacji blokady zakończy się niepowodzeniem z powodu konfliktów blokad przechowywanych przez równoczesne transakcje, silnik bazy danych ponawia próbę eskalacji blokady dla każdych kolejnych 1250 blokad nabytych przez transakcję.

Każde zdarzenie eskalacji działa głównie na poziomie pojedynczej instrukcji Transact-SQL. Gdy zdarzenie się rozpoczyna, silnik bazy danych próbuje eskalować wszystkie blokady należące do bieżącej transakcji w którejkolwiek z tabel, do których odnosi się aktywna instrukcja, pod warunkiem że spełnia wymagania dotyczące progu eskalacji. Jeśli zdarzenie eskalacji rozpoczyna się przed uzyskaniem dostępu do tabeli przez instrukcję, nie zostanie podjęta próba eskalacji blokad w tej tabeli. Jeśli eskalacja blokady powiedzie się, wszelkie blokady uzyskane przez transakcję w poprzedniej instrukcji i nadal przechowywane w momencie rozpoczęcia zdarzenia są eskalowane, jeśli tabela jest przywoływane przez bieżącą instrukcję i jest uwzględniona w zdarzeniu eskalacji.

Załóżmy na przykład, że sesja wykonuje następujące operacje:

• Rozpoczyna transakcję.
• Aktualizacje TableA. Spowoduje to wygenerowanie wyłącznych blokad wierszy w TableA, które są przechowywane do momentu zakończenia transakcji.
• Aktualizacje TableB. Spowoduje to wygenerowanie wyłącznych blokad wierszy w TableB, które są przechowywane do momentu zakończenia transakcji.
• Wykonuje operację SELECT, która łączy TableA z TableC. Plan wykonywania zapytania zakłada pobranie wierszy z TableA przed pobraniem ich z TableC.
• Instrukcja SELECT powoduje eskalację blokady podczas pobierania wierszy z TableA, zanim uzyska dostęp do TableC.

Jeśli eskalacja blokady powiedzie się, tylko blokady przechowywane przez sesję w TableA są eskalowane. Obejmuje to zarówno blokady współdzielone z instrukcji SELECT, jak i blokady wyłączne z poprzedniej instrukcji UPDATE. Podczas gdy tylko blokady sesji nabyte w TableA dla instrukcji SELECT są liczone w celu określenia, czy należy przeprowadzić eskalację blokady, po pomyślnym przeprowadzeniu eskalacji wszystkie blokady przechowywane przez sesję w TableA są zamieniane na wyłączną blokadę na tabeli, a wszystkie inne blokady o niższym poziomie szczegółowości, w tym blokady intencji, na TableA zostają zwolnione.

Nie podjęto próby eskalacji blokad na TableB, ponieważ nie było aktywnego odwołania do TableB w instrukcji SELECT. Podobnie nie podjęto próby eskalacji blokad na TableC, które nie są eskalowane, ponieważ nie były jeszcze dostępne podczas eskalacji.

Eskalacja blokad przy użyciu zoptymalizowanego blokowania

Zoptymalizowane blokowanie pomaga zmniejszyć ilość pamięci blokady, ponieważ przez cały czas trwania transakcji są przechowywane bardzo mało blokad. Gdy silnik bazy danych uzyskuje blokady wierszy i stron, eskalacja blokady może występować w podobny sposób, ale znacznie rzadziej. Zoptymalizowane blokowanie zwykle kończy się powodzeniem w unikaniu eskalacji blokady, obniżając liczbę blokad i wymaganą ilość pamięci blokady.

Po włączeniu zoptymalizowanego blokowania i w domyślnym poziomie izolacji READ COMMITTED, aparat bazy danych zwalnia blokady wiersza i strony natychmiast po zmodyfikowaniu wiersza. Żadne blokady wierszy i stron nie są przechowywane przez czas trwania transakcji, z wyjątkiem pojedynczej blokady identyfikatora transakcji (TID). Zmniejsza to prawdopodobieństwo eskalacji blokady.

Progi blokady eskalacji

Eskalacja blokady jest wyzwalana, gdy eskalacja blokady nie jest wyłączona w tabeli przy użyciu opcji ALTER TABLE SET LOCK_ESCALATION i gdy istnieje jeden z następujących warunków:

• Pojedyncza instrukcja typu Transact-SQL uzyskuje co najmniej 5000 blokad w jednej niepartycyjnej tabeli lub indeksie.
• Pojedyncza instrukcja Transact-SQL uzyskuje co najmniej 5000 blokad w jednej partycji tabeli partycjonowanej, a opcja ALTER TABLE SET LOCK_ESCALATION jest ustawiona na wartość AUTO.
• Liczba blokad w wystąpieniu silnika bazy danych przekracza progi pamięci lub konfiguracji.

Jeśli nie można eskalować blokad z powodu konfliktów blokad, silnik bazy danych okresowo przeprowadza eskalację blokady przy każdych 1,250 nowo pozyskanych blokadach.

Próg eskalacji dla instrukcji Transact-SQL

Gdy silnik bazy danych sprawdza możliwe eskalacje co 1250 nowo uzyskanych blokad, eskalacja blokady nastąpi wtedy i tylko wtedy, gdy instrukcja Transact-SQL uzyskała co najmniej 5000 blokad w jednym odwołaniu do tabeli. Eskalacja blokady jest wyzwalana, gdy instrukcja Transact-SQL uzyskuje co najmniej 5000 blokad w jednym odwołaniu do tabeli. Na przykład eskalacja blokady nie jest uruchamiana, jeśli instrukcja uzyskuje 3000 blokad w jednym indeksie i 3000 blokad w innym indeksie tej samej tabeli. Podobnie eskalacja blokady nie jest wyzwalana, jeśli instrukcja ma sprzężenie samodzielne w tabeli, a każde odwołanie do tabeli uzyskuje tylko 3000 blokad w tabeli.

Eskalacja blokady występuje tylko w przypadku tabel, do których uzyskiwano dostęp w momencie wyzwolenia eskalacji. Załóżmy, że pojedyncza instrukcja SELECT to sprzężenie, które uzyskuje dostęp do trzech tabel w tej sekwencji: TableA, TableBi TableC. Instrukcja uzyskuje 3000 blokad wierszy w indeksie klastrowanym dla TableA i co najmniej 5000 blokad wierszy w indeksie klastrowanym dla TableB, ale jeszcze nie uzyskała dostępu do TableC. Kiedy silnik bazy danych wykryje, że instrukcja nabyła co najmniej 5000 blokad wierszy w TableB, próbuje zwiększyć poziom wszystkich blokad utrzymywanych przez bieżącą transakcję w TableB. Podejmuje również próbę eskalacji wszystkich blokad przechowywanych przez bieżącą transakcję na TableA, ale ponieważ liczba blokad na TableA jest mniejsza niż 5000, eskalacja zakończy się niepowodzeniem. Nie podjęto próby eskalacji blokady dla TableC, ponieważ obiekt ten nie był jeszcze dostępny, gdy nastąpiła eskalacja.

Próg eskalacji dla wystąpienia silnika bazy danych

Za każdym razem, gdy liczba blokad jest większa niż próg pamięci dla eskalacji blokady, aparat bazy danych wyzwala eskalację blokady. Próg pamięci zależy od ustawienia blokad dla opcji konfiguracji.

• Jeśli opcja locks jest ustawiona na domyślną wartość 0, próg eskalacji blokady zostaje przekroczony, gdy pamięć używana przez obiekty blokad stanowi 24 procent pamięci używanej przez aparat bazy danych, z wyłączeniem pamięci AWE. Struktura danych używana do reprezentowania blokady wynosi około 100 bajtów. Ten próg jest dynamiczny, ponieważ silnik bazy danych dynamicznie uzyskuje i zwalnia pamięć, aby dostosować się do różnych obciążeń roboczych.

• Jeśli opcja locks jest wartością inną niż 0, próg eskalacji blokady wynosi 40 procent (lub mniej, jeśli istnieje ciśnienie pamięci) wartości opcji blokad.

Silnik bazy danych może wybrać dowolną aktywną instrukcję z dowolnej sesji do eskalacji, a dla każdego 1250 nowych blokad wybierze instrukcje do eskalacji, o ile pamięć blokady używana w wystąpieniu pozostaje powyżej progu.

Eskalacja blokad z mieszanymi typami blokad

W przypadku eskalacji blokady blokada wybrana dla sterty lub indeksu jest wystarczająco silna, aby spełnić wymagania najbardziej restrykcyjnej blokady na niższym poziomie.

Załóżmy na przykład, że sesja:

• Rozpoczyna transakcję.
• Aktualizuje tabelę zawierającą indeks klastrowany.
• Wystawia polecenie SELECT, które odwołuje się do tej samej tabeli.

Instrukcja UPDATE uzyskuje następujące blokady:

• Wyłączne (X) blokuje zaktualizowane wiersze danych.
• Intencjonalna wyłączność (IX) blokady są nakładane na stronach klastrowanego indeksu zawierających te wiersze.
• Blokada IX na indeksie klastrowanym oraz kolejna na tabeli.

Instrukcja SELECT uzyskuje następujące blokady:

• Udostępnione (S) blokuje wszystkie odczyty wierszy danych, chyba że wiersz jest już chroniony przez blokadę X z instrukcji UPDATE.
• Intent Shared (IS) blokuje wszystkie klastrowane strony indeksu zawierające te wiersze, chyba że strona jest już chroniona blokadą IX.
• Brak blokady w klastrowanym indeksie lub tabeli, ponieważ są one już chronione przez blokady IX.

Jeśli instrukcja SELECT zdobędzie wystarczającą liczbę blokad, aby spowodować eskalację blokady i eskalacja zakończy się powodzeniem, blokada IX w tabeli zostanie przekonwertowana na blokadę X, a wszystkie blokady wiersza, strony i indeksu zostaną zwolnione. Zarówno aktualizacje, jak i odczyty są chronione przez blokadę X w tabeli.

Zmniejszanie blokowania i eskalowania blokady

W większości przypadków mechanizm bazy danych zapewnia najlepszą wydajność, pracując z domyślnymi ustawieniami blokowania i eskalacji blokad.

• Skorzystaj z blokowania zoptymalizowanego .

  • Zoptymalizowane blokowanie oferuje ulepszony mechanizm blokowania transakcji, który zmniejsza zużycie pamięci blokady i blokowanie współbieżnych transakcji. Eskalacja blokady jest znacznie mniej prawdopodobna, gdy jest włączona zoptymalizowana blokada.
  • Unikaj używania podpowiedzi tabel ze zoptymalizowanym blokowaniem. Wskazówki dotyczące tabel mogą zmniejszyć skuteczność zoptymalizowanego blokowania.
  • Włącz opcję READ_COMMITTED_SNAPSHOT dla bazy danych, aby uzyskać największą korzyść z zoptymalizowanego blokowania. Jest to wartość domyślna w usłudze Azure SQL Database.
  • Zoptymalizowane blokowanie wymaga włączenia przyspieszonego odzyskiwania bazy danych (ADR) w bazie danych.

Jeśli wystąpienie aparatu bazy danych generuje wiele blokad i doświadcza częstych eskalacji blokad, rozważ zmniejszenie liczby blokad przy użyciu następujących strategii:

• Używaj poziomu izolacji, który nie generuje wspólnych blokad podczas operacji odczytu.

  • READ COMMITTED poziom izolacji, gdy opcja bazy danych READ_COMMITTED_SNAPSHOT jest ON.
  • poziom izolacji SNAPSHOT
  • poziom izolacji READ UNCOMMITTED Może to być używane tylko w przypadku systemów, które mogą działać z zanieczyszczonymi odczytami.

• Użyj podpowiedzi dotyczących tabeli PAGLOCK lub TABLOCK, aby aparat bazy danych korzystał z blokad strony, sterty lub indeksu zamiast blokad niskiego poziomu. Użycie tej opcji zwiększa jednak problemy użytkowników blokujących innych użytkowników próbujących uzyskać dostęp do tych samych danych i nie powinny być używane w systemach z więcej niż kilkoma równoczesnymi użytkownikami.

• Jeśli zoptymalizowane blokowanie nie jest dostępne, w przypadku tabel partycjonowanych użyj opcji LOCK_ESCALATIONALTER TABLE, aby eskalować blokady do partycji zamiast tabeli lub wyłączyć eskalację blokady dla tabeli.

• Podziel duże operacje wsadowe na kilka mniejszych operacji. Na przykład, gdybyś uruchomił następujące zapytanie, aby usunąć kilkaset tysięcy starych wierszy z tabeli audytowej, a potem okazało się, że spowodowało to eskalację blokady, która zablokowała innych użytkowników:

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

  Usuwając te wiersze po kilkaset naraz, można znacznie zmniejszyć liczbę blokad, które gromadzą się na każdą transakcję i zapobiegać eskalacji blokad. Na przykład:

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• Zmniejsz ślad blokady zapytania, czyniąc zapytanie możliwie najbardziej wydajnym. Obszerne skanowania lub duża liczba wyszukiwań kluczy może zwiększyć prawdopodobieństwo eskalacji blokady; co więcej, zwiększa to prawdopodobieństwo zakleszczeń i ogólnie negatywnie wpływa na współbieżność i wydajność. Po znalezieniu zapytania, które powoduje eskalację blokady, poszukaj możliwości utworzenia nowych indeksów lub dodania kolumn do istniejącego indeksu w celu usunięcia pełnego indeksu lub skanowania tabel oraz zmaksymalizowania wydajności wyszukiwania indeksów. Rozważ użycie Narzędzia do dostrajania silnika bazy danych do przeprowadzenia automatycznej analizy indeksu w zapytaniu. Aby uzyskać więcej informacji, zobacz Tutorial: Database Engine Tuning Advisor. Jednym z celów tej optymalizacji jest sprawienie, aby operacje wyszukiwania w indeksie zwracały jak najmniej wierszy, aby zminimalizować koszt wyszukiwania kluczy (zmaksymalizować selektywność indeksu dla określonego zapytania). Jeśli aparat bazy danych szacuje, że operator logiczny wyszukiwania klucza może zwrócić wiele wierszy, może zastosować optymalizację pobierania wstępnego do przeprowadzenia wyszukiwania. Jeśli silnik bazy danych używa wstępnego pobierania dla wyszukiwania, musi zwiększyć poziom izolacji transakcji dla części zapytania do REPEATABLE READ. Oznacza to, że to, co wygląda jak instrukcja SELECT na poziomie izolacji READ COMMITTED, może uzyskać wiele tysięcy blokad kluczy (zarówno w indeksie klastrowanym, jak i jednym indeksie nieklastrowanym), co może spowodować, że takie zapytanie przekroczy progi eskalacji blokady. Jest to szczególnie ważne, jeśli okaże się, że eskalowany poziom blokady dotyczy udostępnionej blokady tabeli, co jednak nie jest często spotykane przy domyślnym poziomie izolacji READ COMMITTED.

  Jeśli wyszukiwanie klucza za pomocą optymalizacji przefetchowania powoduje eskalację blokady, rozważ dodanie dodatkowych kolumn do indeksu nieklastrowanego, który pojawia się w operatorze logicznym Wyszukiwanie indeksu lub Skanowanie indeksu poniżej operatora logicznego wyszukiwania klucza w planie zapytania. Możliwe jest utworzenie indeksu obejmującego (indeks zawierający wszystkie kolumny w tabeli, które były użyte w zapytaniu), lub przynajmniej indeksu, który obejmuje kolumny używane jako kryteria sprzężenia lub w klauzuli WHERE, jeśli uwzględnienie wszystkiego z listy kolumn SELECT jest niepraktyczne. Sprzężenie zagnieżdżonej pętli może również używać optymalizacji pobierania wstępnego i powoduje to takie samo zachowanie blokujące.

• Nie można eskalować blokady, jeśli inny SPID obecnie trzyma niezgodną blokadę tabeli. Eskalacja blokady zawsze eskaluje się do blokady tabeli i nigdy nie dochodzi do blokady strony. Ponadto, jeśli próba eskalacji blokady nie powiedzie się, ponieważ inny SPID posiada niezgodną blokadę tabeli, zapytanie, które próbowało eskalować blokadę, nie zostaje zablokowane podczas oczekiwania na blokadę tabeli. Zamiast tego nadal uzyskuje blokady na oryginalnym, bardziej szczegółowym poziomie (wiersz, klucz lub strona), okresowo podejmując dodatkowe próby eskalacji. W związku z tym jedną z metod zapobiegania eskalacji blokady dla określonej tabeli jest uzyskanie i utrzymanie blokady na innym połączeniu, które nie jest zgodne z typem eskalowanej blokady. Blokada intencji na wyłączność (IX) na poziomie tabeli nie blokuje żadnych wierszy ani stron, ale nadal nie jest zgodna z eskalowaną blokadą współdzieloną (S) ani wyłączną (X) na poziomie tabeli. Załóżmy na przykład, że musisz uruchomić zadanie wsadowe modyfikujące dużą liczbę wierszy w tabeli mytable, co spowodowało zablokowanie wynikające z eskalacji blokady. Jeśli to zadanie zawsze jest wykonywane w mniej niż godzinę, możesz utworzyć zadanie Transact-SQL zawierające następujący kod i zaplanować uruchomienie nowego zadania kilka minut przed godziną rozpoczęcia zadania wsadowego:

  BEGIN TRAN;
  
  SELECT *
  FROM mytable WITH (UPDLOCK, HOLDLOCK)
  WHERE 1 = 0;
  
  WAITFOR DELAY '1:00:00';
  
  COMMIT TRAN;
  

  To zapytanie uzyskuje i utrzymuje blokadę typu IX na mytable przez jedną godzinę, co zapobiega eskalacji blokady na tabeli w tym okresie. Ta partia nie modyfikuje żadnych danych ani nie blokuje innych zapytań (chyba że inne zapytanie wymusza blokadę tabeli za pomocą wskazówki TABLOCK lub jeśli administrator wyłączył blokadę strony lub wiersza w indeksie na mytable).

• Możesz również użyć flag śledzenia 1211 i 1224, aby wyłączyć wszystkie lub niektóre eskalacje blokady. Jednak te flagi śledzenia wyłączają wszystkie globalne eskalacje blokady dla całego wystąpienia silnika bazy danych. Eskalacja blokad pełni użyteczną funkcję w silniku baz danych, maksymalizując wydajność zapytań, które w przeciwnym razie są spowalniane przez narzut związany z pozyskiwaniem i zwalnianiem kilku tysięcy blokad. Eskalacja blokad pomaga również zminimalizować ilość pamięci wymaganą do śledzenia blokad. Pamięć, którą aparat bazy danych może dynamicznie przydzielić dla struktur blokady, jest skończona, więc jeśli wyłączysz eskalację blokady, a pamięć blokady będzie wystarczająco duża, próba przydzielenia dodatkowych blokad dla dowolnego zapytania może zakończyć się niepowodzeniem i wystąpi następujący błąd: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  Notatka

  Gdy wystąpi błąd MSSQLSERVER_1204, zatrzymuje przetwarzanie bieżącej instrukcji i powoduje wycofanie aktywnej transakcji. Wycofanie może blokować użytkowników lub prowadzić do długiego czasu odzyskiwania bazy danych, jeśli ponownie uruchomisz usługę bazy danych.

  Notatka

  Użycie wskazówki dotyczącej blokady, takiej jak ROWLOCK, zmienia tylko początkowe ustawienie blokady. Wskazówki dotyczące blokady nie uniemożliwiają eskalacji blokady.

Począwszy od programu SQL Server 2008 (10.0.x), zachowanie eskalacji blokady zmieniło się wraz z wprowadzeniem opcji tabeli LOCK_ESCALATION. Aby uzyskać więcej informacji, zobacz opcję LOCK_ESCALATION ALTER TABLE .

Monitorowanie eskalacji blokady

Monitoruj eskalację blokady przy użyciu zdarzenia rozszerzonego lock_escalation, jak pokazano w poniższym przykładzie:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

Dynamiczne blokowanie

Użycie blokad niskiego poziomu, takich jak blokady wierszy, zwiększa współbieżność, zmniejszając prawdopodobieństwo, że dwie transakcje żądają blokad na tym samym fragmencie danych w tym samym czasie. Użycie blokad niskiego poziomu zwiększa również liczbę blokad i zasobów potrzebnych do zarządzania nimi. Korzystanie z wysokich poziomów blokad tabel lub stron zmniejsza obciążenie, ale kosztem obniżenia współbieżności.

Silnik bazy danych używa strategii dynamicznego blokowania w celu określenia najbardziej skutecznych blokad. Aparat bazy danych automatycznie określa, jakie blokady są najbardziej odpowiednie podczas wykonywania zapytania na podstawie cech schematu i zapytania. Na przykład w celu zmniejszenia obciążenia związanego z blokowaniem optymalizator może wybrać blokady stron w indeksie podczas skanowania indeksu.

Blokowanie partycjonowania

W przypadku dużych systemów komputerowych blokady na często odwoływane obiekty mogą stać się wąskim gardłem wydajności, ponieważ uzyskiwanie i zwalnianie blokad powoduje konkurencję o wewnętrzne zasoby blokujące. Partyconowanie blokad usprawnia działanie mechanizmu blokady przez podzielenie jednego zasobu blokady na wiele zasobów blokady. Ta funkcja jest dostępna tylko dla systemów z co najmniej 16 procesorami logicznymi i jest automatycznie włączona i nie można jej wyłączyć. Można partycjonować tylko blokady obiektów. Blokady obiektów, które mają podtyp, nie są partycjonowane. Aby uzyskać więcej informacji, zobacz sys.dm_tran_locks (Transact-SQL).

Omówienie partycjonowania blokady

Zadania blokujące mają dostęp do kilku zasobów współdzielonych, z których dwa są zoptymalizowane poprzez partycjonowanie blokad:

• Spinlock

  Steruje to dostępem do zasobu blokady, takiego jak wiersz lub tabela.

  Bez podziału na partycje, jeden spinlock zarządza wszystkimi żądaniami dotyczącymi blokady dla pojedynczego zasobu blokady. W systemach, które doświadczają dużej ilości aktywności, rywalizacja może wystąpić, gdy żądania blokady oczekują na udostępnienie spinlocka. W takiej sytuacji uzyskanie blokad może stać się wąskim gardłem i negatywnie wpłynąć na wydajność.

  Aby zmniejszyć rywalizację o pojedynczy zasób blokady, partycjonowanie blokady dzieli pojedynczy zasób blokady na różne zasoby blokady w celu dystrybucji obciążenia między wiele blokad obrotowych.

• pamięci

  Służy do przechowywania struktur blokady zasobów.

  Po pozyskaniu spinlocka struktury blokady są przechowywane w pamięci, a następnie są do nich dostęp i są one ewentualnie modyfikowane. Dystrybucja dostępu do blokady między wieloma zasobami pomaga wyeliminować konieczność transferu bloków pamięci między procesorami, co przyczynia się do zwiększenia wydajności.

Wdrożenie i monitorowanie partycjonowania blokad

Partycjonowanie blokad jest domyślnie włączone dla systemów z co najmniej 16 procesorami. Po włączeniu partycjonowania blokady, w dzienniku błędów programu SQL Server rejestrowany jest komunikat informacyjny.

Podczas uzyskiwania blokad w zasobie partycjonowanym:

• Tylko tryby blokady NL, Sch-S, IS, IUi IX są uzyskiwane na jednej partycji.

• Udostępnione (S), wyłączne (X) i inne blokady w trybach innych niż NL, Sch-S, IS, IUi IX należy uzyskać na wszystkich partycjach, począwszy od identyfikatora partycji 0 i następującego w kolejności identyfikatora partycji. Te blokady w zasobie podzielonym na partycje używają więcej pamięci niż blokady w tym samym trybie w zasobie bez partycji, ponieważ każda partycja jest skutecznie oddzielną blokadą. Wzrost pamięci zależy od liczby partycji. Liczniki Wydajności Blokad w SQL Server wyświetlają informacje o pamięci używanej przez blokady partycjonowane i niepartycjonowane.

Transakcja jest przypisywana do partycji po rozpoczęciu transakcji. W przypadku transakcji wszystkie żądania blokady, które można podzielić na partycje, używają partycji przypisanej do tej transakcji. Dzięki tej metodzie dostęp do blokowania zasobów tego samego obiektu przez różne transakcje jest dystrybuowany między różne partycje.

Kolumna resource_lock_partition w widoku zarządzania dynamicznego sys.dm_tran_locks zawiera identyfikator partycji blokady dla zasobu podzielonego na partycje blokady. Aby uzyskać więcej informacji, zobacz sys.dm_tran_locks (Transact-SQL).

Praca z partycjonowaniem zamków

Poniższe przykłady kodu ilustrują partycjonowanie blokad. W przykładach dwie transakcje są wykonywane w dwóch różnych sesjach, aby pokazać sposób działania partycjonowania blokady w systemie komputerowym z 16 procesorami.

Te instrukcje Transact-SQL tworzą obiekty testowe, które są używane w poniższych przykładach.

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

Przykład A

Sesja 1:

Instrukcja SELECT jest wykonywana w ramach transakcji. Ze względu na blokadę z oznaczeniem HOLDLOCK ta instrukcja uzyskuje i zachowuje blokadę typu intencja udostępnienia (IS) na tabeli (na potrzeby tej ilustracji, blokady wierszy i stron są ignorowane). Blokada IS jest uzyskiwana tylko na partycji przypisanej do transakcji. W tym przykładzie przyjęto założenie, że blokada IS jest uzyskiwana na podstawie identyfikatora partycji 7.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Sesja 2:

Transakcja rozpoczyna się, a instrukcja SELECT uruchomiona w ramach tej transakcji uzyskuje i zachowuje współdzieloną (S) blokadę na tabeli. Blokada S jest uzyskiwana na wszystkich partycjach, co powoduje zablokowanie wielu tabel — po jednym dla każdej partycji. Na przykład w systemie z 16 procesorami, 16 blokad S zostanie przydzielonych do identyfikatorów partycji blokady 0–15. Ponieważ blokada S jest zgodna z blokadą IS przechowywaną na partycji o identyfikatorze 7 przez transakcję w sesji 1, nie ma blokowania między transakcjami.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

Sesja 1:

Następująca instrukcja SELECT jest wykonywana w ramach transakcji, która jest nadal aktywna w ramach sesji 1. Z powodu wyłącznej wskazówki blokady (X) transakcja próbuje uzyskać blokadę X na tabeli. Jednak blokada S utrzymywana przez transakcję w sesji 2 blokuje blokadę X o identyfikatorze partycji 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Przykład B

Sesja 1:

Instrukcja SELECT jest wykonywana w ramach transakcji. Ze względu na blokadę z oznaczeniem HOLDLOCK ta instrukcja uzyskuje i zachowuje blokadę typu intencja udostępnienia (IS) na tabeli (na potrzeby tej ilustracji, blokady wierszy i stron są ignorowane). Blokada IS jest uzyskiwana tylko na partycji przypisanej do transakcji. W tym przykładzie przyjęto założenie, że blokada IS jest uzyskiwana na podstawie identyfikatora partycji 6.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Sesja 2:

Instrukcja SELECT jest wykonywana w ramach transakcji. Ze względu na wskazówkę dotyczącą blokady TABLOCKX transakcja próbuje uzyskać wyłączną blokadę (X) w tabeli. Należy pamiętać, że blokada X musi zostać uzyskana na wszystkich partycjach, począwszy od identyfikatora partycji 0. Blokada X jest uzyskiwana na wszystkich identyfikatorach partycji 0–5, ale jest blokowana przez blokadę IS uzyskaną na identyfikatorze partycji 6.

Na identyfikatorach partycji 7–15, których blokada X jeszcze nie osiągnęła, inne transakcje mogą nadal pozyskiwać blokady.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Poziomy izolacji opartej na wersjach wierszy w aparacie bazy danych

Począwszy od programu SQL Server 2005 (9.x), silnik bazodanowy oferuje implementację istniejącego poziomu izolacji transakcji, READ COMMITTED, który udostępnia migawkę na poziomie instrukcji przy użyciu wersjonowania wierszy. Silnik bazy danych oferuje również poziom izolacji transakcji, SNAPSHOT, który udostępnia migawkę na poziomie transakcji także przy użyciu wersjonowania wierszy.

Przechowywanie wersji wierszy to ogólna struktura w programie SQL Server, która wywołuje mechanizm kopiowania na zapis po zmodyfikowaniu lub usunięciu wiersza. Wymaga to, aby podczas uruchamiania transakcji stara wersja wiersza była dostępna dla transakcji, które wymagają wcześniejszego stanu spójnej transakcji. Wersjonowanie wierszy jest używane do implementacji następujących cech:

• Zbuduj tabele inserted i deleted w wyzwalaczach. Wszystkie wiersze zmodyfikowane przez wyzwalacz są wersjonowane. Obejmuje to wiersze zmodyfikowane przez instrukcję, która uruchomiła wyzwalacz, a także wszelkie modyfikacje danych wprowadzone przez wyzwalacz.
• Obsługa wielu aktywnych zestawów wyników (MARS). Jeśli sesja MARS wystawia instrukcję modyfikacji danych (na przykład INSERT, UPDATElub DELETE) w czasie, gdy istnieje aktywny zestaw wyników, wiersze, których dotyczy instrukcja modyfikacji, są wersjonowane.
• Obsługa operacji indeksu określających opcję ONLINE.
• Wsparcie dla poziomów izolacji transakcji opartych na wersjonowaniu wierszy:
  • Nowa implementacja poziomu izolacji READ COMMITTED, która używa wersjonowania wierszy w celu zapewnienia spójności odczytu na poziomie instrukcji.
  • Nowy poziom izolacji, SNAPSHOT, aby zapewnić spójność odczytu na poziomie transakcji.

Wersje wierszy są przechowywane w magazynie wersji. Jeśli w bazie danych włączone jest przyspieszone odzyskiwanie bazy danych (ADR), wówczas magazyn wersji zostanie utworzony właśnie w tej bazie danych. W przeciwnym razie magazyn wersji jest tworzony w bazie danych tempdb.

Baza danych musi mieć wystarczającą ilość przestrzeni na magazyn wersji. Gdy magazyn wersji znajduje się w tempdb, a baza danych tempdb jest pełna, operacje aktualizacji przestają generować wersje, ale nadal kończą się powodzeniem, ale operacje odczytu mogą zakończyć się niepowodzeniem, ponieważ określona wymagana wersja wiersza nie istnieje. Ma to wpływ na operacje takie jak wyzwalacze, MARS i indeksowanie online.

Gdy jest używana metoda ADR i magazyn wersji jest pełny, operacje odczytu nadal kończą się powodzeniem, ale operacje zapisu generujące wersje, takie jak UPDATE i DELETE kończą się niepowodzeniem. INSERT operacje nadal kończą się powodzeniem, jeśli baza danych ma wystarczającą ilość miejsca.

Wersjonowanie wierszy dla transakcji READ COMMITTED i SNAPSHOT to proces dwuetapowy:

1. Ustaw jedną z opcji bazy danych READ_COMMITTED_SNAPSHOT, ALLOW_SNAPSHOT_ISOLATION lub obie na wartość ON.

2. Ustaw odpowiedni poziom izolacji transakcji w aplikacji:

   • Gdy opcja bazy danych READ_COMMITTED_SNAPSHOT jest ON, transakcje ustawiające poziom izolacji READ COMMITTED korzystają z wersjonowania wierszy.
   • Gdy opcja bazy danych ALLOW_SNAPSHOT_ISOLATION jest ON, transakcje mogą ustawić poziom izolacji SNAPSHOT.

Jeśli opcja bazy danych READ_COMMITTED_SNAPSHOT lub ALLOW_SNAPSHOT_ISOLATION jest ustawiona na ON, aparat bazy danych przypisuje numer sekwencji transakcji (XSN) do każdej transakcji, która używa wersjonowania wierszy do manipulacji danymi. Transakcje zaczynają się od momentu wykonania instrukcji BEGIN TRANSACTION. Jednak numer sekwencji transakcji rozpoczyna się od pierwszej operacji odczytu lub zapisu po instrukcji BEGIN TRANSACTION. Numer sekwencji transakcji jest zwiększany o jeden przy każdym przypisaniu.

Gdy opcje bazy danych READ_COMMITTED_SNAPSHOT lub ALLOW_SNAPSHOT_ISOLATION są ustawione na ON, kopie logiczne (wersje) są obsługiwane dla wszystkich modyfikacji danych wykonywanych w bazie danych. Za każdym razem, gdy wiersz jest modyfikowany przez określoną transakcję, instancja silnika bazy danych przechowuje wersję wcześniej zatwierdzonego obrazu wiersza w magazynie wersji. Każda wersja jest oznaczona numerem sekwencji transakcji, która dokonała zmiany. Wersje zmodyfikowanych wierszy są łączone przy użyciu listy powiązań. Najnowsza wartość wiersza jest zawsze przechowywana w bieżącej bazie danych i połączona z wersjonowanymi wierszami w magazynie wersji.

Notatka

W przypadku modyfikacji dużych obiektów (LOB) tylko zmieniony fragment jest kopiowany do magazynu wersji.

Wersje wierszy są przechowywane wystarczająco długo, aby spełniać wymagania transakcji działających na poziomach izolacji opartych na wersjonowaniu wierszy. Silnik bazy danych śledzi najwcześniejszy przydatny numer sekwencji transakcji i okresowo usuwa wszystkie wersje wierszy oznaczone numerami sekwencji transakcji, które są niższe niż najwcześniejszy przydatny numer sekwencji.

Gdy obie opcje bazy danych są ustawione na OFF, wersjonowane są tylko te wiersze, które zostały zmodyfikowane przez wyzwalacze lub sesje MARS, albo odczytane przez operacje indeksu online. Te wersje wierszy są zwalniane, gdy nie są już potrzebne. Proces działający w tle usuwa przestarzałe wersje wierszy.

Notatka

W przypadku transakcji krótko działających wersja zmodyfikowanego wiersza może zostać zapisana w pamięci podręcznej w puli bez zapisywania w magazynie wersji. Jeśli potrzeba przechowywania wersji wiersza jest krótkotrwała, wiersz zostanie usunięty z puli buforów i nie spowoduje narzutu operacji we/wy.

Zachowanie podczas odczytywania danych

W przypadku transakcji działających w obszarze danych odczytu opartych na wersjach wierszy operacje odczytu nie uzyskują udostępnionych (S) blokad na odczytywanych danych, a w związku z tym nie blokują transakcji modyfikujących dane. Ponadto obciążenie związane z blokowaniem zasobów jest zminimalizowane, ponieważ liczba nabytych blokad jest zmniejszana. READ COMMITTED izolacja przy użyciu wersjonowania wierszy i izolacja SNAPSHOT są przeznaczone do zapewnienia spójności odczytu wersjonowanych danych na poziomie instrukcji lub transakcji.

Wszystkie zapytania, w tym transakcje działające na poziomach izolacji opartych na wersjonowaniu wierszy, uzyskują blokady stabilności schematu (Sch-S) podczas kompilacji i wykonywania. W związku z tym zapytania są blokowane, gdy współbieżna transakcja trzyma blokadę modyfikacji schematu (Sch-M) w tabeli. Na przykład operacja języka definicji danych (DDL) uzyskuje blokadę Sch-M przed zmodyfikowaniem informacji o schemacie tabeli. Transakcje, w tym te działające na poziomie izolacji opartym na wersjonowaniu wierszy, są blokowane, gdy próbują uzyskać blokadę Sch-S. Z drugiej strony zapytanie zawierające blokadę Sch-S blokuje współbieżną transakcję, która próbuje uzyskać blokadę Sch-M.

Po rozpoczęciu transakcji z użyciem poziomu izolacji SNAPSHOT, instancja silnika bazy danych rejestruje wszystkie aktualnie aktywne transakcje. Gdy transakcja SNAPSHOT odczytuje wiersz zawierający łańcuch wersji, aparat bazy danych podąża za łańcuchem i pobiera wiersz, w którym znajduje się numer sekwencji transakcji:

• Najbliższy, ale niższy niż numer sekwencyjny transakcji migawki odczytującej wiersz.

• Nie znajduje się na liście transakcji aktywnych w momencie rozpoczęcia transakcji migawki.

Operacje odczytu wykonywane przez transakcję SNAPSHOT pobierają ostatnią wersję każdego wiersza zatwierdzonego w momencie rozpoczęcia transakcji SNAPSHOT. Zapewnia to transakcyjnie spójną migawkę danych, jak istniały na początku transakcji.

READ COMMITTED transakcje przy użyciu przechowywania wersji wierszy działają w taki sam sposób. Różnica polega na tym, że transakcja READ COMMITTED nie używa własnego numeru sekwencji transakcji podczas wybierania wersji wierszy. Za każdym razem, gdy instrukcja jest uruchamiana, transakcja READ COMMITTED odczytuje najnowszy numer sekwencyjny transakcji wystawiony dla tej instancji aparatu bazy danych. Jest to numer sekwencji transakcji używany do wybierania wersji wierszy dla tego zapytania. Dzięki temu transakcje READ COMMITTED mogą zobaczyć migawkę danych, jak istnieją na początku każdej instrukcji.

Notatka

Mimo że READ COMMITTED transakcje korzystające z przechowywania wersji wierszy zapewniają transakcyjnie spójny widok danych na poziomie instrukcji, wersje wierszy generowane lub używane przez tego typu transakcje są utrzymywane do momentu zakończenia transakcji.

Zachowanie podczas modyfikowania danych

Zachowanie zapisów danych różni się zarówno z włączonym, jak i bez włączonego optymalizowanego blokowania.

Modyfikowanie danych bez zoptymalizowanego blokowania

W transakcji READ COMMITTED przy użyciu wersjonowania wierszy wybór wierszy do aktualizacji odbywa się przy użyciu blokującego przeszukiwania, w którym blokada aktualizacji (U) jest nakładana na wiersz danych podczas odczytywania wartości danych. Jest to to samo co transakcja READ COMMITTED, która nie używa wersjonowania wierszy. Jeśli wiersz danych nie spełnia kryteriów aktualizacji, blokada aktualizacji zostanie zwolniona w tym wierszu, a następny wiersz zostanie zablokowany i zeskanowany.

Transakcje działające w izolacji SNAPSHOT przyjmują optymistyczne podejście do modyfikacji danych, zakładając blokady na danych przed wykonaniem modyfikacji w celu wyłącznie wymuszenia ograniczeń. W przeciwnym razie blokady na danych nie są uzyskiwane, dopóki dane nie mają być modyfikowane. Gdy wiersz danych spełnia kryteria aktualizacji, transakcja SNAPSHOT sprawdza, czy wiersz danych nie został zmodyfikowany przez współbieżną transakcję zatwierdzoną po rozpoczęciu transakcji SNAPSHOT. Jeśli wiersz danych został zmodyfikowany poza transakcją SNAPSHOT, wystąpi konflikt aktualizacji i transakcja SNAPSHOT zostanie zakończona. Konflikt aktualizacji jest obsługiwany przez aparat bazy danych i nie ma możliwości wyłączenia wykrywania konfliktów aktualizacji.

Notatka

Operacje aktualizacji działające w ramach izolacji SNAPSHOT są wykonywane wewnętrznie w ramach izolacji READ COMMITTED, gdy transakcja SNAPSHOT uzyskuje dostęp do dowolnego z następujących elementów:

Tabela z ograniczeniem klucza obcego.

Tabela, do której odwołuje się ograniczenie klucza obcego innej tabeli.

Widok indeksowany odwołujący się do więcej niż jednej tabeli.

Jednak nawet w tych warunkach operacja aktualizacji nadal sprawdza, czy dane nie zostały zmodyfikowane przez inną transakcję. Jeśli dane zostały zmodyfikowane przez inną transakcję, transakcja SNAPSHOT napotka konflikt aktualizacji i zostanie zakończona. Konflikty aktualizacji muszą być obsługiwane i ponawiane przez aplikację.

Modyfikowanie danych przy użyciu zoptymalizowanego blokowania

Po włączeniu zoptymalizowanego blokowania i włączeniu opcji bazy danych READ_COMMITTED_SNAPSHOT (RCSI), oraz przy użyciu domyślnego poziomu izolacji READ COMMITTED, czytelnicy nie uzyskują żadnych blokad, a pisarze uzyskują krótkoterminowe blokady niskiego poziomu zamiast blokad wygasających na końcu transakcji.

Włączenie RCSI jest zalecane w celu uzyskania najwyższej wydajności z zoptymalizowanym blokowaniem. W przypadku używania bardziej rygorystycznych poziomów izolacji, takich jak REPEATABLE READ lub SERIALIZABLE, aparat bazy danych przechowuje blokady wierszy i stron do końca transakcji, zarówno dla użytkowników odczytujących, jak i zapisujących, co powoduje zwiększenie blokowania i zajęcia pamięci.

W przypadku włączenia trybu RCSI i używania domyślnego poziomu izolacji READ COMMITTED, pisarze kwalifikują wiersze w odniesieniu do predykatu na podstawie najnowszej zatwierdzonej wersji wiersza, bez uzyskiwania blokad U. Zapytanie czeka tylko wtedy, gdy wiersz spełnia kryteria i istnieje inna aktywna transakcja zapisu na tym wierszu lub stronie. Kwalifikowanie na podstawie najnowszej zatwierdzonej wersji i blokowanie tylko kwalifikowanych wierszy zmniejsza blokowanie i zwiększa współbieżność.

W przypadku wykrycia konfliktów aktualizacji przy użyciu RCSI w domyślnym poziomie izolacji READ COMMITTED, są one obsługiwane i ponawiane automatycznie bez wpływu na obciążenia klientów.

Przy włączonym zoptymalizowanym blokowaniu i korzystaniu z poziomu izolacji SNAPSHOT zachowanie konfliktów aktualizacji jest takie samo jak przy wyłączonym zoptymalizowanym blokowaniu. Konflikty aktualizacji muszą być obsługiwane i ponawiane przez aplikację.

Notatka

Aby uzyskać więcej informacji na temat zmian w zachowaniu funkcji blokady po kwalifikacji (LAQ) w kontekście zoptymalizowanego blokowania, zobacz Zmiany w zachowaniu zapytania w przypadku zoptymalizowanego blokowania i RCSI.

Zachowanie w podsumowaniu

W poniższej tabeli przedstawiono różnice między izolacją SNAPSHOT a izolacją READ COMMITTED przy użyciu wersjonowania wierszy.

Własność	READ COMMITTED poziom izolacji przy użyciu wersjonowania wierszy	SNAPSHOT poziom izolacji
Opcja bazy danych, która musi być ustawiona na ON, aby włączyć wymaganą obsługę.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
Jak sesja żąda określonego typu wersjonowania wierszy.	Użyj domyślnego poziomu izolacji READ COMMITTED lub uruchom instrukcję SET TRANSACTION ISOLATION LEVEL, aby określić poziom izolacji READ COMMITTED. Można to zrobić po rozpoczęciu transakcji.	Konieczne jest wykonanie SET TRANSACTION ISOLATION LEVEL w celu określenia poziomu izolacji SNAPSHOT przed rozpoczęciem transakcji.
Wersja danych odczytanych według instrukcji.	Wszystkie dane zatwierdzone przed rozpoczęciem każdej instrukcji.	Wszystkie dane zatwierdzone przed rozpoczęciem każdej transakcji.
Sposób obsługi aktualizacji.	Bez wykorzystania zoptymalizowanego blokowania: Przywraca wersje wierszy na rzeczywiste dane w celu wyboru wierszy do aktualizacji i stosuje blokady aktualizacji na wybranych wierszach danych. Uzyskuje wyłączne blokady na rzeczywiste wiersze danych, które mają zostać zmodyfikowane. Brak wykrywania konfliktów aktualizacji. Z optymalnym blokowaniem: Na podstawie ostatniej zatwierdzonej wersji wybiera się wiersze, bez konieczności uzyskiwania blokad. Jeśli wiersze kwalifikują się do aktualizacji, zostaną uzyskane ekskluzywne blokady wiersza lub strony. Jeśli zostaną wykryte konflikty aktualizacji, są one obsługiwane i ponawiane automatycznie.	Używa wersji wierszy do wybierania wierszy do zaktualizowania. Próbuje uzyskać wyłączną blokadę rzeczywistego wiersza danych do zmodyfikowania; jeśli jednak dane zostały zmodyfikowane przez inną transakcję, wystąpi konflikt aktualizacji i transakcja migawki zostanie zakończona.
Aktualizowanie wykrywania konfliktów	bez zoptymalizowanego blokowania: Brak. Z zoptymalizowanym blokowaniem: Jeśli zostaną wykryte konflikty aktualizacji, są one obsługiwane i ponawiane automatycznie.	Zintegrowana obsługa. Nie można wyłączyć.

Użycie zasobów przechowywania wersji wierszy

Struktura przechowywania wersji wierszy obsługuje następujące funkcje aparatu bazy danych:

• Wyzwalaczy
• Wiele aktywnych zestawów wyników (MARS)
• Indeksowanie online

Platforma przechowywania wersji wierszy obsługuje również następujące poziomy izolacji transakcji opartej na wersjach wierszy:

• Gdy opcja READ_COMMITTED_SNAPSHOT bazy danych jest ustawiona na ON, transakcje READ_COMMITTED zapewniają konsystencję odczytu na poziomie instrukcji przy użyciu wersjonowania wierszy.
• Gdy opcja ALLOW_SNAPSHOT_ISOLATION bazy danych jest ustawiona na ON, transakcje SNAPSHOT zapewniają spójność odczytu na poziomie transakcji przy użyciu wersjonowania wierszy.

Poziomy izolacji oparte na wersjonowaniu wierszy zmniejszają liczbę blokad uzyskiwanych przez transakcję przez eliminację użycia udostępnionych blokad w operacjach odczytu. Zwiększa to wydajność systemu przez zmniejszenie zasobów używanych do zarządzania blokadami. Wydajność jest również zwiększana przez zmniejszenie częstości blokowania transakcji przez blokady nałożone przez inne transakcje.

Poziomy izolacji opartej na wersjach wierszy zwiększają zasoby wymagane przez modyfikacje danych. Włączenie tych opcji powoduje, że wszystkie modyfikacje danych bazy danych mają być wersjonowane. Kopia danych przed modyfikacją jest przechowywana w magazynie wersji nawet wtedy, gdy nie ma aktywnych transakcji przy użyciu izolacji opartej na wersjach wierszy. Dane po modyfikacji zawierają wskaźnik do danych w wersji w repozytorium wersji. W przypadku dużych obiektów tylko część zmienionego obiektu jest przechowywana w magazynie wersji.

Miejsce używane w bazie danych tempdb

Dla każdego wystąpienia silnika bazy danych magazyn wersji musi mieć wystarczająco dużo miejsca, aby pomieścić wersje wierszy. Administrator bazy danych musi upewnić się, że tempdb i inne bazy danych (jeśli włączono ADR) mają wystarczająco dużo miejsca na obsługę magazynu wersji. Istnieją dwa typy magazynów wersji:

• Magazyn wersji kompilacji indeksu online jest używany na potrzeby kompilacji indeksu online.
• Wspólny magazyn wersji jest używany dla wszystkich innych operacji modyfikacji danych.

Wersje wierszy muszą być przechowywane tak długo, jak aktywna transakcja musi mieć do nich dostęp. Okresowo wątek w tle usuwa wersje wierszy, które nie są już potrzebne i zwalnia miejsce w repozytorium wersji. Długotrwała transakcja uniemożliwia zwolnienie miejsca w przechowalni wersji, jeśli spełnia którykolwiek z następujących warunków:

• Używa izolacji opartej na wersjach wierszy.
• Używa wyzwalaczy, MARS lub operacji kompilacji indeksu online.
• Generuje wersje wierszy.

Notatka

Po wywołaniu wyzwalacza wewnątrz transakcji wersje wierszy utworzone przez wyzwalacz są utrzymywane do końca transakcji, mimo że wersje wierszy nie są już potrzebne po zakończeniu wyzwalacza. Dotyczy to także transakcji READ COMMITTED, które korzystają z wersjonowania wierszy. W przypadku tego typu transakcji wymagany jest spójny transakcyjnie widok bazy danych tylko dla każdej instrukcji w transakcji. Oznacza to, że wersje wierszy utworzone dla oświadczenia w transakcji nie są już potrzebne po zakończeniu oświadczenia. Jednak wersje wierszy utworzone przez każdą instrukcję w transakcji są utrzymywane do momentu zakończenia transakcji.

Jeśli magazyn wersji znajduje się w tempdbi tempdb zabraknie miejsca, aparat bazy danych wymusza zmniejszenie magazynu wersji. Podczas procesu zmniejszania najdłużej działające transakcje, które nie wygenerowały jeszcze wersji wierszy, są oznaczone jako ofiary. Komunikat 3967 jest generowany w dzienniku błędów dla każdej transakcji ofiary. Jeśli transakcja jest oznaczona jako ofiara, nie może już odczytywać wersji wierszy w repozytorium wersji. Podczas próby odczytu wersji wierszy zostanie wygenerowany komunikat 3966 i transakcja zostanie wycofana. Jeśli proces zmniejszania zakończy się pomyślnie, miejsce stanie się dostępne w tempdb. W przeciwnym razie tempdb zabraknie miejsca i następuje:

• Operacje zapisu są nadal wykonywane, ale nie generują wersji. Komunikat informacyjny (3959) pojawia się w dzienniku błędów, ale nie ma to wpływu na transakcję zapisującą dane.

• Transakcje, które próbują uzyskać dostęp do wersji wierszy, które nie zostały wygenerowane z powodu pełnego wycofania tempdb, kończą się błędem 3958.

Miejsce używane w wierszach danych

Każdy wiersz bazy danych może używać maksymalnie 14 bajtów na końcu wiersza na potrzeby informacji o wersjonowaniu wierszy. Informacje o wersjonowaniu wierszy zawierają numer sekwencji transakcji, która zatwierdziła wersję, oraz wskaźnik do wersji wiersza. Te 14 bajtów są dodawane przy pierwszym zmodyfikowaniu wiersza lub po wstawieniu nowego wiersza w dowolnym z następujących warunków:

• opcje READ_COMMITTED_SNAPSHOT lub ALLOW_SNAPSHOT_ISOLATION są ustawione na ON.
• Tabela ma wyzwalacz.
• Jest używanych wiele aktywnych zestawów wyników (MARS).
• Operacje kompilacji indeksu online są obecnie uruchomione w tabeli.

Jeśli magazyn wersji znajduje się w tempdb, te 14 bajtów zostanie usuniętych z wiersza bazy danych przy pierwszym zmodyfikowaniu tego wiersza w każdych z tych warunków:

• Opcje READ_COMMITTED_SNAPSHOT i ALLOW_SNAPSHOT_ISOLATION są ustawione na OFF.
• Wyzwalacz nie istnieje już w tabeli.
• Usługa MARS nie jest używana.
• Operacje kompilacji indeksu online nie są obecnie uruchomione.

14 bajtów jest również usuwanych po zmodyfikowaniu wiersza, jeśli ADR nie jest już aktywne, a powyższe warunki są spełnione.

Jeśli używasz dowolnego z funkcji przechowywania wersji wierszy, może być konieczne przydzielenie dodatkowego miejsca na dysku dla bazy danych, aby pomieścić 14 bajtów na wiersz bazy danych. Dodanie informacji o wersji wierszy może spowodować podziały stron indeksu lub alokację nowej strony danych, jeśli na bieżącej stronie nie ma wystarczającej ilości miejsca. Jeśli na przykład średnia długość wiersza wynosi 100 bajtów, dodatkowe 14 bajtów powoduje, że istniejąca tabela będzie rosnąć do 14 procent.

Zmniejszenie współczynnika wypełnienia może pomóc zapobiec fragmentacji lub zmniejszyć fragmentację stron indeksu. Aby wyświetlić informacje o aktualnej gęstości stron dla danych i indeksów tabeli lub widoku, można użyć sys.dm_db_index_physical_stats.

Miejsce używane przez magazyn wersji trwałej (PVS)

Po włączeniu reguły ADR wersje wierszy mogą być przechowywane w magazynie wersji trwałej (PVS) na jeden z następujących sposobów, w zależności od rozmiaru wiersza przed modyfikacją:

• Jeśli rozmiar jest mały, cała stara wersja wiersza jest przechowywana jako część zmodyfikowanego wiersza.
• Jeśli rozmiar jest pośredni, różnica między starą wersją wiersza a zmodyfikowanym wierszem jest przechowywana jako część zmodyfikowanego wiersza. Różnica jest skonstruowana w sposób umożliwiający aparatowi bazy danych odtworzenie całej starej wersji wiersza w razie potrzeby.
• Jeśli rozmiar jest duży, cała stara wersja wiersza jest przechowywana w oddzielnej tabeli wewnętrznej.

Dwie pierwsze metody są nazywane magazynem wersji w wierszu. Ostatnia metoda nazywa się wersja magazynowania poza wierszem z indeksem . Gdy wersje w wierszu nie są już potrzebne, są usuwane, aby zwolnić miejsce na stronach. Podobnie, strony w tabeli wewnętrznej, zawierające niepotrzebne już wersje poza wierszami, są usuwane przez moduł czyszczący wersje.

Przechowywanie wersji wierszy w ramach wiersza optymalizuje pobieranie danych przez transakcje, które muszą odczytywać wersje wierszy. Jeśli wersja jest przechowywana w wierszu, oddzielny odczyt strony PVS poza wierszem nie jest wymagany.

Funkcja DMV sys.dm_db_index_physical_stats dostarcza informacji o liczbie i typie wersji przechowywanych w wierszu i poza wierszem dla partycji indeksu. Łączny rozmiar danych wersji przechowywanych w wierszu jest zgłaszany w kolumnie total_inrow_version_payload_size_in_bytes.

Rozmiar magazynu wersji poza wierszem jest zgłaszany w kolumnie persistent_version_store_size_kb w widoku DMV sys.dm_tran_persistent_version_store_stats.

Miejsce używane w dużych obiektach

Aparat bazy danych obsługuje kilka typów danych, które mogą zawierać duże ciągi o długości do 2 gigabajtów (GB), takie jak: nvarchar(max), varchar(max), varbinary(max), ntext, texti image. Duże dane przechowywane przy użyciu tych typów danych są przechowywane w serii fragmentów danych połączonych z wierszem danych. Informacje o wersjonowaniu wierszy są przechowywane w każdym fragmencie używanym do przechowywania tych wielkich ciągów znaków. Fragmenty danych są przechowywane w zestawie stron przeznaczonych dla dużych obiektów w tabeli.

Ponieważ nowe duże wartości są dodawane do bazy danych, są przydzielane przy użyciu maksymalnie 8040 bajtów danych na fragment. Wcześniejsze wersje aparatu bazy danych przechowywały do 8080 bajtów danych ntext, textlub image na fragment.

Istniejące dane ntext, texti image dużych obiektów (LOB) nie są aktualizowane, aby zwolnić miejsce na informacje dotyczące przechowywania wersji wierszy podczas uaktualniania bazy danych do programu SQL Server z wcześniejszej wersji programu SQL Server. Za pierwszym razem, gdy dane LOB są modyfikowane, są dynamicznie uaktualniane, aby umożliwić przechowywanie informacji o wersjonowaniu. Dzieje się tak nawet wtedy, gdy wersje wierszy nie są generowane. Po zaktualizowaniu danych LOB maksymalna liczba bajtów przechowywanych na szczegół zostanie zmniejszona z 8080 bajtów do 8040 bajtów. Proces aktualizacji jest odpowiednikiem usuwania wartości LOB i ponownego wstawiania tej samej wartości. Dane LOB są uaktualniane, nawet jeśli zmodyfikowano tylko 1 bajt. Jest to jednorazowa operacja dla każdej kolumny ntext, textlub image, ale każda operacja może wygenerować dużą ilość alokacji stron i aktywności we/wy w zależności od rozmiaru danych LOB. Może również wygenerować dużą ilość aktywności rejestrowania, jeśli modyfikacja zostanie w pełni zarejestrowana. WRITETEXT i UPDATETEXT operacje są minimalnie rejestrowane, jeśli model odzyskiwania bazy danych nie jest ustawiony na PEŁNY.

Aby spełnić to wymaganie, należy przydzielić wystarczającą ilość miejsca na dysku.

Monitorowanie wersjonowania wierszy i magazynu wersji

Do monitorowania procesów przechowywania wersji wierszy, magazynu wersji i izolacji migawek pod kątem wydajności i problemów, aparat bazy danych udostępnia narzędzia w postaci dynamicznych widoków zarządzania i liczników wydajności.

Dynamiczne widoki zarządzania

Następujące dynamiczne widoki zarządzania zawierają informacje na temat bieżącego stanu systemu tempdb i magazynu wersji, a także transakcji przy użyciu wersjonowania wierszy.

• sys.dm_db_file_space_usage. Zwraca informacje o użyciu miejsca dla każdego pliku w bazie danych. Aby uzyskać więcej informacji, zobacz sys.dm_db_file_space_usage (Transact-SQL).

• sys.dm_db_session_space_usage. Zwraca aktywność alokacji i dealokacji stron dla bazy danych według sesji. Więcej informacji znajdziesz w sys.dm_db_session_space_usage (Transact-SQL).

• sys.dm_db_task_space_usage. Zwraca aktywność alokacji i dealokacji stron według zadania dla bazy danych. Aby uzyskać więcej informacji, zobacz sys.dm_db_task_space_usage (Transact-SQL).

• sys.dm_tran_top_version_generators. Zwraca tabelę wirtualną dla obiektów generujących większość wersji w magazynie wersji. Grupuje 256 najdłuższych zagregowanych rekordów według database_id i rowset_id. Ta funkcja służy do znajdowania największych odbiorców magazynu wersji. Dotyczy tylko magazynu wersji w tempdb. Aby uzyskać więcej informacji, zobacz sys.dm_tran_top_version_generators (Transact-SQL).

• sys.dm_tran_version_store. Zwraca tabelę wirtualną, która wyświetla wszystkie rekordy wersji we wspólnym magazynie wersji. Dotyczy tylko magazynu wersji w tempdb. Aby uzyskać więcej informacji, zobacz sys.dm_tran_version_store (Transact-SQL).

• sys.dm_tran_version_store_space_usage. Zwraca wirtualną tabelę, która wyświetla łączną ilość miejsca w tempdb używanego przez zapisy magazynu wersji dla każdej z baz danych. Dotyczy tylko magazynu wersji w tempdb. Aby uzyskać więcej informacji, zobacz sys.dm_tran_version_store_space_usage (Transact-SQL).

  Notatka

  Wykonywanie zapytań sys.dm_tran_top_version_generators i sys.dm_tran_version_store może być kosztowne, ponieważ oba skanują cały magazyn wersji, który może być duży. sys.dm_tran_version_store_space_usage jest wydajne i niedrogie do uruchomienia, ponieważ nie przechodzi przez poszczególne rekordy magazynu wersji, lecz zamiast tego zwraca zbiorczą przestrzeń magazynu wersji używaną przez tempdb na każdą bazę danych.

• sys.dm_tran_active_snapshot_database_transactions. Zwraca tabelę wirtualną dla wszystkich aktywnych transakcji we wszystkich bazach danych w wystąpieniu SQL Server, które używają wersjonowania wierszy. Transakcje systemowe nie są wyświetlane w tym widoku DMV. Aby uzyskać więcej informacji, zobacz sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

• sys.dm_tran_transactions_snapshot. Zwraca wirtualną tabelę, która wyświetla migawki zrobione przez każdą transakcję. Migawka zawiera numer sekwencji transakcji aktywnych korzystających z przechowywania wersji wierszy. Aby uzyskać więcej informacji, zobacz sys.dm_tran_transactions_snapshot (Transact-SQL).

• sys.dm_tran_current_transaction. Zwraca pojedynczy wiersz, który wyświetla informacje o stanie transakcji związane z wersjonowaniem wierszy w bieżącej sesji. Aby uzyskać więcej informacji, zobacz sys.dm_tran_current_transaction (Transact-SQL).

• sys.dm_tran_current_snapshot. Zwraca tabelę wirtualną, która wyświetla wszystkie aktywne transakcje w chwili rozpoczęcia bieżącej transakcji izolacji migawki. Jeśli bieżąca transakcja korzysta z izolacji migawkowej, ta funkcja nie zwraca żadnych wierszy. sys.dm_tran_current_snapshot DMV jest podobny do sys.dm_tran_transactions_snapshot, z tą różnicą, że zwraca tylko aktywne transakcje dla bieżącej migawki. Aby uzyskać więcej informacji, zobacz sys.dm_tran_current_snapshot (Transact-SQL).

• sys.dm_tran_persistent_version_store_stats. Zwraca statystyki trwałego magazynu wersji w każdej bazie danych w przypadku włączenia przyspieszonego odzyskiwania bazy danych. Aby uzyskać więcej informacji, zobacz sys.dm_tran_persistent_version_store_stats (Transact-SQL).

Liczniki wydajności

Poniższe liczniki wydajności monitorują magazyn wersji w tempdb, a także transakcje przy użyciu wersjonowania wierszy. Liczniki wydajności znajdują się w obiekcie wydajności SQLServer:Transactions.

• wolne miejsce w bazie danych tempdb (KB). Monitoruje ilość wolnego miejsca w kilobajtach (KB) w bazie danych tempdb. Do obsługi magazynu wersji obsługującego izolację migawek musi być wystarczająca ilość wolnego miejsca w tempdb.

  Poniższa formuła zawiera przybliżone oszacowanie rozmiaru magazynu wersji. W przypadku długotrwałych transakcji warto monitorować generowanie i szybkość oczyszczania w celu oszacowania maksymalnego rozmiaru magazynu wersji.

  [rozmiar wspólnego magazynu wersji] = 2 * [dane magazynu wersji generowane na minutę] * [najdłuższy czas działania (w minutach) transakcji]

  Najdłuższy czas wykonywania transakcji nie powinien obejmować kompilacji indeksów online. Ponieważ te operacje mogą trwać długo w przypadku bardzo dużych tabel, kompilacje indeksów online używają oddzielnego magazynu wersji. Przybliżony rozmiar magazynu wersji kompilacji indeksu online jest równy ilości danych zmodyfikowanych w tabeli, w tym wszystkich indeksów, podczas gdy kompilacja indeksu online jest aktywna.

• rozmiar magazynu wersji (KB). Monitoruje rozmiar w KB wszystkich magazynów wersji w tempdb. Te informacje pomagają określić potrzebną ilość miejsca w bazie danych tempdb dla magazynu wersji. Monitorowanie tego licznika w danym okresie zapewnia przydatne oszacowanie dodatkowego miejsca potrzebnego do tempdb.

• szybkość generowania wersji (KB/s). Monitoruje szybkość generowania wersji w KB na sekundę we wszystkich sklepach wersji w tempdb.

• współczynnik oczyszczania wersji (KB/s). Monitoruje tempo oczyszczania wersji w KB na sekundę we wszystkich magazynach wersji w tempdb.

  Notatka

  Informacje dotyczące szybkości generowania wersji (KB/s) i szybkości oczyszczania wersji (KB/s) mogą służyć do przewidywania wymagań przestrzeni tempdb.

• liczba jednostek magazynu wersji. Monitoruje liczbę jednostek magazynu wersji.

• tworzenie jednostki Version Store. Monitoruje całkowitą liczbę jednostek magazynu wersji utworzonych do przechowywania wersji wierszy od momentu uruchomienia instancji.

• skracanie jednostki magazynu wersji. Monitoruje łączną liczbę jednostek magazynu wersji obciętych od momentu uruchomienia wystąpienia. Jednostka magazynu wersji jest obcinana, gdy program SQL Server określa, że żadne wiersze wersji przechowywane w jednostce magazynu wersji nie są potrzebne do uruchamiania aktywnych transakcji.

• współczynnik konfliktu aktualizacji. Monitoruje stosunek transakcji migawek aktualizacji, które napotkały konflikty aktualizacji, do łącznej liczby transakcji migawek aktualizacji.

• najdłuższy czas wykonywania transakcji. Monitoruje najdłuższy czas działania w sekundach dowolnej transakcji przy użyciu wersjonowania wierszy. Może to służyć do określenia, czy jakakolwiek transakcja działa przez nieoczekiwanie długi czas.

• Transakcje. Monitoruje łączną liczbę aktywnych transakcji. Nie obejmuje to transakcji systemowych.

• Transakcje Migawkowe. Monitoruje łączną liczbę aktywnych transakcji migawek.

• Zaktualizuj transakcje migawki. Monitoruje łączną liczbę aktywnych transakcji migawkowych, które wykonują operacje aktualizacji.

• Transakcje wersji nie-snapshot. Monitoruje łączną liczbę aktywnych transakcji niebędących migawkami, które generują rekordy wersji.

  Notatka

  Suma transakcji aktualizacji migawek i transakcji wersji bezmigawkowych reprezentuje łączną liczbę transakcji uczestniczących w generowaniu wersji. Różnica między Transakcjami Migawkowymi a Transakcjami Aktualizacji Migawek oznacza liczbę transakcji migawkowych tylko do odczytu.

Przykład izolacji na poziomie opartym na wersjonowaniu wierszy

W poniższych przykładach pokazano różnice w zachowaniu między transakcjami z izolacją SNAPSHOT a transakcjami READ COMMITTED wykorzystującymi wersjonowanie wierszy.

A. Praca z poziomem izolacji migawki

W tym przykładzie transakcja uruchomiona w izolacji poziomu SNAPSHOT odczytuje dane, które są następnie modyfikowane przez inną transakcję. Transakcja SNAPSHOT nie blokuje operacji aktualizacji wykonywanej przez inną transakcję i kontynuuje odczytywanie danych z wersjonowanego wiersza, ignorując modyfikację danych. Jednak gdy transakcja SNAPSHOT próbuje zmodyfikować dane, które zostały już zmodyfikowane przez inną transakcję, transakcja SNAPSHOT generuje błąd i zostaje zakończona.

W sesji 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

W sesji 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

W sesji 1:

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

W sesji 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

W sesji 1:

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B. Praca z izolacją READ COMMITTED przy użyciu wersjonowania wierszy

W tym przykładzie transakcja READ COMMITTED z użyciem wersjonowania wierszy działa współbieżnie z inną transakcją. Transakcja READ COMMITTED działa inaczej niż transakcja SNAPSHOT. Podobnie jak transakcja SNAPSHOT, transakcja READ COMMITTED odczytuje wersjonowane wiersze nawet po tym, jak inna transakcja zmodyfikuje dane. Jednak w przeciwieństwie do transakcji SNAPSHOT transakcja READ COMMITTED:

• Odczytuje zmodyfikowane dane po zatwierdzeniu zmian danych przez inną transakcję.
• Jest w stanie zaktualizować dane zmodyfikowane przez inną transakcję, których nie mogła zaktualizować transakcja SNAPSHOT.

W sesji 1:

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

W sesji 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

W sesji 1:

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

W sesji 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

W sesji 1:

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Włącz poziomy izolacji oparte na wersjonowaniu wierszy

Administratorzy bazy danych kontrolują ustawienia na poziomie bazy danych na potrzeby przechowywania wersji wierszy przy użyciu opcji bazy danych READ_COMMITTED_SNAPSHOT i ALLOW_SNAPSHOT_ISOLATION w instrukcji ALTER DATABASE.

Gdy opcja READ_COMMITTED_SNAPSHOT bazy danych jest ustawiona na ON, mechanizmy używane do obsługi tej opcji są natychmiast aktywowane. Podczas ustawiania opcji READ_COMMITTED_SNAPSHOT tylko połączenie wykonujące polecenie ALTER DATABASE jest dozwolone w bazie danych. Nie może istnieć żadne inne otwarte połączenie w bazie danych do czasu zakończenia ALTER DATABASE. Baza danych nie musi być w trybie pojedynczego użytkownika.

Następująca instrukcja Transact-SQL umożliwia READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

Kiedy opcja bazy danych ALLOW_SNAPSHOT_ISOLATION jest ustawiona na ON, instancja silnika bazy danych nie zaczyna generować wersji wierszy dla zmodyfikowanych danych, aż do zakończenia wszystkich aktywnych transakcji, które zmodyfikowały dane w bazie danych. Jeśli istnieją aktywne transakcje modyfikacji, aparat bazy danych ustawia stan opcji PENDING_ON. Po zakończeniu wszystkich transakcji modyfikacji stan opcji zostanie zmieniony na ON. Użytkownicy nie mogą uruchomić transakcji SNAPSHOT w bazie danych, dopóki opcja nie zostanie ON. Podobnie baza danych przechodzi przez stan PENDING_OFF, gdy administrator bazy danych ustawia opcję ALLOW_SNAPSHOT_ISOLATION na OFF.

Następująca instrukcja Transact-SQL umożliwia ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

W poniższej tabeli wymieniono i opisano stany opcji ALLOW_SNAPSHOT_ISOLATION. Użycie ALTER DATABASE z opcją ALLOW_SNAPSHOT_ISOLATION nie blokuje użytkowników, którzy obecnie uzyskują dostęp do danych bazy danych.

Stan izolacji SNAPSHOT dla bieżącej bazy danych	Opis
OFF	Obsługa transakcji izolacyjnych SNAPSHOT nie została uruchomiona. Żadne transakcje izolacyjne SNAPSHOT nie są dozwolone.
PENDING_ON	Wsparcie dla transakcji izolacyjnych SNAPSHOT znajduje się w stanie przejściowym (z OFF do ON). Otwarte transakcje muszą zostać ukończone. Żadne transakcje izolacyjne SNAPSHOT nie są dozwolone.
ON	Obsługa izolacji transakcji SNAPSHOT została aktywowana. Transakcje SNAPSHOT są dozwolone.
PENDING_OFF	Wsparcie dla transakcji izolacyjnych SNAPSHOT znajduje się w stanie przejściowym (z ON do OFF). SNAPSHOT transakcje uruchomione po tym czasie nie mogą uzyskać dostępu do tej bazy danych. Istniejące transakcje SNAPSHOT nadal mogą uzyskiwać dostęp do tej bazy danych. Istniejące transakcje zapisu nadal używają wersjonowania w tej bazie danych. Stan PENDING_OFF stanie się OFF dopiero wtedy, gdy wszystkie transakcje SNAPSHOT rozpoczęte w momencie, gdy stan izolacji bazy danych SNAPSHOT był ON, zostaną zakończone.

Użyj widoku katalogu sys.databases, aby określić stan obu opcji bazy danych wersjonowania wierszy.

Wszystkie aktualizacje tabel użytkowników i niektórych tabel systemowych przechowywanych w master i msdb generują wersje wierszy.

Opcja ALLOW_SNAPSHOT_ISOLATION jest automatycznie ustawiana na ON w bazach danych master i msdb i nie może być wyłączona.

Użytkownicy nie mogą ustawić opcji READ_COMMITTED_SNAPSHOT na ON w master, tempdblub msdb.

Używanie poziomów izolacji opartych na wersjach wierszy

Struktura przechowywania wersji wierszy jest zawsze włączona i jest używana przez wiele funkcji. Oprócz zapewniania poziomów izolacji bazujących na wersjonowaniu wierszy, służy do obsługi modyfikacji wykonywanych w wyzwalaczach oraz wielu jednoczesnych sesji wyników (MARS) oraz do obsługi odczytów danych podczas operacji indeksowania online.

Poziomy izolacji opartej na wersjach wierszy są włączone na poziomie bazy danych. Każda aplikacja, która uzyskuje dostęp do obiektów z włączonych baz danych, może uruchamiać zapytania przy użyciu następujących poziomów izolacji:

• READ COMMITTED, który używa wersjonowania wierszy, ustawiając w bazie danych opcję READ_COMMITTED_SNAPSHOT na ON, jak pokazano w poniższym przykładzie kodu:

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  Gdy baza danych obsługuje READ_COMMITTED_SNAPSHOT, wszystkie zapytania uruchamiane na poziomie izolacji READ COMMITTED używają wersjonowania wierszy, co oznacza, że operacje odczytu nie blokują operacji aktualizacji.

• Izolowanie SNAPSHOT przez ustawienie opcji bazy danych ALLOW_SNAPSHOT_ISOLATION na ON, jak pokazano w poniższym przykładzie kodu:

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  W przypadku używania zapytań obejmujących wiele baz danych, transakcja uruchomiona w izolacji SNAPSHOT może mieć dostęp do tabel w bazach danych z opcją bazy danych ALLOW_SNAPSHOT_ISOLATION ustawioną na ON. Aby uzyskać dostęp do tabel w bazach danych, które nie mają opcji ALLOW_SNAPSHOT_ISOLATION bazy danych ustawionej na ON, należy zmienić poziom izolacji. Na przykład poniższy przykład kodu przedstawia instrukcję SELECT, która łączy dwie tabele podczas działania w ramach transakcji SNAPSHOT. Jedna tabela należy do bazy danych, w której nie włączono izolacji SNAPSHOT. Gdy instrukcja SELECT działa w izolacji SNAPSHOT, nie może zostać wykonana pomyślnie.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

  Poniższy przykład kodu przedstawia tę samą instrukcję SELECT, która została zmodyfikowana w celu zmiany poziomu izolacji transakcji na READ COMMITTED podczas uzyskiwania dostępu do określonej tabeli. Ze względu na tę zmianę instrukcja SELECT jest wykonywana pomyślnie.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1 WITH (READCOMMITTED)
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

Ograniczenia transakcji przy użyciu poziomów izolacji bazujących na wersjonowaniu wierszy

Podczas pracy z poziomami izolacji opartej na wersjach wierszy należy wziąć pod uwagę następujące ograniczenia:

• nie można włączyć READ_COMMITTED_SNAPSHOT w tempdb, msdblub master.

• Globalne tabele tymczasowe są przechowywane w tempdb. W przypadku uzyskiwania dostępu do globalnych tabel tymczasowych wewnątrz transakcji SNAPSHOT musi wystąpić jedna z następujących czynności:

  • Ustaw opcję bazy danych ALLOW_SNAPSHOT_ISOLATION na ON w tempdb.
  • Użyj zasugerowania izolacji, aby zmienić poziom izolacji dla zapytania.

• transakcje SNAPSHOT kończą się niepowodzeniem, gdy:

  • Baza danych jest ustawiana jako tylko do odczytu po rozpoczęciu transakcji SNAPSHOT, ale przed uzyskaniem dostępu przez transakcję SNAPSHOT do bazy danych.
  • W przypadku uzyskiwania dostępu do obiektów z wielu baz danych, stan bazy danych został zmieniony w taki sposób, że odzyskiwanie bazy danych miało miejsce po rozpoczęciu transakcji SNAPSHOT, ale przed tym, jak transakcja SNAPSHOT uzyska dostęp do bazy danych. Na przykład: baza danych została ustawiona na OFFLINE, a następnie na ONLINEbaza danych została automatycznie zamknięta i ponownie otwarta ze względu na opcję AUTO_CLOSE ustawioną na ONlub baza danych została odłączona i ponownie dołączona.

• Transakcje rozproszone, w tym zapytania w rozproszonych partycjonowanych bazach danych, nie są obsługiwane w izolacji SNAPSHOT.

• Silnik bazodanowy nie przechowuje wielu wersji metadanych systemu. Instrukcje języka definicji danych (DDL) dotyczące tabel i innych obiektów bazy danych (indeksy, widoki, typy danych, procedury składowane i funkcje środowiska uruchomieniowego języka wspólnego) zmieniają metadane. Jeśli instrukcja DDL modyfikuje obiekt, każde współbieżne odwołanie do obiektu w izolacji SNAPSHOT powoduje niepowodzenie transakcji SNAPSHOT. READ COMMITTED transakcje nie mają tego ograniczenia, gdy opcja READ_COMMITTED_SNAPSHOT bazy danych jest ustawiona na ON.

  Na przykład administrator bazy danych wykonuje następującą instrukcję ALTER INDEX.

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD;
  GO
  

  Każda transakcja migawki, która jest aktywna w momencie wykonania instrukcji ALTER INDEX, otrzymuje błąd, jeśli próbuje odwołać się do tabeli HumanResources.Employee po wykonaniu instrukcji ALTER INDEX. READ COMMITTED transakcje korzystające z przechowywania wersji wierszy nie są dotknięte.

  Notatka

  BULK INSERT operacje mogą spowodować zmiany metadanych tabeli docelowej (na przykład podczas wyłączania kontroli ograniczeń). W takim przypadku jednoczesne transakcje izolacyjne SNAPSHOT uzyskujące dostęp do tabel ze zbiorczo wstawionymi danymi kończą się niepowodzeniem.

Dostosowywanie blokowania i wersjonowania wierszy

Dostosowywanie limitu czasu blokady

Gdy instancja silnika bazy danych nie może udzielić blokady transakcji, ponieważ inna transakcja posiada już blokadę powodującą konflikt na zasobie, pierwsza transakcja zostaje zablokowana, czekając na zwolnienie istniejącej blokady. Domyślnie nie ma limitu czasu oczekiwania na blokadę, dlatego transakcja może zostać zablokowana na czas nieokreślony.

Notatka

Użyj dynamicznego widoku zarządzania sys.dm_os_waiting_tasks, aby określić, czy zadanie jest blokowane i co go blokuje. Aby uzyskać więcej informacji i przykładów, zobacz Omówienie i rozwiązywanie problemów z blokowaniem programu SQL Server.

Ustawienie LOCK_TIMEOUT umożliwia aplikacji ustawienie maksymalnego czasu oczekiwania instrukcji na zablokowanym zasobie. Gdy instrukcja czeka dłużej niż ustawienie LOCK_TIMEOUT, zablokowana instrukcja zostaje anulowana automatycznie, a zwrócony zostaje komunikat o błędzie 1222 (Lock request time-out period exceeded). Każda transakcja zawierająca stwierdzenie nie zostaje jednak cofnięta. W związku z tym aplikacja musi mieć program obsługi błędów, który może wychwytować komunikat o błędzie 1222. Jeśli aplikacja nie wychwytuje błędu, aplikacja może kontynuować nieświadomie, że pojedyncza instrukcja w ramach transakcji została anulowana, ale transakcja pozostaje aktywna. Błędy mogą wystąpić, ponieważ instrukcje w dalszej części transakcji mogą zależeć od instrukcji, która nigdy nie została wykonana.

Zaimplementowanie procedury obsługi błędów, która wychwyci komunikat o błędzie 1222 umożliwia aplikacji obsługę sytuacji przekroczenia limitu czasu i podjęcie akcji naprawczej, takich jak: automatyczne ponowne przesłanie instrukcji, która została zablokowana lub cofnięta cała transakcja.

Ważny

Aplikacje korzystające z jawnych transakcji i wymagają zakończenia transakcji po otrzymaniu błędu 1222, muszą jawnie wycofać transakcję w ramach obsługi błędów. Bez tego inne instrukcje mogą zostać przypadkowo wykonane w tej samej sesji, podczas gdy transakcja pozostaje aktywna, co może prowadzić do nieograniczonego wzrostu dziennika transakcji i utraty danych, jeśli transakcja zostanie później wycofana.

Aby określić bieżące ustawienie LOCK_TIMEOUT, wykonaj funkcję @@LOCK_TIMEOUT:

SELECT @@LOCK_TIMEOUT;
GO

Dostosowywanie poziomu izolacji transakcji

READ COMMITTED jest domyślnym poziomem izolacji dla silnika bazy danych. Jeśli aplikacja musi działać na innym poziomie izolacji, może użyć następujących metod, aby ustawić poziom izolacji:

• Uruchom instrukcję SET TRANSACTION ISOLATION LEVEL.
• ADO.NET aplikacje korzystające z przestrzeni nazw System.Data.SqlClient mogą określić opcję IsolationLevel przy użyciu metody SqlConnection.BeginTransaction.
• Aplikacje korzystające z usługi ADO mogą ustawiać właściwość Autocommit Isolation Levels.
• Podczas uruchamiania transakcji aplikacje korzystające z OLE DB mogą wywoływać ITransactionLocal::StartTransaction, ustawiając isoLevel na żądany poziom izolacji transakcji. Podczas określania poziomu izolacji w trybie automatycznego zatwierdzania aplikacje korzystające z ole DB mogą ustawić właściwość DBPROPSET_SESSIONDBPROP_SESS_AUTOCOMMITISOLEVELS na żądany poziom izolacji transakcji.
• Aplikacje korzystające z odBC mogą ustawić atrybut SQL_COPT_SS_TXN_ISOLATION przy użyciu SQLSetConnectAttr.

Po określeniu poziomu izolacji zachowanie blokowania dla wszystkich instrukcji języka manipulowania zapytaniami i danymi (DML) w sesji działa na tym poziomie izolacji. Poziom izolacji pozostaje w mocy do momentu zakończenia sesji lub do momentu ustawienia poziomu izolacji na inny poziom.

W poniższym przykładzie ustawiono poziom izolacji SERIALIZABLE:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

W razie potrzeby poziom izolacji można zmienić dla poszczególnych zapytań lub instrukcji DML, określając wskazówkę na poziomie tabeli. Określanie wskazówki na poziomie tabeli nie ma wpływu na inne instrukcje w sesji.

Aby określić aktualnie ustawiony poziom izolacji transakcji, użyj instrukcji DBCC USEROPTIONS, jak pokazano w poniższym przykładzie. Zestaw wyników może się różnić od zestawu wyników w Twoim systemie.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Oto zestaw wyników.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Wskazówki dotyczące blokowania

Wskazówki dotyczące blokowania można określić dla poszczególnych odwołań do tabeli w instrukcjach SELECT, INSERT, UPDATE, DELETE i MERGE. Sugestie określają typ blokowania lub wersjonowania wierszy, które silnik bazy danych używa dla danych tabeli. Wskazówki dotyczące blokowania na poziomie tabeli mogą być używane, gdy wymagana jest dokładnsza kontrola typów blokad nabytych na obiekcie. Te wskazówki dotyczące blokowania zastępują bieżący poziom izolacji transakcji dla sesji.

Notatka

Wskazówki dotyczące blokowania nie są zalecane do użycia w przypadku włączenia zoptymalizowanego blokowania. Podczas gdy wskazówki dotyczące tabel i zapytań są przestrzegane, zmniejszają one zalety zoptymalizowanego blokowania. Aby uzyskać więcej informacji, zobacz Unikaj wskazówek dotyczących blokowania z użyciem zoptymalizowanej blokady.

Aby uzyskać więcej informacji na temat konkretnych wskazówek dotyczących blokowania i ich działania, zobacz Wskazówki dotyczące tabel (Transact-SQL).

Notatka

Zalecamy, aby wskazówki dotyczące blokowania na poziomie tabeli były używane do zmiany domyślnego zachowania blokowania tylko wtedy, gdy jest to konieczne. Wymuszanie poziomu blokowania może niekorzystnie wpływać na współbieżność.

Silnik bazy danych może potrzebować uzyskać blokady podczas odczytywania metadanych, nawet podczas przetwarzania instrukcji ze wskazówką dotyczącą blokady, która uniemożliwia żądania dotyczące udostępnionych blokad podczas odczytywania danych. Na przykład instrukcja SELECT uruchomiona na poziomie izolacji READ UNCOMMITTED lub użycie wskazówki NOLOCK nie uzyskuje blokad współdzielenia podczas odczytywania danych, ale może czasami żądać blokad podczas odczytywania widoku katalogu systemowego. Oznacza to, że istnieje możliwość zablokowania takiej instrukcji SELECT, gdy współbieżna transakcja modyfikuje metadane tabeli.

Jak pokazano w poniższym przykładzie, jeśli poziom izolacji transakcji jest ustawiony na SERIALIZABLE, a wskazówka blokady na poziomie tabeli NOLOCK jest używana z instrukcją SELECT, blokady zakresu kluczy, które są zazwyczaj używane do utrzymania transakcji SERIALIZABLE, nie są uzyskiwane.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

Jedyną uzyskaną blokadą, która odwołuje się do HumanResources.Employee, jest blokada stabilności schematu (Sch-S). W takim przypadku serializacja nie jest już gwarantowana.

Opcja LOCK_ESCALATIONALTER TABLE unika blokad tabeli podczas eskalacji blokady i włącza blokady HoBT (partycja) w tabelach podzielonych na partycje. Ta opcja nie jest wskazówką blokującą i może służyć do zmniejszenia eskalacji blokady. Aby uzyskać więcej informacji, zobacz ALTER TABLE (Transact-SQL).

Dostosuj blokowanie dla indeksu

Aparat bazy danych używa dynamicznej strategii blokowania, która automatycznie wybiera najlepszy stopień szczegółowości blokowania zapytań w większości przypadków. Zalecamy, aby nie zastąpić domyślnych poziomów blokowania, chyba że wzorce dostępu do tabel lub indeksów są dobrze zrozumiałe i spójne, i istnieje problem z rywalizacją o zasoby do rozwiązania. Zastępowanie poziomu blokowania może znacząco utrudniać współbieżny dostęp do tabeli lub indeksu. Na przykład określenie tylko blokad na poziomie dużej tabeli, z których użytkownicy korzystają często, może powodować wąskie gardła, ponieważ użytkownicy muszą czekać, aż blokada na poziomie tabeli zostanie zwolniona, zanim uzyskają dostęp.

Istnieje kilka przypadków, w których wyłączenie blokowania stron lub wierszy może być korzystne, jeśli wzorce dostępu są dobrze zrozumiałe i spójne. Na przykład aplikacja bazy danych używa tabeli wyszukiwania, która jest aktualizowana co tydzień w procesie wsadowym. Czytniki współbieżne uzyskują dostęp do tabeli za pomocą blokady udostępnionej (S), a cotygodniowe aktualizacje wsadowe uzyskują dostęp do tabeli z blokadą wyłączną (X). Wyłączenie blokady stron i wierszy w tabeli zmniejsza obciążenie związane z blokowaniem przez cały tydzień, umożliwiając czytelnikom równoczesny dostęp do tabeli za pośrednictwem udostępnionych blokad tabeli. Po uruchomieniu zadania wsadowego można wydajnie ukończyć aktualizację, ponieważ uzyskuje ona wyłączną blokadę tabeli.

Wyłączenie blokady stron i wierszy może być akceptowalne lub nie, ponieważ cotygodniowa aktualizacja wsadowa blokuje równoczesnym czytelnikom dostęp do tabeli podczas uruchamiania aktualizacji. Jeśli zadanie wsadowe zmienia tylko kilka wierszy lub stron, można zmienić poziom blokowania, aby zezwolić na blokowanie na poziomie wiersza lub strony, co umożliwi odczytywanie innych sesji z tabeli bez blokowania. Jeśli zadanie wsadowe ma dużą liczbę aktualizacji, uzyskanie wyłącznej blokady w tabeli może być najlepszym sposobem zapewnienia wydajnego uruchomienia zadania wsadowego.

W niektórych obciążeniach może wystąpić typ zakleszczenia, gdy dwie współbieżne operacje uzyskują blokady wierszy w tej samej tabeli, a następnie blokują się nawzajem, ponieważ obie muszą zablokować stronę. Zabranianie blokad wierszy wymusza oczekiwanie jednej z operacji, unikając zakleszczenia. Aby uzyskać więcej informacji na temat zakleszczeń, zapoznaj się z przewodnikiem Zakleszczenia.

Stopień szczegółowości blokowania użytego w indeksie można ustawić przy użyciu instrukcji CREATE INDEX i ALTER INDEX. Ponadto instrukcje CREATE TABLE i ALTER TABLE mogą służyć do ustawiania stopnia szczegółowości blokowania PRIMARY KEY i ograniczeń UNIQUE. W celu zapewnienia zgodności z poprzednimi wersjami procedura składowana systemu sp_indexoption może również ustawić stopień szczegółowości. Aby wyświetlić bieżącą opcję blokowania dla danego indeksu, użyj funkcji INDEXPROPERTY. Blokady na poziomie strony, blokady na poziomie wiersza lub blokady na poziomie strony i na poziomie wiersza mogą być niedozwolone dla danego indeksu.

Niedozwolone blokady	Indeks uzyskiwany przez
Poziom strony	Blokady na poziomie wiersza i na poziomie tabeli
Poziom wiersza	Blokady na poziomie strony i na poziomie tabeli
Poziom strony i poziom wiersza	Blokady na poziomie tabeli

Zaawansowane informacje o transakcji

Transakcje zagnieżdżone

Jawne transakcje można zagnieżdżać. Ma to na celu przede wszystkim obsługę transakcji w procedurach składowanych, które mogą być wywoływane z procesu już w transakcji lub z procesów, które nie mają aktywnej transakcji.

Na poniższym przykładzie pokazano, jak używać zagnieżdżonych transakcji. Jeśli TransProc jest wywoływana, gdy transakcja jest aktywna, wynik zagnieżdżonej transakcji w TransProc jest kontrolowany przez transakcję zewnętrzną, a jej instrukcje INSERT są zarówno zatwierdzane, jak i wycofywane na podstawie zatwierdzenia lub wycofywania zewnętrznej transakcji. Jeśli TransProc jest wykonywany przez proces, który nie ma zaległej transakcji, COMMIT TRANSACTION na końcu procedury zatwierdza instrukcje INSERT.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

Zatwierdzanie transakcji wewnętrznych jest ignorowane przez silnik bazy danych, gdy transakcja zewnętrzna jest aktywna. Transakcja jest zatwierdzana lub wycofywana na podstawie zatwierdzenia lub wycofania na końcu najbardziej zewnętrznej transakcji. Jeśli transakcja zewnętrzna zostanie zatwierdzona, to transakcje zagnieżdżone również zostaną zatwierdzone. Jeśli transakcja zewnętrzna zostanie wycofana, wszystkie transakcje wewnętrzne również zostaną wycofane, niezależnie od tego, czy transakcje wewnętrzne zostały zatwierdzone indywidualnie.

Każde wywołanie COMMIT TRANSACTION lub COMMIT WORK odnosi się do ostatniego uruchomionego BEGIN TRANSACTION. Jeśli instrukcje BEGIN TRANSACTION są zagnieżdżone, instrukcja COMMIT ma zastosowanie tylko do ostatniej transakcji zagnieżdżonej, która jest najbardziej wewnętrzną transakcją. Nawet jeśli instrukcja COMMIT TRANSACTION transaction_name w transakcji zagnieżdżonej odwołuje się do nazwy transakcji zewnętrznej, zatwierdzenie dotyczy tylko najbardziej wewnętrznej transakcji.

Parametr transaction_name instrukcji ROLLBACK TRANSACTION nie może odwoływać się do transakcji wewnętrznej w zestawie nazwanych transakcji zagnieżdżonych. transaction_name może odwoływać się tylko do nazwy najbardziej zewnętrznej transakcji. Jeśli instrukcja ROLLBACK TRANSACTION transaction_name używająca nazwy transakcji zewnętrznej jest wykonywana na dowolnym poziomie zestawu zagnieżdżonych transakcji, wszystkie zagnieżdżone transakcje zostaną wycofane. Jeśli instrukcja ROLLBACK WORK lub ROLLBACK TRANSACTION bez parametru transaction_name jest wykonywana na dowolnym poziomie zestawu zagnieżdżonych transakcji, cofa wszystkie zagnieżdżone transakcje, w tym najbardziej zewnętrzną transakcję.

Funkcja @@TRANCOUNT rejestruje bieżący poziom zagnieżdżania transakcji. Każda instrukcja BEGIN TRANSACTION zwiększa @@TRANCOUNT o jeden. Każda instrukcja COMMIT TRANSACTION lub COMMIT WORK dekrementuje @@TRANCOUNT o jeden. Instrukcja ROLLBACK WORK lub ROLLBACK TRANSACTION, która nie ma nazwy transakcji, powoduje wycofanie wszystkich zagnieżdżonych transakcji i dekrementuje @@TRANCOUNT do 0. ROLLBACK TRANSACTION która używa nazwy najbardziej zewnętrznej transakcji w zestawie zagnieżdżonych transakcji cofa wszystkie zagnieżdżone transakcje i zmniejsza @@TRANCOUNT do 0. Aby określić, czy jesteś już w transakcji, użyj SELECT @@TRANCOUNT, aby sprawdzić, czy jest to 1 lub więcej. Jeśli @@TRANCOUNT ma wartość 0, nie jesteś w transakcji.

Korzystanie z sesji powiązanych

Powiązane sesje ułatwiają koordynację działań między wieloma sesjami na tym samym serwerze. Sesje powiązane umożliwiają dwóm lub więcej sesjom dzielenie tej samej transakcji i blokad, i mogą działać na tych samych danych bez konfliktów związanych z blokadą. Powiązane sesje można tworzyć na podstawie wielu sesji w tej samej aplikacji lub z wielu aplikacji z oddzielnymi sesjami.

Aby wziąć udział w sesji powiązanej, sesja wykonuje wywołanie sp_getbindtoken lub srv_getbindtoken (za pośrednictwem usług Open Data Services), aby otrzymać token powiązania. Token powiązania to ciąg znaków, który jednoznacznie identyfikuje każdą powiązaną transakcję. Token powiązania jest następnie wysyłany do innych sesji, które mają być powiązane z bieżącą sesją. Inne sesje wiążą się z transakcją przez wywołanie sp_bindsession, przy użyciu tokenu powiązania otrzymanego z pierwszej sesji.

Notatka

Aby sp_getbindtoken lub srv_getbindtoken zakończyło się sukcesem, sesja musi mieć aktywną transakcję użytkownika.

Tokeny powiązania muszą być przesyłane z kodu aplikacji, który tworzy pierwszą sesję, do kodu aplikacji, który następnie wiąże swoje sesje z tą pierwszą sesją. Nie ma instrukcji Transact-SQL ani funkcji interfejsu API, za pomocą których aplikacja może uzyskać token powiązania dla transakcji rozpoczętej przez inny proces. Niektóre metody, których można użyć do przesyłania tokenu powiązania, obejmują następujące elementy:

• Jeśli wszystkie sesje są inicjowane z tej samej aplikacji, tokeny powiązania mogą być przechowywane w pamięci globalnej lub przekazywane do funkcji jako parametr.

• Jeśli sesje są wykonywane z oddzielnych procesów aplikacji, tokeny powiązania mogą być przesyłane przy użyciu komunikacji międzyprocesowej (IPC), takiej jak zdalne wywołanie procedury (RPC) lub dynamiczna wymiana danych (DDE).

• Tokeny powiązania mogą być przechowywane w tabeli w wystąpieniu aparatu bazy danych, które mogą być odczytywane przez procesy chcące się powiązać z pierwszą sesją.

Tylko jedna sesja w zestawie powiązanych sesji może być aktywna w dowolnym momencie. Jeśli jedna sesja wykonuje instrukcję na wystąpieniu lub ma oczekujące wyniki z wystąpienia, żadna inna sesja powiązana z tym samym tokenem nie może uzyskać dostępu do wystąpienia, dopóki bieżąca sesja nie zakończy przetwarzania lub anuluje bieżącą instrukcję. Jeśli wystąpienie jest zajęte przetwarzaniem instrukcji z innej powiązanej sesji, wystąpi błąd wskazujący, że przestrzeń transakcji jest używana, a sesja powinna ponowić próbę później.

Po powiązaniu sesji każda sesja zachowuje ustawienie poziomu izolacji. Zmiana ustawienia poziomu izolacji jednej sesji przy użyciu SET TRANSACTION ISOLATION LEVEL nie ma wpływu na ustawienie żadnej innej sesji powiązanej z tym samym tokenem.

Typy powiązanych sesji

Dwa typy powiązanych sesji są lokalne i rozproszone.

• Lokalna sesja powiązana Zezwala lokalnym powiązanym sesjom na udostępnianie przestrzeni transakcji pojedynczej transakcji w jednym wystąpieniu silnika bazy danych.

• sesji powiązanej rozproszonej Umożliwia powiązanym sesjom współużytkowania tej samej transakcji w co najmniej dwóch wystąpieniach, dopóki cała transakcja nie zostanie zatwierdzona lub wycofana przy użyciu koordynatora transakcji rozproszonych firmy Microsoft (MS DTC).

Sesje powiązane rozproszone nie są identyfikowane przez token powiązania w postaci ciągu znaków; są one identyfikowane przez rozproszone numery identyfikacyjne transakcji. Jeśli sesja powiązana jest zaangażowana w transakcję lokalną i wykonuje RPC na serwerze zdalnym z SET REMOTE_PROC_TRANSACTIONS ON, lokalna powiązana transakcja jest automatycznie promowana do rozproszonej powiązanej transakcji przez usługę MS DTC, a sesja MS DTC zostanie uruchomiona.

Kiedy należy używać powiązanych sesji

We wcześniejszych wersjach programu SQL Server sesje powiązane były używane głównie w tworzeniu rozszerzonych procedur składowanych, które muszą wykonywać Transact-SQL instrukcje w imieniu procesu, który je wywołuje. Przekazanie przez proces wywołujący tokenu powiązania jako jednego z parametrów rozszerzonej procedury składowanej umożliwia tej procedurze dołączenie do przestrzeni transakcji procesu wywołującego, co w ten sposób integruje rozszerzoną procedurę składowaną z procesem wywołującym.

W silniku bazy danych procedury składowane korzystające z CLR są bardziej bezpieczne, skalowalne i stabilne niż procedury składowane rozszerzone. Procedury składowane CLR używają obiektu SqlContext do połączenia się z kontekstem sesji wywołującej, a nie sp_bindsession.

Związane sesje mogą służyć do tworzenia aplikacji trójwarstwowych, w których logika biznesowa jest włączona do osobnych programów, które współpracują w ramach jednej transakcji biznesowej. Te programy muszą być kodowane, aby dokładnie koordynować dostęp do bazy danych. Ponieważ obie sesje współdzielą te same blokady, oba programy nie mogą jednocześnie modyfikować tych samych danych. W dowolnym momencie tylko jedna sesja może wykonywać pracę w ramach transakcji; nie może istnieć wykonywanie równoległe. Transakcję można przełączać tylko między sesjami w dobrze zdefiniowanych punktach wydajności, takich jak po zakończeniu wszystkich instrukcji DML i pobraniu ich wyników.

Efektywne kodowanie transakcji

Ważne jest, aby transakcje były jak najkrótsze. Po rozpoczęciu transakcji, system zarządzania bazami danych (DBMS) musi przechowywać wiele zasobów do końca transakcji, aby chronić właściwości transakcji, takie jak niepodzielność, spójność, izolacja i trwałość (ACID). Jeśli dane są modyfikowane, zmodyfikowane wiersze muszą być chronione za pomocą blokad wyłącznych, które uniemożliwiają innym transakcjom odczytywanie wierszy, a blokady wyłączne muszą być przechowywane do momentu zatwierdzenia lub wycofania transakcji. W zależności od ustawień poziomu izolacji transakcji instrukcje SELECT mogą uzyskiwać blokady, które muszą być przechowywane do momentu zatwierdzenia lub wycofania transakcji. Szczególnie w systemach z wieloma użytkownikami transakcje muszą być przechowywane tak krótko, jak to możliwe, aby zmniejszyć rywalizację o blokowanie zasobów między połączeniami współbieżnymi. Długotrwałe, nieefektywne transakcje mogą nie być problemem z małą liczbą użytkowników, ale są one bardzo problematyczne w systemie z tysiącami użytkowników. Począwszy od programu SQL Server 2014 (12.x), aparat bazy danych obsługuje opóźnione trwałe transakcje. Opóźnione trwałe transakcje mogą zwiększyć skalowalność i wydajność, ale nie gwarantują trwałości. Aby uzyskać więcej informacji, zobacz Kontrola Trwałości Transakcji.

Wytyczne dotyczące kodu

Oto wskazówki dotyczące kodowania wydajnych transakcji:

• Nie wymagaj danych wejściowych od użytkowników podczas transakcji. Pobierz wszystkie wymagane dane wejściowe od użytkowników przed rozpoczęciem transakcji. Jeśli podczas transakcji wymagane są dodatkowe dane wejściowe użytkownika, wycofaj bieżącą transakcję i uruchom ponownie transakcję po podaniu danych wejściowych użytkownika. Nawet jeśli użytkownicy reagują natychmiast, czasy reakcji człowieka są znacznie wolniejsze niż szybkość komputera. Wszystkie zasoby przechowywane przez transakcję są utrzymywane przez bardzo długi czas, co może powodować problemy z blokowaniem. Jeśli użytkownicy nie reagują, transakcja pozostaje aktywna, blokuje krytyczne zasoby, dopóki nie zareagują, co może nie nastąpić przez kilka minut, a nawet godzin.

• Nie należy otwierać transakcji podczas przeglądania danych, jeśli w ogóle jest to możliwe. Transakcje nie powinny być uruchamiane do momentu ukończenia wszystkich wstępnych analiz danych.

• Utrzymaj transakcję możliwie jak najkrótszą. Po zapoznaniu się z modyfikacjami, które należy wprowadzić, rozpocznij transakcję, wykonaj instrukcje modyfikacji, a następnie natychmiast zatwierdź lub wycofaj. Nie otwieraj transakcji, zanim będzie to wymagane.

• Aby zmniejszyć blokowanie, rozważ użycie poziomu izolacji opartej na wersjach wierszy dla zapytań tylko do odczytu.

• Inteligentne wykorzystanie niższych poziomów izolacji transakcji. Wiele aplikacji można kodować, aby używać READ COMMITTED poziomu izolacji transakcji. Niewiele transakcji wymaga poziomu izolacji transakcji SERIALIZABLE.

• Inteligentne korzystanie z optymistycznych opcji współbieżności. W systemie z niskim prawdopodobieństwem współbieżnych aktualizacji, obciążenie związane z obsługą okazjonalnego błędu, gdy "ktoś inny zmienił dane po ich odczytaniu", może być znacznie niższe niż obciążenie związane z blokowaniem wierszy podczas każdego odczytu.

• Uzyskaj dostęp do najmniejszej możliwej ilości danych podczas transakcji. Zmniejsza to liczbę zablokowanych wierszy, co w rezultacie redukuje konflikt między transakcjami.

• Unikaj zawsze, gdy jest to możliwe, pesymistycznych wskazówek dotyczących blokowania, takich jak HOLDLOCK. Wskazówki, takie jak poziom izolacji HOLDLOCK lub SERIALIZABLE, mogą powodować, że procesy oczekują nawet na udostępnionych blokadach, i zmniejszyć współbieżność.

• Unikaj używania transakcji niejawnych, jeśli to możliwe. Niejawne transakcje mogą wprowadzać nieprzewidywalne zachowanie ze względu na ich charakter. Zobacz Niejawne transakcje i problemy ze współbieżnością.

Niejawne transakcje i unikanie problemów ze współbieżnością i zasobami

Aby zapobiec problemom ze współbieżnością i zasobami, dokładnie zarządzaj niejawnymi transakcjami. W przypadku korzystania z niejawnych transakcji kolejna instrukcja Transact-SQL po COMMIT lub ROLLBACK automatycznie uruchamia nową transakcję. Może to spowodować otwarcie nowej transakcji podczas przeglądania danych przez aplikację, a nawet wtedy, gdy wymaga danych wejściowych od użytkownika. Po zakończeniu ostatniej transakcji wymaganej do ochrony modyfikacji danych wyłącz niejawne transakcje, dopóki transakcja nie zostanie ponownie wymagana do ochrony modyfikacji danych. Ten proces umożliwia aparatowi bazy danych korzystanie z trybu automatycznego zatwierdzania podczas przeglądania danych przez aplikację i pobierania danych wejściowych od użytkownika.

Ponadto po włączeniu poziomu izolacji SNAPSHOT, chociaż nowa transakcja nie będzie zawierać blokad, długotrwała transakcja uniemożliwi usunięcie starych wersji z magazynu wersji.

Zarządzanie długotrwałych transakcji

Długotrwała transakcja jest aktywną transakcją, która nie została zatwierdzona ani wycofana w odpowiednim czasie. Na przykład, jeśli początek i koniec transakcji są kontrolowane przez użytkownika, typową przyczyną długotrwałej transakcji jest sytuacja, gdy użytkownik rozpoczyna transakcję i następnie opuszcza ją, podczas gdy transakcja czeka na jego odpowiedź.

Długotrwała transakcja może powodować poważne problemy z bazą danych w następujący sposób:

• Jeśli instancja serwera zostanie zamknięta po tym, jak aktywna transakcja wykona wiele niezatwierdzonych modyfikacji, faza odzyskiwania podczas następnego ponownego uruchomienia może trwać znacznie dłużej niż czas określony przez opcję konfiguracji serwera recovery interval lub opcję ALTER DATABASE ... SET TARGET_RECOVERY_TIME. Te opcje kontrolują odpowiednio aktywne i pośrednie punkty kontrolne. Aby uzyskać więcej informacji na temat typów punktów kontrolnych, zobacz Database checkpoints (SQL Server).

• Co ważniejsze jednak, mimo że oczekujące transakcje mogą generować bardzo mało dziennika, opóźniają przycinanie dziennika na czas nieokreślony, powodując wzrost dziennika transakcji i ewentualnie możliwością wypełnienia. Jeśli dziennik transakcji zostanie wypełniony, baza danych nie może wykonać więcej operacji zapisu. Aby uzyskać więcej informacji, zobacz architektura dziennika transakcji programu SQL Server i przewodnik zarządzania, Rozwiązywanie problemów z pełnym dziennikiem transakcji (błąd programu SQL Server 9002)i dziennik transakcji.

Ważny

W usłudze Azure SQL Database transakcje bezczynne (transakcje, które nie zostały zapisane w dzienniku transakcji przez sześć godzin) są automatycznie przerywane w celu zwolnienia zasobów.

Odnajdywanie długotrwałych transakcji

Aby wyszukać długotrwałe transakcje, użyj jednego z następujących elementów:

• sys.dm_tran_database_transactions

  Ten dynamiczny widok zarządzania zwraca informacje o transakcjach na poziomie bazy danych. W przypadku długotrwałej transakcji kolumny o szczególnym znaczeniu obejmują czas pierwszego rekordu dziennika (database_transaction_begin_time), bieżący stan transakcji (database_transaction_state) oraz numer sekwencji dziennika (LSN) rozpocząć rekord w dzienniku transakcji (database_transaction_begin_lsn).

  Aby uzyskać więcej informacji, zobacz sys.dm_tran_database_transactions (Transact-SQL).

• DBCC OPENTRAN

  Ta instrukcja umożliwia zidentyfikowanie identyfikatora właściciela użytkownika transakcji, dzięki czemu można potencjalnie śledzić źródło transakcji w celu odpowiedniego zakończenia (zatwierdzenie lub wycofanie). Aby uzyskać więcej informacji, zobacz DBCC OPENTRAN (Transact-SQL).

Kończenie transakcji

Aby zakończyć transakcję w określonej sesji, użyj instrukcji KILL. Użyj tej instrukcji bardzo ostrożnie, zwłaszcza gdy działają krytyczne procesy. Aby uzyskać więcej informacji, zobacz KILL (Transact-SQL).

Zakleszczenia

Zakleszczenia są złożonym tematem związanym z blokadami, ale różnią się od blokowania.

• Aby uzyskać więcej informacji na temat zakleszczeń, w tym monitorowania, diagnostyki i próbek, zobacz przewodnik Zakleszczenia.
• Aby uzyskać więcej informacji na temat zakleszczeń specyficznych dla usługi Azure SQL Database, zobacz Analizowanie i zapobieganie zakleszczeniom w usłudze Azure SQL Database.

Powiązana zawartość

• Informacje i rozwiązywanie problemów z blokowaniem programu SQL Server
• Omówienie i rozwiązywanie problemów z blokowaniem usługi Azure SQL Database
• Dynamiczne Widoki Zarządzania i Funkcje Związane z Transakcjami (Transact-SQL)
• Koszt wersjonowania wierszy
• sys.dm_tran_locks (Transact-SQL)