Re: problem with transactions - Mailing list pgsql-jdbc

Dave Cramer wrote:
Hi Dave.

>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.
>
>
>
I will check the jboss code tomorow (it is too late by me).

>Why are you doing select count(*) exactly. They are very expensive
>operations.
>
>
I know, but I have only one record in this table and I use it only for
this test.

>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!
>>
>>
>>
>>