NHibernate Persistence - Missing unique constraints

Summary

This guidance explains how add missing unique constraints on saga correlation property columns. This is described in the issue "Unique constraints are not generated for saga correlation properties".

This issue may cause multiple rows in saga data table that represent the same logical saga instance. The duplicated rows are inserted as a result of race condition during saga creation and missing unique constraint on correlation property column.

Compatibility

This issue has been resolved in the following patch versions of the NHibernate Persistence as defined in the NServiceBus support policy:

Affected versions (7.1 and later) that are still supported should be updated to the latest patch release.

Upgrade steps

Steps:

  • For each saga data table:
    • Check if any duplicate values exist in the correlation property column.
    • Merge duplicate rows so that there are no duplicate values in the correlation property column.
    • Add unique constraint on correlation property column
  • Update endpoint to latest patch release.
  • Deploy the new version.

Checking for duplicate correlation property values

The following query detects duplicate rows in the saga data table - it requires providing values for sagaDataTableName and correlationPropertyColumnName variables.

Each row in the result set represents a single logical saga instance that was duplicated. The first column of the result will show the correlation property value for logical saga instance.

Microsoft SQL Server

7.x NServiceBus.NHibernate
declare @sagaDataTableName nvarchar(max) = '...'
declare @correlationPropertyColumnName nvarchar(max) = '...'
declare @sql nvarchar(max)

select @sql = 'select ' + @correlationPropertyColumnName + ', count(*) as SagaRows from ' + @sagaDataTableName + ' group by ' + @correlationPropertyColumnName + ' having count(*) > 1'
exec sp_executeSQL @sql

Oracle

7.x NServiceBus.NHibernate
declare
  sagaDataTableName varchar2(30) := '...';
  correlationPropertyColumnName varchar2(30) := '...';
  detectDuplicates varchar2(500);
  type SagaCursorType is ref cursor;
  sagaCursor SagaCursorType;
  correlationProperty <type of correlationPropertyColumnName>;
  instanceCount number(10);
begin
    detectDuplicates :=
       'select ' || correlationPropertyColumnName || ', count(*)
        from ' || sagaDataTableName || '
        group by ' || correlationPropertyColumnName || '
        having count(*) > 1';
        
    open sagaCursor for detectDuplicates;
    
    loop
      fetch sagaCursor into correlationProperty, instanceCount;
      exit when sagaCursor%NOTFOUND;
      
      dbms_output.put_line(correlationProperty);
      
    end loop;
    
    close sagaCursor;
end;

Add unique constraint on correlation property column

The unique constraint on correlation property column can be added with following query - it requires providing values for sagaDataTableName and correlationPropertyColumnName variables.

Microsoft SQL Server

If adding a unique constraint fails with the error The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ... ensure that all duplicated rows detected have been merged.
7.x NServiceBus.NHibernate
declare @sagaDataTableName nvarchar(max) = '...'
declare @correlationPropertyColumnName nvarchar(max) = '...'
declare @sql nvarchar(max)

select @sql = 'alter table ' + @sagaDataTableName + ' add unique nonclustered ( ' + @correlationPropertyColumnName + ' asc )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on)'
exec sp_executeSQL @sql

Oracle

Oracle requires that a unique constraint has a name. Use the uniqueConstraintName variable to set the name of the constraint.
7.x NServiceBus.NHibernate
declare
  sagaDataTableName varchar2(30) := '...';
  correlationPropertyColumnName varchar2(30) := '...';
  uniqueConstraintName varchar2(30) := '...';
  addUniqueConstraint varchar2(500);
begin
    addUniqueConstraint :=
       'alter table ' || sagaDataTableName || ' add constraint ' || uniqueConstraintName || ' unique (' || correlationPropertyColumnName || ')';
       
    execute immediate addUniqueConstraint;
end;

Last modified