Обсуждение: questions regarding transactions

Hi,

 I'm using JDBC with transactions and I've got a couple of questions:

1) Am I supposed to do "myConn.rollback()" on error? What happens if I
don't do it - will the transaction stay half-committed?! (I ask this,
because rollback() can throw a SQLException, so it's not guaranteed to
work, in my opinion).

2) When I use statements like (SELECT currval('somesequence')) in a
jdbc-transaction, will it be atomic to? Will the sequence be decremented
again when the transaction fails? Can I get wrong numbers, when there is
another transaction which increments somesequence?

3) Is it sufficient to do setAutocommit(false) and set the
transaction-level or do I have to call a statement like "START
TRANSACTION" to properly start an transaction? (I found
the latter example somewhere on the net.)

Many thanks in advance and thank you for the wonderful driver
fabian zeindl

Fabian Zeindl wrote:
> 1) Am I supposed to do "myConn.rollback()" on error? What happens if I
> don't do it - will the transaction stay half-committed?! (I ask this,
> because rollback() can throw a SQLException, so it's not guaranteed to
> work, in my opinion).

Yes. The transaction will stay open until you end it with commit,
rollback, or disconnect the connection.

> 2) When I use statements like (SELECT currval('somesequence')) in a
> jdbc-transaction, will it be atomic to? Will the sequence be decremented
> again when the transaction fails? Can I get wrong numbers, when there is
> another transaction which increments somesequence?

Sequences are not transactional in that sense. If a transaction is
rolled back, the sequence won't be decremented. If you really need to
guarantee that there's no gaps in a series of numbers, you'll need to
use other means.

I'm not sure what you mean by wrong numbers, but two transactions will
never get the same number if that's what you mean. Sequences are safe to
use concurrently from multiple transactions, that's what they're for.

> 3) Is it sufficient to do setAutocommit(false) and set the
> transaction-level or do I have to call a statement like "START
> TRANSACTION" to properly start an transaction? (I found
> the latter example somewhere on the net.)

setAutocommit is the right way to do it. You shouldn't issue BEGIN or
any other transaction-related statements yourself.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

On 11-Jul-07, at 4:32 PM, Fabian Zeindl wrote:

> Hi,
>
>  I'm using JDBC with transactions and I've got a couple of questions:
>
> 1) Am I supposed to do "myConn.rollback()" on error? What happens if I
> don't do it - will the transaction stay half-committed?! (I ask this,
> because rollback() can throw a SQLException, so it's not guaranteed to
> work, in my opinion).
>
> 2) When I use statements like (SELECT currval('somesequence')) in a
> jdbc-transaction, will it be atomic to? Will the sequence be
> decremented
> again when the transaction fails? Can I get wrong numbers, when
> there is
> another transaction which increments somesequence?
>
This is a very common question.
1) currval has no value without a preceding nextval within the same
connection.
  In other words you open a connection, call nextval on the sequence
and it is stored in the connections memory.
Another connection does the same thing and gets a totally different
number.
As Heikki pointed out sequences do not roll back so there is no
chance to get the wrong number as long as you use nextval, and currval
> 3) Is it sufficient to do setAutocommit(false) and set the
> transaction-level or do I have to call a statement like "START
> TRANSACTION" to properly start an transaction? (I found
> the latter example somewhere on the net.)
>
> Many thanks in advance and thank you for the wonderful driver
> fabian zeindl

ok, thank you all ;)

fabian

Fabian Zeindl schrieb:
> Hi,
>
>  I'm using JDBC with transactions and I've got a couple of questions:
>
> 1) Am I supposed to do "myConn.rollback()" on error? What happens if I
> don't do it - will the transaction stay half-committed?! (I ask this,
> because rollback() can throw a SQLException, so it's not guaranteed to
> work, in my opinion).
>
> 2) When I use statements like (SELECT currval('somesequence')) in a
> jdbc-transaction, will it be atomic to? Will the sequence be decremented
> again when the transaction fails? Can I get wrong numbers, when there is
> another transaction which increments somesequence?
>
> 3) Is it sufficient to do setAutocommit(false) and set the
> transaction-level or do I have to call a statement like "START
> TRANSACTION" to properly start an transaction? (I found
> the latter example somewhere on the net.)
>
> Many thanks in advance and thank you for the wonderful driver
> fabian zeindl