0

I'm working on enviorment SQL azure.

I've got a stored procedure that runs from several AKS simultaneously and insert a message.

So it's a race for who writes the message first and the rest fails. The insert fails due to Unique index and send the Id of the failed message to the service.

here is the stored procedure:

CREATE OR ALTER PROCEDURE Saga.AddMessage
(
 @SagaIdentity NVARCHAR(256),
 @MessageId NVARCHAR(64),
 @SagaMessage NVARCHAR(MAX),
 @Created DATETIME2(7),
 @Updated DATETIME2(7),
 @SubscriptionStatus SMALLINT
)
AS
BEGIN
 BEGIN TRY
 INSERT INTO [Saga].[SubscribersMessages] ([SagaIdentity], [MessageId], [SagaMessage], [Created], [Updated], [SubscriptionStatus])
 SELECT @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus 
 WHERE NOT EXISTS (SELECT 1 FROM [Saga].[SubscribersMessages] WHERE MessageId = @MessageId);
 
 SELECT [Id] AS Results
 FROM [Saga].[SubscribersMessages] 
 WHERE MessageId = @MessageId
 END TRY
 BEGIN CATCH
 SELECT [Id] AS Results
 FROM [Saga].[SubscribersMessages] 
 WHERE MessageId = @MessageId
 END CATCH;
END

and here is the unique index :

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Saga.SubscribersMessages','U') AND name = 'UX_SubscribersMessages_MessageId')
BEGIN
 CREATE UNIQUE INDEX UX_SubscribersMessages_MessageId
 ON [Saga].[SubscribersMessages] (MessageId);
END

I have a couple of problems with this service:

  1. Peformance issue - I don't like the fact the the process rely CATCH on failed processed to return the Id.

  2. I want to Partition the table using the Created field in order to truncate partition in the future and i get an error - Column 'Created' is partitioning column of the index 'UX_SubscribersMessages_MessageId'. Partition columns for a unique index must be a subset of the index key I thought about the MessageId as my Primary Key as it is unique but there are downsides to that as the MessageId is NVARCHAR(64).

