I'm trying to run some SQL update scripts as part of an installaion/update process on an SQL Anywhere database. The scripts naturally include DDL i.e. create and alter tables, indexes etc. I was hoping to wrap it all in a neat BEGIN TRANSACTION and COMMIT/ROLLBACK depending on the outcome. However, it seems that the DDL statements cause an implicit commit, so the rollback is useless.
Has anyone encountered this restriction and what (if any) are the alternatives, other than switching to a different RDBMS that does support rollback for DDL?
The following question seems rather similar:
From what I know, there are a few workarounds:
As to the "different DRDBMS that does support rollback for DDL":
What exactly would this mean in a multi-user database: When altering a table with no automatic commit, would that not require all other transactions that try to access that table to wait until the transaction is committed? I'd think this would potentially block other users for longer times...