Transazioni in SQL Data WarehouseTransactions in SQL Data Warehouse

Come si può immaginare, SQL Data Warehouse supporta le transazioni come parte del carico di lavoro del data warehouse.As you would expect, SQL Data Warehouse supports transactions as part of the data warehouse workload. Tuttavia, per garantire che le prestazioni di SQL Data Warehouse siano mantenute al massimo livello, alcune funzionalità sono limitate rispetto a SQL Server.However, to ensure the performance of SQL Data Warehouse is maintained at scale some features are limited when compared to SQL Server. Questo articolo evidenzia le differenze ed elenca le altre.This article highlights the differences and lists the others.

Livelli di isolamento delle transazioniTransaction isolation levels

SQL Data Warehouse implementa le transazioni ACID.SQL Data Warehouse implements ACID transactions. Tuttavia, l'isolamento del supporto delle transazioni è limitato a READ UNCOMMITTED e non può essere modificato.However, the Isolation of the transactional support is limited to READ UNCOMMITTED and this cannot be changed. È possibile implementare numerosi metodi di codifica per evitare letture dirty dei dati se ciò costituisce un problema.You can implement a number of coding methods to prevent dirty reads of data if this is a concern for you. I metodi più diffusi usano CTAS e il cambio della partizione di tabella (spesso noto come modello di finestra temporale scorrevole) per impedire agli utenti di eseguire query sui dati ancora in fase di preparazione.The most popular methods leverage both CTAS and table partition switching (often known as the sliding window pattern) to prevent users from querying data that is still being prepared. Anche le visualizzazioni che filtrano preventivamente i dati costituiscono un approccio comune.Views that pre-filter the data is also a popular approach.

Dimensioni delle transazioniTransaction size

Le dimensioni di una singola transazione di modifica dati sono limitate.A single data modification transaction is limited in size. Il limite è attualmente applicato "per ogni distribuzione".The limit today is applied "per distribution". Per calcolare l'allocazione totale, quindi, è possibile moltiplicare il limite per il conteggio di distribuzione.Therefore, the total allocation can be calculated by multiplying the limit by the distribution count. Per calcolare approssimativamente il numero massimo di righe nella transazione, dividere il limite di distribuzione per le dimensioni totali di ogni riga.To approximate the maximum number of rows in the transaction divide the distribution cap by the total size of each row. Per le colonne di lunghezza variabile valutare la possibilità di usare una lunghezza di colonna media invece delle dimensioni massime.For variable length columns consider taking an average column length rather than using the maximum size.

Ecco alcuni presupposti riportati nella tabella seguente:In the table below the following assumptions have been made:

  • Si è verificata una distribuzione uniforme dei datiAn even distribution of data has occurred
  • La lunghezza media delle righe è 250 byteThe average row length is 250 bytes
DWUDWU Limite per ogni distribuzione (GiB)Cap per distribution (GiB) Numero di distribuzioniNumber of Distributions Dimensioni MAX delle transazioni (GiB)MAX transaction size (GiB) Numero di righe per distribuzione# Rows per distribution Righe max per transazioneMax Rows per transaction
DW100DW100 11 6060 6060 4.000.0004,000,000 240.000.000240,000,000
DW200DW200 1,51.5 6060 9090 6.000.0006,000,000 360.000.000360,000,000
DW300DW300 2.252.25 6060 135135 9.000.0009,000,000 540.000.000540,000,000
DW400DW400 33 6060 180180 12.000.00012,000,000 720.000.000720,000,000
DW500DW500 3,753.75 6060 225225 15.000.00015,000,000 900.000.000900,000,000
DW600DW600 4.54.5 6060 270270 18.000.00018,000,000 1.080.000.0001,080,000,000
DW1000DW1000 7.57.5 6060 450450 30.000.00030,000,000 1.800.000.0001,800,000,000
DW1200DW1200 99 6060 540540 36.000.00036,000,000 2.160.000.0002,160,000,000
DW1500DW1500 11,2511.25 6060 675675 45.000.00045,000,000 2.700.000.0002,700,000,000
DW2000DW2000 1515 6060 900900 60.000.00060,000,000 3.600.000.0003,600,000,000
DW3000DW3000 22,522.5 6060 1.3501,350 90.000.00090,000,000 5.400.000.0005,400,000,000
DW6000DW6000 4545 6060 2.7002,700 180.000.000180,000,000 10.800.000.00010,800,000,000

