TransactionalSession with pessimistic concurrency control outbox leads to uncollected messages

[PhilAch]

The Transactional Session feature can lead to a lot of uncollected messages in the outbox.
This happens when pessimistic concurrency control is used, and no message/command/event is sent or published during the transaction. In this case, an entry is still added to the outbox during the opening of the transaction. But it is never cleaned up later on, as only entries marked as dispatched are deleted.

Expected behavior

Honestly not sure, what should be expected. Maybe it is not supposed to be used in such a configuration, an we have since moved away from pessimistic concurrency for the transactional session endpoint, as there is no real value in it.
Maybe it should just be mentioned in the documentation, or better, show some warning or error during startup, if it is not supported.

Actual behavior

Messages are added to the outbox, but never cleaned up afterwards.

Workaround

To clear orphaned records due to this bug execute the following TSQL:

delete top (@BatchSize) from {tableName} with (rowlock)
where 
    (Dispatched = 1 and DispatchedAt < @DispatchedBefore)
    or 
    (Dispatched = 0 and Operations = '[]' and DispatchedAt IS NULL);

Versions

NServiceBus.TransactionalSession 3.2.0
NServiceBus.Persistence.Sql 8.1.0
NServiceBus.Persistence.Sql.TransactionalSession 8.1.0
NServiceBus.Extensions.Hosting 3.0.0

Steps to reproduce

Small sample application to reproduce the issue.

var connectionString = builder.Configuration.GetConnectionString("mssql");

var endpointConfiguration = new EndpointConfiguration("Samples.ASPNETCore.Sender");

endpointConfiguration.UseSerialization<SystemJsonSerializer>();
endpointConfiguration.EnableInstallers();
endpointConfiguration.UseTransport(new LearningTransport { TransportTransactionMode = TransportTransactionMode.ReceiveOnly });

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.SqlDialect<SqlDialect.MsSqlServer>();
persistence.ConnectionBuilder(() => new SqlConnection(connectionString));

persistence.EnableTransactionalSession();

var outbox = endpointConfiguration.EnableOutbox();
outbox.RunDeduplicationDataCleanupEvery(TimeSpan.FromSeconds(1));
outbox.KeepDeduplicationDataFor(TimeSpan.FromSeconds(30));
outbox.UsePessimisticConcurrencyControl();

builder.UseNServiceBus(endpointConfiguration);

[HttpPost("TransactionSession")]
public async Task<IActionResult> TransactionSession([FromServices] ITransactionalSession session)
{
    await session.Open(new SqlPersistenceOpenSessionOptions());

    await session.Commit();

    return Ok();
}

Relevant log output

No response

Additional Information

No response

[andreasohlund]

Thanks for reporting this @PhilAch this looks like a bug on our end, we are trying to reproduce it on our end and let you know here about our progress

[andreasohlund]

@PhilAch We ranked it as a priority bug, but not critical. This means we have queued it for work with the other priority bugs and a team will take this on when one becomes available to work on it. I don't have an ETA, the team should say something on this issue when they start working on it.

I know you mentioned that you are not currently affected but should that change we have added a workaround section in the description above

[PhilAch]

@andreasohlund Thank you for your feedback.