Przewodnik dotyczący blokowania transakcji i wersjonowania wierszy

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Database w 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.

Note

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 blokad
• 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.

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

Consistency
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.

Note

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.

Isolation
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.

Durability
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 wystąpienie silnika bazy danych, dostarczać mechanizmy zapewniają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 lub wycofana, zostanie ona automatycznie wycofana przez silnik bazy danych. 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.

Transakcje jawne

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.

Note

UPDATE STATISTICS można używać wewnątrz jawnej transakcji. Zatwierdzenie UPDATE STATISTICS jest jednak niezależne od obejmującej transakcji 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 cofnięta. 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.

Transakcje niejawne

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 w zakresie usługi Batch

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 przygotowywania

  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.

• Commit

  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ę.

• Wycofać

  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ę.

Note

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 są wpływane przez 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 instrukcje INSERT pierwsze dwie zostają wycofane, chociaż 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:

• Locking

  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 momentu zakończenia i zatwierdzenia transakcji przez inny 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. Ten problem można by uniknąć, gdyby autor nie mógł zmienić dokumentu, dopóki edytor nie skończy go przeglądać po raz ostatni.

• Odczyty phantom

  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 READ UNCOMMITTED poziomie (lub instrukcje używające wskazówki tabeli NOLOCK) nie nakładają współdzielonych blokad, aby zapobiec modyfikowaniu danych przez inne transakcje. 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, jeśli podczas skanowania indeksu 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

    Jeśli używasz metody READ UNCOMMITTED, jeśli 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, gdy używasz READ COMMITTED poziomu izolacji.

Typy współbieżności

Gdy wiele transakcji próbuje zmodyfikować dane w bazie danych w tym samym czasie, należy zaimplementować system kontrolek, aby modyfikacje wprowadzone przez jedną transakcję nie wpływały negatywnie 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, które mogłyby powodować konflikt z blokadą, dopóki właściciel go nie zwolni. Jest to nazywane pesymistyczną kontrolą, ponieważ jest ona 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 optymistyczne, ponieważ jest zwykle używane w systemach, w których występuje niski spór o dostęp do danych, a koszt sporadycznego wycofania transakcji jest niższy niż koszt blokowania danych podczas operacji 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.

Important

Wybranie poziomu izolacji transakcji nie wpływa 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 efektó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 gwarantuje, SERIALIZABLEż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, które zostały zmodyfikowane, ale nie zatwierdzone przez inne transakcje. Wszystkie skutki uboczne współbieżności mogą wystąpić w READ UNCOMMITTED, ale nie ma blokady odczytu ani wersjonowania, 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	Definition
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, operacje odczytu phantom mogą wystąpić.
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 I transakcje DDL w replikowanych tabelach mogą zakończyć się niepowodzeniem po żądaniu SERIALIZABLE poziomu izolacji. Dzieje się tak, ponieważ zapytania replikacji używają wskazówek, które mogą być niezgodne z SERIALIZABLE poziomem izolacji.

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	Definition
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 dotyczące operacji DDL, które mogą być wykonywane w jawnej transakcji w izolacji migawkowej. Następujące instrukcje DDL nie są dozwolone w ramach izolacji migawki po BEGIN TRANSACTION instrukcji: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME lub dowolnej instrukcji DDL środowiska uruchomieniowego języka wspólnego (CLR). Te oświadczenia są dozwolone w przypadku korzystania z izolacji migawki podczas niejawnych transakcji. 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	Brudny odczyt	Niemozliwy do powtórzenia odczyt	Phantom
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes
SNAPSHOT	No	No	No
SERIALIZABLE	No	No	No

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
ADO.NET aplikacje korzystające z przestrzeni nazw zarządzanej Microsoft.Data.SqlClient lub System.Data.SqlClient mogą wywoływać metodę SqlConnection.BeginTransaction i ustawiać opcję IsolationLevel na Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable lub 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 READ COMMITTED poziomu izolacji transakcje nie będą utrzymywać blokad wierszy i stron niezbędnych do zapisu aż do zakończenia transakcji. Zmniejsza to wymaganą pamięć blokady i zmniejsza potrzebę eskalacji blokady. Ponadto, gdy jest włączone zoptymalizowane blokowanie, optymalizacja blokada po kwalifikacjach (LAQ) ocenia predykaty zapytania w najnowszej zatwierdzonej wersji wiersza bez potrzeby uzyskiwania blokady, co poprawia 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, może być konieczne, aby wystąpienie aparatu bazy danych uzyskało udostępnione blokady w wierszach oraz blokady zamierzonego udostępnienia na stronach i tabelach.

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

