Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread Updating database using SQL
Thu, Aug 24 2006 9:19 AMPermanent Link

Mike Mayer
Hi,

We use many SQL clauses with ALTER and Create Index commands to modify our database, I heart somewhere
that Transaction is not possible in this situation. So we need to have another way to ensure that all SQL commands will be executed
and return database structure to initial state if convertsion is aborted.  
Maybe anyone could share how this problem is solved by other users. I know that it has been actual to many of you here.


Regards,
Mike
Thu, Aug 24 2006 2:28 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Mike,

<< We use many SQL clauses with ALTER and Create Index commands to modify
our database, I heart somewhere that Transaction is not possible in this
situation. So we need to have another way to ensure that all SQL commands
will be executedand return database structure to initial state if
convertsion is aborted.  Maybe anyone could share how this problem is solved
by other users. I know that it has been actual to many of you here. >>

Most database engines do not support metadata alterations in transactions
simply due to the issues involved with doing so.  Even multi-versioning
alone on the catalog data doesn't completely solve the issue because one
basically ends up with every row being updated, thus causing either other
transactions to abort or the metadata alteration to abort.  What you need is
multi-versioning in the catalog (and the actual database) combined with a
row organization that can accomodate new columns being inserted into a table
without touching the row data at all, neither of which DBISAM supports.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Aug 24 2006 5:58 PMPermanent Link

Michael Baytalsky
Hi Tim,

I was thinking about that and what you could do - aside from
you probably have better use for your time Wink- is you could
provide us with method ExecDDLScript. This method would lock
database, do backup (if necessary, cause usually you do them
anyway when restructuring a table), execute all scripts inside
and if they all are executed fine, then finish, otherwise
automatically rollback to the initial version of files and
raise exception. This should be perfectly doable and it makes
a lot of sense (for me at least). This would be something
like simple database wide DDL transaction. Other then that,
DDL should not be transactional. IB has it and it's the easiest
way to actually corrupt the database. This DDL transaction can
even be done in a way that would only lock database for writing,
but you can still read it, while it performs all restructure.
That DDL script can also allow DML scripts. All affected
tables should be backed up and restored in case of failure.

Regards,
Michael

Tim Young [Elevate Software] wrote:
>  Mike,
>
> << We use many SQL clauses with ALTER and Create Index commands to modify
> our database, I heart somewhere that Transaction is not possible in this
> situation. So we need to have another way to ensure that all SQL commands
> will be executedand return database structure to initial state if
> convertsion is aborted.  Maybe anyone could share how this problem is solved
> by other users. I know that it has been actual to many of you here. >>
>
> Most database engines do not support metadata alterations in transactions
> simply due to the issues involved with doing so.  Even multi-versioning
> alone on the catalog data doesn't completely solve the issue because one
> basically ends up with every row being updated, thus causing either other
> transactions to abort or the metadata alteration to abort.  What you need is
> multi-versioning in the catalog (and the actual database) combined with a
> row organization that can accomodate new columns being inserted into a table
> without touching the row data at all, neither of which DBISAM supports.
>
Fri, Aug 25 2006 2:41 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Michael,

<< I was thinking about that and what you could do - aside from you probably
have better use for your time Wink- is you could provide us with method
ExecDDLScript. This method would lock database, do backup (if necessary,
cause usually you do them anyway when restructuring a table), execute all
scripts inside and if they all are executed fine, then finish, otherwise
automatically rollback to the initial version of files and
raise exception. This should be perfectly doable and it makes a lot of sense
(for me at least). >>

Well, it would require an exclusive lock on the database, which ElevateDB
can do but DBISAM cannot since it doesn't support open locking modes on
databases, only tables.  Perhaps it could be done without an exclusive lock
if all data is copied and then only replace the originals once the whole
thing is done.  Actually, the more I think about it, it could be done if the
restructured tables are kept "aside" until transaction commit time.
However, this is definitely going to require some more thought and testing
beyond the initial release of ElevateDB.

<< This would be something like simple database wide DDL transaction. Other
then that, DDL should not be transactional. IB has it and it's the easiest
way to actually corrupt the database. >>

Not to mention that it is slow as hell on large tables, from what I've been
told.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image