Thread: JDBC and transactions
I am using JDBC to connect to my database. I am using connections with autocommit turned off, so I can use transactions.
Questions:
1) When does a transaction start? As soon as I do the first insert/update on the connection or as soon as I set autocommit off?
2) After doing a commit, when does the next transaction start?
The reason I ask these questions is because I have an issue doing DB Vacuum, where free rows are not being reused because there is still an active transaction that was started before the rows were deleted, and I am trying to determine what could be causing the problem.
TIA
Chris White
On Fri, 1 Apr 2005, Chris White (cjwhite) wrote: > 1) When does a transaction start? As soon as I do the first insert/update on > the connection or as soon as I set autocommit off? > 2) After doing a commit, when does the next transaction start? This depends on the driver version you are using. The 7.4 driver starts a transaction immediately upon setAutoCommit(false) and starts the next transaction immediately upon commit. The 8.0 driver starts the transaction on the first execution for both the initial and subsequent transactions. Kris Jurka
Thanks that helps me solve my problem. -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: Friday, April 01, 2005 11:33 PM To: Chris White (cjwhite) Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] JDBC and transactions On Fri, 1 Apr 2005, Chris White (cjwhite) wrote: > 1) When does a transaction start? As soon as I do the first > insert/update on the connection or as soon as I set autocommit off? > 2) After doing a commit, when does the next transaction start? This depends on the driver version you are using. The 7.4 driver starts a transaction immediately upon setAutoCommit(false) and starts the next transaction immediately upon commit. The 8.0 driver starts the transaction on the first execution for both the initial and subsequent transactions. Kris Jurka
So I should setAutoCommit(true) after doing the commit to end the transaction. But won't this waste a transaction, if the next transaction starts immediately upon the commit? Chris -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: Friday, April 01, 2005 11:33 PM To: Chris White (cjwhite) Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] JDBC and transactions On Fri, 1 Apr 2005, Chris White (cjwhite) wrote: > 1) When does a transaction start? As soon as I do the first > insert/update on the connection or as soon as I set autocommit off? > 2) After doing a commit, when does the next transaction start? This depends on the driver version you are using. The 7.4 driver starts a transaction immediately upon setAutoCommit(false) and starts the next transaction immediately upon commit. The 8.0 driver starts the transaction on the first execution for both the initial and subsequent transactions. Kris Jurka
On Sun, 3 Apr 2005, Chris White (cjwhite) wrote: > So I should setAutoCommit(true) after doing the commit to end the > transaction. But won't this waste a transaction, if the next transaction > starts immediately upon the commit? > Well, again that depends on what driver version you are using. 8.0 will not have started a new transaction yet so one will not be used. For 7.4 a transaction indeed will be wasted. You could try and be clever and use setAutoCommit(true) instead of commit() because it will commit your transaction and not start another, but this would be confusing from a readability point at least. Kris Jurka