Resource	Description
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 XACT zasobów blokady. Aby uzyskać więcej informacji, zobacz Dodatki diagnostyczne do 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	Description
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.
Intent	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.
Key-range	Chroni zakres wierszy odczytywanych przez zapytanie podczas korzystania z poziomu izolacji transakcji SERIALIZABLE. Gwarantuje, że inne transakcje nie mogą wstawiać wierszy, które spełniają warunki zapytań transakcji SERIALIZABLE, jeśli zapytania zostałyby uruchomione ponownie.

Blokady udostępnione

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 wykluczające (X), a każda z nich czeka na zwolnienie blokady udostępnionej (S) przez inną transakcję, występuje zakleszczenie.

  W domyślnym READ COMMITTED poziomie S izolacji blokady są krótkie, zwalniane natychmiast po ich użyciu. Chociaż zakleszczenie opisane powyżej jest nadal możliwe, jest znacznie mniej prawdopodobne z krótkim czasem trwania blokad.

  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 odebrać element 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).

Blokady na wyłączność

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 zamiaru

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	Description
Intencja udostępniona (IS)	Zabezpiecza żądane lub pozyskane blokady udostępnione dla niektórych (ale nie wszystkich) zasobów na niższych poziomach hierarchii.
Wyłączność zamiarów (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 ona przechowywana, blokada Sch-M uniemożliwia równoczesny 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 zamków transakcyjnych, w tym zamków 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 aktualizacji zbiorczej (BU) pozwalają wielu wątkom na równoczesne ładowanie danych zbiorczych do tej samej tabeli, uniemożliwiając jednocześnie dostęp do tej tabeli innym procesom, które nie wykonują ładowania zbiorczego. 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.

Tip

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 wstawiać 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ć blokady wyłącznej, 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)	Yes	Yes	Yes	Yes	Yes	No
udostępnione (S)	Yes	Yes	Yes	No	No	No
Aktualizacja (U)	Yes	Yes	No	No	No	No
Wyłączność zamiarów (IX)	Yes	No	No	Yes	No	No
Udostępnione z zamiarem wyłączności (SIX)	Yes	No	No	No	No	No
ekskluzywne (X)	No	No	No	No	No	No

Note

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.

Key	Description
N	Brak konfliktu
I	Illegal
C	Conflict
NL	Brak blokady
SCH-S	Blokada stabilności schematu
SCH-M	Blokada modyfikacji schematu
S	Udostępniona
U	Update
X	Exclusive
IS	Intencja (działania) udostępniona
IU	Aktualizacja intencji
IX	Wyłączność intencji
SIU	Udostępnij z aktualizacją intencji
SIX	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 wspólnego zakresu
RI-N	Wstaw wartość range-null
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	Specjalistyczna aktualizacja zakresu
RX-X	Unikalny zakres ekskluzywny

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 SERIALIZABLE izolacji 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.

Range	Row	Mode	Description
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.

Note

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)	Yes	Yes	No	Yes	Yes	Yes	No
Aktualizacja (U)	Yes	No	No	Yes	No	Yes	No
ekskluzywne (X)	No	No	No	No	No	Yes	No
RangeS-S	Yes	Yes	No	Yes	Yes	No	No
RangeS-U	Yes	No	No	Yes	No	No	No
RangeI-N	Yes	Yes	Yes	No	No	Yes	No
RangeX-X	No	No	No	No	No	No	No

Mechanizmy blokujące konwersję

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

Blokada 1	Blokada 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.

Examples

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

Zapytanie zakresowego skanowania

