db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <tfisc...@apache.org>
Subject Re: Torque Connection pool .. ORA- 01453 - Set Transaction must be the first statement
Date Sat, 25 Mar 2006 06:44:57 GMT
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


Mime
View raw message