数据库并发模式 - SIP 和 SUP

• 下载源代码 - 428 KB

引言

数据库并发，这个词我们希望它总是描述一种健康的现实状态，而不是一个需要解决的问题。本文介绍的模式解决了两个有趣的数据库并发问题。

1. 单一插入模式 (SIP) - 始终拥有唯一行而不违反约束
2. 单一更新模式 (SUP) - 始终拥有安全、自定义的唯一标识符

背景

我喜欢搜索可重用的模式。模式让生活更轻松，使我们能够解决新颖有趣的问题，而不是重温旧问题。我一直在思考两个挑战

1. 有没有简单的方法可以防止数据重复？是否必须始终依赖唯一性约束来防止重复？
2. 自定义标识符。以下代码在用于生成自定义唯一标识符时并不安全。有没有一种安全、简单的方法来做到这一点？

select max(identifier) from mytable

于是，我对此思考了很久。幸运的是，SQL2005 有一些非常出色的功能，使得这些挑战的优雅解决方案成为可能。

• OUTPUT 子句
• INSTEAD OF INSERT 触发器

太棒了——上面两个代码结构赋予了我们如此强大的能力。第一个使得 SUP 成为可能。第二个使得 SIP 成为可能。现在，让我更详细地描述这些模式。请允许我介绍以下两种模式：

1. 单一插入模式 (SIP) - 无论并发数据库活动如何，它都能确保对于任何给定的标准集，一个 Insert 操作始终对应数据库表中的唯一行。可以这样想：与其担心违反唯一性约束或出现重复数据，不如从一开始就防止它发生？
2. 单一更新模式 (SUP) - 它保证对于“单个行的数据库表”，一个 Update 操作将 **始终** 为我们生成一个安全唯一的自定义标识符。我认为关于这一点我不需要再说更多了——除了它非常酷且非常有帮助。 :)

使用代码

为了使用代码，您必须附加到附加 zip 文件中包含的 SQL2005 Express 数据库。附加的 zip 文件包含两个 C# 项目：

• SingleInsertPattern
• SingleUpdatePattern

SIP 模式的关键部分是以下触发器：

ALTER TRIGGER .[dbo].[SingleInsertTableTrigger] 
   ON  [dbo].[SingleInsertTable] 
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    IF(SELECT COUNT(*) FROM SingleInsertTable) = 0
    BEGIN
        INSERT INTO SingleInsertTable
        SELECT
        Update_Time,
        Operation,
        ThreadId
        FROM inserted
    END
    ELSE
    BEGIN
        UPDATE SingleInsertTable
        SET
        Update_Time = i.Update_Time,
        Operation = i.Operation,
        ThreadId = i.ThreadId
        FROM inserted i
    END
END

SUP 模式的关键部分是 SingleUpdateTableCall 存储过程中的以下代码：

-- CLAIM SOME NUMBERS - non-reset identifier
UPDATE SingleUpdateTable
SET
LatestIdentifier = LatestIdentifier + @NumberClaimed,
LatestThread = @ThreadId,
NumberClaimed = @NumberClaimed,
ClaimDate = cast(floor(CAST(GETDATE() as float)) as datetime)
OUTPUT INSERTED.* INTO @SingleUpdateTable

-- CLAIM SOME NUMBERS - identifier is reset every day
    -- UNCOMMENT the following statement instead of using the one above
    -- to reset the unique identifier every day.
/*    UPDATE SingleUpdateTable
    SET
    LatestIdentifier = 
    case when cast(floor(CAST(GETDATE() as float)) > 
              ClaimDate then 1 else LatestIdentifier end 
    + @NumberClaimed,
    LatestThread = @ThreadId,
    NumberClaimed = @NumberClaimed,
    ClaimDate = cast(floor(CAST(GETDATE() as float)) as datetime)
    OUTPUT INSERTED.* INTO @SingleUpdateTable
*/

包含的 C# 项目被编写为此处介绍的模式的测试环境。对于 **SingleInsertPattern** 测试环境，您可以指定以下变量：

• 要创建的线程数。
• 每个线程要执行的测试次数。

**SingleInsertPattern** 测试环境中存在以下选项卡：

• 结果 - 应用到数据库的插入操作收到的结果。
• 数据库 - 存储在数据库中的插入历史记录。这对于与结果选项卡进行比较很有用。

对于 **SingleUpdatePattern** 测试环境，您可以指定以下变量：

• 要创建的线程数。
• 每个线程要执行的测试次数。
• 要声明的最大数字数量 - 这使我们能够模拟每个测试要使用的标识符数量的变化。

**SingleUpdatePattern** 测试环境中存在以下选项卡：

• 结果 - 这些是从数据库收到的结果。
• 数据库 - 这是唯一标识符更新活动的历史记录。
• 操作 - 这是在数据库中存储的实际唯一标识符生成历史记录。这对于与在唯一标识符生成期间收到的结果进行比较很有用。

就是这些了——我希望您觉得这些模式和我一样有用。

关注点

**过度使用触发器的危险……** 在我最初撰写本文期间，我有机会充分体验了“过度使用触发器”的危险。我继承了一些生产代码，其中有一个表的触发器（运行良好）。该表还有一个在其历史表上的触发器，该触发器通过执行全表扫描降低了主表的插入速度。这花了一些时间才找到。SQL Profiler 跟踪帮助我们在 13 日星期五的深夜发现了问题。由于 SIP 使用触发器……这让我想到，如果您使用 SIP，就应该高效地使用 SIP。:) 好了，这是一个糟糕的双关语。

历史

• 2008 年 7 月 29 日 - 初始发布。