Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 4 of 4 total |
Updating database using SQL |
Thu, Aug 24 2006 9:19 AM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 - 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Michael,
<< I was thinking about that and what you could do - aside from you probably have better use for your time - 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 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |