Problem with Trigger in TransactionScope

Problem with Trigger in TransactionScope

Postby cew3 » Tue 12 Apr 2011 14:57

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
cew3
 
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Postby AndreyR » Wed 13 Apr 2011 13:29

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby cew3 » Wed 13 Apr 2011 14:34

AndreyR wrote: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
cew3
 
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Postby AndreyR » Wed 13 Apr 2011 16:01

I recommend you to perform the tests using the Trial version of our provider; it is valid for a month.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby cew3 » Thu 14 Apr 2011 07:51

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
cew3
 
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Postby Shalex » Thu 14 Apr 2011 08:59

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/docs/?licensing.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.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Postby cew3 » Thu 14 Apr 2011 12:58

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

Any ideas?

Best
cew3
cew3
 
Posts: 20
Joined: Wed 15 Jul 2009 14:50

Postby Shalex » Thu 14 Apr 2011 13:17

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).
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Is this resolved...

Postby ramana.bhavaraju » Sat 30 Jul 2011 02:24

I am having the same issue, please let me know the resolution for this.
ramana.bhavaraju
 
Posts: 14
Joined: Thu 28 Jan 2010 17:13

Fixed it..

Postby ramana.bhavaraju » Mon 01 Aug 2011 17:31

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.
ramana.bhavaraju
 
Posts: 14
Joined: Thu 28 Jan 2010 17:13

Postby Shalex » Tue 02 Aug 2011 14:29

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.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Re: Fixed it..

Postby bhav27 » Mon 06 Feb 2012 16:31

ramana.bhavaraju wrote: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?
bhav27
 
Posts: 20
Joined: Tue 20 Apr 2010 11:53

Postby Shalex » Wed 08 Feb 2012 17:54

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.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Re: Problem with Trigger in TransactionScope

Postby teddyFry » Fri 18 May 2012 12:09

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/2012/04/01/tips-to-avoid-deadlocks-in-entity-framework-applications.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.
teddyFry
 
Posts: 7
Joined: Wed 12 Oct 2011 12:33

Re: Problem with Trigger in TransactionScope

Postby Shalex » Mon 21 May 2012 16:11

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.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Next

Return to dotConnect for Oracle