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 Fri, 24 Mar 2006 22:38:35 GMT
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


Mime
View raw message