db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <fisc...@seitenbau.net>
Subject RE: Torque Connection pool .. ORA- 01453 - Set Transaction must be the first statement
Date Mon, 10 Apr 2006 15:15:02 GMT
Jim,

I'm not sure if you can "not use a transaction". E.g. in oracle, every sql
select/update/delete/insert command automatically starts a transaction. The
closest you can come to "not using transactions" in oracle is autocommit
mode where every sql command is wrapped in its own transaction. I'm not
sure what the situation is for DB2.

In my experience, you can use the same code without problems if you are in
autocommit mode in oracle, but this may vary from database to database.
>From my oracle-based experience, I would try to use exactly the same code,
and see if its works in all cases (with/without errors). Only if this does
not work, I'd try to use Torque.getConnection() and dbConn.close() instead
of Transaction.begin() and Transaction.commit()/Transaction.safeRollback.
But to be sure, check the DB2 docs.

    Thomas

Jim Caserta <smoothie_jc@yahoo.com> schrieb am 10.04.2006 16:54:28:

> Thomas,
>
> I see what you mean...
>
> I'm assuming I can take the same approach if I'm Not
> using a Transaction. Correct?
>
> Jim
>
> --- Thomas Fischer <fischer@seitenbau.net> wrote:
>
> > Hi,
> >
> > I am using the following code:
> >
> > Connection dbConn = null;
> > try
> > {
> >     dbConn =
> > Transaction.begin(db2.torque.environment));
> >
> >     ////Do Stuff
> >
> >     //Commit the transaction (Transaction.commit
> > releases connection back
> > to the pool)
> >     Transaction.commit(dbConn);
> >     dbConn = null;
> > }
> > finally
> > {
> >     if (connection != null)
> >     {
> >         // some error occurred, try to rollback and
> > return connection to
> > pool
> >         Transaction.safeRollback(dbConn);
> >         dbConn = null;
> >     }
> > }
> >
> > It is safer to use a finally block than a catch
> > block. In some ugly cases,
> > you get errors and not exceptions, and they are not
> > caught by
> > catch(exception). Also, the finally block works even
> > if you return inside
> > the block.
> >
> > I have also written some docs about this, but I have
> > forgotten to commit it
> > :-(.
> >
> >     Thomas
> >
> >
> > Jim Caserta <smoothie_jc@yahoo.com> schrieb am
> > 10.04.2006 15:27:37:
> >
> > > Thomas,
> > >
> > > I was reading through this thread and I wantwed to
> > be
> > > sure what you are saying. Is the example below the
> > way
> > > we should be handling transactions? Thanks!
> > >
> > >
> > > Connection dbConn = null;
> > > try {
> > >       dbConn =
> > Transaction.begin(db2.torque.environment));
> > >
> > >       ////Do Stuff
> > >       //Commit the transaction
> > (Transaction.commit, should
> > > release connection back to the pool
> > >       Transaction.commit(dbConn);
> > >       Transaction.safeRollback(dbConn);
> > >    }catch(TorqueException e){
> > >       try {
> > >          Transaction.rollback(dbConn);
> > >       } catch (TorqueException e1) {
> > >          Transaction.safeRollback(dbConn);
> > >       }
> > >       }finally {
> > >          if(!dbConn.isClosed()){
> > >             Torque.closeConnection(dbConn);
> > >          }
> > >       }
> > >
> > >
> > > --- Thomas Fischer <tfischer@apache.org> wrote:
> > >
> > > > Using commits/rollbacks without explicitly
> > startung
> > > > a connection may look
> > > > unclean but does not cause any problems in
> > practice
> > > > (at least none known
> > > > to me). The problem described seems to be the
> > other
> > > > way round: there is no
> > > > rollback/commit where should be one.
> > > >
> > > >    Thomas
> > > >
> > > > On Mon, 27 Mar 2006, Greg Monroe wrote:
> > > >
> > > > > I did a quick wander thru the Torque code and
> > saw
> > > > one thing
> > > > > that did not look right to me.  Here's some
> > > > background first:
> > > > >
> > > > > All of the Torque Transaction handling is
> > built on
> > > > the
> > > > > Transaction class. This is used primarily by
> > the
> > > > BasePeer
> > > > > methods like doUpdate(Criteria) and the like.
> > > > >
> > > > > These methods are the ones that automatically
> > wrap
> > > > the
> > > > > DB actions as a transaction with rollback.
> > > > >
> > > > > The first thing that didn't look right to me
> > was
> > > > that the
> > > > > Transaction.beginOptional(dbName,
> > useTransaction)
> > > > method
> > > > > is called with the useTransaction arg set to
> > the
> > > > value of
> > > > > criteria.isUseTransation().  This value is set
> > to
> > > > false by
> > > > > default.
> > > > >
> > > > > So, it seems that if you don't set this
> > explicitly
> > > > on your
> > > > > Criteria, you are not using really using
> > > > transactions but
> > > > > you still have the Transaction try/catch code
> > with
> > > > commits and
> > > > > rollbacks.
> > > > >
> > > > > Shouldn't the default for isUseTransactions()
> > be
> > > > true and/or
> > > > > the code handle the false condition without
> > > > calling the
> > > > > extra transaction methods?
> > > > >
> > > > >
> > > > >> -----Original Message-----
> > > > >> From: vivek sar [mailto:vivextra@gmail.com]
> > > > >> Sent: Saturday, March 25, 2006 4:44 AM
> > > > >>
> > > > >> Thanks Thomas for detailed explanation. I
> > haven't
> > > > dig into
> > > > >> the Torque or dbcp code to tell exactly where
> > the
> > > > fault lies.
> > > > >> The way I understand is that the db starts
> > the
> > > > transaction on
> > > > >> your behalf if you don't start one. In case
> > that
> > > > transaction
> > > > >> fails it will try to rollback. The problem
> > I've
> > > > stated is
> > > > >> while the transaction is rolling back the
> > same
> > > > connection is
> > > > >> somehow being used by other query and that's
> > > > causing the
> > > > >> "ORA-01453" and hanging of the connection.
> > > > >>
> > > > >>  I would think it's a problem with dbcp if
> > not
> > > > torque as dbcp
> > > > >> is the one that handles the connection pool.
> > I
> > > > couldn't find
> > > > >> much on the dbcp commons mailing-archiving
> > list,
> > > > but found
> > > > >> tons of similar problems reported by torque
> > > > users, so I think
> > > > >> most of the people do assume it's a Torque
> > > > problem or
> > > > >> somewhere related to it.
> > > > >>
> > > > >>  Yes, if I do handle the transaction myself I
> > > > don't get into
> > > > >> this issue, but still the connection pool
> > should
> > > > handle the
> > > > >> transactions/connections gracefully if it's
> > > > starting one on
> > > > >> your behalf.
> > > > >>
> > > > >>  I've the autocommit turned on (by default),
> > so
> >
> === message truncated ===
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message