Aby zapewnić możliwość serializacji zapytania skanowania zakresu, to samo zapytanie powinno zwracać 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.

Note

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 gdy ROWLOCK jest używany, wszystkie usunięte wiersze są oznaczone tylko jako usunięte; są usuwane ze strony indeksu później 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 SERIALIZABLE transakcje. Jeśli uzyskuje się blokady wierszy i stron, są one szybko zwalniane i nie są przechowywane 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 blokadę RangeI-N uzyskuje się tylko wtedy, gdy co najmniej jedna transakcja korzysta z poziomu izolacji SERIALIZABLE w instancji. 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, nie nastąpi eskalacja blokady w tym momencie, 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.

Note

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 wyzwala eskalację blokad podczas pobierania wierszy z TableA i 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 żadnej próby eskalacji blokad na obiekcie TableC, które nie zostały eskalowane, ponieważ nie były jeszcze dostępne w momencie, gdy wystąpiła eskalacja.

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 blokady nie mogą być eskalowane z powodu konfliktów blokad, silnik bazy danych co 1250 nowych nabytych blokad okresowo inicjuje eskalację blokady.

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. Również próbuje eskalować wszystkie blokady przechowywane przez bieżącą transakcję na TableA, ale ponieważ liczba blokad na TableA jest mniejsza niż 5000, eskalacja nie powiedzie się. Nie podjęto próby eskalacji blokady dla TableC, ponieważ nie uzyskano jeszcze do niego dostępu w momencie eskalacji.

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.

• locks Jeśli opcja 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 klastrowanych indeksach lub tabelach, ponieważ są 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. 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. Duże skanowania lub duża liczba wyszukiwań kluczy może zwiększyć prawdopodobieństwo eskalacji blokady; ponadto zwiększa to prawdopodobieństwo zakleszczenia, a 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 silnik bazy danych szacuje, że operator logiczny wyszukiwania klucza może zwrócić wiele wierszy, może użyć optymalizacji wstępnego pobierania do wykonania 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 może wyglądać podobnie do SELECT instrukcji na READ COMMITTED poziomie izolacji, 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że być możliwe utworzenie indeksu obejmującego (indeks zawierający wszystkie kolumny w tabeli, które zostały użyte w zapytaniu), lub co najmniej indeksu obejmującego kolumny użyte w kryteriach łączenia lub w klauzuli WHERE, jeśli uwzględnienie wszystkiego na liście kolumn SELECT jest niepraktyczne. Sprzężenie pętli zagnieżdżonej może również korzystać z optymalizacji wstępnego pobierania, co powoduje podobne zachowanie w zakresie blokowania.

• Nie można przeprowadzić eskalacji blokady, jeśli SPID inny aktualnie posiada 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 wyłączności (IX) na poziomie tabeli nie blokuje żadnych wierszy ani stron, ale nadal nie jest zgodna z eskalowaną blokadą współdzieloną (S) lub 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 Baz Danych może dynamicznie przydzielić dla struktur blokad, jest ograniczona. Zatem, jeśli eskalacja blokady zostanie wyłączona i pamięć przeznaczona na blokady urośnie do znacznych rozmiarów, próby przydzielenia dodatkowych blokad dla dowolnego zapytania mogą 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.

  Note

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

  Note

  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

Blokowanie dynamiczne

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 strony 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.

• Memory

  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 niepartycyjnym, ponieważ każda partycja jest skutecznie oddzielną blokadą. Wzrost pamięci zależy od liczby partycji. Liczniki wydajności zamków w programie SQL Server wyświetlają informacje o pamięci używanej przez zamki 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 przechowywana 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 momencie wystąpienia aktywnego zestawu 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 tempdbpliku, a tempdb baza danych jest pełna, operacje aktualizacji przestają generować wersje, ale nadal kończą się powodzeniem. Natomiast operacje odczytu mogą zakończyć się niepowodzeniem, ponieważ wymagana określona 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.

Note

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.

Note

