SQL Server 2008 中的线程安全

引言

本文的目的是演示一个常见的陷阱，即在没有充分关注线程安全的情况下访问共享数据。

背景

根据维基百科（以及许多学者）的定义，线程安全是

线程安全是一个计算机编程概念，适用于多线程程序。一段代码如果是线程安全的，那么它在多个线程同时执行时就能正确运行。特别是，它必须满足多个线程访问同一共享数据的需求，并且在任何给定时间只有一个线程可以访问某个共享数据项的需求。

在设计用于多线程、多源访问的应用程序或服务时，这一主题常常被忽略。在本文中，我们将分析一个需要将共享的增量密钥存储在数据库中的场景。我们将介绍几个无效的解决方案，然后我将演示 SQL 2008 及以上版本中提供的一项新功能，该功能极大地简化了此过程。

设置测试表

我们的共享数据将允许为应用程序及其子系统生成增量密钥，通过一对字符串进行标识。让我们先创建一个支持此类场景的表。

CREATE TABLE [dbo].[UniqueKeys](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [App] [nvarchar](100) NOT NULL,
    [System] [nvarchar](100) NOT NULL,
    [Key] [bigint] NOT NULL
) ON [PRIMARY]   

Id 列仅用于良好度量，以便我们跟踪事物如何变化。应用程序将通过最多 100 个字符的 string 进行标识，其子系统也是如此。最终的解决方案必须

• 如果 App/System 对不存在，则为其创建一个新的记录，并将 Key 值设为 1
• 如果 App/System 对已存在，则递增该记录中由 App/System 对标识的 Key 值
• 保证多个内部和外部线程的线程安全

辅助方法 (Helper Methods)

让我们创建两个方法，它们将帮助我们获取数据并执行 update 语句。

#region Constants

private const string CApp = "GetUniqueKeyBadCode";

private const string CSystem = "Shared";

#endregion

#region Static Fields

private static readonly SqlConnectionStringBuilder Scsb = new SqlConnectionStringBuilder
                                                              {
                                                                  DataSource = @"localhost\SQLEXPRESS",
                                                                  InitialCatalog = "Claims",
                                                                  IntegratedSecurity = true
                                                              };

#endregion

#region Public Methods and Operators

public static void ExecuteSql(string sql)
{
    using (var sqlConn = new SqlConnection(Scsb.ConnectionString))
    {
        using (var sqlCommand = new SqlCommand(sql, sqlConn))
        {
            sqlConn.Open();
            sqlCommand.ExecuteNonQuery();
        }
    }
}

public static IEnumerable<IDataRecord> GetData(string sql)
{
    using (var sqlConn = new SqlConnection(Scsb.ConnectionString))
    {
        using (var sqlCommand = new SqlCommand(sql, sqlConn))
        {
            sqlConn.Open();
            SqlDataReader reader = sqlCommand.ExecuteReader();
            foreach (IDataRecord dataRecord in reader)
            {
                yield return dataRecord;
            }
        }
    }
}

#endregion

我总是偏好使用 SqlConnectionStringBuilder，因为我永远记不住每个参数的正确命名约定。您需要更新 InitialCatalog 属性以指向您的数据库。

接下来，我们创建一个简单的辅助方法 ExecuteSql，它将执行传入的 SQL 语句，而不期望返回任何值。在生产环境中，我会通过要求所有查询都进行参数化来扩展它，因为使用动态 SQL 会使您的应用程序面临 SQL 注入攻击的风险。GetData 辅助方法返回 IDataRecord 的 IEnumerable，并由调用方法自行正确检索和处理返回的每个记录和列。同样，在生产环境中，这也会进行参数化。

您可能想查看我关于此主题的 技巧...

C# 中的无效解决方案

我们的第一次尝试将是在共享的 C# 方法 GetUniqueKey 中处理 Key 的增量。

