Re: problem with transactions

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.

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.

All I can guess from the above is that there is a bug in your connection
pool implementation, in jboss itself, or in the way you're using
transactions within jboss. Does jboss want you to hook into its
transaction manager rather than manage your own transactions? Needing to
reset autocommit when handing the connection back to the pool seems
suspicious, shouldn't the pool deal with that itself?

One possibility is that if something (jboss? connection pool?) is
intercepting the rollback() call before it reaches the JDBC driver, then
the subsequent setAutoCommit() will cause the inserts to be committed
(setAutoCommit() does an implicit commit() -- see the JDBC javadoc).

Either way it seems unlikely that rollback() is broken in the current
JDBC driver -- it's working just fine here.

Can you provide a selfcontained testcase that demonstrates the problem?
You may want to turn on statement logging on the server (log_statement =
true) to see exactly what statements the JDBC driver is sending.

Also, exactly which version of the JDBC driver are you using -- the one
supplied with 7.4.2? Does using a more recent driver from
jdbc.postgresql.org change the behaviour?

-O

On Mon, 10 May 2004, pginfo wrote:

> Hi,
>
> We are using pg 7.4.2 + jdbc to connect to jboss.
>
> 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 find that very hard to believe. The attached file shows the expected
behavior for me. If it fails for you or you can modify it to fail, I
would be very interested.

Kris Jurka

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?

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

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.

It is a setting in the postgresql.conf file. I believe Oliver in his
response to your original question mentioned the specific parameter.

--Barry

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

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!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561

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

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