Here is the execution plan:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.588" Build="16.0.5506.10218" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
 <BatchSequence>
 <Batch>
 <Statements>
 <StmtSimple StatementCompId="4" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.0432927" StatementText="INSERT INTO [Saga].[SubscribersMessages] ([SagaIdentity], [MessageId], [SagaMessage], [Created], [Updated], [SubscriptionStatus])&#xD;&#xA; SELECT @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus &#xD;&#xA; WHERE NOT EXISTS (SELECT 1 FROM [Saga].[SubscribersMessages] WHERE MessageId = @MessageId)" StatementType="INSERT" QueryHash="0x9495ED884EEA5C34" QueryPlanHash="0xB362830815E8CA5B" RetrievedFromCache="true" StatementSqlHandle="0x0900576108E5BB43DB4B85D93C51904339770000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="4" ParentObjectId="814625945" StatementParameterizationType="0" SecurityPolicyApplied="false">
 <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
 <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="432">
 <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
 <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="566169" EstimatedPagesCached="70771" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="2918912" />
 <OptimizerStatsUsage>
 <StatisticsInfo Database="[55a15ae9-f6f0-4e08-8548-21516f68285e]" Schema="[Saga]" Table="[SubscribersMessages]" Statistics="[UX_SubscribersMessages_MessageId]" ModificationCount="68591" SamplingPercent="0.928381" LastUpdate="2024年05月07日T07:13:48.34" />
 </OptimizerStatsUsage>
 <WaitStats>
 <Wait WaitType="PREEMPTIVE_HTTP_REQUEST" WaitTimeMs="15" WaitCount="3" />
 <Wait WaitType="PAGEIOLATCH_EX" WaitTimeMs="9" WaitCount="1" />
 <Wait WaitType="LOGMGR_FLUSH" WaitTimeMs="4" WaitCount="1" />
 </WaitStats>
 <QueryTimeStats CpuTime="0" ElapsedTime="30" />
 <RelOp AvgRowSize="9" EstimateCPU="4E-06" EstimateIO="0.04" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Insert" EstimatedTotalSubtreeCost="0.0432927">
 <OutputList />
 <RunTimeInformation>
 <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="30" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 </RunTimeInformation>
 <Update DMLRequestSort="false">
 <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[Saga_SubscribersMessages]" IndexKind="Clustered" Storage="RowStore" />
 <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[UX_SubscribersMessages_MessageId]" IndexKind="NonClustered" Storage="RowStore" />
 <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[IX_SubscribersMessages_SagaIdentity]" IndexKind="NonClustered" Storage="RowStore" />
 <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[IX_SubscribersMessages_SubscriptionStatus]" IndexKind="NonClustered" Storage="RowStore" />
 <SetPredicate>
 <ScalarOperator ScalarString="[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[SagaIdentity] = RaiseIfNullInsert([@SagaIdentity]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[MessageId] = RaiseIfNullInsert([@MessageId]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[SagaMessage] = RaiseIfNullInsert([@SagaMessage]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[Created] = RaiseIfNullInsert([@Created]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[Updated] = [Expr1006],[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[SubscriptionStatus] = RaiseIfNullInsert([@SubscriptionStatus]),[WalletServiceDemoDb_Dev].[Saga].[SubscribersMessages].[Id] = [Expr1005]">
 <ScalarExpressionList>
 <ScalarOperator>
 <MultipleAssign>
 <Assign>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="SagaIdentity" />
 <ScalarOperator>
 <Intrinsic FunctionName="RaiseIfNullInsert">
 <ScalarOperator>
 <Identifier>
 <ColumnReference Column="@SagaIdentity" />
 </Identifier>
 </ScalarOperator>
 </Intrinsic>
 </ScalarOperator>
 </Assign>
 <Assign>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="MessageId" />
 <ScalarOperator>
 <Intrinsic FunctionName="RaiseIfNullInsert">
 <ScalarOperator>
 <Identifier>
 <ColumnReference Column="@MessageId" />
 </Identifier>
 </ScalarOperator>
 </Intrinsic>
 </ScalarOperator>
 </Assign>
 <Assign>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="SagaMessage" />
 <ScalarOperator>
 <Intrinsic FunctionName="RaiseIfNullInsert">
 <ScalarOperator>
 <Identifier>
 <ColumnReference Column="@SagaMessage" />
 </Identifier>
 </ScalarOperator>
 </Intrinsic>
 </ScalarOperator>
 </Assign>
 <Assign>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Created" />
 <ScalarOperator>
 <Intrinsic FunctionName="RaiseIfNullInsert">
 <ScalarOperator>
 <Identifier>
 <ColumnReference Column="@Created" />
 </Identifier>
 </ScalarOperator>
 </Intrinsic>
 </ScalarOperator>
 </Assign>
 <Assign>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Updated" />
 <ScalarOperator>
 <Identifier>
 <ColumnReference Column="Expr1006" />
 </Identifier>
 </ScalarOperator>
 </Assign>
 <Assign>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="SubscriptionStatus" />
 <ScalarOperator>
 <Intrinsic FunctionName="RaiseIfNullInsert">
 <ScalarOperator>
 <Identifier>
 <ColumnReference Column="@SubscriptionStatus" />
 </Identifier>
 </ScalarOperator>
 </Intrinsic>
 </ScalarOperator>
 </Assign>
 <Assign>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Id" />
 <ScalarOperator>
 <Identifier>
 <ColumnReference Column="Expr1005" />
 </Identifier>
 </ScalarOperator>
 </Assign>
 </MultipleAssign>
 </ScalarOperator>
 </ScalarExpressionList>
 </ScalarOperator>
 </SetPredicate>
 <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00328874">
 <OutputList>
 <ColumnReference Column="Expr1005" />
 <ColumnReference Column="Expr1006" />
 </OutputList>
 <RunTimeInformation>
 <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" />
 </RunTimeInformation>
 <Top RowCount="false" IsPercent="false" WithTies="false">
 <TopExpression>
 <ScalarOperator ScalarString="(1)">
 <Const ConstValue="(1)" />
 </ScalarOperator>
 </TopExpression>
 <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328864">
 <OutputList>
 <ColumnReference Column="Expr1005" />
 <ColumnReference Column="Expr1006" />
 </OutputList>
 <ComputeScalar>
 <DefinedValues>
 <DefinedValue>
 <ColumnReference Column="Expr1006" />
 <ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime2(7),getdate(),0)">
 <Identifier>
 <ColumnReference Column="ConstExpr1007">
 <ScalarOperator>
 <Convert DataType="datetime2" Scale="7" Style="0" Implicit="true">
 <ScalarOperator>
 <Intrinsic FunctionName="getdate" />
 </ScalarOperator>
 </Convert>
 </ScalarOperator>
 </ColumnReference>
 </Identifier>
 </ScalarOperator>
 </DefinedValue>
 </DefinedValues>
 <RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00328854">
 <OutputList>
 <ColumnReference Column="Expr1005" />
 </OutputList>
 <RunTimeInformation>
 <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" />
 </RunTimeInformation>
 <ComputeScalar ComputeSequence="true">
 <DefinedValues>
 <DefinedValue>
 <ColumnReference Column="Expr1005" />
 <ScalarOperator ScalarString="getidentity((750625717),(22),NULL)">
 <Intrinsic FunctionName="getidentity">
 <ScalarOperator>
 <Const ConstValue="(750625717)" />
 </ScalarOperator>
 <ScalarOperator>
 <Const ConstValue="(22)" />
 </ScalarOperator>
 <ScalarOperator>
 <Const ConstValue="NULL" />
 </ScalarOperator>
 </Intrinsic>
 </ScalarOperator>
 </DefinedValue>
 </DefinedValues>
 <RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Anti Semi Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00328844">
 <OutputList />
 <RunTimeInformation>
 <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" />
 </RunTimeInformation>
 <NestedLoops Optimized="false">
 <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="5" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">
 <OutputList />
 <RunTimeInformation>
 <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
 </RunTimeInformation>
 <ConstantScan />
 </RelOp>
 <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="6" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="16349100">
 <OutputList />
 <RunTimeInformation>
 <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="1" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 </RunTimeInformation>
 <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
 <DefinedValues />
 <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[UX_SubscribersMessages_MessageId]" IndexKind="NonClustered" Storage="RowStore" />
 <SeekPredicates>
 <SeekPredicateNew>
 <SeekKeys>
 <Prefix ScanType="EQ">
 <RangeColumns>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="MessageId" />
 </RangeColumns>
 <RangeExpressions>
 <ScalarOperator ScalarString="[@MessageId]">
 <Identifier>
 <ColumnReference Column="@MessageId" />
 </Identifier>
 </ScalarOperator>
 </RangeExpressions>
 </Prefix>
 </SeekKeys>
 </SeekPredicateNew>
 </SeekPredicates>
 </IndexScan>
 </RelOp>
 </NestedLoops>
 </RelOp>
 </ComputeScalar>
 </RelOp>
 </ComputeScalar>
 </RelOp>
 </Top>
 </RelOp>
 </Update>
 </RelOp>
 <ParameterList>
 <ColumnReference Column="@SubscriptionStatus" ParameterDataType="smallint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
 <ColumnReference Column="@Created" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2024-05-07 08:08:32.7700000'" ParameterRuntimeValue="'2024-05-07 08:08:32.7700000'" />
 <ColumnReference Column="@SagaMessage" ParameterDataType="nvarchar(max)" ParameterCompiledValue="N'{&quot;RequestId&quot;:&quot;ef48ce6c-201e-4c88-a823-cb43cb8cb467&quot;,&quot;CorrelationId&quot;:&quot;f519d43d-7a8b-4833-b393-ab96fe85b0ff&quot;,&quot;Created&quot;:&quot;2023年11月21日T14:20:09.9658724Z&quot;}'" ParameterRuntimeValue="N'{&quot;RequestId&quot;:&quot;ef48ce6c-201e-4c88-a823-cb43cb8cb467&quot;,&quot;CorrelationId&quot;:&quot;f519d43d-7a8b-4833-b393-ab96fe85b0ff&quot;,&quot;Created&quot;:&quot;2023年11月21日T14:20:09.9658724Z&quot;}'" />
 <ColumnReference Column="@SagaIdentity" ParameterDataType="nvarchar(256)" ParameterCompiledValue="N'40677e00-5528-4310-bcd1-d1ed19b11758'" ParameterRuntimeValue="N'40677e00-5528-4310-bcd1-d1ed19b11758'" />
 <ColumnReference Column="@MessageId" ParameterDataType="nvarchar(64)" ParameterCompiledValue="N'586772d1101b432390126954ced52ed6'" ParameterRuntimeValue="N'586772d1101b432390126954ced52ed6'" />
 </ParameterList>
 </QueryPlan>
 </StmtSimple>
 </Statements>
 </Batch>
 <Batch>
 <Statements>
 <StmtSimple StatementCompId="5" StatementEstRows="1" StatementId="2" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.0032831" StatementText="SELECT [Id] AS Results&#xD;&#xA; FROM [Saga].[SubscribersMessages] &#xD;&#xA; WHERE MessageId = @MessageId" StatementType="SELECT" QueryHash="0x65EB6536A27948CF" QueryPlanHash="0x79E4B69DD48411A3" RetrievedFromCache="true" StatementSqlHandle="0x09001BDE820A3F9CBFDE19FD57F0EBDA0E040000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="4" ParentObjectId="814625945" StatementParameterizationType="0" SecurityPolicyApplied="false">
 <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
 <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="9" CompileCPU="0" CompileMemory="288">
 <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
 <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="566169" EstimatedPagesCached="70771" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="2918912" />
 <OptimizerStatsUsage>
 <StatisticsInfo Database="[55a15ae9-f6f0-4e08-8548-21516f68285e]" Schema="[Saga]" Table="[SubscribersMessages]" Statistics="[UX_SubscribersMessages_MessageId]" ModificationCount="68591" SamplingPercent="0.928381" LastUpdate="2024年05月07日T07:13:48.34" />
 </OptimizerStatsUsage>
 <QueryTimeStats CpuTime="0" ElapsedTime="0" />
 <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="0" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="16349100">
 <OutputList>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Id" />
 </OutputList>
 <RunTimeInformation>
 <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 </RunTimeInformation>
 <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
 <DefinedValues>
 <DefinedValue>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="Id" />
 </DefinedValue>
 </DefinedValues>
 <Object Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Index="[UX_SubscribersMessages_MessageId]" IndexKind="NonClustered" Storage="RowStore" />
 <SeekPredicates>
 <SeekPredicateNew>
 <SeekKeys>
 <Prefix ScanType="EQ">
 <RangeColumns>
 <ColumnReference Database="[WalletServiceDemoDb_Dev]" Schema="[Saga]" Table="[SubscribersMessages]" Column="MessageId" />
 </RangeColumns>
 <RangeExpressions>
 <ScalarOperator ScalarString="[@MessageId]">
 <Identifier>
 <ColumnReference Column="@MessageId" />
 </Identifier>
 </ScalarOperator>
 </RangeExpressions>
 </Prefix>
 </SeekKeys>
 </SeekPredicateNew>
 </SeekPredicates>
 </IndexScan>
 </RelOp>
 <ParameterList>
 <ColumnReference Column="@MessageId" ParameterDataType="nvarchar(64)" ParameterCompiledValue="N'586772d1101b432390126954ced52ed6'" ParameterRuntimeValue="N'586772d1101b432390126954ced52ed6'" />
 </ParameterList>
 </QueryPlan>
 </StmtSimple>
 </Statements>
 </Batch>
 </BatchSequence>