Il limite delle dimensioni delle transazioni viene applicato per transazione o per operazione.The transaction size limit is applied per transaction or operation. Non viene applicato in tutte le transazioni simultanee.It is not applied across all concurrent transactions. A ogni transazione è quindi consentito scrivere questa quantità di dati nel log.Therefore each transaction is permitted to write this amount of data to the log.

Per ottimizzare e ridurre al minimo la quantità di dati scritti nel log, vedere Ottimizzazione delle transazioni per SQL Data Warehouse.To optimize and minimize the amount of data written to the log please refer to the Transactions best practices article.

Avviso

Le dimensioni massime delle transazioni possono essere ottenute solo per le tabelle distribuite HASH o ROUND_ROBIN in cui i dati sono distribuiti in modo uniforme.The maximum transaction size can only be achieved for HASH or ROUND_ROBIN distributed tables where the spread of the data is even. Se la transazione scrive dati in modo asimmetrico nelle distribuzioni, è probabile che il limite venga raggiunto prima di raggiungere le dimensioni massime delle transazioni.If the transaction is writing data in a skewed fashion to the distributions then the limit is likely to be reached prior to the maximum transaction size.

Stato della transazioneTransaction state

SQL Data Warehouse usa la funzione XACT_STATE() per segnalare una transazione non riuscita con il valore -2.SQL Data Warehouse uses the XACT_STATE() function to report a failed transaction using the value -2. Ciò significa che la transazione non è riuscita ed è contrassegnata solo per il rollback.This means that the transaction has failed and is marked for rollback only

Nota

L'uso di -2 da parte della funzione XACT_STATE per indicare una transazione non riuscita rappresenta un comportamento diverso da SQL Server.The use of -2 by the XACT_STATE function to denote a failed transaction represents different behavior to SQL Server. SQL Server usa il valore -1 per rappresentare una transazione di cui non è possibile eseguire il commit.SQL Server uses the value -1 to represent an un-committable transaction. SQL Server è in grado di tollerare alcuni errori all'interno di una transazione senza doverne indicare l'impossibilità di eseguire il commit.SQL Server can tolerate some errors inside a transaction without it having to be marked as un-committable. Ad esempio, SELECT 1/0 causa un errore, ma non applica alla transazione lo stato per cui non è possibile eseguire il commit.For example SELECT 1/0 would cause an error but not force a transaction into an un-committable state. SQL Server consente anche letture nella transazione di cui non è possibile eseguire il commit.SQL Server also permits reads in the un-committable transaction. SQL Data Warehouse, invece, non consente questa operazione.However, SQL Data Warehouse does not let you do this. Se si verifica un errore in una transazione SQL Data Warehouse, verrà inserito automaticamente lo stato-2 e non sarà più possibile eseguire ulteriori istruzioni SELECT finché non verrà eseguito il rollback dell'istruzione.If an error occurs inside a SQL Data Warehouse transaction it will automatically enter the -2 state and you will not be able to make any further select statements until the statement has been rolled back. È quindi importante verificare il codice dell'applicazione per vedere se usa XACT_STATE(), perché può essere necessario modificarlo.It is therefore important to check that your application code to see if it uses XACT_STATE() as you may need to make code modifications.

Ad esempio, in SQL Server potrebbe essere visualizzata una transazione simile alla seguente:For example, in SQL Server you might see a transaction that looks like this:

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
            PRINT 'ROLLBACK';
            ROLLBACK TRAN;
        END

    END CATCH;

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

SELECT @xact_state AS TransactionState;

Se si lascia il codice come qui sopra, si otterrà il seguente messaggio di errore:If you leave your code as it is above then you will get the following error message:

