c# - 执行大量并发 INSERT 时如何避免 "Violation of UNIQUE KEY constraint"

Question

我正在执行许多并发 SQL INSERT 语句，这些语句在 UNIQUE KEY 约束上发生冲突，即使我也在单个事务中检查给定键的现有记录。我正在寻找一种方法来消除或最大限度地减少我遇到的碰撞数量，同时又不会损害性能(太多)。

背景:

我正在开发一个 ASP.NET MVC4 WebApi 项目，该项目接收大量对 INSERT 记录的 HTTP POST 请求。它每秒收到大约 5K - 10K 请求。该项目的唯一职责是删除重复数据和汇总记录。写起来很重；它的读取请求量相对较小；所有这些都使用带有 IsolationLevel.ReadUncommitted 的事务。

数据库架构

这是数据库表:

CREATE TABLE [MySchema].[Records] ( 
    Id BIGINT IDENTITY NOT NULL, 
    RecordType TINYINT NOT NULL, 
    UserID BIGINT NOT NULL, 
    OtherID SMALLINT NULL, 
    TimestampUtc DATETIMEOFFSET NOT NULL, 
    CONSTRAINT [UQ_MySchemaRecords_UserIdRecordTypeOtherId] UNIQUE CLUSTERED ( 
        [UserID], [RecordType], [OtherID] 
    ), 
    CONSTRAINT [PK_MySchemaRecords_Id] PRIMARY KEY NONCLUSTERED ( 
        [Id] ASC 
    ) 
) 

存储库代码

这是导致异常的 Upsert 方法的代码:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;

namespace MyProject.DataAccess
{
    public class MyRepo
    {
        public void Upsert(MyRecord record)
        {
            var dbConnectionString = "MyDbConnectionString";
            using (var connection = new SqlConnection(dbConnectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        var existingRecord = FindByByUniqueKey(transaction, record.RecordType, record.UserID, record.OtherID);

                        if (existingRecord == null)
                        {
                            const string sql = @"INSERT INTO [MySchema].[Records] 
                                                 ([UserID], [RecordType], [OtherID], [TimestampUtc]) 
                                                 VALUES (@UserID, @RecordType, @OtherID, @TimestampUtc) 
                                                 SELECT CAST(SCOPE_IDENTITY() AS BIGINT";
                            var results = transaction.Connection.Query<long>(sql, record, transaction);
                            record.Id = results.Single();
                        }
                        else if (existingRecord.TimestampUtc <= record.TimestampUtc)
                        {
                            // UPDATE
                        }

                        transaction.Commit();
                    }
                    catch (Exception e)
                    {
                        transaction.Rollback();
                        throw e;
                    }
                }
            }
        }

        // all read-only methods use explicit transactions with IsolationLevel.ReadUncommitted

        private static MyRecord FindByByUniqueKey(SqlTransaction transaction, RecordType recordType, long userID, short? otherID)
        {
            const string sql = @"SELECT * from [MySchema].[Records] 
                                 WHERE [UserID] = @UserID
                                 AND [RecordType] = @RecordType
                                 AND [OtherID] = @OtherID";
            var paramz = new {
                UserID = userID,
                RecordType = recordType,
                OtherID = otherID
            };
            var results = transaction.Connection.Query<MyRecord>(sql, paramz, transaction);
            return results.SingleOrDefault();
        }
    }

    public class MyRecord
    {
        public long ID { get; set; }
        public RecordType RecordType { get; set; }
        public long UserID { get; set; }
        public short? OtherID { get; set; }
        public DateTimeOffset TimestampUtc { get; set; }
    }

    public enum RecordType : byte
    {
        TypeOne = 1,
        TypeTwo = 2,
        TypeThree = 3
    }
}

问题

当服务器承受足够大的负载时，我看到许多异常发生:

Violation of UNIQUE KEY constraint 'UQ_MySchemaRecords_UserIdRecordTypeOtherId'. Cannot insert duplicate key in object 'MySchema.Records'. The duplicate key value is (1234567890, 1, 123). The statement has been terminated.

此异常经常发生，一分钟多达10次。

我尝试过的

• 我尝试将 IsolationLevel 更改为 Serializable。异常发生的频率要低得多，但仍然会发生。此外，代码的性能也受到很大影响；系统每秒只能处理 2K 个请求。我怀疑吞吐量的下降实际上是异常减少的原因，因此我得出结论，这并没有解决我的问题。
• 我考虑过使用 UPDLOCK Table Hint，但我不完全理解它如何与隔离级别协作或如何将它应用到我的代码中。不过，根据我目前的理解，这似乎确实是最好的解决方案。
• 我还尝试将初始 SELECT 语句(用于现有记录)添加为 INSERT 语句的一部分，如 here 所示，但此尝试仍然遇到相同的问题。
• 我尝试使用 SQL MERGE 语句实现我的 Upsert 方法，但这也遇到了同样的问题。

我的问题

• 我能做些什么来防止这种类型的 UNIQUE 键约束冲突？
• 如果我应该使用 UPDLOCK 表提示(或与此相关的任何其他表提示)，我该如何将其添加到我的代码中？我会把它添加到 INSERT 吗？ 选择？两个？

Answer 1

使验证读取锁定:

FROM SomeTable WITH (UPDLOCK, ROWLOCK, HOLDLOCK)

这会序列化对单个键的访问，允许对所有其他键进行并发。

---

HOLDLOCK ( = SERIALIZABLE) 保护一系列值。这可确保不存在的行继续不存在，以便 INSERT 成功。

UPDLOCK 确保任何现有行不会被另一个并发事务更改或删除，以便 UPDATE 成功。

ROWLOCK 鼓励引擎采用行级锁。

这些更改可能会增加出现死锁的可能性。