ADO.net Local Transactions fails persistence tests on Oracle in Serializable isolation mode when running on the build agent

[andreasohlund]

Describe the bug

NOTE: Works fine when running locally

Using serializable fails with:

  insert into "SAGA1"
  (
      Id,
      Metadata,
      Data,
      PersistenceVersion,
      SagaTypeVersion,
      Concurrency,
      CORR_CORRELATIONID
  )
  values
  (
      :Id,
      :Metadata,
      :Data,
      :PersistenceVersion,
      :SagaTypeVersion,
      1,
      :CorrelationId
  )
    ----> Oracle.ManagedDataAccess.Client.OracleException : ORA-08177: can't serialize access for this transaction
  https://docs.oracle.com/error-help/db/ora-08177/
  
    Failed Should_create_new_sagas_when_committed [55 ms]
    Error Message:
     System.Exception : Failed to ExecuteNonQuery. CommandText:
  
  insert into "SAGA1"
  (
      Id,
      Metadata,
      Data,
      PersistenceVersion,
      SagaTypeVersion,
      Concurrency,
      CORR_CORRELATIONID
  )
  values
  (
      :Id,
      :Metadata,
      :Data,
      :PersistenceVersion,
      :SagaTypeVersion,
      1,
      :CorrelationId
  )
    ----> Oracle.ManagedDataAccess.Client.OracleException : ORA-08177: can't serialize access for this transaction
  https://docs.oracle.com/error-help/db/ora-08177/
    Stack Trace:
       at Extensions.ExecuteNonQueryEx(DbCommand command, CancellationToken cancellationToken) in /_/src/SqlPersistence/Extensions.cs:line 120
     at SagaPersister.Save(IContainSagaData sagaData, ISynchronizedStorageSession session, Object correlationId, CancellationToken cancellationToken) in /_/src/SqlPersistence/Saga/SagaPersister_Save.cs:line 44
     at NServiceBus.PersistenceTesting.Sagas.SagaPersisterTests.SaveSagaWithSession[TSagaData](TSagaData saga, ICompletableSynchronizedStorageSession session, ContextBag context, CancellationToken cancellationToken) in /_1/nservicebus.persistencetests.sources/9.2.2/contentFiles/cs/net8.0/NSB.PersistenceTests/Sagas/SagaPersisterTests.cs:line 42
     at NServiceBus.PersistenceTesting.Sagas.When_multiple_sagas_in_outbox_transaction.Should_create_new_sagas_when_committed() in /_1/nservicebus.persistencetests.sources/9.2.2/contentFiles/cs/net8.0/NSB.PersistenceTests/Sagas/When_multiple_sagas_in_outbox_transaction.cs:line 29
     at NUnit.Framework.Internal.TaskAwaitAdapter.GenericAdapter`1.BlockUntilCompleted()
     at NUnit.Framework.Internal.AsyncToSyncAdapter.Await[TResult](Func`1 invoke)
     at NUnit.Framework.Internal.Commands.TestMethodCommand.RunTestMethod(TestExecutionContext context)
     at NUnit.Framework.Internal.Commands.TestMethodCommand.Execute(TestExecutionContext context)
     at NUnit.Framework.Internal.Execution.SimpleWorkItem.<>c__DisplayClass3_0.<PerformWork>b__0()
     at NUnit.Framework.Internal.ContextUtils.<>c__DisplayClass1_0`1.<DoIsolated>b__0(Object _)
     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
  --- End of stack trace from previous location ---
     at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
     at NUnit.Framework.Internal.ContextUtils.DoIsolated(ContextCallback callback, Object state)
     at NUnit.Framework.Internal.ContextUtils.DoIsolated[T](Func`1 func)
     at NUnit.Framework.Internal.Execution.SimpleWorkItem.PerformWork()
  --OracleException
     at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
     at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
     at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQueryAsync(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, OracleConnection connection, ENQ_RefAndOutParamArgCtx enq_refOutArgCtx, Boolean isFromEF, Boolean bAsync)
     at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQueryInternalAsync(Boolean bAsync, CancellationToken cancellationToken)
     at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQueryAsyncHelper(CancellationToken cancellationToken)

https://github.com/Particular/NServiceBus.Persistence.Sql/pull/1652/checks#step:13:925

Steps to reproduce

Add IsolationMode.Serializable to https://github.com/Particular/NServiceBus.Persistence.Sql/pull/1652/files#diff-108cdc49e5677a6d04974a98be8e0d41d90c524882d158ffab77d5bc68a9d494R34

Relevant log output

Additional Information

Workarounds

Possible solutions

Additional information

[andreasohlund]

Testing locally works fine so seems like something related to the build agent

[ngallegos]

We've reviewed this issue and added it to our backlog. It's eligible to be picked up for a future release, but we can't provide a firm timeline.