>On Mon, 9 Apr 2001, Bob Hall wrote:
>
> > > MySQL is providing an SQL frontend to a
> > >bunch of tables and indices, that is it ... it is up to the programmer to
> > >handle the "managing of data" part where it revolves around being
> > >relational ...
> >
> > I've developed database apps in which the data was inserted in
> > batches, which meant that transactions were unnecessary. On the other
> > hand, the apps needed an RDBMS to handle normalized tables.
>
>Okay, so you start the insert, and one of the records in the batch failed
>to insert ... then what? You manually rollback the other ones?
Well, with MySQL, you start the batch over again, but use IGNORE in
the INSERT statement, and MySQL passes over the records that are
already inserted.
> A
>"transaction" is effectively a batch ... if one of the batch fails, either
>the programmer has to manually remember and roll everything back, or you
>let the database itself handle it ..
No, a batch isn't very much like a transaction. In OLTP, the DBA
doesn't initiate a transaction, has no control over it, and can't
recover lost data. In batch processing, the DBA initiates and
controls the batch, and any missing data is in the file.
> > Futhermore, some datawarehousing and web projects involve relational
> > databases that are inserted and updated in batches at night, making
> > transactions unnecessary.
>
>See above ... I have an application that loads ACT! data into a database
>every night ... each contact in the system has something like 20-30 fields
>associated with them ... if, for some reason, *one* of those fields fail
>to insert properly, that contact is invalid, and the transaction that its
>wrap'd in automatically rolls back everything I've done since the start of
>the transaction, so that there is no record of that failed contact except
>in my error log file ... no "incomplete" data, no stray data ...
If your DBMS doesn't give you the capability to restart the batch at
the point where it failed, then that's necessary. I have a hard time
believing, though, that you can't do that in whatever DBMS you're
using. I'll concede that transactions are preferable in that
circumstance, but I also have experience with databases where
transactions have nothing to offer.
>batch or interactive doesn't matter ... its the data integrity that is
>maintained by using transactions that is key ...
>
> > I'm not trying to claim that MySQL can handle all types of db
> > applications. MySQL is a niche product that was never designed to
> > handle certain types of applications. My point is that whether a DBMS
> > is relational depends on the structure of the data it deals with.
> > Whether it needs to support transaction depends on the environment it
> > operates in. I think that your point is that in an OLTP environment,
> > lack of transaction support screws up the data to the point that the
> > database becomes useless. I agree, but not all RDBMSs operate in an
> > OLTP environment.
>
>No, my point is that in any environment that needs the features of being
>"relational" (data spread across multiple tables, link'd together), IMHO,
>transactions are required in order to maintain data integrity *unless* the
>programmer himself wants to take it upon himself to maintain this data
>integrity in the application layer ...
>
>... if data in table C requires that the data saved to table B was stored,
>then if table B fails, the transaction should fail and the changes to
>table A should be reversed automatically ... *shrug* By extension, if the
>data to table C fails for whatever reason, the data put to Tables A and
>B should be automatically reversed ...
... or the missing data inserted. How did DBAs handle batch
processing before there were transactions?
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
