Używanie transakcji w puli SQL w Azure Synapse
Ten artykuł zawiera porady dotyczące implementowania transakcji i tworzenia rozwiązań w puli SQL.
Czego oczekiwać
Jak można się spodziewać, pula SQL obsługuje transakcje w ramach obciążenia magazynu danych. Jednak w celu zapewnienia utrzymania puli SQL na dużą skalę niektóre funkcje są ograniczone w porównaniu z SQL Server. W tym artykule przedstawiono różnice.
Poziomy izolacji transakcji
Pula SQL implementuje transakcje ACID. Poziom izolacji obsługi transakcyjnej jest domyślny do odczytu UNCOMMITTED. Możesz zmienić go na READ COMMITTED SNAPSHOT ISOLATION (ODCZYT ZATWIERDZONE IZOLACJI MIGAWEK), włączając opcję READ_COMMITTED_SNAPSHOT bazy danych dla puli SQL 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 dwa 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 dla transakcji lub operacji. Nie jest ona stosowana 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ówny. Jeśli transakcja zapisuje dane w sposób niesymetryczny w dystrybucjach, limit prawdopodobnie zostanie osiągnięty przed maksymalnym rozmiarem transakcji.
Stan transakcji
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 do oznaczania nieudanej transakcji reprezentuje inne zachowanie SQL Server. SQL Server używa wartości -1 do reprezentowania niezatwierdzonych transakcji. SQL Server może tolerować niektóre błędy wewnątrz transakcji bez konieczności oznaczania ich jako niezatwierdzonych. Na przykład może spowodować błąd, SELECT 1/0 ale nie wymusić transakcji w stanie niezatwierdzonych.
SQL Server zezwala również na odczyty w niezatwierdzonych transakcji. Jednak pula SQL nie pozwala na to. Jeśli błąd wystąpi wewnątrz transakcji puli SQL, automatycznie wprowadzi stan -2 i nie będzie można wykonać żadnych dalszych instrukcji wyboru, dopóki instrukcja nie zostanie wycofana.
W związku z tym ważne jest sprawdzenie, 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. Przyczyną tego problemu jest to, że transakcja w stanie wycofywania nie jest jawnie wycofana przed instrukcją DDL, DML lub SELECT.
Nie uzyskasz danych wyjściowych funkcji ERROR_*.
W puli SQL kod musi być 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;
Oczekiwane zachowanie jest teraz obserwowane. Błąd w 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 odczytem informacji o błędzie w bloku CATCH.
Error_Line(), funkcja
Warto również zauważyć, że pula SQL nie implementuje ani nie obsługuje funkcji ERROR_LINE(). Jeśli masz go w kodzie, musisz go usunąć, aby był zgodny z 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 puli SQL, ale obsługiwana jest również funkcja RAISERROR. Istnieje jednak kilka różnic, które warto zwrócić uwagę na.
- 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 przy 50 000
- Korzystanie z pliku sys.messages nie jest obsługiwane
Ograniczenia
Pula SQL ma kilka innych ograniczeń związanych z transakcjami.
Są one następujące:
- Brak transakcji rozproszonych
- Brak dozwolonych zagnieżdżonych transakcji
- 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 optymalizacji transakcji, zobacz Najlepsze rozwiązania dotyczące transakcji. Aby dowiedzieć się więcej o innych najlepszych rozwiązaniach dotyczących puli SQL, zobacz Najlepsze rozwiązania dotyczące puli SQL.