在 Synapse SQL 池中使用事务Use transactions in Synapse SQL pool

• 05/11/2020
• • 

本文包含有关在 SQL 池中实现事务和开发解决方案的技巧。This article includes tips for implementing transactions and developing solutions in SQL pool.

期望What to expect

如你所料，SQL 池支持将事务作为数据仓库工作负载的一部分。As you would expect, SQL pool supports transactions as part of the data warehouse workload. 但是，为了确保 SQL 池保持在一定规模，相比于 SQL Server，某些功能会受到限制。However, to ensure SQL pool is maintained at scale, some features are limited when compared to SQL Server. 本文重点介绍了两者的差异。This article highlights the differences.

事务隔离级别Transaction isolation levels

SQL 池实现了 ACID 事务。SQL pool implements ACID transactions. 事务支持的隔离级别默认为 READ UNCOMMITTED。The isolation level of the transactional support is default to READ UNCOMMITTED. 在连接到 master 数据库时，可以通过打开用户数据库的 READ_COMMITTED_SNAPSHOT 数据库选项，将其更改为 READ COMMITTED SNAPSHOT ISOLATION。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.

启用后，此数据库中的所有事务都将在 READ COMMITTED SNAPSHOT ISOLATION 下执行，并且将不接受在会话级别设置 READ UNCOMMITTED。Once enabled, all transactions in this database are executed under READ COMMITTED SNAPSHOT ISOLATION and setting READ UNCOMMITTED on session level will not be honored. 有关详细信息，请查看 ALTER DATABASE SET 选项 (Transact-SQL)。Check ALTER DATABASE SET options (Transact-SQL) for details.

事务大小Transaction size

单个数据修改事务有大小限制。A single data modification transaction is limited in size. 限制按每个分发进行应用。The limit is applied per distribution. 因此，通过将限制乘以分发数，可得总分配额。Therefore, the total allocation can be calculated by multiplying the limit by the distribution count.

要预计事务中的最大行数，请将分发上限除以每一行的总大小。To approximate the maximum number of rows in the transaction divide the distribution cap by the total size of each row. 对于可变长度列，考虑采用平均的列长度而不使用最大大小。For variable length columns, consider taking an average column length rather than using the maximum size.

下表中做了两个假设：In the following table, two assumptions have been made:

• 出现平均数据分布An even distribution of data has occurred
• 平均行长度为 250 个字节The average row length is 250 bytes

Gen2Gen2

Gen1Gen1

事务大小限制按每个事务或操作进行应用。The transaction size limit is applied per transaction or operation. 不会跨所有当前事务进行应用。It is not applied across all concurrent transactions. 因此，允许每个事务向日志写入此数量的数据。Therefore each transaction is permitted to write this amount of data to the log.

为优化和最大程度减少写入到日志中的数据量，请参阅事务最佳做法一文。To optimize and minimize the amount of data written to the log, please refer to the Transactions best practices article.

事务状态Transaction state

SQL 池使用 XACT_STATE() 函数（采用值 -2）来报告失败的事务。SQL pool uses the XACT_STATE() function to report a failed transaction using the value -2. 此值表示事务已失败并标记为仅可回滚。This value means the transaction has failed and is marked for rollback only.

SQL Server 还允许读取无法提交的事务。SQL Server also permits reads in the uncommittable transaction. 但是，SQL 池不允许执行此操作。However, SQL pool does not let you do this. 如果 SQL 池事务内部发生错误，它会自动进入 -2 状态，并且在该语句回退之前，你无法执行任何 Select 语句。If an error occurs inside a SQL pool 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.

因此，必须查看应用程序代码是否使用 XACT_STATE()，因为你可能需要修改代码。As such, it's important to check that your application code to see if it uses XACT_STATE() as you may need to make code modifications.

例如，在 SQL Server 中，可能会看到如下所示的事务：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;

前面的代码提供以下错误消息：The preceding code gives the following error message:

Msg 111233, Level 16, State 1, Line 1 111233；当前事务已中止，所有挂起的更改都已回退。Msg 111233, Level 16, State 1, Line 1 111233; The current transaction has aborted, and any pending changes have been rolled back. 此问题的原因：处于仅回退状态的事务未在 DDL、DML 或 SELECT 语句之前显式回退。The cause of this issue is that a transaction in a rollback-only state not being explicitly rolled back before a DDL, DML, or SELECT statement.

不会获得 ERROR_* 函数的输出值。You won't get the output of the ERROR_* functions.

在 SQL 池中，该代码需要稍做更改：In 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;

现在观察到了预期行为。The expected behavior is now observed. 事务中的错误得到了管理，并且 ERROR_* 函数提供了预期值。The error in the transaction is managed and the ERROR_* functions provide values as expected.

所做的一切改变是事务的 ROLLBACK 必须发生于在 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.

Error_Line() 函数Error_Line() function

另外值得注意的是，SQL 池未实现或不支持 ERROR_LINE() 函数。It is also worth noting that SQL pool does not implement or support the ERROR_LINE() function. 如果代码中包含此函数，需要将它删除才能符合 SQL 池的要求。If you have this in your code, you need to remove it to be compliant with SQL pool.

请在代码中使用查询标签，而不是实现等效的功能。Use query labels in your code instead to implement equivalent functionality. 有关详细信息，请参阅 LABEL 一文。For more details, see the LABEL article.

使用 THROW 和 RAISERRORUsing THROW and RAISERROR

THROW 是在 SQL 池中引发异常的新式做法，但也支持 RAISERROR。THROW is the more modern implementation for raising exceptions in SQL pool but RAISERROR is also supported. 不过，有些值得注意的差异。There are a few differences that are worth paying attention to however.

• 对于 THROW，用户定义的错误消息数目不能在 100,000 - 150,000 的范围内User-defined error messages numbers cannot be in the 100,000 - 150,000 range for THROW
• RAISERROR 错误消息固定为 50,000RAISERROR error messages are fixed at 50,000
• 不支持 sys.messagesUse of sys.messages is not supported

限制Limitations

SQL 池有一些与事务相关的其他限制。SQL pool does have a few other restrictions that relate to transactions.

这些限制如下：They are as follows:

• 无分布式事务No distributed transactions
• 不允许嵌套事务No nested transactions permitted
• 不允许保存点No save points allowed
• 无已命名事务No named transactions
• 无已标记事务No marked transactions
• 不支持 DDL，例如用户定义的事务内的 CREATE TABLENo support for DDL such as CREATE TABLE inside a user-defined transaction

后续步骤Next steps

若要了解有关优化事务的详细信息，请参阅事务最佳做法。To learn more about optimizing transactions, see Transactions best practices. 若要了解其他 SQL 池最佳做法，请参阅 SQL 池最佳做法。To learn about other SQL pool best practices, see SQL pool best practices.