Msg 111233, livello 16, stato 1, riga 1 111233;La transazione corrente è stata interrotta ed è stato eseguito il rollback di tutte le modifiche in sospeso.Msg 111233, Level 16, State 1, Line 1 111233;The current transaction has aborted, and any pending changes have been rolled back. Causa: non è stato eseguito il rollback in modo esplicito di una transazione in uno stato di solo rollback prima di un'istruzione DDL, DML o SELECT.Cause: A transaction in a rollback-only state was not explicitly rolled back before a DDL, DML or SELECT statement.

Inoltre non si otterrà l'output delle funzioni di ERROR_.You will also not get the output of the ERROR_ functions.

È quindi necessario modificare leggermente il codice in SQL Data Warehouse:In SQL Data Warehouse 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
            PRINT 'ROLLBACK';
            ROLLBACK TRAN;
        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;

A questo punto si osserva il comportamento previsto.The expected behavior is now observed. L'errore della transazione viene gestito e le funzioni ERROR_* forniscono i valori previsti.The error in the transaction is managed and the ERROR_* functions provide values as expected.

Ciò dimostra che il ROLLBACK della transazione doveva essere eseguito prima della lettura delle informazioni sull'errore nel blocco CATCH.All that has changed is that the ROLLBACK of the transaction had to happen before the read of the error information in the CATCH block.

Funzione Error_Line()Error_Line() function

È importante sottolineare anche che SQL Data Warehouse non implementa né supporta la funzione ERROR_LINE().It is also worth noting that SQL Data Warehouse does not implement or support the ERROR_LINE() function. Se è contenuta nel codice sarà necessario rimuoverla per renderlo compatibile con SQL Data Warehouse.If you have this in your code you will need to remove it to be compliant with SQL Data Warehouse. Anziché implementare una funzionalità equivalente, usare etichette di query nel codice.Use query labels in your code instead to implement equivalent functionality. Per altre informazioni su questa funzionalità, vedere l'articolo Usare etichette per instrumentare query in SQL Data Warehouse.Please refer to the LABEL article for more details on this feature.

Uso di THROW e RAISERRORUsing THROW and RAISERROR

THROW è l'implementazione più moderna per la generazione di eccezioni in SQL Data Warehouse, ma è supportata anche RAISERROR.THROW is the more modern implementation for raising exceptions in SQL Data Warehouse but RAISERROR is also supported. Esistono tuttavia alcune differenze a cui vale la pena prestare attenzione.There are a few differences that are worth paying attention to however.

  • I numeri dei messaggi di errore definiti dall'utente non possono essere compresi nell'intervallo da 100.000 a 150.000 per THROW.User defined error messages numbers cannot be in the 100,000 - 150,000 range for THROW
  • I messaggi di errore di RAISERROR sono fissati a 50.000.RAISERROR error messages are fixed at 50,000
  • L'uso di sys.messages non è supportato.Use of sys.messages is not supported

LimitazioniLimitiations

SQL Data Warehouse presenta qualche altra limitazione relativa alle transazioni.SQL Data Warehouse does have a few other restrictions that relate to transactions.

Ecco quali sono:They are as follows:

  • Nessuna transazione distribuitaNo distributed transactions
  • Non sono consentite transazioni annidateNo nested transactions permitted
  • Non sono consentiti punti di salvataggioNo save points allowed
  • Nessuna transazione denominataNo named transactions
  • Nessuna transazione contrassegnataNo marked transactions
  • Nessun supporto per DDL come CREATE TABLE all'interno di una transazione definita dall'utenteNo support for DDL such as CREATE TABLE inside a user defined transaction

Passaggi successiviNext steps

Per altre informazioni sull'ottimizzazione delle transazioni, vedere Ottimizzazione delle transazioni per SQL Data Warehouse.To learn more about optimizing transactions, see Transactions best practices. Per altre informazioni sulle procedure consigliate per SQL Data Warehouse, vedere Procedure consigliate per Azure SQL Data Warehouse.To learn about other SQL Data Warehouse best practices, see SQL Data Warehouse best practices.