Use transactions with dedicated SQL pool in Azure Synapse Analytics

Tips for implementing transactions with dedicated SQL pool in Azure Synapse Analytics for developing solutions.

Wat u kunt verwachten

As you would expect, dedicated SQL pool supports transactions as part of the data warehouse workload. However, to ensure the performance of dedicated SQL pool is maintained at scale some features are limited when compared to SQL Server. This article highlights the differences and lists the others.

Transaction isolation levels

Dedicated SQL pool implements ACID transactions. Het isolatieniveau van de transactionele ondersteuning is standaard ingesteld op READ UNCOMMITTED. You can change it to READ COMMITTED SNAPSHOT ISOLATION by turning ON the READ_COMMITTED_SNAPSHOT database option for a user database when connected to the master database.

Zodra deze database is ingeschakeld, worden alle transacties in deze database uitgevoerd onder READ COMMITTED SNAPSHOT ISOLATION en wordt het instellen van READ UNCOMMITTED op sessieniveau niet gehonoreerd. Controleer de opties voor ALTER DATABASE SET (Transact-SQL) voor meer informatie.

Transactiegrootte

Eén transactie voor het wijzigen van gegevens is beperkt in grootte. De limiet wordt per distributie toegepast. As such, the total allocation can be calculated by multiplying the limit by the distribution count.

Als u het maximum aantal rijen in de transactie wilt benaderen, deelt u de distributielimiet door de totale grootte van elke rij. Voor kolommen met variabele lengte kunt u overwegen om een gemiddelde kolomlengte te nemen in plaats van de maximale grootte te gebruiken.

In the table below the following assumptions have been made:

• Er is een gelijkmatige verdeling van gegevens opgetreden
• The average row length is 250 bytes

Gen2

Gen1

De limiet voor de transactiegrootte wordt per transactie of bewerking toegepast. Deze wordt niet toegepast op alle gelijktijdige transacties. Daarom mag elke transactie deze hoeveelheid gegevens naar het logboek schrijven.

To optimize and minimize the amount of data written to the log, refer to the Transactions best practices article.

Transactiestatus

Dedicated SQL pool uses the XACT_STATE() function to report a failed transaction using the value -2. Deze waarde betekent dat de transactie is mislukt en alleen is gemarkeerd voor terugdraaien.

For example, in SQL Server you might see a transaction that looks like the following:

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;

De voorgaande code geeft het volgende foutbericht:

Msg 111233, niveau 16, staat 1, regel 1 111233; De huidige transactie is afgebroken en alle wijzigingen die in behandeling zijn, zijn teruggedraaid. Cause: A transaction in a rollback-only state wasn't explicitly rolled back before a DDL, DML, or SELECT statement.

U krijgt geen uitvoer van de ERROR_*-functies.

In dedicated SQL pool, the code needs to be slightly altered:

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;

Het verwachte gedrag wordt nu waargenomen. De fout in de transactie wordt beheerd en de functies ERROR_* bieden waarden zoals verwacht.

Het enige dat is gewijzigd, is dat het terugdraaien van de transactie moest plaatsvinden voordat de foutinformatie in het CATCH-blok werd gelezen.

Error_Line() functie

It is also worth noting that dedicated SQL pool does not implement or support the ERROR_LINE() function. If you have this function in your code, you need to remove it to be compliant with dedicated SQL pool. Gebruik in plaats daarvan querylabels in uw code om equivalente functionaliteit te implementeren. For more information, see the LABEL article.

Use of THROW and RAISERROR

THROW is the more modern implementation for raising exceptions in dedicated SQL pool but RAISERROR is also supported. Er zijn echter enkele verschillen die de moeite waard zijn om aandacht te besteden.

• User-defined error messages numbers can't be in the 100,000 - 150,000 range for THROW
• RAISERROR-foutberichten zijn vastgezet op 50.000
• Het gebruik van sys.messages wordt niet ondersteund

Beperkingen

Dedicated SQL pool does have a few other restrictions that relate to transactions. Ze zijn als volgt:

• Geen gedistribueerde transacties
• Geneste transacties zijn niet toegestaan
• Er zijn geen opslagpunten toegestaan
• Geen benoemde transacties
• Geen gemarkeerde transacties
• Geen ondersteuning voor DDL, zoals CREATE TABLE binnen een door de gebruiker gedefinieerde transactie

Volgende stappen

Voor meer informatie over het optimaliseren van transacties, zie Best practices voor transacties. Additional best practices guides are also provided for Dedicated SQL pool and serverless SQL pool.