db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "vivek sar" <vivex...@gmail.com>
Subject Re: Torque Connection pool .. ORA- 01453 - Set Transaction must be the first statement
Date Sat, 25 Mar 2006 09:43:47 GMT
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 it shouldn't be
problem with that either.

I am still waiting for the right answer where exactly the problem lies -
1) How do I get ORA-01453 if I am not starting the transaction myself
2) Why the connection hangs after the ORA-01453

Thanks,
-vivek


On 3/24/06, Thomas Fischer <tfischer@apache.org> wrote:
> Thanks for finding an explicit condition where this error occurs.
>
> Let me first explain what I meant by "Torque handles the Transaction".
> This was a litle unclear, as I meant "Torque handles all of the
> connection stuff", as e.g. in the someObject.save() method where the
> caller does not care about connection and stuff and all.
>
> Once the user asks Torque for an explicit connection, the _user_ hast to
> take care of the connection. The only thing Torque does is to ask the
> pool to return a connection, see the code of
> TorqueInstance.getConnection().
>
> So returning to your problem, I would claim that Torque has nothing to do
> with it. By asking Torque for an explicit connection, you take the
> responsibility of doing what needs to be done with the connection. This
> needs to be this way because Torque has no way of knowing what happens to
> the connection. E.g. Torque does not know if an error occured or not. So
> if the connection is such that a commit() or rollback() is needed after an
> error, it is your responsibility to do so, Torque can not know about it.
>
> That said, I have made the experience that dbcp does not finish
> Transactions on its own accord after the connection is returned to the
> pool. In my opinion, a rollback() should be issued utomatically to every
> connection returned to the pool, because only this prevents that the user
> retieves a seemingly "fresh" connection from the pool and there are still
> the remainders of the last unfinished transaction attached to it. See
> http://issues.apache.org/scarab/issues/id/TRQS303 and
> http://issues.apache.org/bugzilla/show_bug.cgi?id=35591
>
> Also, if you do not care about transactions, make sure your connection is
> in autocommit mode (this might cause some trouble with clobs and blobs,
> however). I believe dbcp and/or the oracle driver return connections with
> autocommit=true per default, so I guess you have turned it off explicitly.
>
> On my side, I'll go back and add all this to the Torque docs.
>
> Hope this has given some insight,
>
>        Thomas
>
> On Fri, 24 Mar 2006, vivek sar wrote:
>
> > I went through the torque mailing-archive for last one year (whole
> > 2005) and there were around 20 people who reported similar (if not
> > same) problem regarding torque (or dbcp) not releasing the connection
> > back to the pool. In none of the case there was a clear answer how to
> > resolve this problem or whether it's even a bug in torque that needs
> > to be fixed.
> >
> > I think there are two related bugs in Torque that needs to be looked at,
> >
> > Scenario: We don't explicitly start the Transaction and let Torque
> > handle the transaction management, i.e.,
> >
> >           Connection conn = Torque.getConnection("xxx");
> >           Statement st = conn.createStatement();
> >            boolean b = st.execute(sql);
> >
> >  and then close everything in finally block,
> >               Torque.closeConnection(conn);
> >
> > 1) If an "insert" statement fails and there is a subsequent "select"
> > query Torque return error message for the "select" query,
> >
> >        ORA-01453: Set Transaction must be the first statement
> >
> > 2) Once you get "ORA-01453" that connection is frozen and never
> > returned to the connection pool.
> >
> > This is a critical bug, because if you have max active connections as
> > 10, after 10 of "ORA-01453" all the connections are lost and your
> > application basically hangs, no more connections to use.
> >
> > The workaround the problem is to do the transaction management in your
> > code itself,
> >
> >        Connection  conn = Transaction.beginOptional("xxx", true);
> >        Statement st = conn.createStatement();
> >        Transaction.commit(conn);
> >
> >   and in case there is any exception run the rollback,
> >         Transaction.safeRollback(conn);
> >
> > I've tested this and if I handle the transaction myself I don't get
> > the ORA-01453 and the connections are released to pool fine.
> >
> > May be the torque developers can look into this and tell whether it's
> > a bug in torque or dbcp or operational error on the application side.
> >
> > FYI,
> >        I didn't change the transaction isolation level or any such
> > property in the torque.properties. I am using,
> >     torque-3.1.jar, commons-dbcp-1.2.1.jar, commons-collections-3.1.jar
> >
> >
> > Thanks,
> > -vivek
> >
> > On 3/23/06, Thomas Fischer <tfischer@apache.org> wrote:
> >> Hi,
> >>
> >> Torque uses transaction internally. However, from all I've seen so far,
> >> Torque does a good job in committing the Transactions it opens (I've never
> >> heard of problems there).
> >> However, there is a bug in dbcp if you use a transaction isolation other
> >> than READ_COMMITTED and verify the connection using a verify statement.
> >> I've seen it myself when setting the transaction isolation to
> >> SERIALIZABLE. There is a bug report both in Torque's Scarab issue tracker
> >> and in dbcp's bugzilla (however, at the moment at least scarab is unusable
> >> because of problems on the issues.apache.org machine, I'm afraid).
> >> The other thing which could happen is anything which could make a
> >> try...finally block fail. This would have to be something like the
> >> operating system killing a thread, it does not normally happen in java.
> >>
> >>     Hope this helps,
> >>
> >>             Thomas
> >>
> >> ---------------------------------------------------------------------
> >> 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
> >
> >
>
> ---------------------------------------------------------------------
> 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