SubmitChanges() call does not return

webx · Post by **webx** » Mon 07 Jun 2010 15:27

I am experiencing some freezes occurring in SubmitChanges() with LINQ to Oracle 5.70.140.0. The code can work perfectly for days then occasionaly it will freeze at SubmitChanges() after inserting a bunch of entries into a database table with LINQ. The strange thing is it seems to happen randomly so is difficult to reproduce, the amount of rows added to the table before the SubmitChanges() does not seem to make any difference.

The freeze occasionally happens in the following code:

Code: Select all

            Project p = new Project
            {
                Type = projectType,
                CreationDate = DateTime.Now,
                GroupId = group
            };

            db.Projects.InsertOnSubmit(p);
            db.SubmitChanges();

            foreach (CSDBPart prt in parts)
            {
                ProjectPart part = new ProjectPart
                {
                    PartId = prt.PartId,
                    ProjectId = p.Id,
                    Value = prt.Value
                };

                db.ProjectParts.InsertOnSubmit(part);
            }
            db.SubmitChanges();   <-- This is where it will occasionally freeze

In the table ProjectPart, I have set Auto Generate Value to True, Auto-Sync to OnInsert and IdGenerator to (None).

The table ProjectPart has the following Oracle Trigger defined:

Code: Select all

create or replace TRIGGER "CSDB"."ProjectPart_Id_ATR" 
BEFORE INSERT ON "ProjectPart" 
FOR EACH ROW 
DECLARE 
MAX_ID NUMBER; 
CUR_SEQ NUMBER; 
BEGIN 
IF :NEW."Id" IS NULL THEN 
SELECT "ProjectPart_Id_ASQ".NEXTVAL INTO :NEW."Id" FROM DUAL; 
ELSE 
SELECT GREATEST(MAX("Id"), :NEW."Id") INTO MAX_ID FROM "ProjectPart"; 
SELECT "ProjectPart_Id_ASQ".NEXTVAL INTO CUR_SEQ FROM DUAL; 
WHILE CUR_SEQ < MAX_ID 
LOOP 
SELECT "ProjectPart_Id_ASQ".NEXTVAL INTO CUR_SEQ FROM DUAL; 
END LOOP; 
END IF; 
END;

Is it something to do with the trigger?

Any help will be appreciated!

AndreyR · Post by **AndreyR** » Mon 07 Jun 2010 16:44

Could you please post here the call stack of the hanged application?
To get this call stack you should aattach another Visual Studio instance to the hanged process (Debug-> Attach to process).
Then press the Pause button.
Please make sure that the second VS instance has the Enable Just My Code check box cleared.

webx · Post by **webx** » Mon 07 Jun 2010 17:36

Here is the call stack:

