Exception when deleting messages: `SqlTransaction has completed; it is no longer usable`

[Aaronontheweb]

Version Information
Version of Akka.NET? 1.5.38
Which Akka.NET Modules? Akka.Persistence.Sql

Describe the bug

When running the following code - normal post-snapshot cleanup code:

Command<SaveSnapshotSuccess>(x =>
    {
        DeleteMessages(x.Metadata.SequenceNr - 1);
    }
);

The following exception can sometimes happen:

Failed to DeleteMessages ToSequenceNr [10200] for PersistenceId [change-queue] due to: Exception thrown when rolling back database transaction

The inner exception is:  This SqlTransaction has completed; it is no longer usable.

Expected behavior

Delete operation should complete without error.

Actual behavior

It's racy, but sometimes the delete operation can fail with this exception - looks like a transaction / scope management issue.

[Arkatufus]

Is there a full stack trace example of this? And was the journal running in sql.common compatibility mode?

[Aaronontheweb]

So the error occurred here:

Akka.Persistence.Sql/src/Akka.Persistence.Sql/Extensions/ConnectionFactoryExtensions.cs

Lines 35 to 46 in 50c585d

	catch (Exception ex1)
	{
	try
	{
	await tx.RollbackAsync(token);
	}
	catch (Exception ex2)
	{
	throw new AggregateException("Exception thrown when rolling back database transaction", ex2, ex1);
	}
	throw;

We're missing the second inner exception, but the first one indicates to me that the transaction got disposed when it shouldn't have somehow.

[Aaronontheweb]

--> (Inner Exception #1) System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
   at Microsoft.Data.SqlClient.SqlTransaction.ZombieCheck()
   at Microsoft.Data.SqlClient.SqlTransaction.Commit()
   at LinqToDB.Async.AsyncDbTransaction.CommitAsync(CancellationToken cancellationToken)
   at LinqToDB.Data.DataConnection.<>c.<<CommitTransactionAsync>b__3_1>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at LinqToDB.Data.DataConnection.<TraceActionAsync>d__8`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at LinqToDB.Data.DataConnection.<CommitTransactionAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at LinqToDB.Data.DataConnectionTransaction.<CommitAsync>d__7.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

looks like the transaction got hosed even before we tried to commit

[Aaronontheweb]

This is the zombie check that @Arkatufus noted in his logs:

https://github.com/dotnet/SqlClient/blob/b2ee4356eabff748b2e075cda70431d750725fdb/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/SqlTransaction.cs#L455-L468

[Aaronontheweb]

Linq2Db transaction comitting code:

https://github.com/linq2db/linq2db/blob/bc50707294c09851487dd27406e27fb137a06521/Source/LinqToDB/DataContextTransaction.cs#L142-L164

Linq2Db transaction opening code:

https://github.com/linq2db/linq2db/blob/bc50707294c09851487dd27406e27fb137a06521/Source/LinqToDB/Data/DataConnection.Async.cs#L33-L72

This is where a second transaction can open on the same connection, aborting the original one in-progress

[Arkatufus]

Merged into #533