Problem with Trigger in TransactionScope

[cew3]

Hi,

when updating an entity within a TransactionScope, I get the following exception:

Code: Select all

System.Data.UpdateException was unhandled by user code
  Message=Fehler beim Aktualisieren der Einträge. Weitere Informationen finden Sie in der internen Ausnahme.
  Source=System.Data.Entity
  StackTrace:
       bei System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
       bei System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
       bei System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
       bei System.Data.Objects.ObjectContext.SaveChanges()
       bei PRODOK.Core.BusinessLayer.BusinessObjects.CboBase.DeleteDataInDb() in D:\Entwickl\Prodok\V10\Prodok\PRODOK.Core\BusinessLayer\BusinessObjects\CboBase.cs:Zeile 557.
       bei PRODOK.Core.BusinessLayer.BusinessObjects.CboDocument.DeleteDataInDb() in D:\Entwickl\Prodok\V10\Prodok\PRODOK.Core\BusinessLayer\BusinessObjects\CboDocument.cs:Zeile 499.
       bei PRODOK.Core.BusinessLayer.BusinessObjects.CboDocument.DeleteExecute(ExecContext args) in D:\Entwickl\Prodok\V10\Prodok\PRODOK.Core\BusinessLayer\BusinessObjects\CboDocument.cs:Zeile 568.
  InnerException: Devart.Data.Oracle.OracleException
       Message=ORA-08177: can't serialize access for this transaction
ORA-06512: at "PDK-PRJ-ORA_DEMO90D.U_Revisions", line 273
ORA-04088: error during execution of trigger 'PDK-PRJ-ORA_DEMO90D.U_Revisions'
       Source=Devart.Data.Oracle
       ErrorCode=-2147467259
       Code=8177
       Offset=0
       StackTrace:
            bei xc.t.d()
            bei Devart.Data.Oracle.bi.k()
            bei Devart.Data.Oracle.bi.c()
            bei Devart.Data.Oracle.p.a(Int32 A_0, a4 A_1)
            bei Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
            bei Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
            bei Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
            bei Devart.Data.Oracle.Entity.k.a.n()
            bei System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
            bei System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
       InnerException: 

I use dotConnect 5.70.190.0. in direct mode.

The problem seems to occure during executing of the updatee trigger
"ORA-08177: can't serialize access for this transaction"
"ORA-04088: error during execution of trigger"
Without the TransactionScope the trigger works fine.

What's wrong?

Best regards,
cew3

[AndreyR]

We have made a number of changes in the TransactionScope implementation since the 5.70 version of dotConnect for Oracle.
Could you please try upgrading to the latest 6.10.136 build of dotConnect for Oracle?
Please notify us if the error persists.

[cew3]

We have made a number of changes in the TransactionScope implementation since the 5.70 version of dotConnect for Oracle.
Could you please try upgrading to the latest 6.10.136 build of dotConnect for Oracle?
Please notify us if the error persists.

This means that we have to buy new licences to check if the problem still existst...

Would be great to know if then the problem has gone BEFORE spending money for a test.

Best regards
cew3

[AndreyR]

I recommend you to perform the tests using the Trial version of our provider; it is valid for a month.

[cew3]

I downloaded the trial, installed the package in a VM and copied the needed files

• Devart.data.dll -- [5.0.236, 06.04.2011]
  Devart.Data.Oracle.dll -- [6.10.135.0, 06.04.2011]
  Devart.Data.Oracle.Entity.dll -- [6.10.135.0, 06.04.2011]

to my project.

Now i get the error:
"License not found. Please view "Licensing" topic in dotConnect for Oracle documentation for details or contact Devart technical support."


Best regards,
cew

[Shalex]

dotConnect for Oracle uses .NET component licensing. To build an application, dotConnect for Oracle requires a valid license resource. You can build this license resource only on a machine where dotConnect for Oracle is installed.

Please refer to http://www.devart.com/dotconnect/oracle ... nsing.html. There are two ways to add the license resource:
1) automatically - via the Tools > Oracle > License Information menu of Visual Studio;
2) manually - determine the type of your project (ClassLibrary, ASP.NET Web Application, etc) and refer to the corresponding part of the Licensing article.

Notify us about the results.

[cew3]

I tried using the 6.10.135.0 with the same result.
It does not work.

Any ideas?

Best
cew3

[Shalex]

