同時INSERTのLOTSを実行する際に、「UNIQUE KEY制約の違反」を回避する方法

c# dapper database sql sql-server

質問

私は、単一のトランザクション内で指定されたキーの既存のレコードもチェックしていても、UNIQUE KEY制約に衝突している多数の同時SQL INSERT文を実行しています。パフォーマンスを傷つけることなく(あまりにも)、私が得ている衝突の量を排除する、あるいは最小限に抑える方法を探しています。

バックグラウンド:

私はASP.NET MVC4 WebApiプロジェクトで、 INSERTレコードにたくさんのHTTP POSTリクエストを受け取ります。それは約5K - 10Kリクエストを1秒間取得します。プロジェクトの唯一の責任は、レコードの重複排除と集計です。それは非常に重いです。それは比較的少量の読取り要求を有す​​る。これらのすべては、 IsolationLevel.ReadUncommittedトランザクションを使用します。

データベーススキーマ

ここにDBテーブルがあります:

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
    }
}

問題

サーバーの負荷が大きくなると、これらの例外の多くが発生しています。

UNIQUE KEY制約 'UQ_MySchemaRecords_UserIdRecordTypeOtherId'の違反です。オブジェクト 'MySchema.Records'に重複キーを挿入できません。重複するキー値は(1234567890,1,123)です。ステートメントは終了されました。

この例外は、1分に10回も頻繁に発生します。

私が試したこと

  • IsolationLevelSerializable変更しようとしました。例外ははるかに少ない頻度で発生しましたが、まだ発生しました。また、コードのパフォーマンスが大幅に低下しました。システムは1秒間に2K個の要求しか処理できませんでした。私はこのスループットの低下が実際には縮小された例外の原因であったと思うので、これで私の問題が解決されなかったと私は結論づけました。
  • 私はUPDLOCK テーブルヒントの使用を検討しましたが、どのようにアイソレーションレベルと協調しているのか、それを自分のコードにどのように適用するのかを完全に理解していません。それは私の現在の理解から、それが最良の解決策かもしれないように思われる。
  • 私はまた、最初のSELECTステートメント(既存のレコード用)をここに示すようなINSERTステートメントの一部として追加しようとしましたが、この試みにはまだ同じ問題がありました。
  • 私はSQL MERGEステートメントを使ってUpsertメソッドを実装しようとしましたが、同じ問題が発生しました。

私の質問

  • このタイプのUNIQUEキー制約の衝突を防ぐためにできることはありますか?
  • UPDLOCKテーブルのヒント(または他のテーブルのヒント)を使用する必要がある場合は、そのコードにどのように追加しますか? INSERTに追加しますか? SELECT ?どちらも?

受け入れられた回答

検証している読み取りをロックにする:

FROM SomeTable WITH (UPDLOCK, ROWLOCK, HOLDLOCK)

これは、単一のキー上のアクセスを直列化し、他のすべての並行性を可能にします。


HOLDLOCK (= SERIALIZABLE )はある範囲の値を保護します。これにより、存在しない行が引き続き存在しないようになり、 INSERTが成功します。

UPDLOCKは、既存の行が変更されたり、別の同時トランザクションによって削除されないようにして、 UPDATEが成功するようにします。

ROWLOCK 、エンジンが行レベルのロックを取るようにします。

これらの変更 、デッドロックの可能性を高める可能性があります。


人気のある回答

シナリオのエラーを排除しようとするよりも、エラーを許可して抑制するほうが速い場合があります。重複するデータと同期して複数のソースを統合する場合は、競合状態を管理するためにどこかにボトルネックを作成する必要があります。

ハッシュセット内のレコードのユニークな制約を保持するシングルトンマネージャクラスを作成すると、重複がセットに追加されたときに自動的に削除されます。 DBに提出する前にレコードが追加され、ステートメントの完了時に削除されます。こうすることで、ハッシュセットが重複を奪ったり、既存のレコードチェックが試行の最中にコミットされた重複レコードを検出したりします。



ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