65.9K
CodeProject 正在变化。 阅读更多。
Home

异步触发器和游标

h_wiedey
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.43/5 (6投票s)

2013 年 11 月 22 日

CPOL

5分钟阅读

viewsIcon

18708

使用游标异步处理触发器。

介绍 

在关系数据库管理系统 (RDBMS) 中，所有触发器都是同步的，这意味着触发语句之后的下一条语句只有在触发器执行完成后才会执行。

然而，在某些情况下，异步执行可以是一个有效的选项。本文将介绍如何有效地使用游标来实现异步触发器。

触发器中的游标

首先，在触发器中不必使用游标来循环遍历语句影响的所有表行。即使是专业的 MS-SQL（和 Sybase）开发人员也常有一个误解，认为当触发语句影响多行时就必须使用游标。下面的示例展示了一个不使用游标的简单基于集合的触发器是如何工作的。

--The below SQL implements a simple set based trigger
USE sandbox
GO
 
CREATE TABLE Numbers(
    ID int NOT NULL,
    Number int NOT NULL,
    CurrentNumber int NULL,
    PreviousNumber int NULL,
    CONSTRAINT pk_Numbers PRIMARY KEY (ID)
)
GO
 
CREATE TRIGGER tiu_Numbers ON Numbers FOR INSERT, UPDATE
AS
BEGIN
    PRINT 'TRIGGER tiu_Numbers: Updating Numbers.CurrentNumber from inserted'
    UPDATE Numbers 
    SET CurrentNumber = i.Number
    from Numbers n
    JOIN inserted i ON i.ID = n.ID
    PRINT 'Number of rows updated: ' + CONVERT(VARCHAR,@@rowcount)
 
    PRINT 'TRIGGER tiu_Numbers: Updating Numbers.PreviousNumber from deleted'
    UPDATE Numbers 
    SET PreviousNumber = d.Number
    from Numbers n
    JOIN deleted d ON d.ID = n.ID
    PRINT 'Number of rows updated: ' + CONVERT(VARCHAR,@@rowcount)
END
GO
 
INSERT Numbers (ID, Number) VALUES (1, 8), (2, 10), (3, 12)
GO

-- Output is: 

TRIGGER tiu_Numbers: Updating Numbers.CurrentNumber from inserted
 
(3 row(s) affected)
Number of rows updated: 3
TRIGGER tiu_Numbers: Updating Numbers.PreviousNumber from deleted
 
(0 row(s) affected)
Number of rows updated: 0
 
(3 row(s) affected)

-- End output

SELECT * FROM Numbers
GO

-- Output is: 
 
ID          Number      CurrentNumber PreviousNumber
----------- ----------- ------------- --------------
1           8           8             NULL
2           10          10            NULL
3           12          12            NULL
 
(3 row(s) affected)

-- End output

UPDATE Numbers SET Number = Number + 2
GO
 
-- Output is:
 
TRIGGER tiu_Numbers: Updating Numbers.CurrentNumber from inserted
 
(3 row(s) affected)
Number of rows updated: 3
TRIGGER tiu_Numbers: Updating Numbers.PreviousNumber from deleted
 
(3 row(s) affected)
Number of rows updated: 3
 
(3 row(s) affected)

-- End output
 
SELECT * FROM Numbers
GO

-- Output is: 

ID          Number      CurrentNumber PreviousNumber
----------- ----------- ------------- --------------
1           10          10            8
2           12          12            10
3           14          14            12
 
(3 row(s) affected)

-- End output

如上所示，对 inserted 或 deleted 表的基于集合的操作可以覆盖所有受影响的行。事实上，使用游标通常是一个糟糕的选择，因为它们的执行速度通常比基于集合的操作（例如 JOIN）慢。为了使用游标实现上述示例，甚至需要实现两个游标（一个循环遍历 inserted，一个循环遍历 deleted），这会使代码长度增加一倍以上，执行速度也会变慢。

然而，在无法进行基于集合的操作或希望逐行处理的情况下，使用游标是一个有效的选项。例如：

  • 对受影响的每一行调用存储过程。
  • 调用 SQL 命令（例如 PRINT、SEND）并将受影响行中的值作为命令参数使用。
  • 执行管理任务（例如，转储数据库、更新统计信息）
  • 由于应用的复杂性，无法进行基于集合的操作

下一个示例展示了一种需要使用 PRINT 语句的情况。

--Simple Print cursor
CREATE TABLE PrintNumbers(
    ID int NOT NULL,
    Number int NOT NULL,
    CONSTRAINT pk_Numbers PRIMARY KEY (ID)
)
GO

DROP TRIGGER tiu_PrintNumbers
GO

