Re: WARNING: ROLLBACK: no transaction in progress - Mailing list pgsql-jdbc

Bjorn,

I have implemented a few of them

I use a linked list, and get the new connections from the head, and
return connections to the tail.

You need to clear warnings when you retrieve the connection, place a
limit on how many connections you will allow in the pool. That's about
it.

Dave
On Mon, 2003-06-09 at 15:28, Bjørn T Johansen wrote:
> Well, that sounds about right... :)
>
> Maybe I need to do some soul searching to discover whats "right" for
> me.. ;)
>
>
> BTJ
>
> On Mon, 2003-06-09 at 20:59, Dmitry Tkach wrote:
> > Bjørn T Johansen wrote:
> >
> > >Well, I just saw it as more convenient to use an existing connection
> > >pooling, i.e Tomcat's (since it is running under Tomcat). But what you
> > >are saying, is that the "right" way is to implement a connection polling
> > >class myself?
> > >
> > >
> > I don't know :-)
> > Depends on what you mean by the "right" way :-)
> >
> > What I was saying was that if you implemented it yourself, you would not
> > have this problem.
> > What is the "right" way is up to you - it depends on whether you believe
> > that figuring out causes of (and workarounds for) problems like this is
> > worth the saved effort of writing a connection pool on your own. My
> > opinion is that it isn't, but once again, it is just my opinion, whether
> > you agree with it or not is up toi you, and that would define what is
> > the "right" choice for you :-)
> >
> > Dima
> >
> > >
> > >BTJ
> > >
> > >On Mon, 2003-06-09 at 16:33, Dmitry Tkach wrote:
> > >
> > >
> > >>Bjørn T Johansen wrote:
> > >>
> > >>
> > >>
> > >>>Hmmm, I am using the connection pooling in Tomcat to get database
> > >>>connection, don't know if that would make a difference....
> > >>>
> > >>>
> > >>>
> > >>>
> > >>I bet, it does.
> > >>I suspect, when you do c.close(), it doesn't really close the
> > >>connectino, but rather just returns it to the pool... When it does that,
> > >>it, probably, does rollback() on it to make sure that, if somebody
> > >>forgot to complete his transaction, it doesn't get into the pool that
> > >>way....
> > >>
> > >>Frankly, I could never understand why people are so inclined to use all
> > >>those third party connection pooling mechanisms - they have all kinds of
> > >>problems - they 'swallow' native sql exceptions, they do funny stuff
> > >>with transactions etc, etc... The main problem being, they are 'black
> > >>boxes' - you never know what exactly happens to the connection
> > >>before/after you get your hands on it.... :-(
> > >>This seems way too much price to pay for the benefit of not having to
> > >>implement the pool on your own (which is, more or less, just having a
> > >>Vector of  active connections somewhere) ...
> > >>
> > >>Dima
> > >>
> > >>
> > >>
> > >>
> > >>>BTJ
> > >>>
> > >>>On Thu, 2003-06-05 at 02:55, Barry Lind wrote:
> > >>>
> > >>>
> > >>>
> > >>>
> > >>>>Bjørn,
> > >>>>
> > >>>>I can't reproduce your problem.  I used the attached program that I
> > >>>>wrote from the information you provided below and it doesn't have any
> > >>>>such warning in the log files.
> > >>>>
> > >>>>thanks,
> > >>>>--Barry
> > >>>>
> > >>>>
> > >>>>Bjørn T Johansen wrote:
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>>>I am using the new version 7.3.3 where I also compiled the jdbc driver..
> > >>>>>
> > >>>>>The code I use looks something like this..:
> > >>>>>
> > >>>>>   Connection conn = DBPool.getInstance().getConnection();
> > >>>>>   PreparedStatement stmt = null;
> > >>>>>   Statement stmt2 = null;
> > >>>>>
> > >>>>>   try
> > >>>>>   {
> > >>>>>     conn.setAutoCommit(false);
> > >>>>>
> > >>>>>     String sql= "Insert into table () Values (?)";
> > >>>>>     stmt = conn.prepareStatement(sql);
> > >>>>>     int ant = stmt.executeUpdate();
> > >>>>>     stmt.close();
> > >>>>>     stmt = null;
> > >>>>>     conn.commit();
> > >>>>>     conn.close();
> > >>>>>     conn = null;
> > >>>>>   }
> > >>>>>   catch (SQLException ex)
> > >>>>>   {
> > >>>>>     conn.rollback();
> > >>>>>     conn.close();
> > >>>>>     throw new Exception;
> > >>>>>   }
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>Hope this helps...
> > >>>>>
> > >>>>>BTJ
> > >>>>>
> > >>>>>
> > >>>>>On Wed, 2003-05-28 at 11:53, Dave Cramer wrote:
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>>>What version of the driver are you using, this sounds like a bug
> > >>>>>>
> > >>>>>>Can you send some code to re-create it?
> > >>>>>>
> > >>>>>>Dave
> > >>>>>>On Tue, 2003-05-27 at 06:33, Bjorn T Johansen wrote:
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>>>Why do I get this in my logfile, when I never call rollback, only
> > >>>>>>>commit? Should I just ignore this or??
> > >>>>>>>
> > >>>>>>>
> > >>>>>>>Regards,
> > >>>>>>>
> > >>>>>>>BTJ
> > >>>>>>>
> > >>>>>>>
> > >>>>>>>-----------------------------------------------------------------------------------------------
> > >>>>>>>Bjørn T Johansen (BSc,MNIF)
> > >>>>>>>Executive Manager
> > >>>>>>>btj@havleik.no                  Havleik Consulting
> > >>>>>>>Phone: +47 67 54 15 17         Conradisvei 4
> > >>>>>>>Fax: +47 67 54 13 91           N-1338 Sandvika
> > >>>>>>>Cellular: +47 926 93 298       http://www.havleik.no
> > >>>>>>>-----------------------------------------------------------------------------------------------
> > >>>>>>>"The stickers on the side of the box said "Supported Platforms: Windows
> > >>>>>>>98, Windows NT 4.0,
> > >>>>>>>Windows 2000 or better", so clearly Linux was a supported platform."
> > >>>>>>>-----------------------------------------------------------------------------------------------
> > >>>>>>>
> > >>>>>>>
> > >>>>>>>---------------------------(end of broadcast)---------------------------
> > >>>>>>>TIP 2: you can get off all lists at once with the unregister command
> > >>>>>>>  (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >>>>>>>
> > >>>>>>>
> > >>>>>>>
> > >>>>>>>
> > >>>>>>>
> > >>>>______________________________________________________________________
> > >>>>
> > >>>>import java.sql.*;
> > >>>>
> > >>>>public final class test25 {
> > >>>>   private static String DB_URL  = "jdbc:postgresql://localhost:5432/test";
> > >>>>   private static String DB_USER = "test";
> > >>>>   private static String DB_PASS = "test";
> > >>>>
> > >>>>   public static void main(String[] args) throws Exception {
> > >>>>       // load driver
> > >>>>       Class.forName("org.postgresql.Driver");
> > >>>>       // open connection to postgres
> > >>>>        Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
> > >>>>        PreparedStatement stmt = null;
> > >>>>
> > >>>>        try
> > >>>>        {
> > >>>>            conn.setAutoCommit(false);
> > >>>>            //This test assumes a table 'test' exists with one column 'cola' of type text
> > >>>>            String sql= "Insert into test (cola) values (?)";
> > >>>>            stmt = conn.prepareStatement(sql);
> > >>>>            stmt.setString(1,"foo");
> > >>>>            int ant = stmt.executeUpdate();
> > >>>>            stmt.close();
> > >>>>            stmt = null;
> > >>>>            conn.commit();
> > >>>>            conn.close();
> > >>>>            conn = null;
> > >>>>        }
> > >>>>        catch (SQLException ex)
> > >>>>        {
> > >>>>            conn.rollback();
> > >>>>            conn.close();
> > >>>>            throw ex;
> > >>>>        }
> > >>>>    }
> > >>>>}
> > >>>>
> > >>>>
> > >>>>
> > >>>>
--
Dave Cramer <Dave@micro-automation.net>