Thread: 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
Attachment
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