主页 / dba / 问题 / 29426
Accepted
DamagedGoods
Asked: 2012-11-29 03:59:58 +0800 CST

除以零导致错误事务在升级后因算术中止和 ANSI WARNINGS FALSE 而失败

  • 772

这是一个奇怪的问题..

我们收到除以零的错误，导致 SQL Server 终止事务并使测试网站崩溃。

Arithmetic Abort 与 ANSI 警告和所有其他默认值一起设置为 FALSE...没有发生代码更改，因此它也没有在代码中明确设置。

这只发生在服务器上包含非常相似的数据和相同元数据的大多数数据库上，但不是全部。

在我将 2008 测试服务器就地升级到 2012 后，这种情况开始发生。（该问题出现在两种兼容模式下）

如果我在查询窗口中明确地将 ARITHMETICABORT 设置为 false，它运行良好……似乎忽略了数据库范围的设置。这发生在管理工作室或代码中

这是我们检测到的第一段代码，供参考，但我认为这与代码无关

    SELECT  sm.MarkupId, sm.WebSiteCode, sm.CurrencyCode, sm.OfferTitle, sm.Priority, sm.MarkupPercent, sm.MarkupDownAmount, sm.Cumulative,
            sm.AllowAdditionalDiscounts, sm.StartDate, sm.EndDate, sm.DayOfWeek, sm.StartTime, sm.EndTime, sm.DepartureStartDate, sm.DepartureEndDate,
            ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId, ISNULL(sb.CommissionPercent, 0) AS CommissionPercent, eg.Title AS EstabGroup,
            d.DomainName, sm.DurationFrom, sm.DurationTo, sm.BookingTotalFrom, sm.BookingTotalTo,
            ActualCommission = CASE WHEN ISNULL(sb.CommissionPercent, 0) = 0 THEN CAST(( ( sm.MarkupPercent - 100 ) / sm.MarkupPercent ) * 100 AS DECIMAL(18, 2))
                                    ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - ( 100.0 - sm.MarkupPercent ), 2)
                               END, sm.BoardTypeId, BoardTitle = ISNULL(b.Title, '')
    FROM    SupplierMarkup_tbl sm
            INNER JOIN Domain_tbl d ON d.DomainId = sm.DomainId
            LEFT JOIN Supplier_tbl s ON sm.SupplierId = s.SupplierId
            LEFT JOIN acomEstabGroup_tbl eg ON sm.EstabGroupId = eg.EstabGroupId
            LEFT JOIN SupplierBookedItemType_tbl sb ON sb.SupplierId = s.SupplierId
                                                       AND sb.WebsiteCode = sm.WebSiteCode
                                                       AND sb.BookedItemTypeId = 17
            LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = sm.BoardTypeId
    WHERE   1 = 1
    UNION
    SELECT  CASE WHEN cm.IsExtranet = 1
                      AND cm.GrossAdjustment = 1 THEN -2
                 WHEN cm.IsExtranet = 1
                      AND cm.NetAdjustment = 1 THEN -1
                 ELSE 0
            END AS MarkupId, '' AS WebSiteCode, '' AS CurrencyCode, mo.Title AS OfferTitle, -1 AS Priority, cm.MarkupPercent * 100 AS MarkupPercent,
            0 AS MarkupDownAmount, cm.Cumulative, cm.AllowAdditionalDiscounts, cm.StartDate, cm.EndDate, cm.DayOfWeek, cm.StartTime, cm.EndTime,
            cm.CheckInStartDate AS DepartureStartDate, cm.CheckInEndDate AS DepartureEndDate, ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId,
            0 AS CommissionPercent, '' AS EstabGroup, d.DomainName, cm.DurationFrom, cm.DurationTo, cm.BookingTotalFrom, cm.BookingTotalTo, ActualCommission = 0,
            cm.BoardTypeId, BoardTitle = ISNULL(b.Title, '')
    FROM    conContractEstabMarkup_tbl cm
            INNER JOIN Domain_tbl d ON d.DomainId = cm.DomainId
            LEFT JOIN Supplier_tbl s ON s.SupplierId = 97
            LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = cm.BoardTypeId
            LEFT JOIN dbo.MarkupOffer_tbl mo ON mo.MarkupOfferId = cm.MarkupOfferId
    WHERE   cm.EstabId IS NULL
            AND cm.Deleted = 0
    ORDER BY Priority, Supplier

