Transactions not working for SubSonic under Oracle?

The following code sample works perfectly under SQL Server 2005:

using (TransactionScope ts = new TransactionScope()) {
            using (SharedDbConnectionScope scope = new SharedDbConnectionScope()) {
                MyTable t = new MyTable();
                t.Name = "Test";
                t.Comments = "Comments 123";
                t.Save();
                ts.Complete();
            }
        }

But under Oracle 10g it throws a "ORA-02089: COMMIT is not allowed in a subordinate session" error. If I only execute the code inside the SharedDbConnectionScope block then everything works OK, but obviously I won't be able to execute operations under a transaction, thus risking 开发者_StackOverflow中文版data corruption.

This is only a small sample of what my real application does. I'm not sure as to what may be causing this behavior; anyone out there care to shed some light on this issue please?

here's the config:

<configSections> <section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" requirePermission="false"/> </configSections> <connectionStrings> <add name="OracleConnection" connectionString="Data Source=XE;User Id=test;Password=test;"/> </connectionStrings> <SubSonicService defaultProvider="OracleProvider"> <providers> <clear/> <add name="OracleProvider" type="SubSonic.OracleDataProvider, SubSonic" connectionStringName="OracleConnection" generatedNamespace="OracleTest" /> </providers> </SubSonicService> 

Looking up that specific error message suggests that something may be trying to explicitly or implicitly COMMIT.

If you look at OracleDataProvider.cs, inside GetInsertSql(Query qry) an explicit COMMIT is issued:

    if (retrieveID)
        insertSQL.AppendFormat("  RETURNING {0} INTO :lllhhhmmm; COMMIT; END; ", qry.Schema.PrimaryKey.ColumnName);

COMMIT is also called in ExecuteScalar() in the same file. Do you still see the problem when you are updating records instead of inserting them?

Try removing

using (TransactionScope ts = new TransactionScope())

and use only the SharedDbConnectionScope, like this:

        SharedDbConnectionScope scope = new SharedDbConnectionScope();
        using (scope) {
            MyTable t = new MyTable();
            t.Name = "Test";
            t.Comments = "Comments 123";
            t.Save();
            scope.commit();
        }