Udostępnij za pomocą

USTAW POZIOM IZOLACJI TRANSAKCJI (Transact-SQL)

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

Steruje zachowaniem blokowania i przechowywania wersji wierszy instrukcji Transact-SQL wystawionych przez połączenie z programem SQL Server.

Transact-SQL konwencje składni

Składnia

Składnia dla programu SQL Server, usługi Azure SQL Database i bazy danych SQL w usłudze Microsoft Fabric.

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Składnia dla usług Azure Synapse Analytics i Parallel Data Warehouse.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Uwaga / Notatka

Usługa Azure Synapse Analytics implementuje transakcje ACID. Domyślny poziom izolacji to READ UNCOMMITTED. Możesz ją READ COMMITTED SNAPSHOT ISOLATION zmienić, włączając ONREAD_COMMITTED_SNAPSHOT opcję bazy danych dla bazy danych użytkownika po nawiązaniu połączenia z bazą master danych. Po włączeniu wszystkie transakcje w tej bazie danych są wykonywane w obszarze READ COMMITTED SNAPSHOT ISOLATION , a ustawienie READ UNCOMMITTED na poziomie sesji nie jest uznawane. Aby uzyskać więcej informacji, zobacz ALTER DATABASE SET options (Transact-SQL).

Arguments

ODCZYTYWANIE NIEZATWIERDZONYCH

Określa, że instrukcje mogą odczytywać wiersze, które zostały zmodyfikowane przez inne transakcje, ale nie zostały jeszcze zatwierdzone.

Transakcje uruchomione na READ UNCOMMITTED poziomie nie wystawiają blokad udostępnionych, aby uniemożliwić innym transakcjom modyfikowanie danych odczytanych przez bieżącą transakcję. READ UNCOMMITTED Transakcje nie są również blokowane przez blokady wyłącznych, które uniemożliwiłyby bieżącej transakcji odczytywanie wierszy, które zostały zmodyfikowane, ale nie zostały zatwierdzone przez inne transakcje. Po ustawieniu tej opcji można odczytać niezatwierdzone modyfikacje, które są nazywane zanieczyszczonymi odczytami. Wartości w danych można zmienić, a wiersze mogą pojawić się lub zniknąć w zestawie danych przed końcem transakcji. Ta opcja ma taki sam wpływ jak ustawienie NOLOCK dla wszystkich tabel we wszystkich SELECT instrukcjach w transakcji. Jest to najmniej restrykcyjne poziomy izolacji.

W programie SQL Server można również zminimalizować rywalizację o blokowanie, jednocześnie chroniąc transakcje przed zanieczyszczonymi operacjami odczytu niezatwierdzonych modyfikacji danych przy użyciu jednego z następujących elementów:

ODCZYT ZATWIERDZONY

Określa, że instrukcje nie mogą odczytywać danych, które zostały zmodyfikowane, ale nie są zatwierdzane przez inne transakcje. Zapobiega to brudnym odczytom. Dane mogą być zmieniane przez inne transakcje między poszczególnymi instrukcjami w ramach bieżącej transakcji, co powoduje niezwiązane odczyty lub dane fantomowe. Ta opcja jest domyślna dla programu SQL Server.

Zachowanie funkcji READ COMMITTED zależy od ustawienia READ_COMMITTED_SNAPSHOT opcji bazy danych:

  • Jeśli READ_COMMITTED_SNAPSHOT jest ustawiona wartość OFF (wartość domyślna w programie SQL Server), aparat bazy danych używa blokad udostępnionych, aby uniemożliwić innym transakcjom modyfikowanie wierszy, gdy bieżąca transakcja uruchamia operację odczytu. Udostępnione blokady blokują również instrukcję odczytu wierszy zmodyfikowanych przez inne transakcje do momentu zakończenia drugiej transakcji. Typ blokady udostępnionej określa, kiedy jest zwalniany. Blokady wierszy są zwalniane przed przetworzeniem następnego wiersza. Blokady stron są zwalniane po odczytaniu następnej strony, a blokady tabeli są zwalniane po zakończeniu instrukcji.

  • Jeśli READ_COMMITTED_SNAPSHOT jest ustawiona wartość ON, aparat bazy danych używa przechowywania wersji wierszy, aby przedstawić każdą instrukcję z transakcyjnie spójną migawką danych, ponieważ istniała na początku instrukcji. Blokady nie są używane do ochrony danych przed aktualizacjami przez inne transakcje.

    • READ_COMMITTED_SNAPSHOT ON jest domyślną w Azure SQL Database oraz bazie danych SQL w Microsoft Fabric.