CREATE TRIGGER tiu_PrintNumbers ON PrintNumbers FOR INSERT, UPDATE
AS
BEGIN
    PRINT 'TRIGGER tiu_PrintNumbers: Declaring cursor'
    DECLARE cursor_PrintNumbers CURSOR
    FOR SELECT ID, Number FROM inserted

    DECLARE @id int, @number int

    PRINT 'Opening cursor'
    OPEN cursor_PrintNumbers

    PRINT 'Fetch first row'
    FETCH NEXT FROM cursor_PrintNumbers INTO @id, @number
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT 'Number is: ' + CONVERT(varchar, @number)
        FETCH NEXT FROM cursor_PrintNumbers INTO @id, @number
    END

    PRINT 'Closing cursor and waiting'
    CLOSE cursor_PrintNumbers

    PRINT 'Deallocating cursor'
    DEALLOCATE cursor_PrintNumbers
END
GO

INSERT PrintNumbers (ID, Number) VALUES (1, 8), (2, 10), (3, 12)
GO

-- Output is: 

TRIGGER tiu_PrintNumbers: Declaring cursor
Opening cursor
Fetch first row
Number is: 12
Number is: 10
Number is: 8
Closing cursor and waiting
Deallocating cursor

(3 row(s) affected)

-- End output

在上面的示例中，需要使用游标，因为 PRINT 语句不能直接作为基于集合的操作的一部分来执行。

虽然通常不建议使用游标，但它们具有一些使其对异步处理感兴趣的特性。

异步触发器

为了保证一致性，RDBMS 通常不支持本地异步执行触发器。然而，可以通过标记受触发器影响的行（例如，使用 clean/dirty 标志）或让触发器将它们复制到单独的表中以供以后处理来实现异步处理。在这两种情况下，都需要另一个进程定期收集要处理的行。

在语句返回比等待触发器完成其应做的工作更重要的情况下，值得考虑异步执行触发器。

这可能是在以下情况下：

  • 当不需要立即执行时（例如，消息传递）
  • 当执行触发器中的操作会花费太长时间时（例如，触发器需要执行复杂、耗时的操作）
  • 当触发器的开销不会导致高效处理时（例如，只遍历少量行的游标）

但是，使用异步执行时存在一些风险需要考虑：

  • 要处理的队列行可能在被添加到队列时和实际被收集进程拾取时之间发生更改。
  • 在读取行进行进一步处理和写入结果之间的时间段内，原始行可能已更改。
  • 在异步处理完成之前，数据可能不一致或不完整。

为了解决最后一点，在编写访问异步处理的数据的 SQL 语句时需要格外小心。为了解决前两点，游标可以很好地发挥作用。与其他在常规触发器中通常是糟糕选择的游标不同，它们具有一些在声明游标时可以启用的有趣特性，这使得它们成为异步处理的首选。声明语句如下：

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

