[vox-tech] Unnecessary RDBMS features [was Need your help in recovering database.]

[Tim Riley]

Jan W wrote:

> --- Tim Riley <timriley@timriley.net> wrote:
> <SNIP>
> > > Are you actually saying you believe commit/rollback is a *bad* thing?
> >
> > Yea--it's not worth its weight.
> >
> > >
> > > If so, you are definitely the first person I've ever heard say that...
> > >
> > > I'm not criticizing your opinion; I'm just trying to understand.
> >
> > Commit/rollback requires that a copy be made of each datum involved
> > in an insert, update, or delete statement. This is very expensive. But
> > why make the copy? I know my insert, update, or delete is the correct
> > thing to do at the time. If I'm making a mistake to the database,
> > I'll fix it when I catch it.
> <SNIP>
>
> Commit/rollback is invaluable, depending on what you are doing.  There are some
> very good reasons to use commit/rollback functions in your RDBMS.  How exactly
> the rollback is done is implementation specific; the cost should not have much
> effect on performance.

My observations have shown a significant performance hit. Consider what happens:
for every insert, update, or delete, the database's status-quo is copied to
another file on the disk. I think this is why benchmarks have shown Mysql to be
ten times faster than Oracle.

>  The database system that we use in our shop
> (intersystems Cache) has no problem with gigabytes of database and thousands of
> updates daily, with full rollback functionality.

Oh, they function--they don't break the database--they just slow things down.

>  There's no good reason why
> the SIZE of a database has anything to do with it's ability to audit db
> transactions (commit/rollback).
>
> We have been doing testing of DB systems, when we encounter strange errors.
> So, we take a current snapshot of the db, rollback to just before the time that
> the error occured, and step one by one through transactions until the error
> occurs.

I don't think the software technology of commiting/rollbacking transactions is
providing this functionality. Once a transaction is commited, it can't be rolled
back, and how can you take a snapshot of uncommitted transactions in a database?
RDBMS' have a snapshot feature, but its purpose is to synchronize multiple
databases,
which sounds like what you're doing here.

> It _really_ gives clues as to what _EXACTLY_ was going on when an

> error occurs.  There have been times that it's worth it's weight in gold,
> because it has saved hours upon hours of: 1. reentry of information, 2. testing
> of systems, 3. recovery of damaged or corrupted files.  It's for this reason
> why we use intersystems cache, because of it's ability to do this for the past
> 3 or 4 major releases (with no hit on performance).
>
> Commit/rollback is just another feature for an admin to decide if the cost is
> worth the benefits, IMHO.  But just because some RDBMS's can't implement a good
> transactional system (oracle, MySQL) doesn't mean you should throw out a baby
> with bathwater.
>

Both do--Oracle by default, and as I've learned through this thread, that Mysql
through
InnoDB. Correct me if I'm wrong, but I think it's another software technology
that you're pleased with.

>
> My $0.02
>
> jan
>
> PS: Dump your databases often so your databases don't take a dump on you ;)
>
> Nothing is as sad as gigabytes of corrupted db files.  Sorry to hear :(
>
> __________________________________________________
> Do you Yahoo!?
> The New Yahoo! Search - Faster. Easier. Bingo
> http://search.yahoo.com
> _______________________________________________
> vox-tech mailing list
> vox-tech@lists.lugod.org
> http://lists.lugod.org/mailman/listinfo/vox-tech