Please send us the following information:
1) have you installed dotConnect for Oracle on the machine where you are building your project?
2) have you cleared the bin and obj folders of your project and then rebuilt your project after installing the new version of dotConnect for Oracle?
3) the content of the Tools > Oracle > License Information menu of your Visual Studio;
4) the type of your project (ClassLibrary, ASP.NET Web Application, etc).

[ramana.bhavaraju]

I am having the same issue, please let me know the resolution for this.

[ramana.bhavaraju]

After 4 days of rocking my head against the wall, i found the issue was happenning only after a certain number of inserts into a table that had triggers attached to it which inturn write to some other table.

The issue is mainly due to devart transaction scope and the way it executes the sql statements i believe. Because once i chnage the connection string to "Direct = false", i dont have this error anymore.
So devart folks , please fix the issue and please send us a hotfix.

Bye,
Ramana @ Rosetta Inc.

[Shalex]

Ramana, please send us the following information:
1) the exact text of the error message and your call stack;
2) your versions of dotConnect for Oracle (x.xx.xxx) and Oracle server (x.x);
3) a small test project with a model and the DDL/DML script to reproduce the issue in our environment.

[bhav27]

After 4 days of rocking my head against the wall, i found the issue was happenning only after a certain number of inserts into a table that had triggers attached to it which inturn write to some other table.

The issue is mainly due to devart transaction scope and the way it executes the sql statements i believe. Because once i chnage the connection string to "Direct = false", i dont have this error anymore.
So devart folks , please fix the issue and please send us a hotfix.

Bye,
Ramana @ Rosetta Inc.

Hi
We seems to have exactly same issue when using direct connection on version 6.50. Is this problem been solved?

[Shalex]

We cannot reproduce the problem with the following code using dotConnect for Oracle v 6.70.293:

Code: Select all

    try {
        new OracleMonitor() { IsActive = true };
        using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(2, 0, 0))) {
            using (OracleConnection conn = new OracleConnection()) {
                conn.ConnectionString = "server=dboracle;direct=true;sid=orcl1120;uid=scott;pwd=tiger;";
                conn.Open();

                OracleScript script = new OracleScript();
                script.Connection = conn;
                script.ScriptText = @"CREATE TABLE A111 (
                    ID NUMBER(38),
                    DATAA VARCHAR2(20),
                    CONSTRAINT PK_A111 PRIMARY KEY (ID));
                CREATE TABLE B111 (
                    ID NUMBER(38),
                    DATAB VARCHAR2(20),
                    CONSTRAINT PK_B111 PRIMARY KEY (ID));
                CREATE OR REPLACE TRIGGER TRIGGER111
                AFTER INSERT ON A111
                REFERENCING NEW AS NEWVALUES OLD AS OLDVALUES FOR EACH ROW
                BEGIN
                    insert into b111 values (:NewValues.id, :NewValues.dataA);
                END;";
                script.Execute();

                OracleCommand cmd = conn.CreateCommand();
                //cmd.CommandText = "delete from a111";
                //cmd.ExecuteNonQuery();
                //cmd.CommandText = "delete from b111";
                //cmd.ExecuteNonQuery();

                cmd.CommandText = "insert into a111 values (:id, 'some data')";
                cmd.Parameters.Add("id", OracleDbType.Integer);
                for (int i = 0; i < 100000; i++) {
                    cmd.Parameters["id"].Value = i;
                    cmd.ExecuteNonQuery();
                }
            }
            trans.Complete();
        }
    }
    catch (Exception ex) {
        Console.WriteLine(ex.Message + ex.InnerException);
    }

bhav27, please send us the following information:
1) the exact text of the error message and your call stack;
2) your versions of dotConnect for Oracle (x.xx.xxx) and Oracle server (x.x);
3) a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.

[teddyFry]

Hello,

A few days ago I met a very similar problem.
It seems that in a highly concurency environment such as reading/writing data in mass. The default Oracle behavior seems to throw this exception when you try to access data that are still under the effect of a transaction and that may not yet be validated.

To compensate this problem, you should force the transaction to be of type "ReadCommited"

Code: Select all

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, 
new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
...
}

I find this tip here : http://blogs.msdn.com/b/diego/archive/2 ... tions.aspx
But Oracle doesn't support all kind of transaction types and throw a "Network error : 200" (in direct mode in my case).

I hope this tip help someone.

[Shalex]

teddyFry, thank you for posting.
We cannot reproduce the "Network error : 200" error using Direct Mode with any IsolationLevel. Please specify a particular IsolationLevel, your version (x.xx.xxx) of dotConnect for Oracle and, if possible, send us a small test project.