Używanie transakcji z dedykowaną pulą SQL w usłudze Azure Synapse Analytics

  • Artykuł

Porady dotyczące implementowania transakcji z dedykowaną pulą SQL w usłudze Azure Synapse Analytics na potrzeby tworzenia rozwiązań.

Czego oczekiwać

Zgodnie z oczekiwaniami dedykowana pula SQL obsługuje transakcje w ramach obciążenia magazynu danych. Jednak w celu zapewnienia wydajności dedykowanej puli SQL na dużą skalę niektóre funkcje są ograniczone w porównaniu do SQL Server. W tym artykule wyróżniono różnice i wymieniono inne.

Poziomy izolacji transakcji

Dedykowana pula SQL implementuje transakcje ACID. Poziom izolacji obsługi transakcyjnej jest domyślny do odczytu UNCOMMITTED. Możesz zmienić tę opcję na READ COMMITTED SNAPSHOT ISOLATION (ODCZYT ZATWIERDZONE IZOLACJI MIGAWEK), włączając opcję READ_COMMITTED_SNAPSHOT bazy danych dla bazy danych użytkownika po nawiązaniu połączenia z bazą danych master.

Po włączeniu wszystkich transakcji w tej bazie danych są wykonywane w obszarze READ COMMITTED SNAPSHOT ISOLATION i ustawienie READ UNCOMMITTED na poziomie sesji nie zostanie uznane. Aby uzyskać szczegółowe informacje, zapoznaj się z opcjami ALTER DATABASE SET (Transact-SQL).

Rozmiar transakcji

Pojedyncza transakcja modyfikacji danych jest ograniczona. Limit jest stosowany na dystrybucję. W związku z tym łączna alokacja może być obliczana przez pomnożenie limitu według liczby rozkładów.

Aby przybliżyć maksymalną liczbę wierszy w transakcji, podziel limit dystrybucji przez całkowity rozmiar każdego wiersza. W przypadku kolumn o zmiennej długości rozważ użycie średniej długości kolumny zamiast maksymalnego rozmiaru.

W poniższej tabeli zostały podjęte następujące założenia:

  • Wystąpił równomierny rozkład danych
  • Średnia długość wiersza to 250 bajtów

Gen2

DWU Limit na dystrybucję (GB) Liczba dystrybucji MAKSYMALNY rozmiar transakcji (GB) # Wiersze na dystrybucję Maksymalna liczba wierszy na transakcję
DW100c 1 60 60 4,000,000 240,000,000
DW200c 1.5 60 90 6 000 000 360,000,000
DW300c 2.25 60 135 9,000,000 540,000,000
DW400c 3 60 180 12,000,000 720,000,000
DW500c. 3,75 60 225 15,000,000 900,000,000
DW1000c 7,5 60 450 30,000,000 1,800,000,000
DW1500c 11.25 60 675 45,000,000 2,700,000,000
DW2000c 15 60 900 60,000,000 3,600,000,000
DW2500c 18.75 60 1125 75,000,000 4,500,000,000
DW3000c 22,5 60 1,350 90,000,000 5,400,000,000
DW5000c 37,5 60 2,250 150,000,000 9,000,000,000
DW6000c 45 60 2,700 180,000,000 10,800,000,000
DW7500c 56.25 60 3,375 225,000,000 13,500,000,000
DW10000c 75 60 4500 300 000 000 18,000,000,000
DW15000c 112,5 60 6750 450,000,000 27,000,000,000
DW30000c 225 60 13,500 900,000,000 54,000,000,000

Gen1

DWU Limit na dystrybucję (GB) Liczba dystrybucji MAKSYMALNY rozmiar transakcji (GB) # Wiersze na dystrybucję Maksymalna liczba wierszy na transakcję
DW100 1 60 60 4,000,000 240,000,000
DW200 1.5 60 90 6 000 000 360,000,000
DW300 2.25 60 135 9,000,000 540,000,000
DW400 3 60 180 12,000,000 720,000,000
DW500 3,75 60 225 15,000,000 900,000,000
DW600 4.5 60 270 18,000,000 1,080,000,000
DW1000 7,5 60 450 30,000,000 1,800,000,000
DW1200 9 60 540 36,000,000 2,160,000,000
DW1500 11.25 60 675 45,000,000 2,700,000,000
DW2000 15 60 900 60,000,000 3,600,000,000
DW3000 22,5 60 1,350 90,000,000 5,400,000,000
DW6000 45 60 2,700 180,000,000 10,800,000,000

Limit rozmiaru transakcji jest stosowany na transakcję lub operację. Nie jest on stosowany we wszystkich współbieżnych transakcjach. W związku z tym każda transakcja może zapisywać tę ilość danych w dzienniku.

Aby zoptymalizować i zminimalizować ilość danych zapisanych w dzienniku, zapoznaj się z artykułem Transactions best practices (Najlepsze rozwiązania dotyczące transakcji ).

Ostrzeżenie

Maksymalny rozmiar transakcji można osiągnąć tylko dla funkcji HASH lub ROUND_ROBIN tabel rozproszonych, w których rozkład danych jest równomierny. Jeśli transakcja zapisuje dane w sposób niesymetryczny do dystrybucji, limit prawdopodobnie zostanie osiągnięty przed maksymalnym rozmiarem transakcji.

Stan transakcji

Dedykowana pula SQL używa funkcji XACT_STATE(), aby zgłosić nieudaną transakcję przy użyciu wartości -2. Ta wartość oznacza, że transakcja nie powiodła się i jest oznaczona tylko do wycofania.

