Jay Schmidgall wrote:

> Is is possible to configure a ConnectionPool or DataSource so that the
> connections acquired from the DataSource always have autocommit set to
> false? As opposed to finding every place, possibly in third-party code,
> where a connection is acquired and invoking setAutoCommit(false) upon
> that connection.
>
> Thanks,
>
> : jay

Our transactionally aware DataSources will deliver a connection in that mode, if
asked for a connection in the context of a weblogic-coordinated transaction, such
as if you start a UserTransaction, or in tx-required EJBs etc.
Otherwise, the connection will be in the JDBC spec default autoCommit(true) mode.
Any code which manually sets autoCommit state will have to be altered carefully
to also ensure that it commits or rolls back any transaction that is implicitly
made when such a connection is used.
Joe

Joe Weinstein wrote:

> Our transactionally aware DataSources will deliver a connection in that
> mode, if asked for a connection in the context of a weblogic-coordinated
> transaction, such as if you start a UserTransaction, or in tx-required EJBs

Okay, now I'm wondering what a "transactionally aware" DataSource is. Is
that just referrring to a property of the DataSource that WLS uses, or
do I need to do something special to use them? Are these perhaps only XA
DataSources?

Here's the situation: we have two XA datasources set up for use by our
entity beans. We have two other non-XA datasources set up for read-only
use, basically to run stored procedure queries only via the appropriate
JDBC mechanisms: getting the connection from the datasource, etc. I want
these stored procedures to run with auto-commit off because (a) it is
supposed to help performance, and (2) these stored procs do not do any
updates. To simplify our JDBC coding, we are using a third-party library
and it is there that the connection is acquired. Now, the call to the
JDBC code actually occurs from a stateless session bean. Do I need to
set Never or one of the other values
to have the transactionally aware DataSource do what I want?

How will this affect another session bean that invokes one of these
methods?

Thanks!

: jay

Jay Schmidgall wrote:

> Joe Weinstein wrote:
>
>> Our transactionally aware DataSources will deliver a connection in
>> that mode, if asked for a connection in the context of a
>> weblogic-coordinated
>> transaction, such as if you start a UserTransaction, or in tx-required
>> EJBs
>
>
> Okay, now I'm wondering what a "transactionally aware" DataSource is. Is
> that just referrring to a property of the DataSource that WLS uses, or
> do I need to do something special to use them? Are these perhaps only XA
> DataSources?

See the docs at http://edocs.beasys.com/wls/docs81/j...g.html#1056955
for configuring transactionally aware data sources for our pools.

> Here's the situation: we have two XA datasources set up for use by our
> entity beans. We have two other non-XA datasources set up for read-only
> use, basically to run stored procedure queries only via the appropriate
> JDBC mechanisms: getting the connection from the datasource, etc. I want
> these stored procedures to run with auto-commit off because (a) it is
> supposed to help performance, and (2) these stored procs do not do any
> updates. To simplify our JDBC coding, we are using a third-party library
> and it is there that the connection is acquired. Now, the call to the
> JDBC code actually occurs from a stateless session bean. Do I need to
> set Never or one of the other values
> to have the transactionally aware DataSource do what I want?
>
> How will this affect another session bean that invokes one of these
> methods?
>
> Thanks!

If you made those non-XA datasources into XA TxDataSources, the connections
would be in autoCommit(false) mode. What DBMS is this? I don't think any DBMS
but oracle, with it's multiversion concurrency would ever claim that autoCommit(false)
reads were better for performance, and even so, I don't believe it.
Joe

>
> : jay
>

Yes, it is Oracle that we are using, but the references for
autoCommit(false) being better for performance were found on the web, so
that is certainly subject to grain-of-salt.

I'm trying to make use of Oracle's transparency gateway in our stored
procedures, and from the OTN, this requires autoCommit(false). Since
these procs are read-only, I thought that wouldn't be a problem. I'll
try using an XA datasource to see how it works with that. Do you know
of any problems with the transparency gateway and XA transactions?

Also, I can't find the reference now, but I thought I'd seen something
about a problem with XA datasources and read-only operations; are you
aware of anything like that?

Thanks for the help!

: jay

Jay Schmidgall wrote:

> Yes, it is Oracle that we are using, but the references for
> autoCommit(false) being better for performance were found on the web, so
> that is certainly subject to grain-of-salt.
>
> I'm trying to make use of Oracle's transparency gateway in our stored
> procedures, and from the OTN, this requires autoCommit(false). Since
> these procs are read-only, I thought that wouldn't be a problem. I'll
> try using an XA datasource to see how it works with that. Do you know
> of any problems with the transparency gateway and XA transactions?

No.

> Also, I can't find the reference now, but I thought I'd seen something
> about a problem with XA datasources and read-only operations; are you
> aware of anything like that?
> Thanks for the help!

No, not at all. Off the top of my head, there *can't* be a problem. I assume
you're running the standard oracle isolation level? No problem.
Joe

>
> : jay
>

Jay Schmidgall wrote:

> So I'm trying to run with an XA datasource so that I'll have autoCommit
> false, and now I am getting ORA-24777 "use of non-migratable database
> link not allowed". This seems to be only happening with the XA
> datasource. Any thoughts?

None offhand. I would look into oracle docs about what would cause that.
Joe

>
> : jay
>

So I'm trying to run with an XA datasource so that I'll have autoCommit
false, and now I am getting ORA-24777 "use of non-migratable database
link not allowed". This seems to be only happening with the XA
datasource. Any thoughts?

: jay

Joe Weinstein wrote:
> None offhand. I would look into oracle docs about what would cause that.

DBA says we need to change our database configuration to support this,
so looking into that.

Thanks for the help, Joe.

: jay

On Tue, 06 Jul 2004, no@spam wrote:
> So I'm trying to run with an XA datasource so that I'll have
> autoCommit false, and now I am getting ORA-24777 "use of
> non-migratable database link not allowed". This seems to be
> only happening with the XA datasource. Any thoughts?
>
>: jay

Here is what the Oracle documentation says about that error.

http://download-west.oracle.com/docs...80.htm#1006585

Here is where you search for errors:
http://tahiti.oracle.com/pls/db92/db92.error_search

If these links ask you to sign up. Do it. Its quick and free
and well-worth it.
--
Galen Boyer