According to the documentation, quite every DDL statement "commits an open transaction". If this is the case, it's quite difficult to implement a correct DB schema upgrade mechanism without transactions :-( What I wanted is that upgrade scenario runs in a transaction to avoid semi-successful schema upgrades.
On May 16, 10:57 am, Ryan How <[email protected]> wrote: > Hi, > > My understanding is that DROP statements commit open transactions. So it > would appear that even though the drop statement fails, the open > transaction is still committed. > > See > > http://www.h2database.com/html/grammar.html?highlight=drop&search=DRO... > > "This command commits an open transaction." > > My guess is this is the way that it is meant to work. Maybe try putting > an UPDATE to a table that doesn't exist or something like that? > > Hope this helps, > > Ryan > > On 16/05/2011 3:01 PM, Michael wrote: > > > > > > > > > Hi, > > > I've just paid an attention that rollback doesn't revert changes to > > database made in transaction. This is the simplified code of our > > upgrade scenario: > > > // This line is just for showing what connection parameters we're > > using: > > JdbcConnectionPool pool = JdbcConnectionPool.create("jdbc:h2:C: > > \test;CIPHER=AES;LOCK_TIMEOUT=10000;AUTO_SERVER=TRUE", user, password > > + " " + password); > > > Connection connection = pool.getConnection(); > > connection.setAutoCommit(false); > > try { > > Statement statement = connection.createStatement(); > > try { > > statement.executeUpdate("UPDATE versions SET version='1.1'; > > DROP non_existent;"); > > } finally { > > statement.close(); > > } > > connection.commit(); > > > } catch (SQLException e) { > > connection.rollback(); > > } finally { > > connection.setAutoCommit(true); > > } > > > I intentionally wrote "DROP non_existent" to make the query fail, and > > it failed indeed. But the first statement (version increment) > > succeeds, even though connection.rollback() is being called. > > > What am I doing wrong? > > > Thanks, > > Michael -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
