Devart Forums

Posted: **Mon 03 Sep 2018 11:37**

Hi team,

I have a long running process that continuously waits on an Oracle queue, processes messages and waits back on the queue. This is done with a timeout (so the process can verify internal flags periodically) and within a transaction (so the message isn't lost should a processing error occur). The code is similar to the following:

Code: Select all

        (...)
        while(true)
        {
            try
            {
                using (var tx = connection.BeginTransaction())
                {
                    var m = queue.Dequeue(new OracleQueueDequeueOptions
                    {
                        WaitTimeout = 10, // seconds
                        Navigation = OracleQueueNavigation.FirstMessage,
                        DequeueMode = OracleQueueDequeueMode.Remove
                    });

                    process(m);
                    tx.Commit();
                }
            }
            catch (OracleException oe) when (oe.Code == 25228) // timeout or end of fetch during message dequeue
            {
                // OK. Wait on the queue again.
            }
        }

Most of the times this works just fine, but I've been getting sporadic "Connection must be opened" errors as soon as a message is added to the queue. The connection is most certainly open (I've checked) and Dequeue wouldn't react to the new message otherwise!

The exception along with stack trace is as follows:

Code: Select all

System.InvalidOperationException: Connection must be opened.
   at Devart.Data.Oracle.OracleConnection.()
   at Devart.Data.Oracle.OracleLob.()
   at Devart.Data.Oracle.OracleLob.get_IsTemporary()
   at Devart.Data.Oracle.OracleLob.g2ws489mf4g7nz77ms8quve5sfzvna8x   ? ??         ?  ()
   at Devart.Data.Oracle.NativeOracleObjectBase.ConvertFromInternalValue(Object value, OracleAttribute attribute)
   at Devart.Data.Oracle.OracleObject.5wdlpf5qy6cd2tee8kvlb5q8wzhf4xlr   (NativeOracleObject )
   at Devart.Data.Oracle.OracleObject.(   , OracleConnection , Boolean )
   at Devart.Data.Oracle.OracleParameter.(OracleDbType , Object , Type& , Object&, Byte[] , Hashtable , Int32 , Int32  , Int32  , Int32  , Boolean , Boolean  , Boolean  , Encoding  , OracleCommand  , ParameterDirection  , ?   , OracleType )
   at Devart.Data.Oracle.OracleParameter.(  & , Boolean , Boolean , OracleCommand, Byte[] , Hashtable , ?  )
   at Devart.Data.Oracle.OracleCommand.(OracleParameterCollection ,   [] ,    , ? )
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
   at Devart.Data.Oracle.OracleQueue.Dequeue(OracleQueueDequeueOptions deqOptions)

I am running dotConnect v9.6.584 on .NET Core 2.1 (2.1.401) as a 32bit process (32bit OCI).

Could you please investigate?
Thanks in advance.

Posted: **Fri 07 Sep 2018 14:15**

Please create and send us a complete small test project, which reproduces the issues after running an endless cycle for some time. And describe the steps that we should perform for reproducing the issue.

Posted: **Tue 11 Sep 2018 08:21**

I've sent you a sample including instructions on how to setup the environment and test the application.
Please let me know if it didn't reach you.

Posted: **Tue 11 Sep 2018 10:40**

We have received your sample. We will investigate the issue and post here when we get the results.

Posted: **Wed 19 Sep 2018 09:37**

Just an update on this -

After experiencing a scenario similar to the Connection.Open hanging/freezing issue, I've changed my connection string to validate pooled connections as well as disabling dotConnect's timeout management, i.e:

Code: Select all

Validate Connection=true;Connection Timeout=0

I'm unsure if this is related or not but I haven't hit the "Connection must be opened" problem since.
I'll report back if it happens again.

Posted: **Thu 20 Sep 2018 13:38**

We could not reproduce the issue. We run you project, executed dot_q_enqueue.sql and received the "Got a message OK." message. Then we left application running for several hours and execute dot_q_enqueue.sql again. And the "Got a message OK." message is shown. The Oracle connection is active all this time on the server.

rmatbg wrote:Connection Timeout=0

This parameter determines the timeout of opening a new connection, and it does not affect the described issue.

rmatbg wrote:Validate Connection=true

The "Validate Connection=true;" connection string parameter validate connections that are being taken from the pool. For more information, please refer to http://www.devart.com/dotconnect/oracle ... ction.html
If adding this parameter helped, this means that your connection is not open all the time. A connection is closed and placed to the connection pool. The connection is inactive while in the pool, and Oracle server can close connections that are inactive for a long time. When this connection is taken from the pool, it is no longer valid, and it may cause such an error. "Validate Connection=true;" will enable checking a connection whenever it is taken from the pool.

Posted: **Thu 20 Sep 2018 13:50**

I suspected this wouldn't be an easy one to reproduce and might be specific to my environment.

It seems like "Validate Connection=true" is a safer option for me as I'm more concerned about robustness than performance, and the delay introduced by validating the connections seem minimal for my use case.

Thanks for your analysis.

Devart Forums

OracleQueue - "Connection must be opened" raised on Dequeue

OracleQueue - "Connection must be opened" raised on Dequeue

Re: OracleQueue - "Connection must be opened" raised on Dequeue

Re: OracleQueue - "Connection must be opened" raised on Dequeue

Re: OracleQueue - "Connection must be opened" raised on Dequeue

Re: OracleQueue - "Connection must be opened" raised on Dequeue

Re: OracleQueue - "Connection must be opened" raised on Dequeue

Re: OracleQueue - "Connection must be opened" raised on Dequeue