</ShowPlanXML>
Paul White ♦
95.3k30 gold badges439 silver badges689 bronze badges
asked May 7, 2024 at 8:12

2 Answers 2

2

You need to add locking hints to avoid constraint errors. Otherwise what can happen is that the server checks the table, then releases the lock before inserting, allowing another session to jump in. (In SNAPSHOT mode it won't even see the other session).

You can also use an OUTPUT clause, then do a conditional SELECT if there was no results.

CREATE OR ALTER PROCEDURE Saga.AddMessage
(
 @SagaIdentity NVARCHAR(256),
 @MessageId NVARCHAR(64),
 @SagaMessage NVARCHAR(MAX),
 @Created DATETIME2(7),
 @Updated DATETIME2(7),
 @SubscriptionStatus SMALLINT
)
AS
SET XACT_ABORT, NOCOUNT ON;
BEGIN TRAN;
INSERT INTO Saga.SubscribersMessages
 (SagaIdentity, MessageId, SagaMessage, Created, Updated, SubscriptionStatus)
 OUTPUT inserted.Id AS Results
SELECT
 @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus
WHERE NOT EXISTS (SELECT 1
 FROM Saga.SubscribersMessages sm WITH (UPDLOCK, HOLDLOCK)
 WHERE sm.MessageId = @MessageId
);
IF @@ROWCOUNT = 0 
 SELECT sm.Id AS Results
 FROM Saga.SubscribersMessages sm
 WHERE sm.MessageId = @MessageId;
COMMIT;

Alternatively

SET XACT_ABORT, NOCOUNT ON;
BEGIN TRAN;
DECLARE @existingId bigint;
SELECT @existingId = sm.Id
FROM Saga.SubscribersMessages sm WITH (UPDLOCK, HOLDLOCK)
WHERE sm.MessageId = @MessageId;
IF @existingId IS NULL
BEGIN
 INSERT INTO Saga.SubscribersMessages
 (SagaIdentity, MessageId, SagaMessage, Created, Updated, SubscriptionStatus)
 OUTPUT inserted.Id AS Results
 SELECT
 @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus
END
ELSE
BEGIN
 SELECT @existingId AS Results;
END;
COMMIT;

Do yourself a favour and don't quote object and column names unless absolutely necessary, it's really annoying to read.

answered May 7, 2024 at 12:01
1

Avoiding TRY...CATCH

Another option is to alter the existing unique index to silently reject duplicates:

ALTER INDEX UX_SubscribersMessages_MessageId 
 ON Saga.SubscribersMessages
 SET 
 (
 IGNORE_DUP_KEY = ON 
 (
 SUPPRESS_MESSAGES = ON
 )
 );

(note: MERGE statements do not respect IGNORE_DUP_KEY)

Your procedure can now be as simple as:

CREATE OR ALTER PROCEDURE Saga.AddMessage
 @SagaIdentity nvarchar(256),
 @MessageId nvarchar(64),
 @SagaMessage nvarchar(max),
 @Created datetime2(7),
 @Updated datetime2(7),
 @Subscriptionstatus smallint
AS
SET XACT_ABORT, NOCOUNT ON;
-- UX_SubscribersMessages_MessageId has IGNORE_DUP_KEY
INSERT Saga.SubscribersMessages
(
 SagaIdentity, 
 MessageId, 
 SagaMessage, 
 Created, 
 Updated, 
 SubscriptionStatus
)
VALUES
(
 @SagaIdentity, 
 @MessageId, 
 @SagaMessage, 
 @Created, 
 SYSUTCDATETIME(), 
 @SubscriptionStatus
);
SELECT Results = SM.Id
FROM Saga.SubscribersMessages AS SM
WHERE SM.MessageId = @MessageId;

SQL Server internally takes care of the necessary UPDLOCK and SERIALIZABLE hints for you, and you don't have to write an EXISTS clause. Using this approach does make the code less explicit though (hence my code comment).

Partitioning and Uniqueness

There's no perfect answer to this.

If you want to partition a unique index (including a primary key), it must have the partitioning column as part of the key.

Your unique index would have to be on (MessageId, Created) to be partitioned by Created. This means the same MessageId could be present more than once, so long as the Created values are different. Maybe that's acceptable in your system, maybe it isn't.

Alternatively, you could retain the unique index as it is (by specifying ON [filegroup] instead of ON [partition_scheme]), but that would result in an unaligned index (that is, one not partitioned on an equivalent scheme to the base table).

Having an unaligned index disallows partition switching and TRUNCATE TABLE WITH PARTITIONS, which is something you say you want. You could work around that by dropping the unaligned index before the truncation and rebuilding it afterwards, but that's hardly convenient or free.

answered May 8, 2024 at 10:43

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.