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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