[Managed to Native Transition]
OciCall.dll!OciDynamicType.OCIStmtExecute() + 0x2e bytes
Devart.Data.Oracle.dll!Devart.Data.Oracle.ap.a(int A_0 = 1, Devart.Data.Oracle.a4 A_1) + 0x9f bytes
Devart.Data.Oracle.dll!Devart.Data.Oracle.OracleCommand.InternalExecute(System.Data.CommandBehavior behavior = Default, System.IDisposable disposable, int startRecord, int maxRecords = 0, bool nonQuery = false) + 0x309 bytes
Devart.Data.dll!Devart.Common.DbCommandBase.ExecuteDbDataReader(System.Data.CommandBehavior behavior = Default, bool nonQuery = false) + 0xb5 bytes
Devart.Data.dll!Devart.Common.DbCommandBase.ExecuteDbDataReader(System.Data.CommandBehavior behavior) + 0x7 bytes
System.Data.dll!System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() + 0xd bytes
Devart.Data.Linq.dll!Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(string commandText, System.Collections.Generic.List resultsetParameters = null, System.Collections.Generic.IList parameters = Count = Cannot evaluate expression because a native frame is on top of the call stack., bool isBatch, out System.Data.IDbCommand dbCommand = {Devart.Data.Oracle.OracleCommand}) + 0x180 bytes
Devart.Data.Linq.dll!Devart.Data.Linq.h.a(Devart.Data.Linq.SubmitCommand A_0 = {Devart.Data.Linq.SubmitCommand}, Devart.Data.Linq.SubmitCommandBuilder A_1 = {Devart.Data.Linq.SubmitCommandBuilder}) + 0x4c bytes
Devart.Data.Linq.dll!Devart.Data.Linq.h.a(Devart.Data.Linq.SubmitedObject A_0 = {Devart.Data.Linq.SubmitedObject}, Devart.Data.Linq.SubmitCommand A_1 = {Devart.Data.Linq.SubmitCommand}, Devart.Data.Linq.SubmitCommandBuilder A_2 = {Devart.Data.Linq.SubmitCommandBuilder}) + 0x17 bytes
Devart.Data.Linq.dll!Devart.Data.Linq.r.a(Devart.Data.Linq.SubmitedObject A_0, Devart.Data.Linq.SubmitCommand A_1, Devart.Data.Linq.SubmitCommandBuilder A_2) + 0x4e bytes
Devart.Data.Linq.dll!Devart.Data.Linq.s.a(Devart.Data.Linq.m A_0, Devart.Data.Linq.af A_1, object A_2, bool A_3) + 0x1a1 bytes
Devart.Data.Linq.dll!Devart.Data.Linq.aa.a(Devart.Data.Linq.s A_0, Devart.Data.Linq.af A_1, bool A_2) + 0x38 bytes
Devart.Data.Linq.dll!Devart.Data.Linq.aa.a(Devart.Data.Linq.DataContext A_0 = {CSDBServiceLibrary.CSDBDataContext}, System.Data.Linq.ConflictMode A_1 = FailOnFirstConflict) + 0x13b bytes
Devart.Data.Linq.dll!Devart.Data.Linq.aa.b(Devart.Data.Linq.DataContext A_0, System.Data.Linq.ConflictMode A_1) + 0x10 bytes
Devart.Data.Linq.dll!Devart.Data.Linq.DataContext.SubmitChanges(System.Data.Linq.ConflictMode failureMode) + 0x137 bytes
Devart.Data.Linq.dll!Devart.Data.Linq.DataContext.SubmitChanges() + 0xa bytes

AndreyR · Post by **AndreyR** » Wed 09 Jun 2010 16:14

Judging by the call stack, the problem is not associated with our components. Could you please check that your database does not contain deadlocks.
If possible, please try to trace the generated SQL using the DataContext.Log property:

Code: Select all

StringBuilder sb = new StringBuilder();
context.Log = new System.IO.StringWriter(sb);

Try to execute the same code using ADO.NET code or any Oracle administrative tool.

bmarotta · Post by **bmarotta** » Wed 29 Sep 2010 11:44

I am getting the same error using doracle version 5.60.124. This mostly occurs when running on the IIS server. It is very hard to simulate under development environment.

I see that the error is not on the Devart Linq side, but what astonish me is that the CommandTimeout from the context does not work for this case.

We did also check and there is no deadlock on the database. Actually, when there is a deadlock on the database, the command timeout doesn't work either. Guess, if the CommandTimeout works at all...

Also, the log suggestion will never work as the SubmitChanges never returns, and the log can only be seen after the SubmitChanges has finished.

This is really urgent and it is jeopardizing our full application.

StanislavK · Post by **StanislavK** » Wed 29 Sep 2010 15:36

There was a bug with setting the CommandTimeout property of DataContext in the 5.60.124 version. Please try using the latest 5.70.170 version of dotConnect for Oracle where this bug is fixed. You can download it from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (provided that you have and active subscription):
http://secure.devart.com/

