If an exception occurs disposing the SQL Transaction the SQL Connection is not closed

[droyad]

Severity

SQL Connection Leak

Version

Likely a long time

Latest Version

I could reproduce the problem in the latest build

What happened?

If an exception occurs disposing the SQL Transaction, the SQL Connection is not closed, resulting in a connection leak.

The source is this Dispose Method in Nevermore and should likely handle and ignore/aggregate errors and always attempt to dispose the connection.

It's also likely this Dispose method should also be wrapped in try/catch on each line.

Reproduction

Getting these exceptions does depend on timing, the robw/reproduce-8840 branch sets up this reasonably reliably. To trigger it, run a deployment with at least one target.

One of the triggering conditions is #8840

Error and Stacktrace

System.InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.
   at Microsoft.Data.SqlClient.SqlInternalTransaction.Rollback()
   at Octopus.Core.RelationalStorage.RawRelationalTransaction.Dispose() in ./source/Octopus.Core/RelationalStorage/RawRelationalTransaction.cs:line 153
   at Autofac.Core.Disposer.DisposeAsync(Boolean disposing)
   at Autofac.Core.Lifetime.LifetimeScope.DisposeAsync(Boolean disposing)
   at Octopus.Core.Infrastructure.MessageBus.PipelineFactory.BuildAndRun[TEvent](Int64 sequenceNumber, TEvent e, IDictionary`2 metadata, String consumerGroupId, Type handlerType, CancellationToken cancellationToken) in ./source/Octopus.Core/Infrastructure/MessageBus/PipelineFactory.cs:line 53
   at Octopus.Core.Infrastructure.MessageBus.MessagePump.Dispatch[TEvent](Int64 sequenceNumber, TEvent e, IDictionary`2 metadata, CancellationToken cancellationToken) in ./source/Octopus.Core/Infrastructure/MessageBus/MessagePump.cs:line 101

System.InvalidOperationException: The connection does not support MultipleActiveResultSets.
   at Microsoft.Data.SqlClient.SqlInternalTransaction.Rollback()
   at Octopus.Core.RelationalStorage.RawRelationalTransaction.Dispose() in ./source/Octopus.Core/RelationalStorage/RawRelationalTransaction.cs:line 153
   at Autofac.Core.Disposer.DisposeAsync(Boolean disposing)
   at Autofac.Core.Lifetime.LifetimeScope.DisposeAsync(Boolean disposing)
   at Octopus.Core.Infrastructure.MessageBus.PipelineFactory.BuildAndRun[TEvent](Int64 sequenceNumber, TEvent e, IDictionary`2 metadata, String consumerGroupId, Type handlerType, CancellationToken cancellationToken) in ./source/Octopus.Core/Infrastructure/MessageBus/PipelineFactory.cs:line 53
   at Octopus.Core.Infrastructure.MessageBus.MessagePump.Dispatch[TEvent](Int64 sequenceNumber, TEvent e, IDictionary`2 metadata, CancellationToken cancellationToken) in ./source/Octopus.Core/Infrastructure/MessageBus/MessagePump.cs:line 101

### More Information

_No response_

### Workaround

Workaround is available by contacting our Support

[octoreleasebot]

Release Note: Ensure SQL connections are closed when encountering exceptions during disposal.

[Octobob]

🎉 The fix for this issue has been released in:

Release stream	Release
2024.1	2024.1.12860
2024.2	2024.2.9210
2024.3	2024.3.3290