Обсуждение: 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.
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
Вложения
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
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
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 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: >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.
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
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! >> >> >> >>