Changes committed despite active transaction and rollback in Oracle EF Core

[cdMOE]

EDIT
Read comment

Hello,

I encountered a situation where changes to the database are committed despite an active transaction, even when an error occurs and RollbackTransaction() is called.

The following code demonstrates the issue:

public class Worker(ILogger<Worker> logger, IServiceScopeFactory serviceScopeFactory) : BackgroundService
{
    protected override Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var scope = serviceScopeFactory.CreateScope();
        var context = scope.ServiceProvider.GetRequiredService<MyContext>();

        var header = new Header
        {
            Id = 3,
            Data = "something"
        };

        context.Database.BeginTransaction();
        context.Headers.Add(header);
        context.SaveChanges();

        try
        {
            using var cmd = new OracleCommand(string.Empty, context.Database.GetDbConnection() as OracleConnection);
            var tablename = "sometable";
            
            // This will throw ORA-01031 (insufficient privileges)
            var createCommand = $"CREATE GLOBAL TEMPORARY TABLE {tablename} (something number)";
        
            cmd.CommandText = createCommand;
            cmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            logger.LogError(e, "error");
            context.Database.RollbackTransaction();
            return Task.FromException(e);
        }

        context.Database.CommitTransaction();
        
        return Task.CompletedTask;
    }
}

Reproduction Steps

1. Run the provided code with an Oracle database where the user does not have permission to create a table.
2. Observe that an ORA-01031 exception is thrown.
3. Check the database: the mytest_headers table still contains the inserted row.

Expected Behavior

Since a transaction was started using context.Database.BeginTransaction(), I expected the context.Headers.Add(header); operation to not be committed because:

• The transaction was never explicitly committed.
• RollbackTransaction() was called after the exception.

However, after inspecting the database, I see that the row was committed.

Reproducible Test Setup

Repro Project linked.

Table Creation

CREATE TABLE mytest_headers (
    ID NUMBER,
    Data VARCHAR2(50),
    PRIMARY KEY(Id)
);

Database User Setup

CREATE USER mytestuser IDENTIFIED BY mytest123;
GRANT CREATE SESSION TO mytestuser;
GRANT SELECT, INSERT ON mytest_headers TO mytestuser;

Environment

• .net version: 8.0
• Microsoft.EntityFrameworkCore Version 8.0.14
• Oracle.EntityFrameworkCore Version 8.23.70
• Oracle.ManagedDataAccess.Core" Version 23.7.0
• Oracle DB Version: 19

ReproOracleCommand.zip

[cdMOE]

I just realized that this issue is not specific to the OracleException. The transaction was not being used correctly in the first place, so after the first SaveChanges() call, the changes were committed—regardless of whether the later ExecuteNonQuery() call succeeded or threw an exception. Apologies for the oversight.

I still wonder if there is a way to make ODP.NET and EF Core share the same transaction though, this is what challenges me.

[cdMOE]

On second thought, I'm not sure if this behavior is expected. When calling SaveChanges() and then executing OracleCommand.ExecuteNonQuery(), the changes from SaveChanges() are committed immediately, rather than waiting for CommitTransaction() to be called.

Is this the intended behavior?

[alexkeh]

Oracle EF Core doesn't currently support DDL statements in transactions. A DDL in a transaction will commit everything up to and including the DDL.

If the transaction only had DML, then you would not see the commit occur prematurely.

[cdMOE]

Thank you for clarifying @alexkeh ! :)