W przypadku transakcji krótkotrwałych, wersja zmodyfikowanego wiersza może zostać wybuforowana w puli buforów, nie będąc zapisywana 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 ramach izolacji opartej na wersjonowaniu wierszy, operacje odczytu nie uzyskują współdzielonych (S) blokad na odczytywanych danych, co powoduje, że 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.

Note

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ą zakładane, 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.

Note

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 kwalifikuje się oraz gdy istnieje inna aktywna transakcja zapisu dotycząca tego wiersza lub strony. Kwalifikowanie na podstawie najnowszej zatwierdzonej wersji i blokowanie tylko kwalifikowanych wierszy zmniejsza blokowanie i zwiększa współbieżność.

Jeśli konflikty aktualizacji zostaną wykryte przy użyciu wersji RCSI i domyślnego READ COMMITTED poziomu izolacji, są one obsługiwane i ponawiane automatycznie bez żadnego 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ę.

Note

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.

Property	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. W przypadku zoptymalizowanego blokowania: 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:

• Triggers
• 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.

Note

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 redukcji danych najdłużej działające transakcje, które jeszcze nie wygenerowały wersji wierszy, są uznane za 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 w celu przechowywania informacji o wersjonowaniu wiersza. 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.
• Przyspieszone odzyskiwanie bazy danych (ADR) jest włączone.

Te 14 bajtów są usuwane z wiersza bazy danych przy pierwszym zmodyfikowaniu wiersza w ramach wszystkich 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.
• Przyspieszone odzyskiwanie bazy danych (ADR) jest wyłączone.

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 . Kiedy 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, osobny 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 8 080 bajtów danych ntext, text, lub 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. Jednak gdy dane LOB są po raz pierwszy modyfikowane, ich wersja jest dynamicznie uaktualniana, aby umożliwić przechowywanie informacji o wersjonowaniu. Dzieje się tak nawet wtedy, gdy wersje wierszy nie są generowane. Po uaktualnieniu danych LOB, maksymalna liczba bajtów przechowywanych na fragment zmniejsza się z 8 080 bajtów do 8 040 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 ntext kolumny, text lub image, ale każda operacja może wygenerować dużą ilość alokacji stron i działania 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.

DMVs

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).

  Note

  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 wydajny i nie jest kosztowny do uruchomienia, ponieważ nie przechodzi przez poszczególne rekordy magazynu wersji, a zamiast tego zwraca zagregowane miejsce magazynu wersji używane w tempdb poszczególnych bazach 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.

  Note

  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.

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

• Transakcje typu snapshot. 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 niesnapshotowych, które generują rekordy wersji.

  Note

  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órej transakcja SNAPSHOT nie mogła.

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ć SNAPSHOT transakcji w bazie danych, dopóki nie zostanie wybrana opcja 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	Description
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 master danych i msdb i i nie może być wyłączona.

Użytkownicy nie mogą ustawić opcji READ_COMMITTED_SNAPSHOT na ON w master, tempdb lub 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 opartych na wersjonowaniu wierszy, służy do obsługi modyfikacji w wyzwalaczach, wielu aktywnych zestawów wyników (MARS), a także wspiera odczyty danych na potrzeby operacji z indeksami 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 jest włączona dla READ_COMMITTED_SNAPSHOT, wszystkie zapytania wykonywane pod READ COMMITTED poziomem izolacji 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:

• READ_COMMITTED_SNAPSHOT nie można włączyć w systemie 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 bazy danych jest ustawiona READ_COMMITTED_SNAPSHOT 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, gdy wykonywana jest instrukcja ALTER INDEX, otrzymuje błąd, jeśli po wykonaniu instrukcji ALTER INDEX próbuje odwołać się do tabeli HumanResources.Employee. READ COMMITTED Transakcje korzystające z wersjonowania wierszy nie są dotknięte.

  Note

  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 w oczekiwaniu na zwolnienie istniejącej blokady. Domyślnie nie ma limitu czasu dla oczekiwania na blokadę, dlatego transakcja może potencjalnie zostać zablokowana w nieskończoność.

Note

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.

Important

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 używające przestrzeni nazw Microsoft.Data.SqlClient lub System.Data.SqlClient mogą określić opcję IsolationLevel, używając 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 na 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 blokad

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.