这不是严格意义上的答案，因为我不知道为什么会发生这种情况，但我找到了根本原因。希望有人能告诉我为什么会发生这种行为，或者它是否是一个错误？

当 UNION 之后的第二条语句不返回任何行时，就会发生错误。

好久没见过这样的了！

sql-server-2012

1 个回答

  1. Best Answer

    问题出在表达式中：

    ActualCommission = 
    CASE 
        WHEN ISNULL(sb.CommissionPercent, 0) = 0
        THEN CAST(((sm.MarkupPercent - 100) / sm.MarkupPercent) * 100 AS decimal(18, 2))
        ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - (100.0 - sm.MarkupPercent), 2)
    END

    CommissionPercent = 0和的任何行都可能发生被零除错误MarkupPercent = 0。在实践中是否实际发生错误取决于运行时的评估顺序，而这又取决于执行计划的形状和各个计划操作员的详细行为。

    例如，某些计划形状可能会在计算表达式之前从物理上消除有问题的行。其他人可能会推迟对表达式的评估，直到计划中稍后的另一个运算符需要结果为止。这里有许多可能性和微妙之处，导致您观察到明显的“奇怪”行为。

    通常，SQL Server 不保证标量表达式的计算顺序或特定表达式在运行时可能被计算的次数。例外情况是CASE声明，它在大多数情况下确实提供有关评估顺序的保证。从文档中：

    CASE 语句按顺序评估其条件，并在满足条件的第一个条件处停止。在某些情况下，在 CASE 语句接收表达式的结果作为其输入之前计算表达式。评估这些表达式时可能会出错。出现在 CASE 语句的 WHEN 参数中的聚合表达式首先被求值，然后提供给 CASE 语句。

    防止错误的方法是修改CASE表达式，以便使用较早的WHEN子句显式处理被零除。您选择如何使用 和 来计算行，CommissionPercent = 0这MarkupPercent = 0只有信息才能决定。也就是说，ARITHABORT设置为的行为OFF是返回 a NULL。对语句的以下更改CASE会生成ARITHABORT设置为 highly-recommended 的结果ON：

    ActualCommission = 
    CASE
        -- New
        WHEN (sb.CommissionPercent = 0 OR sb.CommissionPercent IS NULL) AND sm.MarkupPercent = 0
        THEN NULL

        WHEN ISNULL(sb.CommissionPercent, 0) = 0
        THEN CAST(((sm.MarkupPercent - 100) / sm.MarkupPercent) * 100 AS decimal(18, 2))
        ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - (100.0 - sm.MarkupPercent), 2)
    END

    明确考虑被零除错误的可能性比依赖ARITHABORT设置要好得多。该链接包含以下声明：

    您应该始终在登录会话中将 ARITHABORT 设置为 ON。将 ARITHABORT 设置为 OFF 会对查询优化产生负面影响，从而导致性能问题。

    ARITHABORT需要ON用于许多较新的引擎功能（例如计算列和索引视图上的索引）。大多数客户端库在连接时明确设置 ARITHABORT为，这会覆盖任何数据库或服务器级别的设置。ON

    我的强烈建议是始终使用推荐的SET设置和代码来防御错误情况。

    SET ANSI_NULLS,
    ANSI_PADDING,
    ANSI_WARNINGS,
    ARITHABORT,
    CONCAT_NULL_YIELDS_NULL,
    QUOTED_IDENTIFIER
    ON;  

SET NUMERIC_ROUNDABORT OFF;
    • 3

相关问题