Re: problem with transactions - Mailing list pgsql-jdbc

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
>>
>>
>>
>>!DSPAM:409fb0d2240831803312480!
>>
>>
>>
>>
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.