Note

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).

Note

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 można zablokować instrukcję 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 NOLOCK dotycząca blokady na poziomie tabeli jest używana z SELECT instrukcją, blokady zakresu kluczy zwykle używane do zachowania integralności SERIALIZABLE transakcji 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, a problem z rywalizacją o zasoby jest rozwiązywany. 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 zewnętrzne i wewnętrzne

Jawna transakcja wewnętrzna może zostać uruchomiona w ramach jawnej transakcji zewnętrznej. 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.

W poniższym przykładzie pokazano użycie transakcji zewnętrznych i wewnętrznych. Jeśli TransProc zostanie wywołane, gdy transakcja jest aktywna, wynik transakcji wewnętrznej w TransProc jest kontrolowany przez transakcję zewnętrzną, a instrukcje INSERT są zatwierdzane lub cofane na podstawie zatwierdzenia lub wycofania transakcji zewnętrznej. 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 inner 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, transakcje wewnętrzne 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 istnieje wiele BEGIN TRANSACTION instrukcji, COMMIT instrukcja ma zastosowanie tylko do ostatniej instrukcji, innymi słowy do najbardziej wewnętrznej transakcji. Nawet jeśli COMMIT TRANSACTION transaction_name instrukcja wewnętrznej transakcji odwołuje się do nazwy transakcji zewnętrznej, zatwierdzenie dotyczy tylko najbardziej wewnętrznej transakcji.

Nie jest dozwolone, aby parametr transaction_name instrukcji ROLLBACK TRANSACTION odwoływał się do wewnętrznej transakcji w zestawie nazwanych transakcji. transaction_name może odwoływać się tylko do nazwy najbardziej zewnętrznej transakcji.

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 zawiera nazwy transakcji, cofa zarówno zewnętrzne, jak i wszystkie wewnętrzne transakcje, a następnie dekrementuje @@TRANCOUNT do 0. Podobnie, ROLLBACK TRANSACTION używający nazwy najbardziej zewnętrznej transakcji, powoduje wycofanie zarówno zewnętrznej, jak i wszystkich wewnętrznych transakcji oraz zmniejsza @@TRANCOUNT do 0. Aby ustalić, czy jesteś już w transakcji, sprawdź SELECT @@TRANCOUNT, czy jest to wartość 1 lub większa. Jeśli @@TRANCOUNT wartość to 0, nie jesteś w transakcji.

Note

Silnik baz danych nie obsługuje zagnieżdżonych transakcji, które można zarządzać niezależnie. Zatwierdzenie wewnętrznej transakcji zmniejsza wartość @@TRANCOUNT, ale nie ma innych efektów. Wycofanie transakcji wewnętrznej zawsze cofa zewnętrzną transakcję, chyba że istnieje punkt zapisywania i jest określony w instrukcji ROLLBACK .

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.

Note

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.

• Rozproszona sesja powiązana Umożliwia powiązanym sesjom dzielenie tej samej transakcji w co najmniej dwóch wystąpieniach, dopóki cała transakcja nie zostanie zatwierdzona lub wycofana przy użyciu Rozproszonego Koordynatora Transakcji Microsoft (MS DTC).

Rozproszone sesje powiązane nie są identyfikowane za pomocą ciągu znaków jako tokenu powiązania; 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 kilka godzin.

• Nie otwieraj 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 konieczne.

• 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 o niskim prawdopodobieństwie równoczesnych aktualizacji obciążenie związane z okazjonalnym błędem typu "ktoś inny zmienił twoje dane po ich odczytaniu" może być znacznie niższe niż obciążenie związane z zawsze blokowaniem wierszy podczas ich odczytywania.

• 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 SNAPSHOT poziomu izolacji, mimo że nowa transakcja nie będzie przechowywać 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 będzie mogła wykonywać żadnych 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.

Important

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 w dzienniku (database_transaction_begin_time), bieżący stan transakcji (database_transaction_state) oraz numer sekwencji dziennika (LSN) rekordu begin 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).

Deadlocks

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.

Treści powiązane

• 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)