Thread: JDBC and transactions

JDBC and transactions

From
"Chris White (cjwhite)"
Date:
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
 

Re: JDBC and transactions

From
Kris Jurka
Date:

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

Re: JDBC and transactions

From
"Chris White (cjwhite)"
Date:
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

Re: JDBC and transactions

From
"Chris White (cjwhite)"
Date:
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

Re: JDBC and transactions

From
Kris Jurka
Date:

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