(参见 http://technet.microsoft.com/en-us/library/ms180169%28v=sql.105%29.aspx)

从上面来看，有趣的选项是：

  • KEYSET：获取一行时，使用基表的键列从基表中选择实际行。这确保始终获取最新值。但是，在游标打开时定义了作用域内的行。后续插入的行将被忽略。
  • DYNAMIC：每次获取一行时都会执行 select。这也确保获取行的最新值。DYNAMIC 比 KEYSET 选项慢，但可以识别后续插入的行。
  • SCROLL_LOCKS：获取行时会对其进行独占锁定，以防止其他人更改。与 DYNAMIC 一起使用时，仅锁定当前获取的行。与 KEYSET 一起使用时，在打开游标时锁定所有行。
  • OPTIMISTIC：在写入（使用 UPDATE WHERE CURRENT）之前，会检查该行自获取以来是否已更改。写入更改行的尝试将失败，并且可以采取适当的措施。

这些功能也可以手动实现，但与使用内置功能相比效率较低。

示例

在第一个示例中，应该为每个表行生成一个随机数，直到生成的随机数在指定的近似值范围内。

--Repetitively generate random number for each row in table Random

CREATE TABLE Random(
    ID int NOT NULL,
    Number int NOT NULL CHECK (Number BETWEEN 0 AND 99),
    Tolerance int NOT NULL CHECK (Tolerance BETWEEN 0 AND 100),
    Approximation int NULL,
    Iteration int NULL,
    DirtyFlag CHAR(1) NOT NULL DEFAULT 'D' CHECK (DirtyFlag in ('C','D')),
    CONSTRAINT pk_Random PRIMARY KEY (ID)
)
GO

--Trigger to mark rows for later processing
CREATE TRIGGER tiu_Random ON Random FOR INSERT, UPDATE
AS
BEGIN
    IF NOT UPDATE (DirtyFlag)
    BEGIN
        PRINT 'Setting dirty flag.'
        UPDATE Random
        SET DirtyFlag = 'D'
        FROM Random r 
        JOIN inserted i ON i.ID = r.ID
        WHERE r.DirtyFlag <> 'D'
    END
END
GO

--Procdure to process rows in Random and updating Approximation, Iteration, DirtyFlag
CREATE PROCEDURE pr_processRandom
AS
BEGIN
    PRINT 'Declaring cursor'
    DECLARE cursor_Random CURSOR SCROLL KEYSET OPTIMISTIC
    FOR SELECT Number, Tolerance FROM Random WHERE DirtyFlag = 'D'
    FOR UPDATE OF Approximation, Iteration, DirtyFlag

    DECLARE @number int, @tolerance int, @approximation int, @iteration int, @match int

    PRINT 'Opening cursor'
    OPEN cursor_Random

    PRINT 'Fetch first row'
    FETCH NEXT FROM cursor_Random INTO @number, @tolerance

    PRINT 'Start looping'
    WHILE (@@FETCH_STATUS = 0)
    BEGIN

        SELECT @iteration = 0
        SELECT @match = 0

        PRINT 'Processing'
        WHILE (@match = 0)
        BEGIN
            SELECT @iteration = @iteration + 1
            SELECT @approximation = CONVERT(INT, RAND() * 100)
            IF @tolerance > 0
            BEGIN
                IF @approximation = @number SELECT @match = 1
            END
            ELSE 
            BEGIN
                IF @number BETWEEN (@approximation - (@number * @tolerance/100)) 
                    AND (@approximation + (@number * @tolerance/100)) 
                SELECT @match = 1 
            END
        END

        PRINT 'Update current row'
        UPDATE Random
        SET Approximation = @approximation, Iteration = @iteration, DirtyFlag = 'C'
        WHERE CURRENT OF cursor_Random

        IF @@ERROR = 16947
        BEGIN
            PRINT 'Refetching current row as it was changed outside of this cursor'
            FETCH RELATIVE 0 FROM cursor_Random INTO @number, @tolerance
        END
        ELSE
        BEGIN
            PRINT 'Fetch next row'
            FETCH NEXT FROM cursor_Random INTO @number, @tolerance
        END
    END

    PRINT 'Closing cursor'
    CLOSE cursor_Random

    PRINT 'Deallocating cursor'
    DEALLOCATE cursor_Random
END
GO

INSERT Random (ID, Number, Tolerance) VALUES (1, 8, 10), (2, 10, 20), (3, 80, 5)
GO

EXEC pr_processRandom
GO

-- Output:

Declaring cursor
Opening cursor
Fetch first row
Start looping
Processing
Update current row

(1 row(s) affected)
Fetch next row
Processing
Update current row

(1 row(s) affected)
Fetch next row
Processing
Update current row

(1 row(s) affected)
Fetch next row
Closing cursor
Deallocating cursor

-- End output

SELECT *
FROM Random
GO

-- Output:

ID          Number      Tolerance   Approximation Iteration   DirtyFlag
----------- ----------- ----------- ------------- ----------- ---------
1           8           10          8             8           C
2           10          20          10            208         C
3           80          5           80            68          C

(3 row(s) affected)

-- End output

可以使用以下方法安排此过程定期运行：

  • 外部调度器
  • MS SQL Server 内置调度器（MS SQL Server Express 版本不可用）
  • 系统存储过程 sp_procoption

下面的代码片段显示了如何使用 sp_procoption 在 MS SQL Server 实例启动时安排 pr_processRandom 运行。

USE master
GO
--Wrapper procedure BackgroundTask to call pr_processRandom with a 1 minute waiting period
CREATE PROCEDURE BackgroundTask
AS
BEGIN
    WHILE OBJECT_ID ('sandbox..pr_processRandom') IS NOT NULL
    BEGIN
        EXEC sandbox..pr_processRandom
        WAITFOR DELAY '00:01' 
    END
END
GO
--Schedule BackgroundTask to be started on startup
sp_procoption   @ProcName = 'BackgroundTask',
                @OptionName = 'startup',
                @OptionValue = 'on'
GO

关注点

请参阅以下 URL：

http://technet.microsoft.com/en-us/library/ms190211%28v=sql.105%29.aspx 关于游标锁定，

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx 关于游标性能测试，

http://msdn.microsoft.com/en-us/library/windows/desktop/ms675119%28v=vs.85%29.aspx 关于游标类型，

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0d1bc3cd-22d6-4b8f-bf87-ecc0121ac85b/what-is-the-difference-between-static-cursordynamic-cursor-and-keyset-cursor?forum=transactsql 关于不同游标类型的快速概述。

历史 

2013 年 11 月：初始版本。

© . All rights reserved.