search

dotnetcore / CAP

Distributed transaction solution in micro-service base on eventually consistency, also an eventbus with Outbox pattern
http://cap.dotnetcore.xyz
MIT License
6.68k stars 1.28k forks source link

Small issue with SQL Server infrastructure initialization having UseStorageLock = true #1486

Closed alex-dolzhok closed 8 months ago

alex-dolzhok commented 8 months ago

Hi guys. There is a small issue in services configuration if we set UseStorageLock = true. During tables initialization I'm having errors: _Violation of PRIMARY KEY constraint 'PK_cap.Lock'. Cannot insert duplicate key in object 'cap.Lock'. The duplicate key value is (publish_retryv1) _Violation of PRIMARY KEY constraint 'PK_cap.Lock'. Cannot insert duplicate key in object 'cap.Lock'. The duplicate key value is (received_retryv1)

As a solution I propose to add a check for Lock records existence here: src/DotNetCore.CAP.SqlServer/IStorageInitializer.SqlServer.cs

IF NOT EXISTS(SELECT 1 FROM {GetLockTableName()} WHERE [Key] = @PubKey) INSERT INTO {GetLockTableName()} ([Key],[Instance],[LastLockTime]) VALUES(@PubKey,'',@LastLockTime); IF NOT EXISTS(SELECT 1 FROM {GetLockTableName()} WHERE [Key] = @RecKey) INSERT INTO {GetLockTableName()} ([Key],[Instance],[LastLockTime]) VALUES(@RecKey,'',@LastLockTime);

yang-xiaodong commented 8 months ago

Why IGNORE_DUP_KEY = ON not working?

alex-dolzhok commented 8 months ago

Sorry, my bad: I was creating Lock table myself, didn't pay attention on IGNORE_DUP_KEY = ON parameter in create table instruction. Thanks!