Is it possible to configure ConnectionPool/DataSource w/autocommit=false? - Weblogic

This is a discussion on Is it possible to configure ConnectionPool/DataSource w/autocommit=false? - Weblogic ; 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 ...

+ Reply to Thread
Results 1 to 10 of 10

Thread: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?

  1. Is it possible to configure ConnectionPool/DataSource w/autocommit=false?

    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


  2. Re: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?



    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


  3. Re: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?

    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


  4. Re: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?



    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
    >



  5. Re: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?

    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


  6. Re: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?



    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
    >



  7. Re: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?



    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
    >



  8. Re: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?

    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


  9. Re: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?

    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


  10. Re: Is it possible to configure ConnectionPool/DataSource w/autocommit=false?

    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

+ Reply to Thread