As for logging, please try using the DbMonitor application (or the OracleMonitor object on its own):
http://www.devart.com/dotconnect/oracle ... nitor.html
http://www.devart.com/dotconnect/oracle ... nitor.html
In contrast to DataContext.Log, DbMonitor logs SQL commands at the moment they are sent to the server.

bmarotta · Post by **bmarotta** » Thu 30 Sep 2010 11:18

Hi Stanislav,

thanks for the response. We will migrate the version. Not sure if this is related, but we did a dump on the IIS process and we found another source for the hanging.

There is a thread that is starting a Transaction and the response for this transaction does not comeback (or takes too long) and this blocks all other context calls.

First question:

Can I set a timeout for the opening of a connection?

Second question:

Is there a better way to handle the transaction on the context?

Please note that I have a multi-threaded application. I am sure that we are not sharing context among threads an that, at each time, I have only one context per thread.

Nevertheless, due to the code architecture, I may do a commit, than launch some new queries, than do another commit and even execute some sql command directly. We need to have these commits and command execution on the same transaction.

Here is the code for the Transaction. It is implemented on the context partial class:

Code: Select all

        public bool BeginTransaction()
        {
            if (InTransaction)
                return false;
            // Assure we have a connection
            Connection.Open();
            Transaction = Connection.BeginTransaction();
            return true;
        }

This is the call stack for the thread:

4283e014 7c82860c [HelperMethodFrame_1OBJ: 4283e014] System.Threading.WaitHandle.WaitOneNative(Microsoft.Win32.SafeHandles.SafeWaitHandle, UInt32, Boolean, Boolean)
4283e0c0 792b68af System.Threading.WaitHandle.WaitOne(Int64, Boolean)
4283e0dc 7928aa17 System.Threading.WaitHandle.WaitOne(System.TimeSpan, Boolean)
4283e0f8 27fec04d Devart.Common.Utils.WaitOne(System.Threading.WaitHandle, System.TimeSpan, Boolean)
4283e108 1d90d6a4 Devart.Common.DbConnectionPool.GetObject(Devart.Common.DbConnectionBase)
4283e13c 1d90d410 Devart.Common.DbConnectionFactory.a(Devart.Common.DbConnectionBase)
4283e16c 1d90d2e5 Devart.Common.DbConnectionClosed.Open(Devart.Common.DbConnectionBase)
4283e19c 1d90d0b1 Devart.Common.DbConnectionBase.Open()
4283e1cc 1d90c5ce Devart.Data.Oracle.OracleConnection.Open()
4283e1fc 25efcc97 IssuanceEngine.Data.PumaDataContext.BeginTransaction()
4283e208 25efc486 IssuanceEngine.BusinessObjects.Workflow.WorkflowObjectProcessThread.ProcessNextItem(IssuanceEngine.BusinessObjects.Workflow.PersistentWorkflowExecution)
4283e2a4 25ef9cd0 IssuanceEngine.BusinessObjects.BaseProcessThread`1+c__DisplayClass4[[System.__Canon, mscorlib]].b__3()
4283e2ac 25ef767d IssuanceEngine.BusinessObjects.BaseProcessThread`1[[System.__Canon, mscorlib]].CallProtected(System.Action)
4283e2f0 25ef941a IssuanceEngine.BusinessObjects.BaseProcessThread`1[[System.__Canon, mscorlib]].ProcessQueue()
4283e350 25ef807f IssuanceEngine.BusinessObjects.BaseProcessThread`1[[System.__Canon, mscorlib]].MainLoop()
4283e430 2458a29a IssuanceEngine.BusinessObjects.ThreadManagement.ThreadManager+c__DisplayClass11.b__10()
4283e45c 792d6d66 System.Threading.ThreadHelper.ThreadStart_Context(System.Object)
4283e468 792f5681 System.Threading.ExecutionContext.runTryCode(System.Object)
4283e898 79e71b4c [HelperMethodFrame_PROTECTOBJ: 4283e898] System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode, CleanupCode, System.Object)
4283e900 792f5577 System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
4283e91c 792e01c5 System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
4283e934 792d6ce4 System.Threading.ThreadHelper.ThreadStart()
4283eb60 79e71b4c [GCFrame: 4283eb60]
4283ee50 79e71b4c [ContextTransitionFrame: 4283ee50]
OS Thread Id: 0x1c0 (46)