Uwaga

Użycie -2 przez funkcję XACT_STATE w celu określenia nieudanej transakcji reprezentuje inne zachowanie, aby SQL Server. SQL Server używa wartości -1 do reprezentowania transakcji niezatwierdzonych. SQL Server może tolerować niektóre błędy wewnątrz transakcji bez konieczności oznaczania jej jako nieuprzytwierdzonych. Na przykład SELECT 1/0 może spowodować błąd, ale nie wymusić transakcji w stanie nieupolecanym. SQL Server zezwala również na odczyty w niezatwierdzonych transakcjach. Jednak dedykowana pula SQL nie pozwala na to. Jeśli wystąpi błąd wewnątrz dedykowanej transakcji puli SQL, automatycznie wprowadzi stan -2 i nie będzie można wykonać żadnych dalszych instrukcji wyboru, dopóki instrukcja nie zostanie wycofana. Dlatego ważne jest, aby sprawdzić, czy kod aplikacji używa XACT_STATE(), ponieważ może być konieczne wprowadzenie modyfikacji kodu.

Na przykład w SQL Server może zostać wyświetlona transakcja, która wygląda następująco:

SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;

BEGIN TRAN
    BEGIN TRY
        DECLARE @i INT;
        SET     @i = CONVERT(INT,'ABC');
    END TRY
    BEGIN CATCH
        SET @xact_state = XACT_STATE();

        SELECT  ERROR_NUMBER()    AS ErrNumber
        ,       ERROR_SEVERITY()  AS ErrSeverity
        ,       ERROR_STATE()     AS ErrState
        ,       ERROR_PROCEDURE() AS ErrProcedure
        ,       ERROR_MESSAGE()   AS ErrMessage
        ;

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN;
            PRINT 'ROLLBACK';
        END

    END CATCH;

IF @@TRANCOUNT >0
BEGIN
    PRINT 'COMMIT';
    COMMIT TRAN;
END

SELECT @xact_state AS TransactionState;

Powyższy kod zawiera następujący komunikat o błędzie:

Msg 111233, Poziom 16, Stan 1, Wiersz 1 111233; Bieżąca transakcja została przerwana i wszystkie oczekujące zmiany zostały wycofane. Przyczyna: Transakcja w stanie wycofywania nie została jawnie wycofana przed instrukcją DDL, DML lub SELECT.

Nie uzyskasz danych wyjściowych funkcji ERROR_*.

W dedykowanej puli SQL kod musi zostać nieco zmieniony:

SET NOCOUNT ON;
DECLARE @xact_state smallint = 0;

BEGIN TRAN
    BEGIN TRY
        DECLARE @i INT;
        SET     @i = CONVERT(INT,'ABC');
    END TRY
    BEGIN CATCH
        SET @xact_state = XACT_STATE();

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN;
            PRINT 'ROLLBACK';
        END

        SELECT  ERROR_NUMBER()    AS ErrNumber
        ,       ERROR_SEVERITY()  AS ErrSeverity
        ,       ERROR_STATE()     AS ErrState
        ,       ERROR_PROCEDURE() AS ErrProcedure
        ,       ERROR_MESSAGE()   AS ErrMessage
        ;
    END CATCH;

IF @@TRANCOUNT >0
BEGIN
    PRINT 'COMMIT';
    COMMIT TRAN;
END

SELECT @xact_state AS TransactionState;

Zaobserwowano oczekiwane zachowanie. Błąd transakcji jest zarządzany, a funkcje ERROR_* zapewniają wartości zgodnie z oczekiwaniami.

Wszystko, co się zmieniło, jest to, że wycofanie transakcji musiało nastąpić przed odczytaniem informacji o błędzie w bloku CATCH.

Error_Line() , funkcja

Warto również zauważyć, że dedykowana pula SQL nie implementuje ani nie obsługuje funkcji ERROR_LINE(). Jeśli ta funkcja jest w kodzie, musisz usunąć ją tak, aby była zgodna z dedykowaną pulą SQL. Zamiast tego użyj etykiet zapytań w kodzie, aby zaimplementować równoważne funkcje. Aby uzyskać więcej informacji, zobacz artykuł LABEL .

Korzystanie z funkcji THROW i RAISERROR

THROW to bardziej nowoczesna implementacja do zgłaszania wyjątków w dedykowanej puli SQL, ale obsługiwana jest również funkcja RAISERROR. Istnieje kilka różnic, które warto jednak zwrócić uwagę.

  • Liczby komunikatów o błędach zdefiniowanych przez użytkownika nie mogą znajdować się w zakresie od 100 000 do 150 000 dla funkcji THROW
  • Komunikaty o błędach RAISERROR zostały naprawione na poziomie 50 000
  • Korzystanie z pliku sys.messages nie jest obsługiwane

Ograniczenia

Dedykowana pula SQL ma kilka innych ograniczeń związanych z transakcjami. Są one następujące:

  • Brak transakcji rozproszonych
  • Brak dozwolonych transakcji zagnieżdżonych
  • Brak dozwolonych punktów zapisywania
  • Brak nazwanych transakcji
  • Brak oznaczonych transakcji
  • Brak obsługi języka DDL, takiego jak CREATE TABLE wewnątrz transakcji zdefiniowanej przez użytkownika

Następne kroki

Aby dowiedzieć się więcej na temat optymalizowania transakcji, zobacz Najlepsze rozwiązania dotyczące transakcji. Dodatkowe przewodniki dotyczące najlepszych rozwiązań są również dostępne dla dedykowanej puli SQL i bezserwerowej puli SQL.