Verwenden von Transaktionen in einem SQL-Pool in Azure Synapse
Dieser Artikel enthält Tipps für das Implementieren von Transaktionen und Entwickeln von Lösungen in einem SQL-Pool.
Ausblick
Wie zu erwarten, unterstützen SQL-Pools Transaktionen als Teil der Data Warehouse-Workload. Allerdings sind im Vergleich zu SQL Server einige Features eingeschränkt, um eine angemessene Leistung von SQL-Pools sicherzustellen. In diesem Artikel werden die Unterschiede hervorgehoben.
Transaktionsisolationsstufen
SQL-Pools implementieren ACID-Transaktionen. Die Isolationsstufe der Transaktionsunterstützung ist standardmäßig auf READ UNCOMMITTED (Lesen ohne Commit) festgelegt. Sie können diese in READ COMMITTED SNAPSHOT ISOLATION (Read Committed-Momentaufnahmeisolation) ändern, indem Sie die Datenbankoption READ_COMMITTED_SNAPSHOT für einen Benutzer-SQL-Pool aktivieren, wenn Sie mit der Masterdatenbank verbunden sind.
Nach der Aktivierung werden alle Transaktionen in dieser Datenbank unter READ COMMITTED SNAPSHOT ISOLATION ausgeführt, und die Einstellung READ UNCOMMITTED auf Sitzungsebene wird nicht berücksichtigt. Ausführliche Informationen finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).
Transaktionsgröße
Eine einzelne Transaktion zur Datenänderung ist in Bezug auf die Größe beschränkt. Der Grenzwert wird pro Verteilung angewendet. Die Gesamtzuordnung kann also ermittelt werden, indem der Grenzwert mit der Verteilungsanzahl multipliziert wird.
Um eine Annäherung für die maximale Zeilenanzahl in der Transaktion zu erhalten, teilen Sie die Verteilungsobergrenze durch die Gesamtgröße jeder Zeile. Bei Spalten mit variabler Länge können Sie erwägen, anstelle der maximalen Größe eine durchschnittliche Spaltenlänge zu verwenden.
Für die Tabelle unten gelten die folgenden beiden Annahmen:
- Gleichmäßige Verteilung der Daten
- Durchschnittliche Zeilenlänge beträgt 250 Byte
Gen2
| DWU | Obergrenze pro Verteilung (GB) | Anzahl der Verteilungen | Max. Transaktionsgröße (GB) | Anzahl der Zeilen pro Verteilung | Max. Zeilenzahl pro Transaktion |
|---|---|---|---|---|---|
| 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 | 4\.500 | 300.000.000 | 18.000.000.000 |
| DW15000c | 112,5 | 60 | 6\.750 | 450.000.000 | 27.000.000.000 |
| DW30000c | 225 | 60 | 13.500 | 900.000.000 | 54.000.000.000 |
Gen1
| DWU | Obergrenze pro Verteilung (GB) | Anzahl der Verteilungen | Max. Transaktionsgröße (GB) | Anzahl der Zeilen pro Verteilung | Max. Zeilenzahl pro Transaktion |
|---|---|---|---|---|---|
| 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 |
Die Obergrenze für die Transaktionsgröße wird pro Transaktion oder Vorgang angewendet. Sie wird nicht übergreifend für alle gleichzeitigen Transaktionen angewendet. Daher ist es für jede Transaktion zulässig, diese Menge an Daten in das Protokoll zu schreiben.
Informationen zum Optimieren und Reduzieren der Datenmenge, die in das Protokoll geschrieben wird, finden Sie im Artikel Bewährte Methoden für Transaktionen.
Warnung
Die maximale Transaktionsgröße kann nur für Tabellen mit HASH- oder ROUND_ROBIN-Verteilung erreicht werden, bei denen die Daten gleichmäßig verteilt werden. Wenn bei der Transaktion Daten auf verzerrte Weise in die Verteilungen geschrieben werden, wird die Obergrenze wahrscheinlich vor der maximalen Transaktionsgröße erreicht.
Transaktionsstatus
SQL-Pools verwenden die XACT_STATE()-Funktion, um eine fehlgeschlagene Transaktion mit dem Wert „– 2“ zu melden. Dieser Wert bedeutet, dass die Transaktion fehlerhaft und nur für den Rollback markiert ist.
Hinweis
Die Verwendung von "-2" in der XACT_STATE-Funktion zum Kennzeichnen einer fehlgeschlagenen Transaktion stellt für SQL Server unterschiedliche Verhalten dar. SQL Server verwendet den Wert "-1" für eine Transaktion, für die kein Commit durchgeführt werden kann. SQL Server kann einige Fehler innerhalb einer Transaktion tolerieren, ohne als nicht commitfähig gekennzeichnet zu werden. SELECT 1/0 würde beispielsweise einen Fehler verursachen, aber keinen nicht commitfähigen Transaktionszustand erzwingen.
SQL Server lässt auch Lesevorgänge in der nicht commitfähigen Transaktion zu. SQL-Pools lassen dies dagegen nicht zu. Wenn in einer SQL-Pool-Transaktion ein Fehler auftritt, wird die Transaktion sofort in den Zustand „– 2“ versetzt, und Sie können erst dann weitere SELECT-Anweisungen ausführen, wenn ein Rollback für die ursprüngliche Anweisung ausgeführt wurde.
Es ist daher wichtig, zu überprüfen, ob in Ihrem Anwendungscode XACT_STATE() verwendet wird, da Sie den Code möglicherweise ändern müssen.
In SQL Server begegnet Ihnen z. B. möglicherweise eine Transaktion wie die folgende:
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;
Der vorangehende Code gibt die folgende Fehlermeldung aus:
Meldung 111233, Ebene 16, Status 1, Zeile 1 111233; Die aktuelle Transaktion wurde abgebrochen, und alle ausstehenden Änderungen wurden zurückgesetzt. Ursache: Eine Transaktion in einem Nur-Rollback-Status wurde vor einer DDL-, DML- oder SELECT-Anweisung nicht explizit zurückgesetzt.
Sie erhalten auch keine Ausgabe der ERROR_*-Funktionen.
Bei einem SQL-Pool muss der Code leicht geändert werden:
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;
Jetzt werden Sie das erwartete Verhalten feststellen. Der Fehler in der Transaktion wird verwaltet, und die ERROR_*-Funktionen geben wie erwartet Werte zurück.
Die einzige Änderung besteht darin, dass die ROLLBACK-Ausführung für die Transaktion vor dem Lesen der Fehlerinformationen im CATCH-Block erfolgen muss.
Error_Line()-Funktion
Es ist auch erwähnenswert, dass die ERROR_LINE()-Funktion in SQL-Pools nicht implementiert oder unterstützt wird. Wenn diese in Ihrem Code enthalten ist, müssen Sie sie entfernen, um die Kompatibilität mit SQL-Pools zu gewährleisten.
Verwenden Sie stattdessen Abfragebezeichnungen in Ihrem Code, um entsprechende Funktionalität zu implementieren. Weitere Details finden Sie im Artikel LABEL.
Verwenden von THROW und RAISERROR
THROW ist die modernere Implementierung zum Auslösen von Ausnahmen in SQL-Pools, RAISERROR wird jedoch ebenfalls unterstützt. Es gibt aber einige erwähnenswerte Unterschiede.
- Benutzerdefinierte Fehlermeldungsnummern können für THROW nicht im Bereich 100.000 bis 150.000 liegen.
- RAISERROR-Fehlermeldungen sind bei 50.000 festgelegt.
- Die Verwendung von "sys.messages" wird nicht unterstützt.
Einschränkungen
SQL-Pools verfügen über einige weitere Einschränkungen in Bezug auf Transaktionen.
Dies sind:
- Keine verteilten Transaktionen
- Keine geschachtelten Transaktionen zulässig
- Keine Speicherpunkte zulässig
- Keine benannten Transaktionen
- Keine markierten Transaktionen
- Keine Unterstützung für DDL wie z.B. CREATE TABLE innerhalb von benutzerdefinierten Transaktionen
Nächste Schritte
Weitere Informationen zum Optimieren von Transaktionen finden Sie unter Bewährte Methoden für Transaktionen. Weitere Informationen zu anderen Best Practices für SQL-Pools finden Sie unter Bewährte Methoden für den Synapse SQL-Pool in Azure Synapse Analytics (ehemals SQL DW).