Re: questions regarding transactions

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