connection.rollback() doesn't rollback transaction

[Michael]

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

[Ryan How]

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=DROP#drop_table

"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

[Michael]

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 <r...@exemail.com.au> 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...

[Ryan How]

Hi,

What I do is backup the database, run the upgrade, then on a failure the
backup can be restored. But this really means that the database needs to
be taken offline during any upgrades in-case users connect, update data,
the upgrade fails and they lose their data on the restore. Would that
work for you?. I guess the alternative is to implement running the DDL
statements in transactions, but that sounds hard to me :)

Cheers, Ryan

[Michael Spector]

Hi,

Thanks, this can work. My only concern is the upgrade time.

BTW, just found that DDL statements upgrade is in the H2 roadmap for v4 :-)

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.