在 Azure Synapse 的 SQL 池中使用事务

本文包含有关在 SQL 池中实现事务和开发解决方案的技巧。

期望

如你所料，SQL 池支持将事务作为数据仓库工作负载的一部分。 但是，为了确保 SQL 池保持在一定规模，相比于 SQL Server，某些功能会受到限制。 本文重点介绍了两者的差异。

事务隔离级别

SQL 池实现了 ACID 事务。 事务支持的隔离级别默认为 READ UNCOMMITTED。 在连接到 master 数据库时，可以通过打开用户 SQL 池的 READ_COMMITTED_SNAPSHOT 数据库选项，将其更改为 READ COMMITTED SNAPSHOT ISOLATION。

启用后，此数据库中的所有事务都将在 READ COMMITTED SNAPSHOT ISOLATION 下执行，并且将不接受在会话级别设置 READ UNCOMMITTED。 有关详细信息，请查看 ALTER DATABASE SET 选项 (Transact-SQL)。

事务大小

单个数据修改事务有大小限制。 限制按每个分发进行应用。 因此，通过将限制乘以分发数，可得总分配额。

要预计事务中的最大行数，请将分发上限除以每一行的总大小。 对于可变长度列，考虑采用平均的列长度而不使用最大大小。

下表中做了两个假设：

• 出现平均数据分布
• 平均行长度为 250 个字节

Gen2

Gen1

事务大小限制按每个事务或操作进行应用。 不会跨所有当前事务进行应用。 因此，允许每个事务向日志写入此数量的数据。

为优化和最大程度减少写入到日志中的数据量，请参阅事务最佳做法一文。

事务状态

SQL 池使用 XACT_STATE() 函数（采用值 -2）来报告失败的事务。 此值表示事务已失败并标记为仅可回滚。

SQL Server 还允许读取无法提交的事务。 但是，SQL 池不允许执行此操作。 如果 SQL 池事务内部发生错误，它会自动进入 -2 状态，并且在该语句回退之前，你无法执行任何 Select 语句。

因此，必须查看应用程序代码是否使用 XACT_STATE()，因为你可能需要修改代码。

例如，在 SQL Server 中，可能会看到如下所示的事务：

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;

前面的代码提供以下错误消息：

Msg 111233, Level 16, State 1, Line 1 111233；当前事务已中止，所有挂起的更改都已回退。 此问题的原因：处于仅回退状态的事务未在 DDL、DML 或 SELECT 语句之前显式回退。

不会获得 ERROR_* 函数的输出值。

在 SQL 池中，该代码需要稍做更改：

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;

现在观察到了预期行为。 事务中的错误得到了管理，并且 ERROR_* 函数提供了预期值。

所做的一切改变是事务的 ROLLBACK 必须发生于在 CATCH 块中读取错误信息之前。

Error_Line() 函数

另外值得注意的是，SQL 池未实现或不支持 ERROR_LINE() 函数。 如果代码中包含此函数，需要将它删除才能符合 SQL 池的要求。

请在代码中使用查询标签，而不是实现等效的功能。 有关详细信息，请参阅 LABEL 一文。

使用 THROW 和 RAISERROR

THROW 是在 SQL 池中引发异常的新式做法，但也支持 RAISERROR。 不过，有些值得注意的差异。

• 对于 THROW，用户定义的错误消息数目不能在 100,000 - 150,000 的范围内
• RAISERROR 错误消息固定为 50,000
• 不支持 sys.messages

限制

SQL 池有一些与事务相关的其他限制。

这些限制如下：

• 无分布式事务
• 不允许嵌套事务
• 不允许保存点
• 无已命名事务
• 无已标记事务
• 不支持 DDL，例如用户定义的事务内的 CREATE TABLE