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(at)ejurka(dot)com]
Sent: Friday, April 01, 2005 11:33 PM
To: Chris White (cjwhite)
Cc: pgsql-jdbc(at)postgresql(dot)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(at)ejurka(dot)com]
Sent: Friday, April 01, 2005 11:33 PM
To: Chris White (cjwhite)
Cc: pgsql-jdbc(at)postgresql(dot)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