Re: problem with transactions - Mailing list pgsql-jdbc

Ivan,

First I would try to find out if the connection is being wrapped by
another class. Probably is, then hunt around in that class to see why
the rollback is not being issued right away.

Why are you doing select count(*) exactly. They are very expensive
operations.

Dave
On Mon, 2004-05-10 at 12:33, pginfo wrote:
> Dave Cramer wrote:
>
> >It is in the conf file, and then you need to restart postgres
> >
> >DAve
> >On Mon, 2004-05-10 at 11:14, pginfo wrote:
> >
> >
> >>Barry Lind wrote:
> >>
> >>
> >>
> >>>I would suggest that you turn on sql statement logging on your
> >>>database server and see exactly what the jdbc driver is sending to the
> >>>server.  I suspect this information will help you track down the problem.
> >>>
> >>>thanks,
> >>>--Barry
> >>>
> >>>
> >>>pginfo wrote:
> >>>
> >>>
> >>>
> >>>>Hi,
> >>>>
> >>>>We are using pg 7.4.2 + jdbc to connect to jboss.
> >>>>
> >>>>It looks that pg have problems with transactions and jdbc.
> >>>>
> >>>>My example:
> >>>>
> >>>>Connection con = ... I get connection to my db.
> >>>>
> >>>>con.setAutoCommit(false);    // begin transaction
> >>>>
> >>>>// do some inserts
> >>>>
> >>>>con.rollback(); // rollback
> >>>>
> >>>>After this code all is working very well.
> >>>>All inserts are rollbacked and so on.
> >>>>
> >>>>And now the second example:
> >>>>
> >>>>Connection con = ... I get connection to my db.
> >>>>
> >>>>con.setAutoCommit(false);    // begin transaction
> >>>>
> >>>>// do some inserts
> >>>>
> >>>>con.rollback(); // rollback
> >>>>con.setAutoCommit(true);    // set autocommit. We are using
> >>>>connection pool and will to have clear connection.
> >>>>
> >>>>For my supprice the last example do not rollback any inserts !
> >>>>I do not see nothink bad in this code, and also it is working perfect
> >>>>on oracle + jdbc and also MSSQL + jdbc.
> >>>>
> >>>>Pls., can some one check the situation and post the result.
> >>>>
> >>>>I tryed to insert sole sllep for 5 sec. before the last
> >>>>con.setAutoCommit(true); , but it do not help.
> >>>>
> >>>>Is it bug into the jdbc or pg ?
> >>>>
> >>>>I tested it also with pg 7.4.1 and 7.4. and I have the same problem.
> >>>>
> >>>>regards,
> >>>>ivan.
> >>>>
> >>>>
> >>>>---------------------------(end of broadcast)---------------------------
> >>>>TIP 8: explain analyze is your friend
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>How to do it?
> >>Will I need to recompile pg or I can set it up into conf-file?
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 4: Don't 'kill -9' the postmaster
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> Hi,
>
> I am ready with first results from my test.
> I send a part of my pg log with comments:
>
> I make getConnection();
> LOG:  statement: rollback; begin;
>
> I only check the count to be sure that the command is executed in place.
> LOG:  statement: select count(*) from  tt
>
> conn.setAutoCommit(false);
> LOG:  statement: begin;
>
> Test the value from my table. It is as expected.
> LOG:  statement: select count(*) from  tt
>
> Insert new row.
> LOG:  statement: insert into tt values (1)
>
> Check the new  count. It is ok.
> LOG:  statement: select count(*) from  tt
>
> !!! I make conn.rollbac(), but do not find any command in my pg LOG ! It
> is big mistake. I think by jboss, but I am not sure for now.
>
> I check the value after rollback and it is wrong.
> LOG:  statement: select count(*) from  tt
>
> I close the connection. Jboss sends rollback now! I do not know why for now.
> LOG:  statement: rollback; begin;
>
>
> Any idea how to search the problem?
> Of course I can  close connection instead rollback, but it is not the
> best solution.
>
> regards,
> ivan.
>
>
>
> !DSPAM:409fc09161237423516082!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561