Ważne

Wybranie poziomu izolacji transakcji nie wpływa na blokady uzyskane w celu ochrony modyfikacji danych. Transakcja zawsze uzyskuje wyłączną blokadę na wszystkich danych, które modyfikuje, i przechowuje ją do momentu zakończenia transakcji, niezależnie od poziomu izolacji ustawionego dla tej transakcji. Ponadto aktualizacja wykonana na READ COMMITTED poziomie izolacji używa blokad aktualizacji w wybranych wierszach danych, natomiast aktualizacja wykonana na SNAPSHOT poziomie izolacji używa wersji wierszy do wybierania wierszy do aktualizacji. W przypadku operacji odczytu poziomy izolacji transakcji definiują przede wszystkim poziom ochrony przed skutkami modyfikacji wprowadzonych przez inne transakcje. Aby uzyskać więcej informacji, zobacz Transaction Locking and Row Versioning Guide (Podręcznik blokowania transakcji i przechowywania wersji wierszy).

Izolacja migawki obsługuje dane FILESTREAM. W trybie izolacji migawki dane FILESTREAM odczytane przez dowolną instrukcję w transakcji są transakcyjnie spójną wersją danych, które istniały na początku transakcji.

Jeśli READ_COMMITTED_SNAPSHOT opcja bazy danych to ON, możesz użyć READCOMMITTEDLOCK wskazówki tabeli, aby zażądać udostępniania blokady zamiast przechowywania wersji wierszy dla poszczególnych instrukcji w transakcjach uruchomionych na READ COMMITTED poziomie izolacji.

Uwaga / Notatka

Po ustawieniu READ_COMMITTED_SNAPSHOT opcji tylko połączenie wykonujące ALTER DATABASE polecenie jest dozwolone w bazie danych. Nie może istnieć żadne inne otwarte połączenie w bazie danych, dopóki ALTER DATABASE nie zostanie ukończone. Baza danych nie musi być w trybie pojedynczego użytkownika.

POWTARZALNY ODCZYT

Określa, że instrukcje nie mogą odczytywać danych, które zostały zmodyfikowane, ale nie zostały jeszcze zatwierdzone przez inne transakcje, i że żadne inne transakcje nie mogą modyfikować danych odczytanych przez bieżącą transakcję do momentu zakończenia bieżącej transakcji.

Blokady udostępnione są umieszczane na wszystkich danych odczytywanych przez każdą instrukcję w transakcji i są przechowywane do momentu zakończenia transakcji. Zapobiega to modyfikowaniu wszystkich wierszy odczytanych przez bieżącą transakcję przez inne transakcje. Inne transakcje mogą wstawiać nowe wiersze zgodne z warunkami wyszukiwania instrukcji wystawionych przez bieżącą transakcję. Jeśli bieżąca transakcja następnie ponawia próbę instrukcji, pobiera nowe wiersze, co powoduje odczyty phantom. Ponieważ blokady udostępnione są przechowywane na końcu transakcji zamiast zwalniania na końcu każdej instrukcji, współbieżność jest niższa niż domyślny READ COMMITTED poziom izolacji. Użyj tej opcji tylko wtedy, gdy jest to konieczne.

MIGAWKA

Określa, że dane odczytywane przez dowolną instrukcję w transakcji to transakcyjnie spójna wersja danych, które istniały na początku transakcji. Transakcja może rozpoznawać tylko modyfikacje danych, które zostały zatwierdzone przed rozpoczęciem transakcji. Modyfikacje danych wprowadzone przez inne transakcje po rozpoczęciu bieżącej transakcji nie są widoczne dla instrukcji wykonywanych w bieżącej transakcji. Efekt jest taki, jakby instrukcje w transakcji pobierały migawkę zatwierdzonych danych, ponieważ istniały na początku transakcji.

Z wyjątkiem sytuacji, gdy baza danych jest odzyskiwana, SNAPSHOT transakcje nie żądają blokad podczas odczytywania danych. SNAPSHOT Transakcje odczytujące dane nie blokują innych transakcji podczas zapisywania danych. Transakcje zapisujące dane nie blokują SNAPSHOT transakcji odczytu danych.

