동시 INSERT 작업을 많이 할 때 "UNIQUE KEY 제약 조건 위반"을 방지하는 방법

c# dapper database sql sql-server

문제

단일 트랜잭션 내에서 주어진 키의 기존 레코드를 검사 중이지만 UNIQUE KEY 제약 조건에 충돌하는 많은 동시 SQL INSERT 문을 수행하고 있습니다. 나는 성능을 (너무 많이) 손상시키지 않고 얻는 충돌의 양을 제거하거나 최소화하는 방법을 찾고있다.

배경:

나는 INSERT 레코드에 많은 HTTP POST 요청을받는 ASP.NET MVC4 WebApi 프로젝트를 작업 중입니다. 초당 약 5K - 10K 요청을받습니다. 프로젝트의 유일한 책임은 레코드 중복 제거 및 집계입니다. 그것은 무겁게 쓰고 있습니다. 비교적 적은 양의 읽기 요청을 가지고 있습니다. 이들 모두는 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 
    ) 
) 

저장소 코드

Exception의 원인이되는 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 변경해 보았습니다. 예외는 훨씬 적게 발생하지만 여전히 발생합니다. 또한 코드의 성능이 크게 떨어졌습니다. 시스템은 초당 2K 개의 요청을 처리 할 수있었습니다. 나는이 처리량의 감소가 실제로 감소 된 예외의 원인이라고 생각하여 이것이 내 문제를 해결하지 못했다고 결론을 내렸다.
  • UPDLOCK 테이블 힌트 사용을 고려했지만 격리 수준과 어떻게 협력하는지 또는 내 코드에 적용하는 방법을 완전히 이해하지 못했습니다. 그것은 내 현재의 이해에서 그것이 최선의 해결책일지도 모르는 것처럼 보입니다.
  • 또한 기존의 SELECT 문을 INSERT 문에 포함 시키려고 시도했지만 여기 에는 여전히 같은 문제가있었습니다.
  • SQL MERGE 문을 사용하여 Upsert 메서드를 구현하려고했지만 동일한 문제가 발생했습니다.

내 질문

  • 이러한 유형의 UNIQUE 키 제약 조건 충돌을 방지하기 위해 할 수있는 일이 있습니까?
  • 만약 내가 UPDLOCK 테이블 힌트 (또는 그 문제에 대한 다른 테이블 힌트)를 사용해야한다면 어떻게 코드에 추가 할 수 있을까요? INSERT 추가할까요? SELECT ? 양자 모두?

수락 된 답변

유효성 검사를위한 읽기를 잠금으로 설정하십시오.

FROM SomeTable WITH (UPDLOCK, ROWLOCK, HOLDLOCK)

이것은 단일 키의 액세스를 직렬화하여 다른 모든 키의 동시성을 허용합니다.


HOLDLOCK (= SERIALIZABLE )은 값 범위를 보호합니다. 이렇게하면 존재하지 않는 행이 계속 존재하지 않으므로 INSERT 가 성공합니다.

UPDLOCKUPDATE 가 성공할 수 있도록 기존 행이 변경되거나 다른 동시 트랜잭션에 의해 삭제되지 않도록합니다.

ROWLOCK 은 엔진이 행 수준 잠금을 취 하도록 권장 합니다.

이러한 변경으로 인해 교착 상태가 발생할 수 있습니다 .


인기 답변

시나리오의 오류를 제거하고 시도하는 것보다 오류를 허용하고 억제하는 것이 더 빠를 수도 있습니다. 여러 소스를 겹치는 데이터와 동 기적으로 통합하려는 경우 경쟁 환경을 관리하기 위해 어딘가에 병목을 생성해야합니다.

해시 셋의 레코드의 고유 한 제한 조건을 보유한 싱글 톤 관리자 클래스를 만들면 복사본에 세트가 추가 될 때 자동으로 삭제할 수 있습니다. DB에 제출하기 전에 레코드가 추가되고 명령문 완료시 제거됩니다. 그런 식으로 해시 세트가 중복 된 레코드를 가져 오거나 시도한 레코드의 맨 위에있는 기존 레코드 검사가 커밋 된 중복 레코드를 검색합니다.



아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.