private static long GetUniqueKey()
{
    List<IDataRecord> current = GetData(string.Format("SELECT [Key] _
    FROM dbo.UniqueKeys WHERE [App]='{0}' And [System]='{1}'", CApp, CSystem)).ToList();
    if (current.Any())
    {
        //Record found
        long currentKey = current.First().GetInt64(0) + 1;
        ExecuteSql(string.Format("UPDATE dbo.UniqueKeys SET [Key]={0} _
        WHERE [App]='{1}' AND [System]='{2}'", currentKey, CApp, CSystem));
        return currentKey;
    }
    //Record not found
    ExecuteSql(string.Format("INSERT INTO [dbo].[UniqueKeys] _
    ([App],[System],[Key])VALUES('{0}','{1}',1)", CApp, CSystem));
    return 1;
}

表面上看，这个方法看起来完全没问题，并且似乎实现了预期的结果。首次执行时，它会创建一个新记录

Id    App                      System       Key
----- ------------------------ ------------ --------------------
1     GetUniqueKeyBadCode      Shared       1

第二次执行时，它将返回 2，依此类推，只要该方法是顺序执行的。然而，当您从多个线程执行同一个方法时，情况会发生戏剧性的变化。测试它的最简单方法是使用 Task Parallel Library。

Parallel.For(0, 100, i => GetUniqueKeyBadCode());

根据您的计算机架构，您将得到不同的结果，但在我的情况下，一台四核笔记本电脑，我得到了四个相同的记录

Id                   App                  System     Key
-------------------- -------------------- ---------- --------------------
1                    GetUniqueKeyBadCode  Shared     31
2                    GetUniqueKeyBadCode  Shared     31
3                    GetUniqueKeyBadCode  Shared     31
4                    GetUniqueKeyBadCode  Shared     31

之所以出现这个结果，是因为当我的并行循环开始时，前四个请求（分别发送到四个不同的核心）没有找到预先存在的记录，因此在四个不同的线程上继续创建新记录。所有后续的调用都只是基于 App/System 对的相等性来更新这四个记录。此外，由于所有后续调用都发生在四个独立的线程上，最终的 Key 值是 31，因为多个线程被赋予了相同的预先存在的值。

单平台应用程序的修复

如果您的应用程序是单平台，也就是说，只有一个数据库更新源，例如一个服务或一个桌面应用程序，那么这个问题的修复非常简单，只需将方法的内容包含在一个带有共享 static readonly object 的 lock 块中即可

private static readonly object Locker = new object();
private static long GetUniqueKeySinglePlatform()
{
    lock (Locker)
    {
        List<IDataRecord> current = GetData(string.Format("SELECT [Key] _
        FROM dbo.UniqueKeys WHERE [App]='{0}' And [System]='{1}'", CApp, CSystem)).ToList();
        if (current.Any())
        {
            //Record found
            long currentKey = current.First().GetInt64(0) + 1;
            ExecuteSql(string.Format("UPDATE dbo.UniqueKeys SET [Key]={0} _
            WHERE [App]='{1}' AND [System]='{2}'", currentKey, CApp, CSystem));
            return currentKey;
        }
        //Record not found
        ExecuteSql(string.Format("INSERT INTO [dbo].[UniqueKeys] _
        ([App],[System],[Key])VALUES('{0}','{1}',1)", CApp, CSystem));
        return 1;
    }
} 

像之前那样并行执行后，结果是

Id                   App                  System     Key
-------------------- -------------------- ---------- --------------------
1                    GetUniqueKeyBadCode  Shared     100

然而，这仍然不能解决同时在多个服务器上运行此方法的问题，例如，在负载均衡的 Web 服务中。为此，我们必须将唯一密钥生成移到数据库层。

使用基于 SQL 的方法

您可能会倾向于在 SQL 中复制 C# 的逻辑。毕竟，什么比创建一个事务并执行几个条件 SQL 语句更简单呢？我的建议是……不要这样做。您很快就会发现，仅仅依靠 SQL 事务本身并不能保证线程安全。相反，您必须利用一个新的 SQL 语句，该语句允许在一个步骤中执行 INSERT 或 UPDATE。这个语句是 MERGE，根据 Microsoft 的文档

根据与源表的连接结果，在目标表上执行插入、更新或删除操作。例如，您可以根据另一表中发现的差异，在其中一个表中插入、更新或删除行来同步两个表。

以下是一个利用 MERGE 来 INSERT 新记录或 UPDATE 现有记录的新值的示例存储过程。

CREATE PROCEDURE [dbo].[GetUniqueKeyTV]
    @app VARCHAR(100) ,
    @sys VARCHAR(100) ,
    @retVal BIGINT OUT
AS 
    BEGIN
        DECLARE @T TABLE([Key] BIGINT);
        SET NOCOUNT ON

        MERGE INTO dbo.UniqueKeys WITH (TABLOCKX) AS Target
            USING ( VALUES
                ( @app ,
                  @sys ,
                  1
                ) ) AS Source ( [App], [System], [Key] )
            ON ( Target.App = Source.App
                 AND Target.[System] = Source.[System]
               )
            WHEN MATCHED 
                THEN UPDATE
                    SET     Target.[Key] = Target.[Key] + 1 
            WHEN NOT MATCHED BY TARGET 
                THEN 
       INSERT  (
              [App] ,
              [System] ,
              [Key]
                    
            ) VALUES
            ( Source.[App] ,
              Source.[System] ,
              Source.[Key]
                    
            ) OUTPUT 
               inserted.[Key] INTO @T;
        SELECT  @retVal = [Key]
        FROM    @T

        SET NOCOUNT OFF
    END

GO 

第一部分，我们告诉 MERGE 将更新哪个表，并建议数据库引擎在使用目标表的任何操作期间使用表锁。这确保在 MERGE 语句执行期间不会更新其他记录，尽管会增加并发表锁的数量，但由于没有对任何先前步骤的依赖，因此不会发生死锁。

然后，我们继续描述我们对每种情况的意图。如果找到匹配 App/System 对的记录，我们只需将 Key 的值加 1。如果未找到记录，我们继续向数据库插入一条新记录，并将其默认值设为 1。无论哪种情况，我们将新值（1 或 n+1）存储在一个表变量中，以便通过 retVal OUTPUT 参数返回它。在执行 MERGE 语句的结果中还有其他“OUTPUT 对象”，我强烈建议您根据自己的节奏仔细阅读其 文档。

我使用 Adam Mechanic 编写的一个简单实用工具 SQLQueryStress（可在此 处下载）测试了这种方法，该方法涉及 200 个并行线程，每个线程请求 1000 次。收到的最终唯一密钥：200,000，与我的预期完全一致。

总结 

线程安全是许多开发人员之间一个极其重要且备受争议的问题。有些人期望它能够以某种神奇的方式正常工作，而另一些人则会争论其含义。对我个人而言，它仅仅意味着我编写的代码可以在多个线程和环境中安全地执行，同时返回逻辑上一致的结果。

历史

• 2013 年 6 月 16 日 - 初始版本