W fazie wycofywania odzyskiwania bazy danych transakcje żądają blokady, SNAPSHOT jeśli zostanie podjęta próba odczytu danych zablokowanych przez inną transakcję, która jest cofana. Transakcja SNAPSHOT jest blokowana do momentu wycofania tej transakcji. Blokada jest zwalniana natychmiast po udzieleniu.

Przed ALLOW_SNAPSHOT_ISOLATION rozpoczęciem transakcji korzystającej z ON poziomu izolacji należy ustawić SNAPSHOT opcję bazy danych na wartość . Jeśli transakcja korzystająca SNAPSHOT z poziomu izolacji uzyskuje dostęp do danych w wielu bazach danych, ALLOW_SNAPSHOT_ISOLATION musi być ustawiona na ON wartość w każdej bazie danych.

Nie można ustawić transakcji na SNAPSHOT poziom izolacji, który rozpoczął się od innego poziomu izolacji; powoduje to przerwanie transakcji. Jeśli transakcja rozpoczyna się na SNAPSHOT poziomie izolacji, możesz zmienić ją na inny poziom izolacji, a następnie z powrotem na SNAPSHOT. Transakcja rozpoczyna się po raz pierwszy, gdy uzyskuje dostęp do danych.

Transakcja uruchomiona na SNAPSHOT poziomie izolacji może wyświetlać zmiany wprowadzone przez tej transakcji. Jeśli na przykład transakcja wykonuje UPDATE tabelę, a następnie wystawia instrukcję SELECT względem tej samej tabeli, zmodyfikowane dane są uwzględniane w zestawie wyników.

Uwaga / Notatka

W trybie izolacji migawki dane FILESTREAM odczytane przez dowolną instrukcję w transakcji są transakcyjnie spójną wersją danych, które istniały na początku transakcji, a nie na początku instrukcji.

SERIALIZACJI

Określa następujące warunki:

  • Instrukcje nie mogą odczytywać danych, które zostały zmodyfikowane, ale nie zostały jeszcze zatwierdzone przez inne transakcje.

  • Żadne inne transakcje nie mogą modyfikować danych odczytanych przez bieżącą transakcję do momentu zakończenia bieżącej transakcji.

  • Inne transakcje nie mogą wstawiać nowych wierszy z wartościami klucza, które spadną w zakresie kluczy odczytanych przez dowolne instrukcje w bieżącej transakcji do momentu zakończenia bieżącej transakcji.

Blokady zakresu są umieszczane w zakresie wartości klucza, które są zgodne z warunkami wyszukiwania każdej instrukcji wykonywanej w transakcji. Blokuje to inne transakcje podczas aktualizowania lub wstawiania wierszy, które kwalifikują się do dowolnej instrukcji wykonanej przez bieżącą transakcję. Oznacza to, że jeśli którakolwiek z instrukcji w transakcji jest wykonywana po raz drugi, odczytuje ten sam zestaw wierszy. Blokady zakresu są przechowywane do momentu zakończenia transakcji. Jest to najbardziej restrykcyjne poziomy izolacji, ponieważ blokuje cały zakres kluczy i przechowuje blokady do momentu zakończenia transakcji. Ponieważ współbieżność jest niższa, użyj tej opcji tylko wtedy, gdy jest to konieczne. Ta opcja ma taki sam wpływ jak ustawienie HOLDLOCK dla wszystkich tabel we wszystkich SELECT instrukcjach w transakcji.

Uwagi

Jednocześnie można ustawić tylko jedną z opcji poziomu izolacji i pozostaje ustawiona dla tego połączenia, dopóki nie zostanie jawnie zmieniona. Wszystkie operacje odczytu wykonywane w ramach transakcji działają zgodnie z regułami dla określonego poziomu izolacji, chyba że wskazówka tabeli w FROM klauzuli instrukcji określa różne zachowanie blokowania lub przechowywania wersji dla tabeli.

