First, in the transactional paradigm,
if your applications are written in a way that is dependent on the calling of
``rollback'' instead of ``commit'' in critical situations, then transactions
are more convenient. Moreover, transactions ensure that unfinished updates or
corrupting activities are not committed to the database; the server is given
the opportunity to do an automatic rollback and your database is saved.
MySQL, in almost all cases, allows you
to solve for potential problems by including simple checks before updates and
by running simple scripts that check the databases for inconsistencies and
automatically repair or warn if such occurs. Note that just by using the MySQL
log or even adding one extra log, one can normally fix tables perfectly with no
data integrity loss.
Moreover, fatal transactional updates
can be rewritten to be atomic. In fact,we will go so far as to say that all
integrity problems that transactions solve can be done with LOCK TABLES or
atomic updates, ensuring that you never will get an automatic abort from the
database, which is a common problem with transactional databases.
Not even transactions can prevent all
loss if the server goes down. In such cases even a transactional system can
lose data. The difference between different systems lies in just how small the
time-lap is where they could lose data. No system is 100% secure, only ``secure
enough.'' Even Oracle, reputed to be the safest of transactional databases, is
reported to sometimes lose data in such situations.
To be safe with MySQL, you only need
to have backups and have the update logging turned on. With this you can
recover from any situation that you could with any transactional database. It
is, of course, always good to have backups, independent of which database you
use.
The transactional paradigm has its
benefits and its drawbacks. Many users and application developers depend on the
ease with which they can code around problems where an abort appears to be, or
is necessary, and they may have to do a little more work with MySQL to either
think differently or write more. If you are new to the atomic operations
paradigm, or more familiar or more comfortable with transactions, do not jump
to the conclusion that MySQL has not addressed these issues. Reliability and
integrity are foremost in our minds. Recent estimates indicate that there are
more than 1,000,000 mysqld servers currently running, many of which are in
production environments. We hear very, very seldom from our users that they
have lost any data, and in almost all of those cases user error is involved.
This is, in our opinion, the best proof of MySQL's stability and reliability.
Lastly, in situations where integrity
is of highest importance, MySQL's current features allow for transaction-level
or better reliability and integrity. If you lock tables with LOCK TABLES, all updates
will stall until any integrity checks are made. If you only obtain a read lock
(as opposed to a write lock), then reads and inserts are still allowed to
happen. The new inserted records will not be seen by any of the clients that
have a READ lock until they release their read locks. With INSERT DELAYED you
can queue inserts into a local queue, until the locks are released, without
having the client wait for the insert to complete.
``Atomic,'' in the sense that we mean
it, is nothing magical. It only means that you can be sure that while each
specific update is running, no other user can interfere with it, and there will
never be an automatic rollback (which can happen on transaction based systems
if you are not very careful). MySQL also guarantees that there will not be any
dirty reads. You can find some example of how to write atomic updates in the
commit-rollback section.
We have thought quite a bit about
integrity and performance, and we believe that our atomic operations paradigm
allows for both high reliability and extremely high performance, on the order
of three to five times the speed of the fastest and most optimally tuned of
transactional databases. We didn't leave out transactions because they are hard
to do. The main reason we went with atomic operations as opposed to
transactions is that by doing this we could apply many speed optimizations that
would not otherwise have been possible.
Many of our users who have speed
foremost in their minds are not at all concerned about transactions. For them
transactions are not an issue. For those of our users who are concerned with or
have wondered about transactions vis-a-vis MySQL, there is a ``MySQL way'' as
we have outlined above. For those where safety is more important than speed, we
recommend them to use the BDB tables for all their critical data.
One final note: We are currently
working on a safe replication schema that we believe to be better than any
commercial replication system we know of. This system will work most reliably
under the atomic operations, non-transactional, paradigm. Stay tuned.
No comments:
Post a Comment