JDBC Statement Timeout

[Malcolm Edgar <ma...@gmail.com>]

Hi All,

I am having some deadlock issues with the Oracle database, and want to be
able to set the JDBC statement timeout.

>From what I understand this isn't supported in Cayenne 3.1, and I am after
advice on the best way to do this in a Cayenne context.

http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29

Thanks in advance for any help.

regards Malcolm Edgar

Re: JDBC Statement Timeout

[Malcolm Edgar <ma...@gmail.com>]

Hi Guys,

I ended up wrapping the JNDIDataSourceFactory, DataSource and Connection
classes and configuring this in the domain configuration file. The wrapped
Connection class sets the queryTimeout when the Statement objects are
created.

<node name="formcenterNode-JNDI"
 factory="com.avoka.core.cayenne.CoreJNDIDataSourceFactory"
 parameters="java:/FormCenterDS">
<map-ref name="formcenterMap"/>
</node>

This flushed out the errors, which we are now working to resolved.

Couple of thoughts on this: it would be nice to be able to set the
queryTimeout on the SelectQuery class, as this would give you more fine
grained control over the execution of statements, rather than having a
global setting with what we are doing above.

I think probably the better solution for this problem would be for me to
use a better Connection pool which will release blocked connections, and
allow the database to kill the sessions which are causing deadlocks. The
connection pool in JBoss 7.0.2 is pretty basic.

Are there any 3rd party connection pools you would recommend for OLTP
production usage?

thanks Malcolm Edgar




On Wed, Oct 15, 2014 at 10:40 AM, Andrus Adamchik <an...@objectstyle.org>
wrote:

> Yep, that's one way of doing it, though requiring to use a custom query
> everywhere. A DI alternative of the same solution is to do something like
> this in a custom Module:
>
>   binder.bind(OracleSniffer.class).to(MyOracleSniffer.class);
>
> MyOracleSniffer would implement DbAdapterDetector which is a decorator of
> OracleSniffer. It will return a DbAdapter decorator of the OracleAdapter,
> overriding DbAdapter.getAction(..) to set timeouts.
>
> And yes, we need a better way to do this. But at least this one is
> non-invasive in that it doesn't require you to change idiomatic Cayenne
> code elsewhere.
>
> Andrus
>
>
>
> On Oct 13, 2014, at 3:00 PM, Emanuele Maiarelli <
> maiarelli.emanuele@gmail.com> wrote:
> > http://cayenne.195.n3.nabble.com/query-timeout-needed-td1070.html
> >
> > The link explains the solution for cayenne 1.2.
> >
> > You can try with cayenne 3.1 being aware that packages named
> > org.objectstyle.* have been renamed as org.apache.*
> >
> >
> >
> > 2014-10-13 4:50 GMT+02:00 Malcolm Edgar <ma...@gmail.com>:
> >
> >> Hi All,
> >>
> >> I am having some deadlock issues with the Oracle database, and want to
> be
> >> able to set the JDBC statement timeout.
> >>
> >> From what I understand this isn't supported in Cayenne 3.1, and I am
> after
> >> advice on the best way to do this in a Cayenne context.
> >>
> >>
> >>
> http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29
> >>
> >> Thanks in advance for any help.
> >>
> >> regards Malcolm Edgar
> >>
>
>

Re: JDBC Statement Timeout

[Andrus Adamchik <an...@objectstyle.org>]

Yep, that's one way of doing it, though requiring to use a custom query everywhere. A DI alternative of the same solution is to do something like this in a custom Module:

  binder.bind(OracleSniffer.class).to(MyOracleSniffer.class);

MyOracleSniffer would implement DbAdapterDetector which is a decorator of OracleSniffer. It will return a DbAdapter decorator of the OracleAdapter, overriding DbAdapter.getAction(..) to set timeouts. 

And yes, we need a better way to do this. But at least this one is non-invasive in that it doesn't require you to change idiomatic Cayenne code elsewhere.

Andrus



On Oct 13, 2014, at 3:00 PM, Emanuele Maiarelli <ma...@gmail.com> wrote:
> http://cayenne.195.n3.nabble.com/query-timeout-needed-td1070.html
> 
> The link explains the solution for cayenne 1.2.
> 
> You can try with cayenne 3.1 being aware that packages named
> org.objectstyle.* have been renamed as org.apache.*
> 
> 
> 
> 2014-10-13 4:50 GMT+02:00 Malcolm Edgar <ma...@gmail.com>:
> 
>> Hi All,
>> 
>> I am having some deadlock issues with the Oracle database, and want to be
>> able to set the JDBC statement timeout.
>> 
>> From what I understand this isn't supported in Cayenne 3.1, and I am after
>> advice on the best way to do this in a Cayenne context.
>> 
>> 
>> http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29
>> 
>> Thanks in advance for any help.
>> 
>> regards Malcolm Edgar
>> 

Re: JDBC Statement Timeout

[Emanuele Maiarelli <ma...@gmail.com>]

http://cayenne.195.n3.nabble.com/query-timeout-needed-td1070.html

The link explains the solution for cayenne 1.2.

You can try with cayenne 3.1 being aware that packages named
org.objectstyle.* have been renamed as org.apache.*



2014-10-13 4:50 GMT+02:00 Malcolm Edgar <ma...@gmail.com>:

> Hi All,
>
> I am having some deadlock issues with the Oracle database, and want to be
> able to set the JDBC statement timeout.
>
> From what I understand this isn't supported in Cayenne 3.1, and I am after
> advice on the best way to do this in a Cayenne context.
>
>
> http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29
>
> Thanks in advance for any help.
>
> regards Malcolm Edgar
>