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