Poziomy izolacji transakcji definiują typ blokad uzyskanych w operacjach odczytu. Udostępnione blokady uzyskane dla READ COMMITTED lub REPEATABLE READ są zazwyczaj blokadami wierszy, chociaż blokady wierszy można eskalować do blokad strony lub tabeli, jeśli znaczna liczba wierszy na stronie lub tabeli jest przywoływane przez odczyt. Jeśli transakcja modyfikuje wiersz po jego odczytaniu, transakcja uzyskuje blokadę wyłączną w celu ochrony tego wiersza, a blokada wyłączna zostanie zachowana do momentu zakończenia transakcji. Jeśli na przykład transakcja REPEATABLE READ ma blokadę współużytkowaną w wierszu, a transakcja modyfikuje wiersz, blokada współużytkowanego wiersza jest konwertowana na wyłączną blokadę wiersza.

Z jednym wyjątkiem można przełączyć się z jednego poziomu izolacji na inny w dowolnym momencie podczas transakcji. Wyjątek występuje podczas zmiany z dowolnego poziomu izolacji na SNAPSHOT izolację. Spowoduje to niepowodzenie transakcji i wycofanie. Można jednak zmienić transakcję uruchomioną w SNAPSHOT izolacji na dowolny inny poziom izolacji.

Po zmianie transakcji z jednego poziomu izolacji na inny zasoby odczytywane po zmianie są chronione zgodnie z regułami nowego poziomu. Zasoby, które są odczytywane przed zmianą, będą nadal chronione zgodnie z regułami poprzedniego poziomu. Na przykład jeśli transakcja zmieniła się z READ COMMITTED na SERIALIZABLE, udostępnione blokady uzyskane po zmianie są przechowywane do końca transakcji.

Jeśli wystąpi problem SET TRANSACTION ISOLATION LEVEL w procedurze składowanej lub wyzwalaczu, gdy obiekt zwróci kontrolę, poziom izolacji zostanie zresetowany do poziomu w mocy, gdy obiekt został wywołany. Jeśli na przykład ustawisz REPEATABLE READ w partii, a następnie partia wywołuje procedurę składowaną, która ustawia poziom izolacji na SERIALIZABLEwartość , ustawienie poziomu izolacji zostanie przywrócone REPEATABLE READ , gdy procedura składowana zwróci kontrolę do partii.

Uwaga / Notatka

Funkcje zdefiniowane przez użytkownika i typy środowiska uruchomieniowego języka wspólnego (CLR) zdefiniowane przez użytkownika nie mogą wykonywać polecenia SET TRANSACTION ISOLATION LEVEL. Można jednak zastąpić poziom izolacji przy użyciu wskazówki tabeli. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące tabel (Transact-SQL).

Gdy używasz sp_bindsession metody do powiązania dwóch sesji, każda sesja zachowuje ustawienie poziomu izolacji. Zmiana SET TRANSACTION ISOLATION LEVEL ustawienia poziomu izolacji jednej sesji nie ma wpływu na ustawienie żadnych innych powiązanych sesji.

SET TRANSACTION ISOLATION LEVEL działa w czasie wykonywania lub wykonywania, a nie w czasie analizy.

Zoptymalizowane operacje ładowania zbiorczego na zapytaniach blokowych sterty, które są uruchamiane na następujących poziomach izolacji:

  • SNAPSHOT
  • READ UNCOMMITTED
  • READ COMMITTED używanie przechowywania wersji wierszy

Z drugiej strony zapytania uruchamiane na tych poziomach izolacji blokują zoptymalizowane operacje obciążenia zbiorczego na stertach. Aby uzyskać więcej informacji na temat operacji ładowania zbiorczego, zobacz Importowanie zbiorcze i eksportowanie danych (SQL Server).

Bazy danych z obsługą funkcji FILESTREAM obsługują następujące poziomy izolacji transakcji.

Poziom izolacji dostęp Transact-SQL Dostęp do systemu plików
Odczytywanie niezatwierdzonych SQL Server Nieobsługiwane
Odczyt zatwierdzony SQL Server SQL Server
Powtarzalny odczyt SQL Server Nieobsługiwane
Serializacji SQL Server Nieobsługiwane
Odczyt zatwierdzonej migawki SQL Server SQL Server
Snapshot SQL Server SQL Server

Przykłady

W poniższym przykładzie ustawiono TRANSACTION ISOLATION LEVEL element dla sesji. Dla każdej instrukcji Transact-SQL, która jest następująca, program SQL Server przechowuje wszystkie udostępnione blokady do końca transakcji.

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

Treści powiązane

  • Last updated on