I have 37 other threads waiting for the lock release. Here are some call stack examples for the other blocking threads:

0258f64c 7c82860c [GCFrame: 0258f64c]
0258f788 7c82860c [HelperMethodFrame: 0258f788] System.Threading.Monitor.Enter(System.Object)
0258f7dc 27b8b746 Devart.Common.DbConnectionPool.DoomObject()
0258f810 27b8b40c Devart.Common.DbConnectionPoolGroup.a(Devart.Common.DbConnectionPool)
0258f860 27b8b10c Devart.Common.DbConnectionPoolGroup.a(System.Object)
0258f8a4 792a842f System.Threading._TimerCallback.TimerCallback_Context(System.Object)
0258f8ac 792e01ef System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
0258f8c4 792a839b System.Threading._TimerCallback.PerformTimerCallback(System.Object)
0258fa50 79e71b4c [GCFrame: 0258fa50]
0258fb9c 79e71b4c [ContextTransitionFrame: 0258fb9c]

-----------------------

2799ec58 7c82860c [HelperMethodFrame: 2799ec58] System.Threading.Monitor.Enter(System.Object)
2799ecac 1d90d51f Devart.Common.DbConnectionPool.GetObject(Devart.Common.DbConnectionBase)
2799ece0 1d90d410 Devart.Common.DbConnectionFactory.a(Devart.Common.DbConnectionBase)
2799ed10 1d90d2e5 Devart.Common.DbConnectionClosed.Open(Devart.Common.DbConnectionBase)
2799ed40 1d90d0b1 Devart.Common.DbConnectionBase.Open()
2799ed70 1d90c5ce Devart.Data.Oracle.OracleConnection.Open()
2799eda0 2478c01a Devart.Data.Linq.Provider.ConnectionManager.GetOpenConnection()
2799edc8 2478bec8 Devart.Data.Linq.Provider.ConnectionManager.b(Devart.Data.Linq.Provider.IConnectionUser)
2799eddc 2478b2cc Devart.Data.Linq.Provider.DataProvider.a(b, System.Object[], System.Object[], System.Object)
2799eee0 2478b256 Devart.Data.Linq.Provider.DataProvider.a(b, System.Object[])
2799eef4 2478b1b3 Devart.Data.Linq.Provider.DataProvider+b.a(Devart.Data.Linq.Provider.b, System.Object[])
2799ef08 244e6e92 Devart.Data.Linq.DataQuery`1[[System.__Canon, mscorlib]].i()
2799ef1c 799d5f87 System.Collections.Generic.List`1[[System.__Canon, mscorlib]]..ctor(System.Collections.Generic.IEnumerable`1)
2799ef54 6c2fc036 System.Linq.Enumerable.ToList[[System.__Canon, mscorlib]](System.Collections.Generic.IEnumerable`1)
2799ef64 25ef85e7 IssuanceEngine.BusinessObjects.Workflow.WorkflowDispatcherProcessThread.GetNextItemsToProcess()
2799efb4 25ef828e IssuanceEngine.BusinessObjects.BaseProcessThread`1+c__DisplayClass1[[System.__Canon, mscorlib]].b__0()
2799efc0 25ef767d IssuanceEngine.BusinessObjects.BaseProcessThread`1[[System.__Canon, mscorlib]].CallProtected(System.Action)
2799f004 25ef7ea5 IssuanceEngine.BusinessObjects.BaseProcessThread`1[[System.__Canon, mscorlib]].MainLoop()
2799f0e4 2458a29a IssuanceEngine.BusinessObjects.ThreadManagement.ThreadManager+c__DisplayClass11.b__10()
2799f110 792d6d66 System.Threading.ThreadHelper.ThreadStart_Context(System.Object)
2799f11c 792e01ef System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
2799f134 792d6ce4 System.Threading.ThreadHelper.ThreadStart()
2799f360 79e71b4c [GCFrame: 2799f360]
2799f650 79e71b4c [ContextTransitionFrame: 2799f650]

------------------------------

42c1e1c8 7c82860c [HelperMethodFrame: 42c1e1c8] System.Threading.Monitor.Enter(System.Object)
42c1e21c 1d90d51f Devart.Common.DbConnectionPool.GetObject(Devart.Common.DbConnectionBase)
42c1e250 1d90d410 Devart.Common.DbConnectionFactory.a(Devart.Common.DbConnectionBase)
42c1e280 1d90d2e5 Devart.Common.DbConnectionClosed.Open(Devart.Common.DbConnectionBase)
42c1e2b0 1d90d0b1 Devart.Common.DbConnectionBase.Open()
42c1e2e0 1d90c5ce Devart.Data.Oracle.OracleConnection.Open()
42c1e310 2478c01a Devart.Data.Linq.Provider.ConnectionManager.GetOpenConnection()
42c1e338 2478bec8 Devart.Data.Linq.Provider.ConnectionManager.b(Devart.Data.Linq.Provider.IConnectionUser)
42c1e34c 2478b2cc Devart.Data.Linq.Provider.DataProvider.a(b, System.Object[], System.Object[], System.Object)
42c1e450 2478b256 Devart.Data.Linq.Provider.DataProvider.a(b, System.Object[])
42c1e464 2478b1b3 Devart.Data.Linq.Provider.DataProvider+b.a(Devart.Data.Linq.Provider.b, System.Object[])
42c1e478 27b8c8c9 Devart.Data.Linq.DataQuery`1[[System.Int32, mscorlib]].i()
42c1e488 6cc3e2a0 System.Collections.Generic.HashSet`1[[System.Int32, mscorlib]].UnionWith(System.Collections.Generic.IEnumerable`1)
42c1e4b4 6cc3dc2b System.Collections.Generic.HashSet`1[[System.Int32, mscorlib]]..ctor(System.Collections.Generic.IEnumerable`1, System.Collections.Generic.IEqualityComparer`1)
42c1e4cc 6cc3dbd4 System.Collections.Generic.HashSet`1[[System.Int32, mscorlib]]..ctor(System.Collections.Generic.IEnumerable`1)
42c1e4dc 27fb3085 IssuanceEngine.Web.Manager.WorkItemManager.GetBusinessUnitIds(Puma.Transfer.WorkItems.WorkItemFilterScope, Int32)
42c1e550 27fb2ad8 IssuanceEngine.Web.Manager.WorkItemManager.FilterWorkItems(Puma.Transfer.WorkItems.WorkItemFilter, Puma.Transfer.WorkItems.DisplayedWorkItemInformation[])
42c1e580 27fb286c IssuanceEngine.Web.Manager.WorkItemManager.GetWorkItems(Puma.Transfer.WorkItems.WorkItemFilter, Puma.Transfer.WorkItems.DisplayedWorkItemInformation[])

Thanks for the support,

StanislavK · Post by **StanislavK** » Fri 01 Oct 2010 13:26

As for the connection timeout, please try setting the Connection Timeout parameter of the connection string. It specifies the period in which the connection should be opened; if this period is exceeded, an exception is thrown.

Provided that you use a single connection in any particular transaction, the BeginTransaction() method should be the proper way to handle transactions. If you need distributed transactions with several connections, please use the TransactionScope environment.

According to the stack trace, the problem occurs when the attempt to retrieve a connection from the pool is performed. To resolve the problem, please try the following options:
- increase the pool size; please use the Max Pool Size connection string parameter;
- use the OCI pool (if you are not using the Direct connection mode); please set the Oci Session Pooling connection string parameter to true;
- disable connection pooling at all; set the Pooling parameter to false for this.

The detailed information on the connection string parameters is available here:
http://www.devart.com/dotconnect/oracle ... tring.html

bmarotta · Post by **bmarotta** » Fri 01 Oct 2010 18:01

Hi Stanislav,

thanks for the tips. I've already tried the connection timeout and it does not work. At least I never got one and I still have the random hangings on my server.

I tried turning off the pooling but, for my surprise, when doing so, the connection gets randomly closed by the context!!!

If I do:

Code: Select all

context.connection.Open()

context.Transaction = Connection.BeginTransaction();

context.SubmitChanges();

... Do my stuff here. So other queries, I don't no

context.Transaction.Commit();

I get an error on the Commit: Connection must be opened.

If I check the context.Connection.State, it is closed. I am pretty sure this is a bug. At least on the 5.60.124 version.

Any other ideas?!

StanislavK · Post by **StanislavK** » Mon 04 Oct 2010 16:00

As for the problem with Connection Timeout, we've reproduced the problem. We will inform you when it is fixed.

Could you please describe in some details the actions you try to perform when the problem with the closed connection occurs? For example, are you inserting multiple rows in a single transaction? Can there be periods when the connection is opened but no interoperations with the server are performed? Also, is the physical connection to the server stable? We couldn't reproduce the issue in our environment.

bmarotta · Post by **bmarotta** » Wed 20 Oct 2010 07:19

Hi Stanislaw,

sorry but we didn't have time yet to try to reproduce the LoadWith on your test application. But anyway, was the connection timeout problem solved on the last Release? We really need this thing fixed...

StanislavK · Post by **StanislavK** » Thu 21 Oct 2010 15:11

Please tell us if you are using the Direct or OCI connection mode. The Connection Timeout parameter is supported in the Direct connection mode only; in the OCI mode, the time to open the connection is determined by the Oracle client and cannot be changed.

bmarotta · Post by **bmarotta** » Fri 22 Oct 2010 08:42

Hi Stanislaw,

our connection string looks like this:

User Id=xxx;Server=xxx;Persist Security Info=False;Unicode=True

So I guess we are using OCI mode. Is that right?

Do you think we can also solve the problem that it always hangs on the below calls by configuring something on the OCI:

Code: Select all

OciDynamicType.nativeOCIStmtFetch(HandleRef , HandleRef , Int32 , UInt16 , UInt32 )

OciDynamicType.nativeOCIStmtExecute(HandleRef , HandleRef , HandleRef , Int32 , Int32 , Int32 , Int32 , Int32 )

OciDynamicType.nativeOCILobGetLength(HandleRef , HandleRef , IntPtr , Int32& )

OciDynamicType.nativeOCITransCommit(HandleRef , HandleRef , UInt32 )

All of the above calls may randomly hang (by hang I mean they do really never return) and this is really what is killing our application today

Regards,

StanislavK · Post by **StanislavK** » Tue 26 Oct 2010 08:44

You are right, this connection string corresponds to the OCI connection mode.

Could you please describe the situations in which the problem with these OCI calls occurs?

Also, did you try using the Direct connection mode? LinqConnect models can be created and used in both Direct and OCI modes; for the complete list of the Direct mode limitations, please refer to
http://www.devart.com/dotconnect/oracle ... tMode.html

bmarotta · Post by **bmarotta** » Wed 27 Oct 2010 09:03

It happens randomly in ANY part of the code under heavy load. By ANY, I mean that any database call can hang. By hang, I mean it stays infinitely there.

It happens mostly on our PROD and PRE-PROD servers which are 8-core machines. On the developer machines (2-core) and other servers (2-core) it is very hard to reproduce.

If you want I can send you an application dump (which is usually a huge file) so that you can analyze it.

I know that the current version we are using does not implement the CommandTimeout correctly. My question is if I will get the CommandTimeout during the fetch also.